May 12, 2008 at 3:05 pm
How can you setup security so that a user can not use an ODBC connection with an application other than the intended?
You create a Access frontend using a file ODBC. A user can then use it with other applications, like Excel given them full rights.
1. If I use Trusted, they can create their own ODBC to attach with any application.
2. If I create a SQL login, the user has to remember another user/passord and they can create their own ODBC to attach with any application.
3. If I use a named SQL user, the user name and password gets saved unencrpyed either in the linked table of in the dsn.
I can not find a solution?
Jack
May 13, 2008 at 4:07 pm
Have you looked at using an application role?
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 13, 2008 at 5:44 pm
I have read about it only.
If I understand it:
1. I would start with a granting limited rights to a trusted group, enough to be able to run a stored procedure. The stored procedure grants the user additional rights.
2. In the Access frontend, on startup I would execute the stored procedure.
3. On close of the Access frontend, a second stored procedure is fired to revoke rights.
I am real fuzzy on this...any insight you have would be helpful, before I try it.
Jack
May 14, 2008 at 7:45 am
Okay, I'd advise against trying to lock access to an application rather than a user. In general I take the security stance that if you are happy to grant a user access to data, why should it bother you the application they use? They have access to the data already.
A common reason is that support don't like users using Enterprise Manager or Query Analyzer, but in essence if you grant their access properly they'll only be able to do what you want anyway, so who cares?
Add to this that following best practise for application coding would ensure your users only ever interact with the database via input validating stored procedures and you're really struggling to find a reason to restrict based on the connecting application
Certainly you don't want to use ODBC connections, the whole point is that they are Open DataBase Connections, you want something closed that will obscure the connection string. You can also set the 'Application Name' in your connection string, and use this to filter connections. Could be more trouble than it's worth though.
May 14, 2008 at 6:33 pm
jack_stockton (5/13/2008)
I have read about it only.If I understand it:
1. I would start with a granting limited rights to a trusted group, enough to be able to run a stored procedure. The stored procedure grants the user additional rights.
2. In the Access frontend, on startup I would execute the stored procedure.
3. On close of the Access frontend, a second stored procedure is fired to revoke rights.
I am real fuzzy on this...any insight you have would be helpful, before I try it.
Jack
Sorry, I haven't had to use application roles either. Like you, I have just read about them and how they are supposed to work.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply