Is it possible to set Access persmissions using Active Directory users?
Edit: The overall objective is to allow some users to see certain tables and deny this permission for other users. I'm wondering if it can be done using active directory users.
views:
797answers:
3
+5
A:
Depends on what you mean by Access permissions. Access user level security do not interact with Active Directory in any way. ACC: Microsoft Access Security FAQ Available in Download Center It is suggested you reread this FAQ several times. I must admit I never quite understood it. Also see ACC2000: Overview of How to Secure a Microsoft Access Database
Now what you could do is read the Active Directory data for the logged in user and groups and such. Then with some local tables mapping the various AD groups along with the login userid to the various objects and menu items in Access you could control access in this fashion. Note however that local tables can possibly be mucked with by a savvy user, etc, etc.
The most useful URL I found was the following newsgroup posting need help on get list of W2K ad Domain (fqdn) by using VB Options I kept a page of notes when I was working on this topic but they may or may not be useful. I can post them if desired.
Tony Toews
2009-09-15 08:46:21
+1
A:
According to the few questions you posted these last days on Access, it seems obvious to me that you should consider switching your tables (not your forms) from an Access/mdb file to a SQLExpress server, where all these security issues can be easily managed. Upsize your database, add your connection string as a public variable in your client app (or in an xml file, local table, or anything else that can hold the string, even an extra property of your access file can do the trick through the currentDb.createProperty method), and go for a real client-server configuration.
Philippe Grondier
2009-09-15 13:51:13
Not necessarily. If, for exmaple, he wants purchasing department users to only view purchasing forms, which of course, access purchasing tables, how does he efficiently do that? If the user opens an accounts receivable form which accesses an A/R table does Access then return an error code which he handles in code?
Tony Toews
2009-09-15 18:46:15
@Tony, once you have your user groups, you can associate your tables (or, even better, views) on the server to these groups. And yes, Access can easily return an error message when the view cannot be accessed. You can develop a more sophisticated solution, with a table listing the forms, another listing the users, and a many to many link table saying who can access what. But you'll finally end up setting permissions at the server level because this will be your stronger security level
Philippe Grondier
2009-09-15 20:50:06
i don't need a high security, i'm just checking if there is a simple way to secure my db, if not - it's not even important. it's just a simple, temporary, quick, inside-company app. if it was anything else i would certainly use sqlExpress. and i'm not 'he' (that's for the tony toews)
agnieszka
2009-09-16 15:32:42
+3
A:
I agree with the things that both Tony and Philippe has posted. I just want to add a bit:
If you really need security, then a Jet/ACE back end is not going to do the job for any significant definition of the word "security". Jet ULS is crackable and fairly easily so for anyone with even basic programming chops. Thus, if it's DATA SECURITY that you're looking form, Philippe is right that you should choose a different database engine.
But if you are only looking to control ACCESS in your front-end application, you have three choices:
maintain a couple of tables in your database of your users and the permissions on each of the objects.
implement Jet user-level security.
use AD users/groups in place of Jet ULS.
None of these choices is seamless.
And all of them mean that your front-end has to be programmed to deal with the issues.
If you're restricting access for security reasons, then it makes sense to use a database engine that integrates with Windows security (i.e., SQL Server).
If you're doing it just to streamline program flow, and to adapt the app at runtime to the needs of particular users, then you don't necessarily need security on the data store so much as you need a way to keep track of who is using the database and what groups they belong to, and then what parts of the app they should have access to (and, secondarily, what level of access, read/write, read-only, etc.).
I have used Jet ULS for this last purpose for years, but have never been entirely happy with it because it's not that easy to make it user-manageable. Integration with AD would be a good choice, but that means that whoever administers your app needs to have the permission to manage AD users. This may not be something your friendly neighborhood sysadmin is willing to agree to.
On the other hand, if you end up needing both back-end security and front-end access control, you can't beat a SQL Server back end using Windows security for one-stop shopping via AD.
David-W-Fenton
2009-09-16 00:55:28