multiuser enviornment, passwords etc. (Access 2K, Win 2KPro, SQL Server 2K

Topic

New Reply

I’m browsing around on the access help files now, but thought I’d ask the lounge about any general considerations…

I have an Access db that serves up data from a SQL Server db and plan to place it in a secured share drive, when that’s available, but need to create a user/permissions schema at any rate. What I’d *like* to do is hook Windows login UID/profiles to Admin (me), Supervisor and User roles. I know you can setup a fairly complete sec policy using the Access tools but am trying to keep the need to do logins to a minimum so it would be best to use the existing Windows login for the various user roles. I started using the user/roles tools and it seemed that they want a suite of login/pwds and I’m trying to avoid that.

In addition, I want to be able to have Access know who’s doing what (even if it’s multiuser) so I can audit individual changes to this and that, so I’ve got a few birds to kill (and avoid any hunting accidents )

All in all, I’m only moderately interested in security, as the db can’t function if you’re not a member of the group approved to access the folder in the first place. In addition I am maintaining the SQL Server login permissions so that’s another level of protection.

I’m more interested in having the db know who’s on and log actions programmatically as needed. the only other thing to consider is some functions/forms should only be visible to the Supervisors. Any ideas/hints/suggestions?

TIA

Reply | Quote

Replies

You cannot synchronize the login id’s from Windows and from Access. They are two separate entities. If you want to implement security in Access, you will have to use its User Level Security. Lots of links at: http://home.bendbroadband.com/conradsystem…s.html#Security%5B/url%5D Once you’ve implement Access’s ULS, then you can use the CurrentUser() function to determine who is running the database.

If you choose not to implement ULS, then you can query the Windows login to determine who is logged onto the computer. Here’s a function to help: http://www.mvps.org/access/api/api0008.htm%5B/url%5D You would be able to have similar functionality to CurrentUser(), but you would not have the benefit of controlling different users’ permissions on different database objects.

Reply | Quote

thanks for the tips. CurrentUser() requires workgroup sec settings, which I am trying to avoid, so I will try to implement the access mvp function. once i can figure out how to call it from a button event…

Reply | Quote

As jacksonmacd said, you won’t have the benefit of the permission structure built in to Access if you only make use of the Windows login information, but you can still lock things down reasonably tightly if you force the users to use your forms and don’t allow them to get directly to the tables themselves; just check the windows login information before doing anything “secure”; you can even make command buttons only appear if the user is supposed to be able to use the buttons (by setting the visible property) and make fields in your forms read-only or invisible as needed. Beware of people getting around your security by simply importing into a new database though (that might not be an issue for you since you indicated that you have a SQL Server backend).

Reply | Quote