access 2007 managing user roles

Hi, i have an access database located on a network that is accessed by several people.  Is there a way to create and assign user roles, password (user selected passwords) and manage abilities within the system based on these user roles?  If not natively available in access, is there a "plug-in" or add-on that I can use.  Perhaps using Acccess as a front end to somethign else?  Please give any ideas you may have.  THank you!
LadyHagoodAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
2007 doesn't have any of those mechanisms, so you'd have to build it yourself, or use something like this:

http://www.peterssoftware.com/las.htm

Light Application Security can be added to your database, and you can then define users, groups, roles, etc. It's not air-tight security by any means, and is more of a navigational aid than anything else.

If you'd like to build it yourself, let us know and we can help. In general, you'd add tables for Users, Groups, UserGroups, Objects, and GroupObjects.

Users
=============
UserID
UserName
Password
etc

Groups
==============
GroupID
GroupName
etc

UserGroups
================
UserGroupID
UserID
GroupID

Objects
===========
ObjectID
ObjectName
etc etc

GroupObjects
===============
GroupObjectID
GroupID
ObjectID
AllowOpen
AllowEdit
AllowAdd
AllowDelete
etc etc

So you would store the Users, Groups and Objects, then add Users to Groups (UserGroups table), and then assign Permissions to the UserGroup (GroupObjects table). When the user wants to open an Object, you would query the GroupObjects table to determine if they have that permission, for example.
0
 
LadyHagoodAuthor Commented:
Thanks using the diy method once the db is split how easy is it for an experienced user to break or bypass
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
It's quite simple - the user can easily open the backend tables and see the Users, Groups, etc etc, or just get directly to the tables.

You can avoid some of this by using a "launcher" app. This is where you build the database and implement the Database Password (but don't reveal the password to your users). You then build a "launcher" app that does nothing more than open your database, using the correct password (which is embedded in your compiled code). So the user opens the "launcher", enters their username/pass, and you then validate that login, and open the real database using the secret password.

If you need true data security, you'd need to move to a more robust database engine (like SQL server), and deploy a compiled application (like a .mde/.accde file, or a .NET application).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.