Access Cookbook - Ken Getz [107]
At this point you'll want to secure the database. You can either run the Security Wizard or manually secure it. If you manually secure it, you'll create a new database (this is how you transfer ownership of the database) and then import all of the objects. Next, remove all permissions for the Users group and the Admin user. The Admins group has full permissions by default—only the Admins group can work with users and groups and has irrevocable administrative permissions on the database. If you use the Security Wizard, it will also remove all permissions from the Admin user and the Users group and encrypt the new database (you can do this manually if you choose).
You need to create your own custom groups and assign the desired level of permissions to these groups. Every user is required to be a member of the Users group (otherwise, a user would not be able to start Access), so grant to Users only those permissions that you want everyone to have. Members of the Admins group have irrevocable power to administer database objects, so make sure to limit membership in the Admins group to only those users who are administrators.
Create your own users and assign them to the groups that reflect the level of permissions you want them to have. Do not assign permissions directly to users, because that is extremely difficult to administer; users inherit permissions from the groups of which they are members, and keeping track of the permissions assigned to a group is much easier than keeping track of the separate permissions of individuals. If a user is a member of multiple groups, that user will have all the permissions granted to any of those groups plus any permissions assigned specifically to the user (this is known as the "least-restrictive" rule). There is no way to deny permissions to a user if that user is a member of a group that has been granted those permissions. If you need to create specific permissions for only a single user, create a group for that user and assign the permissions to the group; then add the user to the group. The reason for this becomes clear when you consider that the user may leave unexpectedly, and you may have to set up permissions for the replacement on short notice.
Test security by logging on as users with varying levels of permissions. Try to do things that a user at that level shouldn't be able to do. The only way you'll be able to see if your database security is working is to bang on it and try to break it.
Discussion
The Microsoft Jet database engine, which Access uses to store and retrieve its objects and data, employs a workgroup-based security model. Every time the Jet database engine runs, it looks for a workgroup file, which holds information about the users and groups of users who can open databases during that session. The default workgroup file, System.mdw, is identical across all installations of Access. That's why it's important not to skip the first step of creating a new workgroup file.
The workgroup file contains the names and security IDs of all the groups and users in that workgroup, including passwords. Each workgroup file contains built-in groups (Admins and Users) and a generic user account (Admin). You can't delete any of the built-in accounts, but you can add your own group and user accounts.
The built-in accounts each have their own characteristics and properties:
The built-in Admins group is always present, and its users have administration