Microsoft Jet defines users of your application in two ways: as individual users or as groups of users.
Note Users and groups share the same namespace in Microsoft Jet: You can’t have a user and group with the same name.
If you’ve never built or administered a user-level security system before, it’s important to understand that there is no such thing as a permission on an object that exists by itself. Permissions on objects are always granted to specific users or predefined groups of users. That is, a permission exists only in the dual context of a user or group and the object it applies to.
After a user logs on, Microsoft Jet uses a special number to identify that user. This number, called a security identifier (SID) is a machine-readable value that varies in length and will be 128 bytes at most. Microsoft Jet creates a SID when a user or group is created. When a user requests access to objects, Microsoft Jet uses the user’s or group’s SID for identification. The user’s name and password are not used. The name and password are used only for authentication — the process of verifying a person’s identity. From then on, Microsoft Jet uses the SIDs to determine a user’s access to objects.
To generate a SID, Microsoft Jet uses a personal identifier (PID). This value is a variable-length string that you specify when you create a user or group. The user or group name and PID are fed to the encryption program that generates the SID for that account. If you feed the same user name and PID back into the encryption program, you get the same SID. This gives you the ability to re-create user accounts if your workgroup information file (system database) becomes corrupted or is lost.
Figure 10.2 Creating security identifiers
Microsoft Jet defines a default user account named “Admin.” Before user-level security is established, all users are implicitly logged on as the Admin user. Unfortunately, the Admin user is not very well named, as this user has no particular administrative characteristics. Because most users are logged on as Admin without ever knowing it, and because by default all users have permissions for the objects they create, any objects owned by the Admin user or for which the Admin user has explicit permissions are unsecured. Think of the Admin user as the default User account or, for those familiar with Windows NT security, the Everybody account.
Understanding how the SIDs of these default accounts are generated helps you understand how the security model works. The Admin user’s SID is identical across all installations of the Microsoft Jet database engine. Even if no one in your workgroup is using the Admin account (because you have password-protected it), all objects owned by Admin, or for which Admin has explicit permissions, are still open to anyone using Microsoft Jet. This is one of the most common misunderstandings regarding Microsoft Jet security. Fortunately, once you understand the problem, it’s easily prevented: Make sure that Admin has no explicit permissions and owns no objects. The User-Level Security Wizard makes this easy.
Microsoft Jet also creates two accounts, Creator and Engine, that are used internally. These accounts are not accessible by users.
Microsoft Jet defines two default groups for you: the Admins group and the Users group.
The Admins Group
The Admins group is designed to hold user accounts for people who are the true administrators of the workgroup. They manage user and group membership and have the power to clear users’ passwords. Members of the Admins group that was in use when you created the database always have permission to grant permissions on any object in that database. For example, if user Bob is a member of the Admins group, he may not have permissions to open a particular table, but he will be able to grant himself permission to open that table, even if he doesn’t own the table. The Admins group of other workgroup information files don’t have this privilege. The Admins group should have at least one member at all times.
Caution Although Microsoft Jet allows you to delete the last member of the Admins group, this is not advised, because only the object’s owner has irrevocable permissions for that object. If that owner’s account ever becomes corrupted or deleted, and cannot be re-created because you don’t have that user’s name and PID, there is no way to recover the permissions for that object. In other words, there is no member of the Admins group to fall back on.
The SID of the Admins group is unique for each workgroup information file and is generated when the file is created. When you use the Workgroup Administrator to create the workgroup information file, this SID is generated by encrypting three strings that you enter: user name, organization name, and workgroup ID (WID). The Microsoft Access Setup program also creates a workgroup information file, the default System.mdw. However, Setup uses only the user’s name and the company name (provided during Setup) to generate the SID for the Admins group of the default workgroup information file. Therefore, you shouldn’t use this default workgroup information file to secure your database, because these two values are available from the Microsoft Access Help menu. If you use the default workgroup information file, unauthorized users trying to breach security could re-create an identical Admins account by using the Workgroup Administrator to create a new file with these values, and then adding themselves to the Admins group.
Before establishing user-level security, use the Workgroup Administrator to create a new workgroup information file, making sure to enter a workgroup ID value that is known only to you. This ensures that the new workgroup information file contains an Admins group with a unique, secure SID. For information about using the Workgroup Administrator, see “Creating the Workgroup Information File” later in this chapter.
The Users Group
The Users Group is the default group for new users. By definition, all users of your application are members of the Users group. Any permissions assigned explicitly to the Users group are available to all users in all installations of Microsoft Jet. By default, the Users group has full permissions for all newly created objects. This is the main mechanism Microsoft Jet uses to “hide” security for the majority of applications that don’t need it. Even though Microsoft Jet security is always “on,” if you don’t need its functionality and don’t want your users to have to log on and worry about permissions, Microsoft Jet ensures that you don’t have to worry about security by making all users members of the Users group and granting that group full permissions.
Ownership
Understanding the concept of ownership is crucial to understanding the Microsoft Jet security model. The user who creates an object owns that object. This ownership grants that user special privileges for that object; thus, he or she can always assign or revoke permissions for that object. This privilege cannot be revoked by any other user, including members of the Admins group.
Before you establish user-level security, the default Admin user is the owner of the database and all the objects in it. To effectively manage security, you need to change the ownership of the database and all the objects in it. You can do this for all objects except the database itself directly through Data Access Objects (DAO) by using the Owner property, or in Microsoft Access on the Change Owner tab in the User And Group Permissions dialog box (Tools menu, Security submenu). However, you can’t change the owner of a database on the Change Owner tab in the User And Group Permissions dialog box. The only way to transfer the ownership of all of the objects and the database itself is to log on as the user you want to own the database, create a new blank database, and then import all of the objects. When you run the User-Level Security Wizard to secure your database, it uses this method to transfer ownership of the database and all of its objects.
Note These procedures don’t change the ownership of queries whose RunPermissions property is set to Owner’s. You can change ownership of a query only if you own the query, or if its RunPermissions property is set to User’s. For more information about the RunPermissions property, see “Using the Microsoft Access RunPermissions Property with User-Level Security” later in this chapter.
The owner of the database itself is the user account in use when the database is created. The database owner can always open a database and create new objects in it. The database owner can also grant or revoke the permission to open a database. A database owner’s rights can’t be revoked by using the User And Group Permissions dialog box.
Important If you don’t transfer database ownership to a secure user account, any users of Microsoft Access or Visual Basic can open your database and grant themselves full permissions.