Jose's Database Programming Corner

Understanding Jet Security


Note: You must have a copy of Access available to create a secured database application. The Jet security system relies on having a uniquely defined system database which can only be created by the Workgroup Administrator applet that ships with Access.


Introduction

The Jet database engine employs a user level security system. In a manner similar to Windows NT and NetWare network security, the objects themselves do not have passwords assigned, but have permissions granted to user and group accounts. Once validated, a user will have the authority to do whatever is authorized by the permissions granted to the user account or to any group of which the user is a member. This permits an administrator to manage a very granular set of security attributes where, for example, an individual user may have the authority to read but not write data. Although the security model does not have the ability to assign column level permissions, through the use of queries and the "run with owner permissions" attribute, you can effectively handle permissions at the row and column level for data.

There are two core objects involved in the Jet security system: the system database and the Workspace object. The system database, which can only be created by the Access Workgroup Administrator, stores user and group account information. The Workspace object is used to open databases in the context of a user account. Permissions are then assigned to user and group accounts for database objects.

Once a system database has been established, the primary tasks in administering Jet security are creating and maintaining user and group accounts and assigning permissions to database objects.

If you haven't already done so, you should also read the Jet security white paper. You can download the white paper and the Jet security Wizard from my downloads page or from the Microsoft® Access web site.

One dissapointment in Visual Basic 4.0 and 5.0 was the continued lack of ability to create a system database without having a copy of Access available. I find this and the rather poor documentation for creating and using secured databases with VB to be a serious omission on the part of Microsoft. I'll digress for a moment to encourage you to send mail to vbwish@microsoft.com to request that the ability to create a system database be included in the next version of VB so that VB may at last have complete independence from Access. Although I think Access is one of the most powerful database programming tools available to VB programmers, I also think it's unfair of Microsoft to require that VB developers purchase a copy of Access for the simple function of creating a system database. Additionally, this makes it impossible to programmatically create a system database - something I also consider a glaring omission.


Return to Top of Page Return to top of page

User and Group Accounts

User and group accounts are the cornerstone of the Jet security system. User accounts represent individual users of the database. Group accounts are used to collectively assign permissions to database objects.

Default Accounts

In addition to the user and group accounts that you create, the Workgroup Administrator creates several accounts by default.

One common cause of confusion centers around the Admin user account. Admin, with the exception of being the default account used by Jet and being a member of Admins by default, has no special authority in a database. In fact, the Admin user would be better named as "Default", since that's really all it is - the default account. Unfortunately, the name was poorly chosen by the developers.

The Admins Group

The Admins group, rather than the Admin user, is the account that has special authority under Jet. Admins always has the Administer permission for database objects. This means that members of Admins can change permissions or change ownership of objects. This gives members of the Admins group the ability to assign authority to any object in the database to other users and groups. In a secured system, you will want to control who has membership of the Admins group because these users are essentially gods in the databases they use. Even object owners (we'll discuss ownership later) are subject to the powers of members of Admins since the Admins group has the authority to change the ownership of objects (but not databases).

The term "admin" was probably used a little too liberally in Access, creating confusion for users. Admin user, Admins group, Administer permission, etc. It would have been less confusing if some variation of "admin" were used for some of these, but unfortunately its something you're going to have to learn to live with. The true security key in Access is the Administer permission - which grants authority to change permissions and ownership. The Administer permission is always available to the Admins group and by default also is available to the Admin user.

Remember that the authority of the Admins group applies only to members of the Admins group in the system database in use at the time the database was created. This is a key point which cannot be overlooked if you are securing a Jet database. If you are using the default Access system database, your application will not be secure. Anyone opening the database using the default Admin account will have Administer permission on all database objects.

Database Object Owners

The owner of a database object is the user that created the object. Along with the Admins group, owners also have special permissions on the objects they own. In fact, owners of objects always have full and irrevocable permissions on the objects they own. This is somewhat disguised by the Access user interface. While a member of Admins can revoke the Administer permission for an object owner, the action is ignored by the security system.

I've used this capability to my advantage in Access for objects that I use as templates. When creating new reports, for example, I have a blank template with several properties, controls, and procedures already set up. Using the security menu, I then revoke my own permission to modify the design. This prevents me from accidentally changing the design of the template. If I ever do need to modify the template, I can simply grant myself the Modify permission, make and save my changes, then revoke Modify permission for myself - essentially making the object read-only again.

The database owner, database object owners and the Admins group have a interesting relationship. By default, Admins will always have Administer permission for database objects. Although the object owner or a member of Admins can revoke the Administer permission, a member of Admins can grant the permission to himself or the Admins group. There's really no effective means of preventing the Admins group of having access to an object. To demonstrate this, open a database as the database owner, go to the permissions dialog box, and attempt to set the permissions on the system tables (MSys...). You'll find that the database owner can change the permissions set by Jet on the system tables for the database, even though they are not owned by the database owner. (Don't do this with a production database!)


Return to Top of Page Return to top of page

Permissions

There are several levels of permissions available for database objects.

Object Type Permission Description
Database Open/Run Open the database file.
Open Exclusive Open the database exclusively.
Note: Generally you should probably clear this permission for all users except those who actually need it. This will prevent users from inadvertently opening the database exclusively in Access by setting the "Exclusive" check box in the file open dialog.
Table Read Data Open the table and read the data in the records.
Update Data Modify existing records.
Insert Data Create new records.
Delete Data Delete records from the table.
Read Design Read the design specifications for the table.
Note: If any permission for the data in a table is granted, Read Design permission must also be granted. The database engine cannot present the data without being able to read the design. Revoking Read Design revokes all rights for the table.
Modify Design Change the design of the table.
Administer Assign permissions and change ownership.
Query See Table The permissions which can be assigned for queries are identical to those for tables.
Form Open/Run Open and use the form.
Read Design Read but not modify the form design, including code.
Modify Design Change the form design.
Administer Set permissions or change ownership.
Report See Form The permissions for reports are the same as those for forms.
Macro Open/Run Execute the macro.
Read Design View but change the macro definition.
Modify Design View and change the macro definition.
Administer Set permissions and change ownership.
Module Read Design View but not change the code in the module.
Note: There is no administrative way to prevent someone from executing the code in a module. If you wish to prevent a user from running the code, you will have to build a switch in the code based on the current user account or group membership.
Modify Design Change the code in the module.
Administer Set permissions and change ownership.

There are a few things to consider regarding object permissions:


Return to Top of Page Return to top of page

Securing a Jet Database

There are several steps involved in securing a new or existing Jet database, but one item is absolutely essential for the security system to be effective. You must use the Access Workgroup Administrator to create a new and unique system database.

Here are the steps to follow to secure a new database:

  1. Create a System Database
  2. Create the Owner Account
  3. Add the Owner to Admins
  4. Remove the Admin User from the Admins Group
  5. Enable the Login Dialog
  6. Login as the Owner
  7. Create the Database
  8. Revoke All Permissions for Admin and the Users Group

Creating a System Database

To create a system database, start the Workgroup Administrator and click the Create... button. In the Workgroup Owner Information dialog box, enter your name, organization, and a workgroup ID of up to 20 characters. Before continuing, write these values down. If you ever need to recreate the system database, you will need to enter these values exactly as you enter them here. It's also a good idea to use a difficult to guess workgroup ID so no one else can create an identical system database. The workgroup administrator will then prompt you for a location for the new file (the file name is normally system.mda, although that name isn't required). After choosing OK again, you will see the Confirm Workgroup Information dialog. Verify that the information is correct and click OK. You will receive a message that you have successfully created the new system database. Dismiss this message and the original dialog for the Workgroup Administrator. When you're done, put the values for the name, organization and workgroup ID in a safe place where you can find them if you need them. Remember, you must use the exact same values if you ever need to recreate the system database.

Setting up Accounts

Once you have created a new system database, start Access and open any database. You will be logged on as the Admin user. From the Security menu, choose Users..., click New... and enter the name you wish to use for the owner account and a Personal ID for this account. Make sure you write these values down and store them in a safe place. If you ever need to recreate the account, you will have to enter the same values exactly as you enter them here. After creating the account, you can immediately add it to the Admins group.

Once the new owner account has been created and added to the Admins group, you can remove the Admin user from the Admins group. Remember, you must first add the new owner account to Admins before you can remove the Admin user since the Admins group must always have at least one member. At this point, you should also enable the login dialog box by assigning a password to the Admin user account.

Unless you have a need for a guest login, I also recommend that at this point you assign a difficult to guess password to the Guest user and remove the user from the Guests group. While the Guest account by default will have no permissions for any objects, disabling a free login gives you a small extra bit of insurance.

Creating the Database

After you have completed setting up the accounts, you are ready to create the database. First, you must quit and restart Access. With a password assigned to the Admin user, you will see the login dialog box. Enter the name of the owner account you created earlier (it won't have a password yet). From the empty database window, click New Database... on the File menu and create the new database.

To protect the owner account, you should immediately assign it a password. (As always, make the password difficult to guess.)

Before creating any objects, you should setup the default permissions you will use for users and groups. From the Security menu, choose Permissions.... In the permissions dialog, you will see your user account, Admin, and Guest. If you select Groups, you'll see Admins, Guests, and Users. Since at this point there are no objects in the database, each class of object will have only the entry in the list with the exception of the Database object type, which will show only . Starting with the current database, revoke all permissions for the Admin user. Next, revoke (at a minimum) Administer permission for the Users group.

As an added security measure, I revoke all permissions from the Users group for all objects and create my own equivalent of Users for the database. This ensures that only those users I have expressly granted authority to will be allowed even to open the database. Additionally, since the default Admin user will always be a member of Users, this also ensures that anyone who is not using your system database will not be able to open the database.

Once you have completed these steps, you should quit and restart Access, logging in using the new owner account and password. Before beginning to create database objects, you may wish to setup whatever user and group accounts you intend to use and assign default permissions to the various classes of database objects. This will make it easier to manage permissions later when you have a large number of objects created.


Return to Top of Page Return to top of page

Accessing a Secured Database

There are two keys to accessing a secured Jet database: the SystemDB property of the DBEngine and the Workspace object. Before setting up a workspace, you must set the SystemDB property to point to your system database so that you can use your user and group accounts, then use the appropriate account to create a Workspace object. Let's take a look at a code sample:

' Note: msUser, msPassword, and msSystemDB are module level variables whose
' values have been established elsewhere. mWS is a module level Workspace object

  DBEngine.SystemDB = msSystemDB
  Set mWS = DBEngine.CreateWorkspace(msUser, msUser, msPassword)

You'll need to establish the value of the system database, user name, and password before creating the workspace. Since the system database is typically a fairly stable property, you can probably store the value in the registry using SaveSettings and GetSettings. (In a really stable environment, you may even be able to get with coding the path to the system database as a constant.) The user name and password, however, are typically more dynamic (they may change every time the program is run). You will probably need to build a login dialog box to obtain these values.

Do NOT append your Workspace object to the Workspaces collection of the DBEngine. The Workspaces collection is shared by all instances of the DBEngine, so if the Workspace is appended, another application could loop through the collection and use your Workspace to bypass your login dialog box.

Once a Workspace has been established, you can proceed to call the OpenDatabase method to open your database file. Depending on the nature of the application and your preferred strategy for developing the application, you may wish to declare the Workspace and Database object variables so that they are persistent for the life of the application (created in the startup code as global or module variables) or create them on demand. At a mimimum, you will wish to declare they user name and password variables so their duration is the life of the program or the user will need to provide them every time you open a database. The "conventional wisdom" is to create data access objects on demand to lessen the possibility of a corrupted database if the application or machine should crash while the application is open. If you are willing to risk the possibility of corrupted files, you may choose to have the objects be persistent for the life of the program. If you decide to create them on demand, keep in mind that you will take a performance penalty for doing it.


Return to Top of Page Return to top of page

Programming Jet Security

In most cases, you can use Microsoft Access to manage security for your application. Access has a user interface for creating user and group accounts, assigning users to groups, and granting or revoking permissions. While the interface is somewhat clumsy to use, it is sufficient for most tasks. There are, however, some circumstances where you may need to manage security programmatically.

You may of course simply not like the Access interface and want to replace it. I don't like it myself and would probably write a replacement if I had the time. Here are some of the problems with the Jet security interface:

The lack of ability to see inherited permissions in particular is something I consider to be a serious deficiency in the Access security interface. This can make it extremely difficult to detect who has permissions for an object or what permissions any given user has for an object.

These are the steps you need to take to manage user and group accounts:

You can find sample code in the Visual Basic help files.

Granting and revoking permissions for database objects is done by using the Permissions property of (normally) the appropriate Document object. The Document objects are members of the Documents collection of the associated Container object. These objects are a common point of confusion for many Jet programmers, so let's take a closer look at them.

A Jet database has a large number of objects and collections. One of the most misunderstood is the Containers collection and it's children. When you look at a database using the Access database window, you'll see tabs for Tables, Queries, Forms, Reports, Macros, and Modules. Additionally, when looking at the database programmatically, you can see a TableDefs collection (representing saved tables), a QueryDefs collection (presenting saved queries), and from within Access, a Forms and Reports collection. If you look at an object model chart of a database, you'll also see a Containers collection having the following Container objects: Tables, Forms, Reports, Scripts, and Modules. Here's what the various objects represent:

Object Represents Usage
TableDefs Collection Saved Tables Use the TableDefs collection to see or modify the structure of the table.
QueryDefs Collection Saved Queries Use the QueryDefs collection to see or modify the structure of queries.
Forms Collection Open Forms This represents currently open forms and is used only at run-time within Access. You can see and modify form properties while the user is working with the form or even design the form programmatically.
Reports Collection Open Reports This is the same as the Forms collection, except it refers to open reports. Again, this collection is only used within Access and is not available in Visual Basic.
Tables Container Saved Tables and Queries Remember, tables and queries in Access share the same name space, thus the single container for both types of objects. Use the Tables container to get to the individual Document objects representing each saved table or query. It is the Document object that has the Permissions property.
Forms Container Saved Forms Unlike the Forms collection, the Forms container provides access to all saved forms and can be used to set form permissions.
Reports Container Saved Reports This is the same as the Forms container, but refers to reports. Use the Reports container to set permissions for reports.
Scripts Container Save Macros Why the Access developers called this "Scripts" instead of "Macros" is beyond me, but there it is. Scripts represents saved macros. Remember when assigning permissions for Macros that there can be many individually callable macros within a single Script document object. You can only set the permissions for the entire Document object, not the individual macros contained within it.
Modules Container Saved Modules Use the Modules container to set design time permissions for code modules in Access. Remember, there is no way to administratively prevent someone from running the code. You can only grant or revoke permission to see it or modify it.

To assign permissions, take the following steps:

When modifying the Permissions property, the key to remember is that the value is a bit field representing all available permissions. To grant a permission, use the bitwise Or operator with the appropriate permission constant. To revoke a permission, use the bitwise And Not operator. Be careful not to use the addition (+) or subtraction (-) operators.

The following example revokes and then grants permission to delete data from a table for the Users group:

  ' db object reference created elsewhere
  Dim doc As Document

  ' create object reference for Customers table
  Set doc = db.Containers("Tables").Documents("Customers")
  ' set UserName property to the Users group
  doc.UserName = "Users"
  ' revoke the Delete Data permission
  doc.Permissions = doc.Permissions And Not dbSecDeleteData
  ' grant it
  doc.Permissions = doc.Permissions Or dbSecDeleteData
  ' release reference
  Set doc = Nothing

Return to Top of Page Return to top of page

Suggestions for Managing Accounts

As with any user level security system, you can save yourself a considerable amount of administrative effort by using groups to assign permissions and using user accounts strictly to validate logins. I generally divide users into several potential groups:

With one exception, I always assign permissions only to groups rather than users. That exception is the database owner account. Rather than work with an individual user account, I create a special account for administrative use. In this case, the database owner is a role rather than an individual user. This account (often simply "dbo") is the account which actually owns the database and often owns many of its objects. The account has complete authority over all objects and data.

In addition to managing object permissions, another common use for user accounts is to track user activity. It can often be helpful to know who is creating and modifying data. However, in order to do this, you will need to code the appropriate insert, update, and delete procedures for the tables on which you wish to enable this type of tracking.

There are two general methods to enable this type of tracking. You can add fields to the table to store the name of the user who created or last modified a record or you can use a log table. Adding fields to the table is generally simpler, but using a log table can be more flexible and may provide better performance.

In either case, you will need to modify your code to make sure that updates are properly logged. If you are using Visual Basic to develop the application, this requires adding additional code anywhere the data is changed. In an Access application, this can be more complex since you will need to prevent users from opening the tables for modifications in the database window. One method of doing this is to use the security system to make the appropriate tables read-only, then using a priviledged Workspace in code to change data. Another method is to use queries designed with the WITH OWNERACCESS OPTION clause in the query. This allows you to design a query that allows modifications on a table for which the end user does not have permission. (In the Access query design window, set the Run Permissions to Owners to enable this option.)

Regardless of the method you choose, you may wish to restrict the number of tables which have activity tracking in order to minimize the associated overhead and coding complexity. Using a log table only for critical data and adding created by and/or modified by fields to other tables that are less sensitive can also be an acceptable compromise.


Return to Top of Page Return to top of page

| © 1997 Joe Garrick |
I don't know if this email is valid anymore, the webpages are no longer available on the Internet