Microsoft Office 2000/Visual Basic Programmer's Guide   

The Secured Database

The final element of Microsoft Jet security is your database. This is where the objects that have to be secure are stored. The actual permissions related to specific objects for specific users and groups are stored in system tables in your database. However, the definitions of user or group accounts themselves are not stored here. This information is stored in the workgroup information file.

For example, when a user requests access to an object, Microsoft Jet already has retrieved the SIDs for the user and any groups to which he or she belongs from the workgroup information file when the user logged on. These SIDs are then compared to the SIDs in your database's system tables to verify that the user has sufficient permissions to access the object.

Permissions

Microsoft Jet defines a set of permissions that give you fine control over a user's access to an object. For example, you can allow a user to read an object's contents, but not to change them.

Microsoft Jet supports both explicit and implicit permissions. Explicit permissions are those you explicitly grant to an individual user. When an explicit permission is granted to a user, no other users are affected. Implicit permissions, on the other hand, are granted to a group. Because a group contains more than one user, all users in a group receive the permissions granted to the group. If a user is added to a group, that user automatically inherits all the implicit permissions of that group. Microsoft Jet uses the least restrictive set of permissions among the permissions granted to a user and all groups to which that user belongs. For example, if a user has explicit read permission for a table but no write permission, and that same user also belongs to a group that has write permission for the table, the user will be able to write to the table because of the higher level of the implicit write permission.

In nearly all cases, the permissions in User-Level security will be much easier to manage if you use the following strategy:

  1. Revoke all permissions on all objects for the default Users group and remove the default Admin user from the Admins group.

  2. Create the appropriate groups for the different levels of access you want to provide to users.

  3. Don't assign permissions to individual users; assign permissions only to groups.

  4. Assign each user to the appropriate group for the level of access you want the user to have.

You can use the User-Level Security Wizard to perform all of these steps and more for you. For information about how to run the wizard, see "Running the User-Level Security Wizard" later in this chapter.

Microsoft Jet allows you to set permissions on all of the objects in the database except for modules and the links to data access pages. Access provides no form of security for data access page links other than control over who can open a database, but you can password-protect all of the modules stored in a database by locking the VBA project. The Jet database engine also allows you to set permissions for new objects; that is, you can set permissions that will be inherited by any new object of a given type that is created after User-Level security is established. You can also set permissions on the database itself to control who can open the database, who can open the database for exclusive access, and who can administer the database. The following table summarizes the permissions you can set as they are identified in the User and Group Permissions dialog box.

Permission Permits a user to Applies to
Open/Run Open a database, form, or report, or run a macro Databases, forms, reports, and macros
Open Exclusive Open a database with exclusive access Databases
Read Design View objects in Design view Tables, queries, forms, reports, and macros
Modify Design View and change the design of objects, or delete them Tables, queries, forms, reports, and macros
Administer For databases, set database password, replicate a database, and change startup properties

For database objects, have full access to objects and data, including ability to assign permissions

 Databases, tables, queries, forms, reports, and macros
Read Data View data Tables and queries
Update Data View and modify but not insert or delete data Tables and queries
Insert Data View and insert but not modify or delete data Tables and queries
Delete Data View and delete but not modify or insert data Tables and queries

Note   Some permissions automatically imply the selection of others, which is reflected when you select a permission in the User and Group Permissions dialog box. For example, if you select the Update Data permission for a table, the Read Data and Read Design permissions are automatically selected because you also need these permissions in order to modify the data in a table.

For information about setting permissions by using VBA code, see "Using VBA Code to Work with User-Level Security" later in this chapter.