SQL back end, Access front end: How do you secure data ??

[ipisors]

I'm looking for suggestions on how to secure SQL data when my MS Access database must access it using linked tables. Generally speaking on the Internet, assertions that migrating to SQL is more "secure" are rampant; explanations of just how that is are scarce.



Sure I can restrict access and put different roles on different tables, then put different people in those roles, but that doesn't really do me any good. Ultimately I want to achieve the following: Allow them to do all kinds of stuff (including Update, Delete, Insert) using my access application, but I would NOT want them to have those same rights if they installed SQL Server Management Studio, or for that matter, simply created an ODBC connection using another access or excel application.

In other words, ideally, I'd like to have SQL server view my Access application as a user in its own right. Not sure if that type of impersonation or identity is possible to achieve.

Or if there are other thoughts?

[ranman256]

Create a WORKGROUP. menu, file, info, manage users /permissions.
put users in, and passwords.
This will limit access.

[ipisors]

Create a WORKGROUP. menu, file, info, manage users /permissions.
put users in, and passwords.
This will limit access.

Isn't workgroup stuff part of the whole security model that ended more or less with Access 2003? At the moment I don't have access to my 2010 version, so looking at 2007.

Regardless of / beyond that, though, I'm afraid this isn't what I was looking for. I already am capable of limiting access through any number of code techniques for front end users. My question is more about the SQL tables. No matter how much the front end limits access, ultimately if you want someone to be able to DELETE from a sql table, via your Access front end, you're going to give them that capability, right? But what if, on the other hand, you DON'T want them to be able to DELETE from that sql table, via any method other than through your Access interface.

[John_G]

Maybe you could put a password on the SQL database, and have the Access front-end automatically open the database with that password, without user intervention (just make sure your users don't see the code that does it!).

John

[ipisors]

Maybe you could put a password on the SQL database, and have the Access front-end automatically open the database with that password, without user intervention (just make sure your users don't see the code that does it!).

John

Thanks John, had not heard of putting password on sql database. Will check it out!