George Snelling
Microsoft Corporation
January 1996
The purpose of this document is to explain the Microsoft® Access security model to developers and potential administrators of secured applications. We begin with a general discussion of the model, differentiating between share-level security and user-level security. Then we describe how to turn security "on" and explore in detail how permission checking works. Next, we explain user and group accounts, including the default accounts that ship with Microsoft Access, and how to create your own accounts. We touch briefly on owner-permission queries, file system encryption, and changes in security between Microsoft Access version 2.0 and Microsoft Access for Windows® 95. Finally, we outline common user scenarios, including step-by-step instructions for securing and distributing your applications.
There are three main reasons that people use Microsoft Access security:
If you build Microsoft Access applications and face any of these problems, this paper is for you.
Microsoft Access 95 now provides both share-level security and user-level security. In a share-level security system, objects are assigned passwords, and anyone who knows the password can access the object. Microsoft Access 95 supports share-level security with a database password.
Unlike most other desktop database management systems, however, Microsoft Access also provides user-level security. In a user-level security system, users are authenticated when they start Microsoft Access by logging on with a password. Administrators grant specific permissions, such as Read Data or Modify Design, to specific users and groups on specific objects. Different users can have different permissions on the same objects.
For example, suppose you have an Employees database containing a Salary table. If you simply want to disallow some users from opening the database, but all users who are authorized to open the database are allowed to do anything they want once inside the database, assign a password to the database. Users won't have to log on to Microsoft Access, and your administration is quite simple—just change the password periodically.
However, suppose now that you have two groups of people who need access to your Employees database: managers, who can update the Salary table, and payroll personnel, who can view but not update the table. By implementing user-level security, you can assign Update Data permissions to the managers group and Read Data permissions to the payroll group. Then you make sure that people logging onto your system are enlisted in the appropriate groups. The individuals manage their own passwords, and their passwords are used to verify their identity rather than having any direct effect on their permissions.
When working with user-level security, it's important to remember that there's no such thing as a permission on an object that exists all by itself—permissions on objects are always granted to users and groups. In a Microsoft Access database, it doesn't make sense to say, "the Salary table has Read Data permissions" because no user is indicated. It does make sense to say "the managers group has Read Data permissions on the Salary table."
In a nutshell, security functions as follows: Administrators assign specific permissions on database objects to users and groups. When a user starts up Microsoft Access in a secure environment, she logs on, usually with a password. The password's function is to authenticate the user—to prove that she is who she says she is. Microsoft Access checks and remembers all the groups to which that user belongs. Every time the user tries to perform an action on an object, such as open a form, browse a table, or modify a query, Microsoft Access checks to see if the user or any of the groups to which the user belongs have the necessary permission. If so, Microsoft Access performs the action. If not, Microsoft Access tells the user that he or she doesn't have permissions to perform the requested operation, and the operation fails.
It's important to realize that Microsoft Access security is always "on"—that is, every time a user performs any action, Microsoft Access first checks to make sure the user has permissions to perform that action. However, most Microsoft Access users never realize they are logging on and never see a security-related message. How does this happen? The illusion that security is not "turned on" is created by granting full permissions, by default, to the Users group (all users) on all objects and by automatically logging on each person as a default user (Admin) without displaying a Log on dialog box. Until a developer or administrator takes explicit action to expose the security subsystem, most users will never notice that it exists. This allows Microsoft Access to be extremely secure, with no "backdoor" modes of operation, but still keeps security virtually invisible to users who don't need it.
Microsoft Access is designed to be the most secure desktop database management system you can buy. However, it has no security rating with the U.S. government or any other certifying body, and it is not guaranteed by Microsoft to be secure. Skilled hackers with enough time and computing resources, and a desire to break into your database, could crack Microsoft Access security. If you have applications that require absolute security, you should consider using a server database such as Microsoft SQL Server™ on the Microsoft Windows NT® operating system, and a compiled application programming language such as Microsoft Visual C/C++®.
Microsoft Access stores its security information in two different places. User and group information is stored in a workgroup database, named by default System.mdw. The location of this database is specified in the Windows registry or with the /wrkgrp command-line switch. The workgroup database stores which users and groups exist, which users belong to which group, logon passwords (encrypted, of course), and the internal security identifier (SID) for each user and group. Workgroup databases are created by the Workgroup Administrator. The User and Group Accounts command on the Security submenu of the Tools menu changes data in the workgroup database. A single workgroup database can be used by many application databases.
On the other hand, all the permissions that users and groups have on objects in a database are stored in the database itself. After a user's identity and group membership have been established by checking the logon strings against data in the workgroup database, all permission checking is done against system tables within the user database. The User and Group Permissions command on the Security submenu of the Tools menu affects data in the open database, not the workgroup database. If you move objects from one database to another, their security settings travel with them.
Each user and group has associated with it a security identifier (SID). The SID is a machine-generated, nonreadable binary string that uniquely identifies the user or group. When a user logs on, Microsoft Access looks in the MSysAccounts* table of the workgroup database for a user of the same name (case insensitive). If a user with the same name is found, it then validates the password (case sensitive). If the password matches, the SID of the user is retrieved and saved in an internal structure. The password is used only to validate users when they log on. It has no other effect on security.
*Disclaimer This document refers in several places to the Microsoft Access system tables (those prefixed with MSys*). In spite of this, these tables are officially "undocumented" and are subject to change in future versions. Any applications that read directly from system tables are likely to fail in future versions of Microsoft Access.
By default, Microsoft Access first attempts to log on as the user Admin, with a blank password. If this log on fails, the user is presented with the Log on dialog box. If a user name and password were specified on the command line (using the /User and /Pwd flags), Microsoft Access first tries to log on using that user name and password. If this log on fails, the user is presented with the Log on dialog box. Once logged on, the user's SID is retrieved. Then the SIDs of all groups to which that user belongs are retrieved and saved in the same internal structure. These SIDs are used for all subsequent security operations within Microsoft Access.
There are two types of permissions: explicit and implicit. Explicit permissions are those given directly to a user. When they are granted, no other users are affected. Implicit permissions are those granted to a group. When they are granted, all users who are members of the group get the permissions of the group. Implicit permissions belong to the group, not the users. If users are removed from the group, they no longer have the permissions of the group. If users are added to the group, they get all the permissions assigned to the group. If the permissions of the group are changed, all users in the group are affected.
When a user attempts to do something, she will get the least restrictive permissions of: 1) all the groups in which she is a member (her implicit permissions), and 2) those given directly to her (her explicit permissions). Therefore, if the user Dawn has no permissions on the Salary table, but she is a member of the Payroll group, which has Read Data permissions on the same table, Dawn will be able to read the table. If she is removed from the Payroll group, and she belongs to no other groups with permissions on the table, she will be denied access the next time she tries to browse salaries.
It is highly recommended that you administer a secured installation using implicit permissions—that is, assigning permissions to groups, rather than directly to users. After your group permissions are established, they remain relatively constant, and the only administrative task you have is to assign individual users to the appropriate groups as their responsibilities change.
When you view a user's permissions on an object in the Microsoft Access User and Group Permissions dialog box, you see only the explicit permissions. To view the implicit permissions, view the permissions of each group to which the user belongs. Through the Microsoft Access user interface (UI), there's no way to see the combination of all the user's explicit and implicit permissions on an object. In other words, you can't select a user and see in one place whether the user can perform an action on an object. You need to look at the user's account and the accounts of all the user's groups as well. Through code, however, it's possible to build such a UI. To view and update permissions programmatically, you need to use Access Basic code and Data Access Objects (DAO).
Permissions on an object can be changed by anyone who has permissions to do so. By default, the following users have this permission: members of the Admins group of the workgroup database in use when the database was created, the owner of the object, and anyone who has Administer permissions on the object, either explicitly or implicitly. This means that even though users might not be able to perform an action, such as reading a table, they may be able to grant themselves permissions to read that table. For any given object, there are two user/group accounts that have the irrevocable right to assign permissions on that object, even if the UI indicates that those users don't have Administer permissions: the owner of the object (usually the user who creates it), and the Admins group of the workgroup database in use when the database was created.
In Microsoft Access version 2.0, there were two permissions on the database object itself: Open/Run and Open Exclusive. Microsoft Access 95 adds a third database-level permission: Administer.
Open/Run permission gives you a simple, clean way to lock unauthorized users out of your database entirely.
Open Exclusive permission prevents users from inadvertently locking each other out of a shared database by opening it exclusively. In Microsoft Access 2.0, databases were opened in exclusive mode by default, which could cause headaches in a multiuser setting. Microsoft Access 95 now opens databases in shared mode by default, so this should become less of a problem.
Administer permission protects two actions: converting a database to a replicable database, and setting the database password.
You don't need to set up a full-blown secure system with user log on to use the Open Exclusive or Administer permissions. If your main concern is to protect well-meaning users from hurting themselves or other members of their workgroups by inadvertently opening a database exclusively or by setting a database password, you can implement "speed-bump" security very simply, preventing these actions without taking on the administrative burden of managing a secure workgroup and forcing users to log on.
To do this, simply remove the Open Exclusive permission and/or the Administer permission from all the default users and groups, including the Admin user, the Admins group, and the Users group. Then, users starting Microsoft Access under the default account, Admin, won't be able to open the database exclusively or set the database password. If you don't choose to set up users and groups with log ons, it's possible for users to subvert these permission by opening the User and Group Permissions dialog box on the Security submenu and grant themselves this permission, but well-meaning users will have no reason to do so. If you want to absolutely prevent users from setting a database password, but you don't want them to have to log onto Microsoft Access, follow the steps described below to secure a database without forcing users to log on.
You can set the permissions that users and groups will receive by default on all newly created objects in a database. For example, you can set the default so that Users groups will get no permissions on newly created tables. This reduces your administrative burden, because you don't need to keep periodically checking to make sure that newly created objects are properly secured. In the User and Group Permissions dialog box, select the user or group for which you want to define default permissions. Then choose the object type in the Object Type combo box, and click the New Object token in the Object Name list box. Choose the permissions you want the user or group account to inherit for all new objects of that type, and click Apply.
Note You can prevent users from creating new database objects, tables, and queries by removing permissions on the container object using code, but you cannot prevent them from creating new forms, reports, macros, or modules.
Each user can belong to one or more groups. Users and groups share the same namespace, so a group and user can't have the same name. User and group accounts are not stored in individual databases; they are stored in system tables in the workgroup database, usually called System.mdw.
Each user and group account is uniquely identified to Microsoft Access by a security identifier, or SID. SIDs are 128-bit machine-readable keys. When a user logs on to Microsoft Access, her SID and the SID of all the groups to which she belongs are read into an internal structure. This array of SIDs serves as the user's identity for the duration of the session.
When you create a new user or group account, you are prompted for a user name and a PID (personal identifier). The PID is not the password. It is a variable-length, alphanumeric, human-readable string that is fed, along with the user's name, to an encryption program to generate the SID for that account. The purpose of the PID is to enable you to re-create a user or group's SID if your workgroup database ever becomes lost or corrupted, or if you need to create identical SIDs (identical accounts) in multiple workgroups. If you feed in the same user name and PID to the encryption program, you will get the same SID. The only time that the PID has any relevance is when a new account is being created. It is not used or referred to after that, nor is it stored anywhere in the database or the workgroup file.
After you have created a new user account, you usually assign the user a password. Again, the password is not the same as the PID. The password's function is to authenticate the user when she logs onto the workgroup. Users can change their own passwords. Passwords have no effect on the user's SID or her permissions. Members of the Admins group (described below) can clear the passwords of all users in a workgroup.
As stated earlier, the easiest way to administrate a workgroup is to create new groups and assign permissions to the groups, rather than to individual users. Then you can change individual users' effective permissions by adding or deleting them from groups. Most installations only require three or four groups to give them the granularity of permission control that they need, and it's much easier to maintain permissions for three or four groups than for 50 or 100 users.
Microsoft Access defines one default user, Admin, and two default groups, Admins and Users. Understanding how the SIDs of these default accounts are generated and what permissions they are granted by default helps you understand how security works.
The Admin user is the default user account. Anyone using Microsoft Access without logging on is using the Admin account (unless they are logged on to a different account using the /User and /Pwd flags on the command line). Admin is not very well named, as it has no particular administrative abilities. Because most users are logged on as the Admin user without ever knowing it, and because all users by default have permissions to objects they create, any objects that are owned by the Admin user or to which the Admin user has explicit permissions are unsecure. Think of the Admin account as the DefaultUser account or, for those familiar with Windows NT, the Everybody account.
SID: The Admin user's SID is identical across all installations of Microsoft Access and Visual Basic®. Even if no one in your workgroup is using the Admin account (because you have password-protected it), all objects owned by the Admin user or to which the Admin user has explicit permissions are still open to anyone using their own copy of Microsoft Access or Visual Basic. This is one of the most common misunderstandings in Microsoft Access security administration. Fortunately, once the problem is understood, it's easily prevented: Make sure the Admin user has no explicit permissions and owns no objects. The Security Wizard described below makes this easy.
Default Permissions: By default, the Admin account doesn't receive any permissions on new objects. However, when any user creates an object, the user account is automatically granted full permissions on this object by default, and the user becomes the owner of that object. Because all users in unsecured environments log on as Admin, the Admin account usually owns a lot of objects and has a lot of permissions. When it's time to secure your database, using the Security Wizard is the easiest way to remove those permissions.
The Users group is the default group. All Microsoft Access users always belong to the Users group. Thus, any permissions that are assigned explicitly to the Users group are available to all users in all installations of Microsoft Access. If you ever need to unsecure an object, simply assign permissions on that object to the Users group.
Note Universal membership in the Users group is implemented by code in the Microsoft Access UI, not the Microsoft Jet database engine. Therefore, programmers adding new user accounts through DAO code need to explicitly add new users to the Users group themselves—it won't happen automatically. For more information, search Microsoft Access Help for "User". The sample code illustrates how to create a new user and add him or her to the Users group using code.
SID: The Users group's SID is identical across all installations of Microsoft Access. (Note that Visual Basic applications that don't refer to a workgroup database run under the permissions of the Admin user, not the Users group.) Therefore, if you assign permissions to the Users group on objects in a database that you keep in London and then mail that database to users in Sydney, those users will be able to access those objects, even though they are members of a different workgroup.
Default Permissions: By default, the Users group gets full permissions on all newly created objects. This is the main mechanism Microsoft Access uses to "hide" security from the majority of users who don't need it. The easiest way to remove this permission from all objects in a database is to use the Security Wizard, which removes all permissions from the Users group and the Admin user.
Users who are members of the Admins group (note the "s") are the true administrators of a workgroup. They manage user and group membership, and they have the power to clear users' passwords. There must be at least one member of the Admins group at all times.
SID: Because the Admins group is invested with super user capabilities, understanding how the Admins group's SID is created is key to understanding the security model. It is created by encrypting three strings: user name, company name, and workgroup ID. The Microsoft Access setup program uses only the first two—the user's name and the company name—as encryption seeds for the Admins group's SID. Because these two strings are available from the Help menu, it is critical that users implementing security use the Workgroup Administrator to create a new Admins group with a secure SID.
The Workgroup Administrator prompts for all three seed strings: a user name, a company name, and a workgroup ID. These strings are fed into an encryption program to generate the Admins group SID. Thus, it is very important to record these three strings (which are case sensitive) in a secure, off-site location. If the workgroup database ever becomes destroyed or corrupted beyond repair, you can generate a new Admins group with an identical SID by running the Workgroup Administrator again and entering the same strings. A member of this re-created Admins group will be able to grant himself or herself permissions on all objects that were created when the old workgroup database was in effect.
Default Permissions: The Admins group of the workgroup database in use when the database was created by default gets full permissions on all new objects in a database. More importantly, the Admins group's Administer permissions, the right to change permissions, can't be revoked through the Microsoft Access UI. Even if you clear the Administer check box in the User and Group Permissions dialog box for the Admins group on an object, the permission remains. It is therefore very important to know and keep track of which workgroup database was in use when the database was created. To stress this point, italics are used throughout this document where it comes up.
Note The irrevocability of the Admins group Administer permissions is enforced through the Microsoft Access UI, not through the Microsoft Jet database engine. This means that Administer privileges can be revoked for the Admins group through DAO code. This is generally not advised, because then only the object's owner will have irrevocable permissions on that object. If the owner account ever becomes corrupted or erased, and it can't be re-created because nobody recorded that user's name and PID, there would be no way to recover permissions on that object.
The Guest user and the Guests group accounts were created by default in Microsoft Access version 2.0, primarily to give users in an unfamiliar workgroup a default account with which to log on. Their SIDs are identical across all installations. Microsoft Access 95 no longer creates these accounts. You will continue to see the Guest user and Guests group if you are using a workgroup database from Microsoft Access version 2.0 running under Microsoft Access 95.
The key thing to remember about the default accounts is that the SID of the Admins group account is unique across workgroups, but the SIDs of the other two default accounts, Admin user and Users group, are identical across all installations of Microsoft Access. Therefore, permissions assigned to the Admins group are secure, but permissions assigned to either of the other default accounts are available to anyone with a copy of Microsoft Access.
The CurrentUser function in Access Basic returns the name of the current user. This is not the same as the SID. It is possible for two users in different workgroup databases to have the same user name but different SIDs, because when their accounts were created, different PIDs were fed to the SID generating program and different SIDs were created. Watch out for this problem if you are using the CurrentUser function to log user activity and are monitoring activity across different workgroup databases, where users could conceivably have the same name but are in fact different.
Ownership plays an important role in Microsoft Access security. The user who creates an object (as identified by his or her SID, not his or her user name) owns the object. The object's owner always has permission to assign permissions on that object, even if someone with Administer privileges has revoked his or her explicit permissions on that object. Unlike the Administer privileges of the Admins group, this irrevocability is enforced by the Microsoft Jet database engine—not the Microsoft Access user interface. Therefore, it cannot be revoked through DAO code. It can only be transferred to another user.
There are two ways to transfer ownership of a object. The first is to change the owner through the UI. The second is to re-create the object. This is usually accomplished by importing it or exporting it into a new database. The Security Wizard, available by clicking the User-Level Security Wizard command on the Security submenu of the Tools menu, does this to create a new secured database with all objects owned by the user who runs the wizard. Note that in order to re-create an object, the person doing the importing/exporting or copying/pasting must have permissions to read the object (and its data if the object is a table or query) in the first place.
As mentioned above, the Admin user has the same SID in all workgroup databases. It is therefore critical that the Admin user not own any objects. If the Admin user does, anyone who has Microsoft Access or Visual Basic who can open the database can give themselves permissions to those objects. To see who owns an object, click the Change Owner tab of the User and Group Permissions dialog box.
Microsoft Access queries have a property called RunPermissions, available through the Query Properties property sheet. This "property" is really a UI construct that maps to the WITH OWNER ACCESS OPTION clause of the SQL property of the Microsoft Jet database engine query. It can have one of two settings: User's or Owner's. If it is set to User's (the default), nothing special happens—the query executes normally. However, if it is set to Owner's, when the query is executed, only the permissions of the owner of the query are considered when attempting to access tables or queries on which the query is dependent. This very powerful feature allows you to build secure views on your data that can provide row-level and column-level security for your users.
For example, suppose you have a secure database with an Employees table and a separate Salary table. Using owner-permission queries, and setting insert, update, and delete permissions separately on the underlying tables, you can build views on the two tables that allow one group to view, but not update, the salary column, another to be able to view and update the salary column, and a third to view only the salary column if salary is less than a certain amount. The users involved would have no permissions whatsoever on the underlying tables.
Only the owner of a query can save it if the RunPermissions property is set to Owner's. Even members of the Admins group of the workgroup database in use when the database was created can't save a query created by another user if the RunPermissions property is set to Owner's. It would be a security breach to do so. Note, though, that anyone with Modify Design permissions on the query can set the RunPermissions property to User's and successfully save the query. If you need to change the owner of an owner-permission query, first change it to a regular query, then change the owner, and finally change it back to an owner-permission query.
If you have more than one developer in your workgroup who may need to modify the design of an owner-permission query, create a Developers group, change the queries to regular queries, use the Change Owner tab to change the owner of the queries to this group, then add all your developers to the Developers group, and finally change the queries back to owner-permission queries. Then, any member of the Developers group will be able to edit the query and save changes.
To display a table or query, Microsoft Access needs to know field names and other field properties such as Format, InputMask, and so on. Thus, for a user to read the data in a table or query and display it in the UI, he or she must also have permissions to read the design of the table or query. That's why checking the Read Data permission check box in the User and Group Permissions dialog box automatically checks the Read Design check box as well. If you don't want your users to see your table or query definitions, you can use owner-permission queries to restrict their access to this information. Here are the steps:
Your users will be able to update data in Secret Table through forms based on My Owner Query. However, they won't be able to view the design of the table. If they try to view the design of Secret Table, they will receive the message "You don't have permissions to view Secret Table."
Encryption is separate from Microsoft Access security. It is used to prevent someone using a file or disk editor (such as Norton Utilities®) from reading or writing data directly to an .mdb file, bypassing the Microsoft Jet database engine. Encryption by itself is meaningless without implementing security, because by default anyone can open an unsecured database because they are transparently logging on as the Admin user and thus have access to the data.
Microsoft Jet reads and writes all data one page at a time. Pages are 2K in size. Encryption is done at the page level, not at the data level. That is, at the level that encryption is being done, there is no notion of what is on the page, only that there's 2K of data that needs to be encrypted and written, or read and decrypted. This implies that everything in a Microsoft Access .mdb file is encrypted, including tables, queries, forms, indexes, and so on. Microsoft Access uses the RSA Data Security Incorporated RC4 algorithm for database encryption.
If a database isn't encrypted, it would be possible for a (very) knowledgeable person to use a disk editor to read the SID of the database owner or the SID of the Admins group of the workgroup database in use when the .mdb was created. Given that SID, this person could assume ownership of the database using the methods outlined above and do anything he or she wanted with the database. For this reason, it is recommended that if you want to protect against such intrusions, you should encrypt your database. The Security Wizard encrypts databases as part of the process of securing them.
Only the creator (owner) of a database or a member of the Admins group of the workgroup database in use when the database was created can encrypt or decrypt it.
Due to the overhead of encrypting and decrypting, there is a performance degradation of approximately 10 to 15 percent in encrypted databases. Encrypted files are also essentially incompressible (using utilities such as PKZIP®, Stacker®, or DriveSpace™).
The Microsoft Access Security Wizard is by far the easiest way to secure databases. It allows you to choose which object types to secure—tables, queries, forms, reports, macros, or modules. The selected object types are secured by revoking all permissions from all accounts except that of the user running the wizard. Beginning with Microsoft Access 95, the Security Wizard is included in the retail product.
To secure a database, log on as a user other than Admin who has read permissions on all objects and data in a database. Then open the database and invoke the Security Wizard by clicking User-Level Security Wizard on the Security submenu of the Tools menu. The Security Wizard creates a new secured copy of the database. It will leave the original copy unmodified. The Security Wizard can be used to secure databases in Microsoft Access 95 format.
If you have an existing workgroup database and some, but not all, of your users are upgrading to Microsoft Access 95, leave your workgroup in Microsoft Access 2.0 format—it will work fine for both sets of users. If all users in your workgroup are upgrading to Microsoft Access 95, you should upgrade your workgroup database to Microsoft Access 95 format. Microsoft Access 95 uses the Microsoft Jet database engine version 3.0, which is much faster in multiuser environments. You should not convert your 2.0 workgroup database to Access 95. Instead, you should re-create a new 95 workgroup from scratch. If you have recorded the names and PIDs of the group accounts in your old workgroup database, use those strings to re-create groups in your new workgroup. Then re-create user accounts and assign them to the appropriate groups. You won't need to resecure your databases, because the SIDs of the new groups will be the same as the old ones. However, if you don't have the group names and PIDs from your old workgroups, you will need to create new user and group accounts and resecure your databases when you convert them.
The location of the system database is specified in the Windows registry under the key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0\Jet\3.0\Engines\Jet\SystemDB
You can configure your application to point to a different workgroup database by launching it with one of two startup command-line options. You can specify a custom user profile registry key for you application with the /Profile startup command-line option. This replaces the /ini option used in Microsoft Access 2.0. It enables you to override any of the defaults that are stored in the Microsoft Access 95 section of the registry.
If all you want to do is change the workgroup database, use the /Wrkgrp startup command-line option followed by the full path of the workgroup database. For example
C:\Msoffice\Access\MSACCESS.EXE C:\Apps\Myapp.mdb /Wrkgrp C:\Apps\Mywrkgrp.mdw
Search Microsoft Access Help for "Startup command-line options" for more information.
You've written an application using Microsoft Access—call it MyApp.mdb. You've created all the objects while you were logged on as Admin (the default). Now you want to secure the database. Here are the steps:
The new database is now secure. The only people who can get into the objects in MyApp.mdb are those you gave permissions to in Step 9 and anyone who is a member of the Admins group of the workgroup database in use when you ran the Security Wizard.
If you are planning to secure your database from the beginning as part of the design phase of your project, repeat Steps 1 through 4 above by creating a new workgroup database and a new owner, removing the Admin user from the Admins group. Create your own custom groups and users. You can now create a new database that will not be owned by the Admin user. For each container object in the database (tables, queries, forms, reports, macros, and modules), you can set permissions for new objects for each group to inherit before you actually create any objects. You can then test your security structure as you are building your application instead of waiting until the last minute.
You've followed the steps under "Securing an Existing Database," above. Joe is the owner of the database and all objects in it. The Admins group of the workgroup database in effect when Joe ran the Security Wizard has permissions to change permissions on any object in the database. The goal here is to make the database open to all users of Microsoft Access. Remember, the SIDs of the Admin user and the Users group are constant in all workgroups. In addition, Visual Basic users running applications that do not use a workgroup database all log on as the Admin user. So by giving permissions on an object to the Users group and the Admin user, you unsecure that object for all users.
Your database is now completely unsecured. Anyone who can get to the MyApp.mdb file has full permissions on all the objects in it.
You have written an application using a split front-end/back-end architecture. Your data is stored in Data.mdb, and your code, forms, reports, and so on are in Program.mdb, along with linked tables pointing to tables in Data.mdb. You want to secure both databases and distribute them to your users. When they install Program.mdb, you want them to be able to relink to the tables in Data.mdb, based on a network share name on which they will keep Data.mdb, and you don't necessarily know the name of this share when it comes time to secure your application.
Because the users have Modify Design permissions on the linked tables, they can reset the connect string to the back-end tables if the file system location of Data.mdb ever changes. They won't be able to make any modifications to the design of the real tables in Data.mdb.
If you want to secure some objects in a database (such as your modules), but you don't care about granting different permissions to different classes of users, you might want to consider securing you application without making your users log on.
Now you need a way to perform administrative duties such as setting permissions and assigning users to groups. Because you have removed the Admin user from the Admins group, you won't be able to perform these actions when logged on as Admin. And because the Admin user has no password, the Log on dialog box won't appear. You'll need to log on as Joe (or another member of the Admins group) to perform these duties. You can do this by editing the command-line argument of the Microsoft Access Program Manager icon to includes Joe's name and password using the /User and /Pwd command-line options. When you start Microsoft Access using this icon, even though you don't encounter the Log on dialog box, you will be logged on as Joe and will be able to perform administrative duties.
Of course, if you choose to keep this icon on your computer, rather than deleting and re-creating the command line each time, you'll have to make sure your computer is physically secure, because someone could copy the user name and password arguments from the command line and gain access to your system.
You have built a multiuser application that will be administered by users at a remote site. You want to lock up your code and other objects to protect your intellectual property and to prevent users from inadvertently breaking your application. Different users at the sites have permissions to different forms in the database because they are authorized to perform different duties. You don't want to manage individual user accounts yourself because you are not on site and they change frequently. How can you grant remote administrators the ability to manage user accounts without at the same time giving them access to your code?
Now Fred will be able to create new user accounts, reset passwords, and add users to the groups that you have already created. As users are added to these groups, they will automatically gain the permissions that you assigned to these group accounts in step 4. This is because the SIDs of the group accounts in MySys.mdw are identical to the SIDs of the custom group accounts in CusSys.mdw. However, the SIDs of the Admins group in the two workgroups are different. (Remember that these SIDs are generated from the strings fed into the Workgroup Administrator, and you used different strings in step 5.) Because the database was originally secured under MySys.mdw, not CusSys.mdw, the Admins group in MySys.mdw has ultimate permission-setting privileges over all the objects in the database. The Admins group of CusSys.mdw does not. So while Fred can add and delete users from the groups you created, he does not have any special privileges to your objects, and he therefore cannot see or modify the design of any of the objects to which you haven't granted him permissions.
Programming security is documented in Building Applications, available in the Microsoft Access Developer's Toolkit, in Help (search for "Security"), and in the Microsoft Jet Database Engine Programmer's Guide, available from Microsoft Press®. What follows here is a general overview to give the developer an idea of the capabilities available.
For security purposes, all Microsoft Access objects—tables, queries, forms, report, macros, and modules—are considered documents and are manipulated through the Document object and the Documents collection of Microsoft Jet Data Access Objects (DAO). There are User and Group objects and Users and Groups collections. By manipulating the Users and Groups collections, developers can programmatically manage which users belong to which groups. Permissions are properties of Document objects that are assigned to specific User and Group objects. The permissions are bit masks that are defined by security constants.
A session of the Microsoft Jet database engine is represented programmatically by the Workspace object. As mentioned above, all Jet sessions run within the context of a particular user account that is authenticated by a user name and password when the session is started. Launching Microsoft Access from Program Manager opens a Jet database engine workspace. Workspaces can also be opened programmatically by creating a new Workspace object. Because all Jet database engine sessions must run within the context of a particular user, you must specify a user name and password when you create a new workspace.
Note that creating a new workspace with the user name "Admin" and an empty password has the same effect on security as launching Microsoft Access in its default configuration. If the log on succeeds, the workspace is created with the security context set to the Admin account. If the log on fails (because someone has password-protected the Admin account), an error is returned and the Workspace object is not created.
You can write code in an application that has permissions to do things that your users can't do through the UI. You do this by opening new workspaces under the name and password of a user account (often a member of the Admins group of the workgroup database in use when the database was created) who has greater permissions than those of the user currently logged on. If you do this, make sure that the module in which you place this code is itself properly secured, because the user name and password will be visible to anyone who has Read Design permissions on the module. Use this technique only when you have to; literal text strings in modules are vulnerable to attacks by hackers. If you choose to do this, it's best to write your own simple encryption program for storing the name and password of the privileged user, so that the user name and password are constructed from several different strings stored in separate places in your code.
Replicable databases are no different from normal databases in that they use the same security model. Therefore, if you are developing replicable databases in a secure environment, you need to make sure that you distribute your workgroup database (System.mdw) at each location where a replica is used.
You cannot replicate a workgroup database itself, so it is recommended that copies be distributed to each location. Another method is to enter the same group names and associated PIDs in separate workgroup databases at each location. Assign permissions to the groups in the Design Master, which will then be inherited by the groups at each location.
You must have Administer permissions on the database to convert it to a replicable database or perform any other administrative tasks on an existing replica, such as transferring Design Master status, making local objects replicable, or creating additional replicas on the Design Master.
Because the Admins group, the Users group, and the Admin user have default Administer permissions, you need to make sure that these are removed in your secured database before creating the Design Master or any replicas.
If you build applications with Visual Basic that use the Microsoft Jet database engine, you can secure your tables and queries just as you can in Microsoft Access applications. All the programming interfaces described above are part of the Data Access Objects that ship with the Jet database engine. To implement security, however, you will need to be able to create workgroup databases with the Workgroup Administrator, and currently this utility only ships with Microsoft Access. Because of this, and because Microsoft Access provides a UI to set permissions, many Visual Basic programmers choose to set up the security for their applications using Microsoft Access and the Microsoft Access Security Wizard. In your application's registry entry, establish your application's workgroup by setting the SystemDB key to the full path of your workgroup database.
For many applications, the only UI you'll need to build to use security is a Log on dialog box. Using the strings that the user enters into this dialog box, you create a new Workspace object. This starts a Jet database engine session and logs the user on under his or her new account. Once this has been accomplished, permission checking functions exactly the same as it does in Microsoft Access applications. If users try to perform any actions to which they don't have permissions, such as writing to a Salary table, Jet will deny the action and return a trappable error.