User Level Security

The most robust form of security provided for the MDB file format is user-level security. It enables you to grant permissions to groups of users and/or to specific users for each object in a database. Objects include tables, queries, forms, reports, and macros, as well as the database itself. Because user-level security provides such granular permissions to database objects, implementing this security can be quite complex. Thorough planning and documentation will be invaluable to set up and maintain user-level security over the lifetime of a database solution. It's important to get the design right the first time — it will be costly to change the model during implementation or after development is complete.

User-level security does not override shared-level security. User-level security requires the user to log on to use a database in Access. However, if the user opens a shared-level protected database, the user also must have the password to that database. As with shared-level security, user-level security does not prevent the data from being viewed using tools other than Access. So again, consider the option of encoding the database to prevent viewing the data from other text reader tools.

There are two main database components in the user-level security model:

1. The MDW file, also known as the Workgroup Information File.

2. The Access database solution files (MDBs) that are to be secured.

With those two main components, there are two primary steps necessary to secure a database with userlevel security.

1. Create or update the MDW file to define user groups and users.

2. Set up the database to grant user groups or individual users of the MDW file specific permissions to objects in the database.

The distinction, detachment, and dependency between the MDW file and the secured database will be clarified by the following discussion.

The MDW File

The workgroup information for each of the users and groups is stored in the MDW file. Every ACE database session requires loading some workgroup information for any given database file. By default, Access has a blank SYSTEM.MDW file with two groups (Admins and Users) and one user account (Admin). (The Admin user's password is set to blank by default.) Access automatically tries to log into all databases as the Admin user whenever a new Jet/ACE session is started, unless directed to login with different credentials. If Access cannot log in as the Admin user using a blank password, the Login dialog box is displayed to request user credentials. ACE loads/denies object permissions based on the User/Group Security ID's defined for each entity in the workgroup, which stored in the MDW file. The permissions applied to each specific database object for each Security ID is stored in the MDB database file.

The information stored in the MDW uniquely identifies an individual Access user and the user groups to which a given user belongs. New users and groups can be added to the MDW, but the file itself does not contain any information about the database that is being secured, it only stores the Security IDs for each User and Group. The secured database knows nothing about the users or groups defined in the workgroup, it only knows what permissions are defined for any given Security ID. Because of the distinction between the MDW and the secured database, a single MDW can contain many different user and group Security IDs that can be used for multiple database solutions.

When implementing the security model, you can define both groups and users for those groups. It is preferred, but not required, to apply specific database object permissions only to groups and not to individual users. Ideally, groups are designed around the roles and tasks a user will have in the database solution. For example, one group might handle accounting activities whereas another group maintains customer contacts. The most common method is defining all of the various group types that should have access to each database object, so that the permissions can be set for the group from the tasks a user of that group would need to perform. Once the database solution is in use, a database administrator can add specific users to each of those groups.

Users can be assigned to one or more user groups. If a user handles only accounting activities, that user can be initially assigned to the accounting group. Later, if the user becomes involved with customer contacts (through organizational changes or additions to his responsibilities), the user can be assigned to the customer contacts group in addition to the accounting group. Permissions are cumulative, meaning that the user will have the maximum permissions allowed by combining the permissions rather than restrictions of each group that the user belongs to. There will be more on the cumulative effect a bit later.

Granting permissions to a database and the objects within it is best done through groups rather than individual users. This enables you or the database administrator to change the permissions for large groups of users by simply modifying the group(s) to which those users belong, which is stored in the MDW file. Any User/Group changes are picked up the next time the user logs into the updated MDW file. On the other hand, if permissions are granted to the specific users and groups are not assigned, the administrator would need to change the permissions for each user manually, which can become quite cumbersome when there are a large number of users and/or objects in the database that need to be updated.

When you create an MDW file, you specify an internal name (separate from the file name), an organization, and a Workgroup ID (WID). These three values provide the unique information used to authenticate the MDW file. Because almost anyone could recreate an MDW file from this information, be sure to keep it secure; you'll also want to back it up if you have to recreate the MDW.

Each user in the workgroup is assigned his own Personal ID (PID) number, which is also stored in the MDW file. Any user can belong to one or more user groups. Each group is assigned its own Group ID (GID). The authentication information in the MDW file, along with the username and PID, uniquely identifies an Access user.

Usernames in the MDW file are in no way associated with the Windows user information or NT Domain Authentication. The user and group information contained in the MDW is completely independent of the Windows User Account. Any Windows User Account can log in to the database, provided the user supplies the correct database and MDW username and passwords.

To identify users with the MDW file, the database engine running the application receives a set of identification credentials, considered a pass code. Each pass code has a unique characteristic based on the authentication information in the file plus the username and PID, or the authentication information in the file plus the group name and GID. The actual secured database application (not the MDW file) will use these pass codes to determine whether to grant a user permission to the objects in that database.

As previously mentioned, every ACE and Jet database session requires information from an MDW file. A default system MDW file is created for each Windows User Account when a database is opened if there is no default SYSTEM.MDW file present. The default location for this file is C:\Users\user name\AppData\Roaming\Microsoft\Access\System.mdw on Windows Vista or C:\Documents and Settings\user name\Application Data\Microsoft\Access\System.mdw for previous versions of Windows. The Access application is joined to this MDW by default if no other MDW is explicitly specified, meaning that the information contained in SYSTEM.MDW is used when databases are opened. By default, the Admin password in the default SYSTEM.MDW is set to blank, which is why there is no password prompt when opening normal, non-secured databases in Access. For this reason, it is extremely important to understand that if the default SYSTEM.MDW file is modified, and users and groups are added to the default SYSTEM.MDW file, the user for that Windows User Account will always be prompted to log on when opening any database, which can become extremely confusing. It is highly recommended that you never modify the default SYSTEM.MDW file. It's always preferable to create a new MDW file and to use a Windows shortcut to open a secured database with the specific MDW file, which will be discussed shortly.

Though not recommended, the Access client can also be joined to a different MDW than the default MDW file created for the Windows user account. To join the Access application to a different preexisting system.mdw file, use the Workgroup Administrator dialog box. In previous versions of Access, the Workgroup Administrator dialog box could be invoked by selecting Tools O Security O Workgroup Administrator. In Access 2007, the Workgroup Administrator option is no longer available through the Access UI to discourage changing the default joined MDW file. However, you can still use this dialog box; launch it by calling the RunCommand object with the acCmdWorkgroupAdministrator parameter. But remember, if you do decide to modify the permissions in the default system MDW file, the Access user for that Windows account may be prompted to enter a password for his account for every database opened, not just one specific database solution.

If you decide to manipulate the information in the default joined MDW file, it is critical that you create a backup copy of the current MDW. The default MDW file name is SYSTEM.MDW. The current default location is defined in the Workgroup Administrator dialog box.

0 0

Post a comment