I think this is where the confusion lies. If you intended to give a user permission to access the data, then it's not a problem, is it? I think the problem you're talking about lies outside of SQL Server or Access - the problem is with the user. If you cannot trust the user to not manipulate the data against company rules, then why give them permission at all. Things like this are more of an HR problem than an IT one.
In all fairness to the original poster of this thread, I think this is a bit of an oversimplification-in-the-interest-of-dismissing the legitimate concern.
Security is a legitimate concern, and the answer to your question is: "YES", absolutely, security-against-user-malusage is completely, 100% a legitimate and normal concern.
We have clients who use a portal. do I "trust" those clients to manipulate my database and data outside of the front end app we've given them? Of course not, that wouldn't be at all normal, and this doesn't mean it's an HR problem. It's a normal reason to create apps.
Secondly, trust isn't necessarily what's at issue. It's accuracy.
I agree with OP that the whole reason you want to have a FE to begin with is somewhat diluted if you can't guarantee that your FE is the only tool doing the data manipulation.
I understand the point dbGuy was making, I just think it went a little too far.
Geico loves to provide me an app for the purpose of self-service on my automobile insurance policy, but that trust and desire does not go quite far enough to giving me ODBC access and saying "have away", does it??
@KitaYama
I may be crucified for this suggestion, but I'll make it anyway. Here is one approach: Create a SQL Auth user account for the sql server. Create a front end which IS, in its own right, very well locked down, using all the available tools you can learn, including and most especially probably the Runtime or something similar. Now create code in the front end that links ALL tables on-the-fly when the db is open, using the SQL Auth account AND password. (Yes, that may leave a pw plain text in your connection string, I didn't say this was a solution without a problem). (But, if Doug Steele has done much with his DSNless connections code over the years, there may even be a way to avoid this). Now your FE is equipped to make all data updates using the FE only! When the database quits, un-link all tables. Utilize various methods to ensure that they must use your button to close the DB (easy). People may point out various flaws in this plan, and that's fine, BUT - if your main thing that you want to eliminate is the user having the ability to connect to the tables you had to give them permissions to via any other app, then this solution may get rid of that, unless their hacking intentions are SO strong & able that they hack into your FE and see the table links. Now
that would be a scenario I'd justify & agree with using dbGuy's logic on to dismiss, as it's much more extreme.