Presented by: Paul Litwin
Paul Litwin is a senior consultant with MCW Technologies focusing on application development employing Microsoft® Access, Microsoft® Visual Basic® Microsoft® Office, Microsoft® SQL Server™, VBScript and Microsoft Internet technologies. He's the editor of Smart Access, a monthly newsletter for Microsoft Access developers (www.pinpub.com), and has written numerous articles and reviews for various publications, including Smart Access, Visual Basic Programmer's Journal, Visual Basic Developer, and PC World. Paul is a Microsoft Access MVP and a section leader on the VBPJ CompuServe forum and Windows Developer's Exchange (www.windx.com). He recently completed work on Microsoft Access 97 Developer's Handbook and has authored several books on Microsoft Access 2.0 and Microsoft Access 95. Paul trains developers for Application Developers Training Company and is a regular speaker at conferences
This talk has been adapted, with permission, from Access 97 Developer's Handbook by Paul Litwin, Ken Getz, and Mike Gilbert (Sybex 1997). Copyright © 1997 Sybex Inc.
Email: plitwin@mcwtech.com
Since Microsoft® Access 1.0, the Jet engine has offered a sophisticated workgroup-based security model (it's also referred to as a user-based security model) rather than the more common database-based model most other desktop database management systems use. Under the simpler file-oriented model, security revolves around a database and is self contained within the confines of that database. Each database has its own security system that is independent of others. In contrast, in Jet's workgroup-based security model, every database used by members of a workgroup shares the same security system.
Jet's workgroup-based security is based on users and their permissions, not passwords. Most desktop databases employ password-based security if they implement any security features at all. (Jet also offers a limited password-based system that's briefly discussed later in this paper.) In these systems users enter a password that identifies them to the system as valid users. Every user who shares a given security level shares that same password, so the system is incapable of identifying individual users. In contrast, Jet's security model requires each user to have both a user name and a password. The password merely verifies that users are who they claim to be. Once verified, the password leaves the picture. With Jet, users manage their own individual passwords, which they can change at will without affecting other users. Passwords can be more secure since they're not shared by lots of users.
Jet security is made up of two parts:
Security in Jet is always on; it can't be turned off. The security system, however, remains invisible until you're ready to use it. This is possible because of the presence of several default user and group accounts.
Every workgroup file starts out with two predefined group accounts (Admins and Users) and one predefined user account (Admin). When a user starts an Microsoft Access session, Jet always attempts to log on the user as Admin with a blank password. If this logon attempt fails, only then does Jet prompt the user for a user name and password using the Logon dialog. Thus, as long as you keep the Admin password blank (a zero-length string), security remains invisible.
With the introduction of Jet 3.0 (Microsoft® Access 95), Microsoft has added a much simpler alternative to workgroup-bases security: database passwords. This system allows you to set a single password for a database that all users must know to open the database. While much simpler to implement and use, this system is very easily compromised because all users use the same password. In addition, it doesn't let you track individual users' activity in a shared database. However, you can use both workgroup-based security and database passwords at the same time.
You set a database password by selecting Tools/Security/Set Database Password. Once this option is set, whenever you open the database you will be met with the Password Required dialog. You must have the database open exclusively to set the database password.
The remainder of this paper focuses on the more powerful workgroup-based security model.
At the interface level, a workgroup is defined as a group of users who work together. At the Jet engine level, a workgroup is defined as all users sharing the same workgroup file.
The workgroup file is a special encrypted database, by default called SYSTEM.MDW, that Microsoft Access and Jet use to store a number of pieces of information having to do with users, including:
In a multiuser environment you can choose to place the workgroup file either on the file server or on each workstation. Usually, you'll want to place it on the file server, which makes the maintenance of user and group accounts much easier. On the other hand, if your security settings are fairly static, you could reduce network traffic by placing a copy of the workgroup file on each workstation.
Microsoft includes a utility program called the Workgroup Administrator (WRKGADM.EXE) you can use to create a new workgroup (workgroup file) or to change to another workgroup. You won't find this program on your Start menu, nor will you find the executable file in your Microsoft Access directory. The Microsoft Access 97 install program places WRKGADM.EXE in the \Windows\System directory. Go figure! Once you've located the Workgroup Administrator program, run it and choose to create a new workgroup, you are met with the Workgroup Owner Information dialog, as shown in Figure 1.
Figure 1: The information entered into the Workgroup Owner Information dialog is used to uniquely identify a workgroup.
Since the workgroup file is such a vital part of security, we recommend regularly backing up this file and storing a copy of it safely off site. You should also consider storing a copy of the Name, Organization, and Workgroup ID fields in hard-copy form in a secure off-site location.
Based on your entries in the Workgroup Owner Information dialog, Jet generates an encrypted binary ID called the Workgroup security identifier (SID). Jet uses the Workgroup SID to uniquely identify the Admins group account in the workgroup. The significance of this built-in account is discussed in the section "Special Status: Built-In Accounts" later in this paper.
Jet uses user and group accounts to dole out security permissions. Only users and groups can have permissions. Both types of accounts share the same name space, so you need to ensure that all names are unique for a workgroup. Thus, you can't have a user and a group with the same name.
In Microsoft Access you use the Tools/Security/User and Group Accounts command to create and manage user and group accounts (see Figure 2).
Figure 2: The user Geoff is a member of the Users, Employees, and Programmers groups
Only members of the Admins group can add, delete, and change the membership for user and group accounts, but any user can view accounts (this is change from Microsoft Access 2.0) and change his or her account password.
When you create a new user or group account in Jet, you must enter a non-blank, 4–20 character, case-sensitive personal identifier (PID). Jet combines the name of the account with the PID to create a SID for each user or group account. Once you've entered a PID, you can never view or change it.
We recommend that only a single database administrator create accounts and PIDs and that this individual keep a written off-site record.
After you create a new user account, you can add an optional 1–14-character, case-sensitive account password using the Change Logon Password tab of the User and Group Accounts dialog. (Unlike PIDs, passwords are optional.) Jet uses passwords only at logon time to verify the identity of a user.
Only users can change their own passwords, but members of the Admins group can clear another user's password.
Both passwords and PIDs are stored in the workgroup file in an encrypted format.
Jet uses the internally generated account SIDs to uniquely identify user and group accounts across workgroups. Except for some of the special built-in accounts that are discussed in the section "Special Status: Built-In Accounts" later in this paper, Jet treats accounts in different workgroups with the same name, but different PIDs (and thus different SIDs) as distinct.
A group account is more than simply a collection of users. In many situations you can use a group account in place of a user account. Although you cannot log on as a group, you can do almost anything else with a group account, including owning objects. A group account may not own a database.
As mentioned earlier in this paper, the Jet security system includes several built-in accounts that make it possible for security to remain invisible until it's needed. These built-in accounts include the Admin user and the Admins and Users groups. It's important that you understand how these "special status" accounts work; otherwise your database won't be secure. The following table describes the three built-in accounts:
| Account Type | Account | Same SID for All Workgroups? | Comments |
| User | Admin | Yes | Default user account |
| Group | Admins | No | Members have special privileges |
| Group | Users | Yes | All user accounts are members of Users |
None of the special accounts can ever be deleted from a workgroup. Each of these accounts is described in more detail in the next few sections.
Admin user
All new workgroups initially contain the Admin user account with a blank password. As mentioned previously, Microsoft Access always attempts to log you on as the Admin user with a blank password. Only if this logon attempt fails does Microsoft Access prompt you for a user name and password.
You cannot delete the Admin user, but you can remove it from the Admins group as long as Admins has at least one other member.
Admins group
The Admins group is uniquely identified across workgroups. Jet requires that there always be at least one member of the Admins group. This requirement makes it impossible to have a workgroup with no administrator.
Members of the Admins group have special, irrevocable administrative rights. Their membership in Admins, however, is revocable by another Admins member. As long as they are members of Admins, they can grant themselves permissions to all database objects in the databases in their workgroup. In addition, members of Admins always have the ability to manage user and group accounts in their workgroup.
Users group
The Users group is the default group for all Microsoft Access users. All built-in user accounts-as well as new user accounts created using the Microsoft Access UI-will be members of the Users group. Microsoft Access won't allow you to remove users from the Users group.
By default, the Users group gets full permissions on newly created objects.
Using the Microsoft Access user interface (UI), you assign permissions to database objects with the Tools/Security/User and Group Permissions command (see Figure 3). Although you can change only one type of object at a time, you can select multiple objects (in contiguous or discontiguous groups) in the Permissions dialog.
Figure 3: The Programmers group account has ReadDesign and ModifyDesign permissions for the basSecurityUtilities module.
Each database container object in Jet has a set of associated permissions you can set. Each type of object has a different set of settable permissions. For example, tables and queries don't have an Open/Run permission, but they have several permissions that control how data may be read or updated. On the other hand, forms, reports, and macros have no data permissions but do have an Open/Run permission.
Permissions are not completely independent of each other; some permissions imply other permissions. For example, you can't have UpdateData permissions if you don't also have ReadDesign and ReadData permissions. Thus, UpdateData permission also implies these other permissions.
In addition to setting permissions on existing objects, you can set permissions on new objects. You do this by choosing <New objectname> in the User and Group Permissions dialog (see Figure 3). This setting does not control the ability to create new objects; it controls only the permissions the account will receive for new objects. Although you can remove all permissions for new objects, this will not prevent users from creating new objects. In addition, since they will become the owner of any objects they create, they can always grant themselves Administer rights to these objects.
By using DAO, however, you can prevent users from creating new tables and queries.
Users in the Jet security model have both implicit and explicit permissions. Explicit permissions are those permissions explicitly given to users and associated directly with a user account. Implicit permissions are those permissions users receive because of their membership in groups.
A user's set of permissions for an object will be based on the union of the user's explicit permissions and implicit permissions. A user's security level is always the least restrictive of the user's explicit permissions and the permissions of any and all groups to which the user belongs.
In addition to the permissions that are granted to accounts, you need to be aware of ownership, because database owners and object owners have special privileges.
The user who creates a database is the database's owner. This user maintains special irrevocable rights to the database. This user will always be able to open the database. Only user accounts, not group accounts, can own databases.
Database ownership cannot be changed, but you can always create a new database using a different user account and import all the database's objects into another database.
Each database container object also has an owner. Initially, this is the user who created the object and may or may not be the same user account as the database owner. You can use the Change Owner tab of the Tools/Security/User and Group Permissions command to view and change object owners (see Figure 4).
Figure 4: Using the Change Owner dialog, Alicia is about to change the ownership for four tables from Admin to the Managers group.
The new owner for an object may be a group account.
Queries created in Microsoft Access QBE have a property, RunPermissions, that governs whether Jet uses the query user's (the person running the query) permissions or the owner's permissions when checking the security permissions for each of the source tables in a query. In Microsoft Access SQL, setting this property to "Owner's" translates to the "WITH OWNERACCESS OPTION" clause.
This property allows you to present data to users who lack access rights to the underlying tables. Using this feature, you can effectively apply column-level and row-level security to a table.
For example, using an OwnerAccess query, you could let members of the Programmers group view and update all the columns in the tblEmployee table except for the Salary field. To do this, you would perform the following steps:
As good as Jet security is, a very knowledgeable hacker equipped with a low-level disk editor might be able to directly open the .MDB file and break into your database. The only way to guard against such a hacker is to encrypt the database.
Encrypting a database does not secure it. It is only one of a series of steps for properly securing a database. Only the database owner or members of the Admins group can encrypt or decrypt a database.Using Microsoft Microsoft Access, you can encrypt or decrypt a database using the Tools/Security/Encrypt/Decrypt Database command. The Microsoft Access Security Wizard encrypts databases as its final step in securing a database.
Encryption has two negative side effects. First, it reduces database performance by approximately 10 to 15 percent. Second, it makes the database uncompressible by programs such as PKZip, LHA, Stacker, and DriveSpace.
Programming security with DAO revolves around two object hierarchies that correspond to the division in Jet security discussed in the section "Two Parts to Security" earlier in this paper:
| Part of Security | Corresponding DAO Object Hierarchy |
| User and group accounts and their passwords | Users and Groups hierarchy |
| Object permissions | Containers and Documents hierarchy |
You manipulate user and group accounts-the elements of security that are stored in the workgroup file-using the object hierarchy shown in Figure 4.
Figure 4: Data access objects hierarchy for users and groups.
User and group objects are direct descendants of a workspace object; they are independent of any database objects. This is consistent with the physical location of the user and group account information-in the workgroup file.
The user and group object hierarchies overlap. Each user object contains a Groups collection that contains the names of all the groups to which a user belongs. Similarly, each group object contains a Users collection that contains the names of all users belonging to that group.
You manipulate object permissions-the elements of security that are stored in databases-using the object hierarchy shown in Figure 5.
Figure 5: Data access objects hierarchy for containers and documents. You use these object collections to manage security permissions.
Unlike user and group objects, permissions are manipulated using container and document objects that are descendants of the database object. Again, this is consistent with the physical location of these security elements.
Using DAO, you can create, delete, and list user and group accounts. In addition, you can list the groups to which a user belongs and the users who are members of a group. Jet 3.5 lets any user view user and group account information, although only Admins members can update account information.
Users collections and user objects
There are two types of collections of users: collections of a workspace and collections of a group account. The Users collection for a workspace contains all the user accounts for that workspace. The Users collection for a group account contains all the members of the group.
The Users collection has a single property, Count, and three methods, described in the following table.
| Type | Property/Method | Purpose |
| Property | Count | The number of user objects contained in the collection |
| Method | Append | Adds a new user to a collection |
| Delete | Removes a user from a collection | |
| Refresh | Refreshes the collection |
The Users collection contains user objects. User objects have three properties and two methods, described in the following table.
| Type | Property/Method | Purpose |
| Property | Name | Name of the user. Read/write for new users not yet appended to the Users collection. Read-only otherwise |
| Password | Case-sensitive password for the user account. Write-only for new users not yet appended to the Users collection. Not available otherwise | |
| PID | Case-sensitive personal identifier for the user account. Write-only for new users not yet appended to the Users collection. Not available otherwise | |
| Method | CreateGroup | Creates a new group object. When appended to the user object's Groups collection, this method adds the user to that group |
| NewPassword | Replaces an existing password with a new one |
For example, you could use the following subroutine to enumerate the user accounts in the workgroup to the Debug window:
Sub ListUsers()
Dim wrk As WorkSpace
Dim usr As User
Set wrk = DBEngine.Workspaces(0)
Debug.Print "The Users collection has the following " & _
wrk.Users.Count & " members:"
For Each usr In wrk.Users
Debug.Print usr.Name
Next usr
End Sub
If you wished instead to list only user accounts that were members of the Managers group, you could use the following:
Sub ListManagers()
Dim wrk As WorkSpace
Dim grpManagers As Group
Dim usr As User
Set wrk = DBEngine.Workspaces(0)
Set grpManagers = wrk.Groups!Managers
For Each usr In grpManagers.Users
Debug.Print usr.Name
Next usr
End Sub
To create a new user account, you use the CreateUser method of a workspace object.
Groups collections and group objects
There are two types of collections of groups: collections of a workspace and collections of a user account. The Groups collection for a workspace contains all the group accounts for that workspace. The Groups collection for a user account contains all the groups to which that user belongs.
The Groups collection has a single property and three methods, described in the following table.
| Type | Property/Method | Purpose |
| Property | Count | The number of group objects contained in the collection |
| Method | Append | Adds a new group to a collection |
| Delete | Removes a group from a collection | |
| Refresh | Refreshes the collection |
The Groups collection contains group objects. Group objects have two properties and a single method, described in the following table.
| Type | Property/Method | Purpose |
| Property | Name | Name of the group. Read/write for new groups not yet appended to the Groups collection. Read-only otherwise |
| PID | Case-sensitive personal identifier for the group account. Write-only for new groups not yet appended to the Groups collection. Not available otherwise | |
| Method | CreateUser | Creates a new user object. When appended to the group object's Users collection, this method adds the user to the group |
Groups don't have passwords; you can't log on as a member of a group.
You might use the following code to enumerate the names of all the groups in the Groups collection of the default workspace:
Sub ListGroups()
Dim wrk As WorkSpace
Dim grp As Group
Set wrk = DBEngine.Workspaces(0)
Debug.Print "The Groups collection has the following " & _
wrk.Groups.Count & " members:"
For Each grp In wrk.Groups
Debug.Print grp.Name
Next grp
End Sub
To create a new group account, you use the CreateGroup method of a workspace object.
Creating new accounts
You can use the adhCreateUser function, located in the basSecurityUtilities module of OFF412.MDB, to create a new user account. It takes as its parameters the name of the new user, the new user's PID, and the new user's password. It returns True if successful. This function, which requires the user running it to be a member of the Admins group, is shown here:
Function adhCreateUser(ByVal strName As String, _
ByVal strPID As String, ByVal strPW As String) As Boolean
Dim wrk As Workspace
Dim usrNew As User
Dim strMsg As String
Const adhcProcName = "adhCreateUser"
adhCreateUser = False
Set wrk = DBEngine.Workspaces(0)
'Create new user account and append to Users collection
Set usrNew = wrk.CreateUser(strName, strPID, strPW)
wrk.Users.Append usrNew
'Must also add user account to Users group
usrNew.Groups.Append wrk.CreateGroup("Users")
adhCreateUser = True
End Function
Creating a new user is much like creating any new object with DAO. adhCreateUser works by creating a new user object using the CreateUser method of the default workspace. The new user is created when the user's object is appended to the workspace's Users collection. To be consistent with the Microsoft Access UI-and more important, to make it so the new account will be able to open databases in the workgroup-adhCreateUser finishes by appending the new account to the built-in Users group.
basSecurityUtilities also includes a similar function, adhCreateGroup, for creating new group accounts.
Checking for group membership
You can check whether a user is a member of a group using adhIsGroupMember. This function takes as its input the name of the user and the group and returns True if the user is a member. adhIsGroupMember is shown here (if the optional varUser parameter is missing, it defaults to the current user):
Function adhIsGroupMember(ByVal strGroup As String, _
Optional ByVal varUser As Variant) As Boolean
On Error GoTo adhIsGroupMemberErr
Dim wrk As Workspace
Dim usr As User
Dim grp As Group
Dim strMsg As String
Dim intErrHndlrFlag As Integer
Dim varGroupName As Variant
Const adhcFlagSetUser = 1
Const adhcFlagSetGroup = 2
Const adhcFlagCheckMember = 4
Const adhcFlagElse = 0
Const adhcProcName = "adhIsGroupMember"
adhIsGroupMember = False
'Initialize flag for determining
'context for error handler
intErrHndlrFlag = adhcFlagElse
Set wrk = DBEngine.Workspaces(0)
'Refresh users and groups collections
wrk.Users.Refresh
wrk.Groups.Refresh
If IsMissing(varUser) Then varUser = CurrentUser()
intErrHndlrFlag = adhcFlagSetUser
Set usr = wrk.Users(varUser)
intErrHndlrFlag = adhcFlagSetGroup
Set grp = wrk.Groups(strGroup)
intErrHndlrFlag = adhcFlagCheckMember
varGroupName = usr.Groups(strGroup).Name
If Not IsEmpty(varGroupName) Then
adhIsGroupMember = True
End If
adhIsGroupMemberDone:
On Error GoTo 0
Exit Function
adhIsGroupMemberErr:
Select Case Err
Case adhcErrNameNotInCollection
Select Case intErrHndlrFlag
Case adhcFlagSetUser
strMsg = "The user account '" & varUser & _
"' doesn't exist."
Case adhcFlagSetGroup
strMsg = "The group account '" & strGroup & _
"' doesn't exist."
Case adhcFlagCheckMember
Resume Next
Case Else
strMsg = "Error " & Err.Number & ": " & _
Err.Description
End Select
Case adhcErrNoPermission
strMsg = "You don't have permission to perform " & _
"this operation."
Case Else
strMsg = "Error " & Err.Number & ": " & Err.Description
End Select
MsgBox strMsg, vbCritical + vbOKOnly, "Procedure " & _
adhcProcName
Resume adhIsGroupMemberDone
End Function
This function checks for membership using the following statement:
varGroupName = usr.Groups(strGroup).Name
This statement attempts to set a variable to the Name property of the group object in the Groups collection of the user object. There's nothing special here about the Name property; any readable property of the group object would suffice. Because of the symmetry of the Users and Groups collections, adhIsGroupMember could have also checked for the name of the user object in the Users collection of the group object. The following alternate statement would yield the same result:
varUserName = grp.Users(strUser).Name
adhIsGroupMember doesn't require the user running it to be a member of the Admins group.
The adhIsGroupMember function would be useful if you wish to change application functionality based on the security level of the user. For example, you might disable or hide certain command buttons if a user is of one level of security while enabling or showing these same buttons for a higher level of user.
The following code, from frmCheckGroup, disables the cmdEdit command button if the user is not a member of the Admins group:
' Enable Edit button if member of Admins
If adhIsGroupMember("Admins") Then
Me!cmdEdit.Enabled = True
Me!cmdEdit.ControlTipText = _
"Toggles between Edit and Browse modes."
Else
Me!cmdEdit.Enabled = False
Me!cmdEdit.ControlTipText = _
"You're not a member of Admins, so you can't edit data."
End If
The control would only be enabled if the current user was a member of the Admins group.
Permissions are properties of documents and their containers. Like other DAO collections, the Containers collection and Documents collection have a single property, Count, that indicates the number of objects in the collection. These two collections also have a single method, Refresh, which you can use to make sure the collections are current.
Document and container objects
Container and document objects have several properties. They are listed in the following table.
| Object | Property | Description |
| Container | Inherit | Determines whether any changes are inherited by new objects. If you set permissions on a container and set Inherit to True, Jet uses these permissions when creating new documents for the container |
| Name | Read-only. The name of the container. For example, the Tables container contains a database's tables and queries | |
| Owner | Read/write. The user or group account that owns the object. By default, all object containers are owned by the engine user | |
| Permissions | Read/write. A Long Integer that stores explicit permission information for the container. When you use this property with the Inherit property, you can set permissions for new documents of a container | |
| AllPermissions | Read-only. A Long Integer that stores the union of explicit and implicit permission information for the container | |
| UserName | Read/write. When you read or write permissions for a container, the permissions are account specific. By default, this property points to the current user. You use UserName to view or set permissions for different user and group accounts | |
| Document | Container | Read-only. The container to which the document belongs |
| DateCreated | Read-only. The date the document was created | |
| LastUpdated | Read-only. The date the document's schema was last changed | |
| Name | Read-only. The name of the document | |
| Owner | Read/write. The user or group account that owns the object. By default, the owner is the document's creator | |
| Permissions | Read/write. A Long Integer that stores explicit permission information for the document | |
| AllPermissions | Read-only. A Long Integer that stores the union of explicit and implicit permission information for the document | |
| UserName | Read/write. When you read or write permissions for a document, the permissions are account specific. By default, this property points to the current user. You can use UserName to view or set permissions for different user and group accounts |
Permission constants
Microsoft predefines several database security constants you can use to simplify the reading and writing of permissions. These constants are outlined in following table. They also can be viewed with the Object Browser. The use of these constants is discussed in the next few sections.
| Constant | Meaning |
| dbSecNoAccess | No access to the object |
| dbSecFullAccess | Full access to the object |
| dbSecDelete | Can delete the object |
| dbSecReadSec | Can read the object's security-related information |
| dbSecWriteSec | Can alter access permissions |
| dbSecWriteOwner | Can change the Owner property setting |
| dbSecCreate | Can create new documents; valid only with a Container object |
| dbSecReadDef | Can read the table definition, including column and index information |
| dbSecWriteDef | Can modify or delete the table definition, including column and index information |
| dbSecRetrieveData | Can retrieve data from the Document object |
| dbSecInsertData | Can add records |
| dbSecReplaceData | Can modify records |
| dbSecDeleteData | Can delete records |
| dbSecDBAdmin | Gives user permission to make a database replicable, change the database password, and set startup properties |
| dbSecDBCreate | Can create new databases; valid only on the Databases container object in the workgroup file (SYSTEM.MDW) |
| dbSecDBExclusive | Can open the database exclusively |
| dbSecDBOpen | Can open the database |
| acSecMacExecute | Can run the macro |
| acSecMacReadDef | Can read the definition of the macro |
| acSecMacWriteDef | Can modify the definition of the macro |
| acSecFrmRptExecute | Can open the form or report |
| acSecFrmRptReadDef | Can read the definition of the form or report and its module |
| acSecFrmRptWriteDef | Can modify the definition of the form or report and its module |
| acSecModReadDef | Can read the definition of the global module |
| acSecModWriteDef | Can modify the definition of the global module |
Reading permissions
You can read the permissions of an object simply by checking the value of the Permissions or AllPermissions property of the object. Permissions returns explicit permissions only, whereas AllPermissions returns the union of explicit and implicit permissions. They both return the Long Integer corresponding to the user's permissions for the object. For example, you could query the permission of the tblOrder table and store the value into the variable lngPermission with the following assignment statement (assuming you have previously set the db object variable to point to a database):
lngPermission = _ db.Containers!Tables.Documents!tblOrder.Permissions
Often, you'll want to check whether a user has some minimum permissions to an object. You can do this using bitwise arithmetic (also referred to as bit twiddling). This works because Jet stores each individual permission as a different bit of the 4-byte Long Integer value. You check a permission value for a specific set of bits-some permissions are actually the result of setting several bits-by using the And operator to mask off the bits in which you are interested and comparing the result of the operation to these same bits. Using the predefined permission constants makes this easy:
fOk = ((doc.Permissions And dbSecConstant) = dbSecConstant)
fOk will be set to True if the document object has that permission-as defined by dbSecConstant-set to True, and False if the document object does not.
For example, you could use the following function (found in the basSimpleExamples module of OFF412.MDB) to determine whether the user Kizzie has read permission for tblCustomer:
Function CanKizzieRead() As Boolean
' Simple example that illustrates correct
' syntax to check a specfic permission
' against Permissions property.
' Checks explicit permissions only for:
' User = Kizzie,
' Object = tblCustomer,
' Permission = dbSecRetrieveData.
Dim db As Database
Dim doc As Document
Set db = CurrentDb()
Set doc = db.Containers!Tables.Documents!tblCustomer
doc.UserName = "Kizzie"
CanKizzieRead = _
((doc.Permissions And dbSecRetrieveData) _
= dbSecRetrieveData)
End Function
If Kizzie has ReadData permission to tblCustomer, CanKizzieRead returns True; otherwise it returns False. The trick in this example is to use the bitwise And operator to mask off the complete permissions with only the permission you are interested in-in this case, dbSecRetrieveData (ReadData permission).
CanKizzieRead checks only for explicit permissions. We could have also checked Kizzie's implicit permissions by replacing the last part of the function with:
CanKizzieRead = ((doc.AllPermissions And dbSecRetrieveData) _ = dbSecRetrieveData)
Another function in the OFF412.MDB, CanKizzieReadAtAll, checks the AllPermissions property.
There's another way to check a permission value against a constant that works only if one bit is set on in the constant:
' Don't do this fOk = (doc.Permissions And dbSecConstant) <>0
This method, however, will fail with constants that have more than one bit set. Thus, because many of the security constants have multiple bits set, you shouldn't use it.
Writing permissions
Writing permissions is similar to reading them. You have two choices when writing the permissions of an object:
To replace a set of permissions, you simply set the permissions to the new value. For example, you could change the permission for tblOrder to give the user ModifyDesign permission using the following code:
Set doc = db.Containers!Tables.Documents!tblOrder doc.Permissions = dbSecWriteDef
To add a permission on top of the existing permission set, you use the bitwise Or operator. For example, you could use the following code to add ModifyDesign permission to the existing set of permissions for tblOrder:
Set doc = db.Containers!Tables.Documents!tblOrder doc.Permissions = doc.Permissions Or dbSecWriteDef
Using this method of assigning permissions is often preferable because it guards against inadvertently removing other permissions the user may have. For example, if the user also had ReadData permission to tblOrder, that permission would be preserved using this technique. This would not be true in the previous example.
To subtract a permission from a user while preserving all other permissions, you use a bitwise And Not operation. For example, to take away the same permission from a user, replace the second line in the preceding example with the following:
doc.Permissions = doc.Permissions And Not dbSecWriteDef
The following subroutine can be used to toggle Kizzie's ReadData permission for tblCustomer:
Sub SetKizzieRead(fRead As Boolean)
' Simple example that illustrates correct
' syntax to set a specfic permission.
' Sets permission for:
' User = Kizzie,
' Object = tblCustomer,
' Permission = dbSecRetrieveData.
Dim db As Database
Dim doc As Document
Set db = CurrentDb()
Set doc = db.Containers!Tables.Documents!tblCustomer
doc.UserName = "Kizzie"
If fRead Then
doc.Permissions = _
doc.Permissions Or dbSecRetrieveData
Else
doc.Permissions = _
doc.Permissions And Not dbSecRetrieveData
End If
End Sub
For example, if you typed the following into the Debug window, Kizzie would have ReadData permission for tblCustomer:
Call SetKizzieRead(True)
Note that this code affects Kizzie's explicit permissions only. The only way to affect a user's implicit permissions is to change the permission properties of any groups to which he or she belongs.
Securing a database properly takes great care. It's very easy to make a mistake and leave yourself open to possible security intrusions. Fortunately, Microsoft has created a wizard that ships with Microsoft Access to help secure databases properly. Using the Security Wizard, however, does not automatically guarantee a secure database; the wizard is only one step in a series of steps that you must follow to properly secure a database:
Warning Don't skip any steps or you run the risk of thinking you have a secured database when you do not.
Note: New for Microsoft Access 97, the Security Wizard now removes the database’s Open/Run permission for all user and group accounts except the Admins group and the user account of the user who ran the wizard. This means that users who are not members of the Admins group will not be able to open the database after you’ve run the wizard. You can, of course, add this permission to any user or group in the workgroup.
You can reverse the process of securing a database by following these steps:
The trick to this technique is to give an unsecured group-Users-full permissions on all the objects and to then transfer ownership of the database and all its objects to an unsecured user-Admin.
The Jet security system is powerful and fully programmable. Because of its power, however, it can be difficult to grasp. Give yourself time to grasp it all-it may take a while.
For more information on security, check out the following resources:
Access 97 Developer's Handbook, by Paul Litwin, Ken Getz, and Mike Gilbert (Sybex 1997).
This paper is excerpted from the book. The security chapter in the book is much more detailed and includes a library of reusable security functions you can use in your code. The book is available in most major bookstores as well as many electronic bookstores, including www.caryp.com and www.amazon.com.
Control Jet Security, by Paul Litwin. Visual Basic Programmer's Journal, September 1996 (Volume 6, Number 10).
This article details Jet 3.0 security and how to control it from Visual Basic 4.0. Includes code samples for both 16-bit and 32-bit versions of Visual Basic.
Microsoft Access Security White Paper
The Microsoft Access 95 version of the Microsoft security white paper can be found at ftp://ftp.microsoft.com/kb/softlib/mslfiles/SECURE70.EXE. At the time this paper went to press, the Microsoft Access 97 version of the white paper was not yet available. There are very little changes in security, however, between Microsoft Access 95 and Microsoft Access 97. (It's also possible that the Microsoft Access 97 version of the white paper will be available by the time you read this. Look for a similarly-named file at http://www.microsoft.com/kb/softlib/mslfiles/.)
Microsoft Access Security FAQ
The Microsoft Access Security Frequently Asked Questions (FAQ) paper, co-authored by Andy Baron, Mary Chipman, Chris Bell, and Paul Litwin, can be found at http://www.wji.com/access/w1002314.html. This paper provides answers to a number of commonly asked security questions for all versions of Microsoft Access and Visual Basic.
© 1997 Microsoft Corporation. All rights reserved.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market
conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Microsoft and Visual Basic are registered trademarks and SQL Server is a trademark of Microsoft Corporation.
Other product or company names mentioned herein may be the trademarks of their respective owners.