Microsoft Access User-Level Security
User-level security is the most flexible and secure method of protecting the sensitive data, the code, and the design of objects in your application. In addition, user-level security is the only form of Microsoft Access security that allows you to establish different levels of access to sensitive data and objects.
Before you begin establishing user-level security for your application, make sure you understand the Microsoft Access user-level security model. The following sections discuss the significance of a workgroup-based user-level security model; how you can create user and group accounts to create a workgroup and establish permissions; and how you can use the tools that Microsoft Access provides, such as the User-Level Security Wizard, to help secure your databases. You can also establish security and perform security actions that aren't available using the Microsoft Access user interface by using Data Access Objects (DAO) in Visual Basic.
See Also For more information on establishing security using Visual Basic, see "Using Data Access Objects to Establish User-Level Security" later in this chapter.
The Microsoft Access User-Level Security Model
Unlike the security models of most other database systems, the primary form of security in Microsoft Access is user-level security rather than share-level security. (Microsoft Access does provide a simple form of share-level security: the ability to protect opening a database with a password.) Microsoft Access user-level security is similar to the security used in most network environments, such as Microsoft Windows NT Server. When users start Microsoft Access, they enter a name and password in the Logon dialog box. The password confirms the user's identity by checking the name and password against data in the workgroup information file. The password can be changed at any time by that user without affecting anyone else on the system. When users log on, each user is also identified as a member of one or more groups.
When a user opens a secured database, Microsoft Access determines the user's level of access to an object (including the database itself) by checking the set of permissions assigned to that user for that object. Different users and groups can have different permissions for the same objects. Every time the user tries to perform an action on an object, such as opening a form, running a query, or modifying the data in a table, Microsoft Access checks to see if the user, or any of the groups to which the user belongs, has the necessary permissions to carry out the operation. If so, the action is performed. If not, Microsoft Access informs the user that he or she doesn't have permissions to perform the requested action, and causes the operation to fail.
In contrast, database systems that provide share-level security associate passwords with specific objects, and users must supply passwords to access those objects. Any user who knows the password to a specific object can access it, and a user's level of access is determined by the kind of password that user has been given. For example, a manager may be given an update password for a table which allows him or her to change records, while a general staff member may be given a read password which only allows him or her to view records.
You can create a system similar to this with Microsoft Access user-level security by creating a Managers group with Update Data permission and a Staff group with only Read Data permission, and then assigning each user to the appropriate group. Users don't supply passwords when accessing the objects, because they have been identified as a member of the appropriate group when they logged on to Microsoft Access.
Where Is Workgroup and Permission Information Stored?
Microsoft Access stores information about users and groups in a database called a workgroup information file. A workgroup information file stores:
The name of each user and group.
The list of users that make up each group.
The encrypted logon password of each user.
The security identifier (SID) of each user and group. The SID is a machine-generated, non-readable binary string that uniquely identifies the user
or group.
Each workgroup information file defines a workgroup, and can be used by many Microsoft Access databases. A workgroup is a group of users in a multiuser environment who share data and the same workgroup information file. You manage users, their passwords, and the groups they are assigned to by using the User And Group Accounts dialog box (Tools menu, Security submenu).
See Also For more information on users and groups, see "Users and Groups" later in this chapter.
Permissions that you assign to users and groups for the objects in a database are stored in hidden system tables within the database. Even if a new workgroup information file is established, the permissions associated with the objects in a database don't change. You assign permissions on the Permissions and Change Owner tabs of the User And Group Permissions dialog box; this information affects the objects in the open database, not the workgroup information file.
The following illustration shows how these elements of Microsoft Access user-level security are related.
The location of the workgroup information file in use is specified in the Windows Registry. You can create a new workgroup information file or specify which file to use with the Workgroup Administrator. You can also specify which file to use when starting Microsoft Access with the /wrkgrp command-line option.
See Also For more information on using the Workgroup Administrator, search the Help index for "Workgroup Administrator." For more information on how the workgroup information file is specified in the Registry, search the Help index for "workgroup information file, specifying in Registry."
Users and Groups
You use the User And Group Accounts dialog box (Tools menu, Security submenu), to create new user and group accounts for your workgroup. When you create a new user or group account, you supply a name and a personal identifier (PID). The PID is a case-sensitive alphanumeric string that can be from 4 through 20 characters long. Microsoft Access feeds the name and the PID into an encryption program to generate the security identifier (SID) for that account. The SID is used internally by Microsoft Access to uniquely identify and validate users and groups. It is important to note that the PID is not a password. It is used only when creating user and group accounts. A user's password can be designated only after an account for that user has been created.
Important Be sure to write down the exact, case-sensitive names and PIDs that you use to create user and group accounts and keep them in a secure place. If your workgroup information file becomes damaged or lost and you need to re-create it, you to must re-enter the exact names and PIDs to re-create identical SIDs for the user and group accounts you created. Additionally, you need to use the Workgroup Administrator to create an identical workgroup information file to contain these accounts by re-entering the name, company name, and workgroup ID (WID) used when it was originally created. For more information on creating an identical workgroup information file, see "Admins" later in this section.
A Microsoft Access workgroup information file contains the following default user and group accounts:
Admin The default user account. Before user-level security is established, all users are automatically logged on using the Admin user account when they start Microsoft Access. As a result, the Admin user owns and has full permissions on all objects created in the database. Because the Admin user's SID is identical across all installations of Microsoft Access and Visual Basic, all objects that the Admin user owns or has permissions to are open to anyone using another copy of Microsoft Access or Visual Basic. When establishing user-level security, it's important to make sure that the Admin user doesn't own or have any permissions on objects that you want to secure. For assistance with this process, run the User-Level Security Wizard provided with Microsoft Access.
See Also For more information on running the User-Level Security Wizard, see "Establishing Security with the User-Level Security Wizard" later in this chapter.
Admins The system administrator's group account. There must be at least one member of the Admins group at all times. Before user-level security is established, the default Admin user is the only member of the Admins group. Because members of the Admins group have the power to perform actions such as changing permissions, modifying user and group memberships, and clearing users' passwords, you want to be careful when assigning users to this group.
Important The Microsoft Access Setup program uses only the user's name and the company name provided during setup to create the SID for the Admins group of the default workgroup information file. Because these two values are available from the Microsoft Access Help menu, someone trying to breach security could re-create an identical Admins account by using the Workgroup Administrator to create a new workgroup information file with these values, and then add themselves to the Admins group.
Before establishing user-level security, you should use the Workgroup Administrator to create a new workgroup information file, making sure to enter a third value known only to you, the workgroup ID (WID). This ensures that the new workgroup information file contains a new Admins group with a unique, secure SID.
Record the three case-sensitive strings used to create the new workgroup information file and keep them in a secure place. In the event that the workgroup information file becomes lost or damaged, you can re-create a workgroup information file with an identical Admins group by re-entering these three strings. Members of this re-created Admins group will be able to grant themselves permissions on all objects that were created when the old workgroup information file was in effect.
The Admins group's Administer permission
the right to change permissionscan't be revoked using the Microsoft Access user interface. Even if you clear the Administer check box on the Permissions tab of the User And Group Permissions dialog box (Tools menu, Security submenu) for the Admins group on an object, the permission remains. It is possible to revoke the Admins group's Administer permission by using Data Access Objects (DAO) code, but this isn't recommended because there is no way to grant the permission back again. It's better to make sure you're using a workgroup information file created with a secure WID, which ensures a unique and secure Admins group, and then make sure only the appropriate users are assigned to the Admins group.
Users The default group account comprising all user accounts. Microsoft Access automatically adds user accounts to the Users group when you create them. Any permissions assigned explicitly to the Users group are available to all users in all installations of Microsoft Access and Visual Basic because, like the Admin user's SID, the Users group's SID is identical in all workgroup information files. Similar to concerns about the default Admin user, when establishing user-level security, it's important to make sure that the Users group doesn't own or have permissions on objects that you want secure.
Permissions
Explicit permissions are those permissions granted directly to a user; no other users are affected. Implicit permissions are those permissions granted to a group; all users who are members of a group get the permissions assigned to that group.
The easiest way to administrate a secure workgroup is to create new groups and assign permissions to the groups, rather than to individual users. Then you can change individual users' permissions by adding or removing them from groups. In a simple situation, you may want to use only the default groups to define permissions: assigning administrators to the Admins group and all other users to the Users group. If you do this, keep in mind that because the Users group is identical across all installations of Microsoft Access, any permissions you assign to the Users group will be available to all users of Microsoft Access. For a higher level of security, revoke all permissions for the Users group, create your own groups, and assign permissions to them.
When a user tries to perform an operation on an object, that user's security level is determined by the least restrictive of the permissions explicitly granted to that user and the permissions implicitly granted to the groups to which that user belongs. For example, if User1 has no explicit permissions on the Orders table but belongs to the Clerks group, which has Read Data permission on the Orders table, User1 will be able to read the data in the table. If User1 is removed from the Clerks group and doesn't belong to another group with permissions on the Orders table, User1 will be denied any access to the Orders table.
Permissions can be changed on a object contained in a database by:
Members of the Admins group of the workgroup information file in use when the database was created.
The owner of the object.
Any user who has Administer permission on the object.
Permissions for a database itself can be changed only by the owner of the database or by members of the Admins group.
Even though users may not currently be able to perform an action, they may be able to grant themselves permissions to perform the action. This is true if the user is a member of the Admins group of the workgroup information file in use when the database was created, or if the user is the owner of an object.
Permissions for New Objects
You can set the permissions that users and groups receive by default for all new objects in a database. For example, you can prevent the Users group from getting permissions for new tables. This reduces your administrative burden, because you don't need to keep checking to make sure that new objects are properly secured.
Note You can prevent users from creating new tables and queries by using Visual Basic code to remove permissions on the Container object, but you can't prevent users from creating new forms, reports, macros, or modules.
See Also For information on how to assign default permissions for new objects, search the Help index for "permissions, assigning and removing." For more information on the types of permissions you can assign, search the Help index for "setting permissions."
Ownership
The user who creates an object is the owner of that object. The owner of an object can always grant permissions on that object, even if a member of the Admins group has revoked his or her explicit permissions on that object. One way to change the ownership of an object is to use the Change Owner tab on the User And Group Permissions dialog box (Tools menu, Security submenu). In addition to being able to transfer ownership to another user, the Change Owner tab is the only way to transfer ownership to a group. A group can own any kind of object except the database itself.
Another way to transfer ownership to a user is to log on as that user and re-create the object. To re-create an object, you don't have to start from scratch. You can make a copy of the object, or import it or export it to another database. To re-create an object, you must have Read Design permission for the object and, if the object is a table or query, you must also have Read Data permission.
Note These procedures don't change the ownership of queries whose RunPermissions property is set to Owner's. You can change ownership of a query only if you own the query, or if its RunPermissions property is set to User's. For more information on the RunPermissions property, see "Using the RunPermissions Property with User-Level Security" later in this chapter.
The user who creates a database is the database's owner. The database owner can always open a database and create new objects in it. The database owner can also grant or revoke the permission to open a database. A database owner's rights can't be revoked by using the User And Group Permissions dialog box. Before you establish user-level security, the default Admin user is the owner of the database.
Important If you don't transfer database ownership to a secure user account, any user of Microsoft Access or Visual Basic can open your database. You can't change the owner of a database by using the Change Owner tab on the User And Group Permissions dialog box. The only way to transfer the ownership of all of the objects and the database itself is to log on as the user you want to own the database, create a new blank database, and then import all of the objects. When you run the User-Level Security Wizard to secure your database, it uses this method to transfer ownership of the database and all of its objects.
Establishing Security with the User-Level Security Wizard
Once you understand how the different pieces of the Microsoft Access user-level security model fit together, you can proceed with the steps to secure your database. While you can perform individual steps yourself, the User-Level Security Wizard is the easiest and most reliable way to secure your database.
To establish user-level security with the User-Level Security Wizard, you specify the object types that you want secured and the User-Level Security Wizard revokes permissions on those objects for all users and groups except the user that is currently logged on. The User-Level Security Wizard creates a new, secured copy of your database, and leaves the original copy unmodified. The User-Level Security Wizard also re-creates linked tables and table relationships in the new database. After you run the User-Level Security Wizard, you can create new users and groups and assign permissions to control how the objects in your application are used.
To establish security with the User-Level Security Wizard
1 If you don't have a secure workgroup information file, create one with the Workgroup Administrator. Write down the exact, case-sensitive values you type in the Name, Organization, and Workgroup ID boxes and store them in a safe place. You'll need these values if you ever need to re-create your workgroup information file.
If you do have a secure workgroup information file, use the Workgroup Administrator to make sure that it is in use before proceeding. You must be a member of the Admins group of this file to be able to continue. Do this before you start Microsoft Access.
2 Start Microsoft Access. On the Tools menu, point to Security, and then click User And Group Accounts.
3 In the User And Group Accounts dialog box, create a new user to be the owner and administrator of the database, and then add that user to the Admins group.
4 In the Name box on the Users tab, select the Admin user, and then click the Change Logon Password tab and assign a password to the Admin user. This causes the Logon dialog box to appear the next time you start Microsoft Access.
5 Click the Users tab, and then remove the Admin user from the Admins group.
6 Exit Microsoft Access and restart it, logging on as the administrator user that you created in step 3.
7 Open the database you want to secure.
8 On the Tools menu, point to Security, and then click User-Level Security Wizard.
9 Select the check boxes for the object types you want to secure, and then click OK.
The User-Level Security Wizard creates a new database, exports copies of all of the objects from the original database, secures the object types you selected by revoking all permissions of the Users group for those objects in the new database, and then encrypts the new database. The original database isn't changed in any way.
If you chose to secure all object types in the database, the User-Level Security Wizard removes the Users group's Open/Run permission for the database itself. This means that only members of the Admins group of the workgroup information file in use in step 1 can open the new secured database. All other users of Microsoft Access and Visual Basic can't open the database or access the secured objects within it.
If you chose to secure only some object types in the database, the User-Level Security Wizard doesn't remove the Users group's Open/Run permission for the database itself. In this case, all users of Microsoft Access and Visual Basic can open the new secured database and access unsecured objects, but they can't access the secured objects within it.
10 Create your own users and groups. Assign appropriate permissions to the group accounts, and then add individual users to the appropriate groups. Typical permissions may include Read Data and Update Data permissions for tables and queries, and Open/Run permission for forms and reports.
See Also For information on creating users and groups, see "Users and Groups" earlier in this chapter. For information on how to assign permissions and a list of permissions you can assign, search the Help index for "setting permissions."
The new database created in step 9 is now secure. The user that you logged on as in step 6 is now the owner of all objects as well as the database itself. The only people who can use the objects in your application are those you gave permissions to in step 10, and members of the Admins group of the workgroup information file you created or specified in step 1.
Securing a Front-end/Back-end Application
There are some additional factors to take into account when establishing user-level security for an application that has been split into a back-end database that contains only tables and a front-end database that contains the remaining objects and links to the tables in the back-end database. You may not know the name of the specific network location for the back-end database in advance, or you may want to allow a database administrator to move the back-end database. In either situation, you should make sure that users will be able to relink the tables in the back-end database.
To establish user-level security for a front-end/back-end application
1 For both databases, follow the steps for establishing security with the User-Level Security Wizard.
2 Assign permissions to the appropriate groups to read, update, insert, or delete data in the back-end database. To do so, you can either assign permissions directly to the tables in the back-end database, or you can remove all permissions to those tables and create queries in the front-end database that use the tables in the back-end database that have the RunPermissions property set to Owner's.
See Also For more information on the RunPermissions property, see "Using the RunPermissions Property with User-Level Security" later in this chapter.
3 Assign Open/Run permission for the back-end database. This is required even if you are using queries with the RunPermissions property set to Owner's.
4 In the front-end database, grant users Modify Design permission for the linked tables. Granting users Modify Design permission on linked tables in the front-end database doesn't grant them the same rights to the tables in the back-end database.
5 By default, after running the User-Level Security Wizard, users have permission to create new tables and queries in the front-end database. If Database Access Objects (DAO) code has been used to remove this permission, you must use DAO code and the dbSecCreate constant to reassign this permission.
See Also For more information on using DAO code to assign permissions, see "Assigning Permissions for Objects" later in this chapter.
6 When the users first install your application, have them run the Linked Table Manager from the front-end database to refresh the links to the tables in the back-end database in its new location.
Tip You can also write code that prompts users during startup to refresh table links. For sample code that does this, see the RelinkTables and RefreshLinks functions in the RefreshTableLinks module of the Orders sample application.
Because users have Modify Design permission for the linked tables in the front-end database, they can reset the link to the back-end tables if the location of the back-end database changes. They won't be able to make any modifications to the design of the actual tables in the back-end database.
Securing a Database Without Requiring Users to Log On
If you want to secure some objects in your database such as your modules and the design of your objects, but you don't care about establishing different levels of access for different groups of users, you may want to consider securing your application without requiring your users to log on.
To establish user-level security without requiring users to log on
1 Follow the steps to secure your application by using the User-Level Security Wizard.
2 While logged on as a member of the Admins group, assign permissions to the Admin user account for objects that you want to be available to all users. Typical permissions may include Read Data and Update Data permissions for tables and queries, and Open/Run permission for forms and reports. If you have secured your application properly, the Admin user should no longer be a member of the Admins group.
3 Clear the password for the Admin user.
Users can now open your application without logging on. When users start Microsoft Access without logging on, Microsoft Access automatically logs them on using the Admin user account. As a result, they have only the permissions assigned in step 2. This works for any workgroup because the Admin user account is the same in every workgroup information file. Only members of the Admins group of the workgroup information file that was in use when you ran the User-Level Security Wizard in step 1 have full permissions on the objects in your database.
Important You should not distribute copies of the workgroup information file in use when you secured the database using this procedure. If you need to allow a trusted user to administer your database, you should give the copy of the workgroup information file only to that user.
To perform administrative functions, you must use the workgroup information file that was in use when you secured the database. There are two ways you can log on as a member of the Admins group of that workgroup information file:
You can temporarily define a password for the Admin user to reactivate the logon procedure, and log on as a member of the Admins group.
You can use the /pwd and /user command-line options to specify your password and user name when starting Microsoft Access. If you define a shortcut that uses the /pwd and /user command-line options to do this, you should physically secure the computer that the shortcut is located on to prevent unauthorized users from accessing this information.
Removing User-Level Security
If you want to remove user-level security, perform the following procedure:
To remove user-level security
1 Make a backup copy of the database you want to remove security from.
2 Start Microsoft Access and log on as a workgroup administrator (a member of the Admins group).
3 Open the database.
4 On the Tools menu, point to Security, then click User And Group Permissions.
5 In the User And Group Permissions dialog box, give the Users group full permissions on all objects in the database.
Tip You can select all of the objects in the Object Name box at once by clicking the first item in the list, holding down SHIFT, and then clicking the last item in the list.
6 Exit and restart Microsoft Access, and then log on as the default Admin user.
7 Create a new blank database and leave it open.
8 Import all the objects from the original database into the new database.
9 If users will be using the current workgroup information file when opening the database, you need to clear the password for the Admin user to turn off the Logon dialog box for the current workgroup. This is not necessary if users will be using the default workgroup information file created when installing Microsoft Access.
The new database is now completely unsecured. Anyone who can open the new database has full permissions on all its objects. This works for any workgroup because the Admin user account and Users group account are the same in every workgroup information file, and all users, including the Admin user, are members of the Users group. The workgroup information file in use when the new database was created in step 7 defines the Admins group for the new database.
Using the RunPermissions Property with User-Level Security
For Microsoft Access to display a table or query, it needs to read the design of that table or query. For example, it needs to read field names and other field properties, such as the Format and InputMask properties. As a result, for a user to read and display the data in a table or query, that user must also have permissions to read the design of the table or query. (This is why selecting the Read Data permission check box in the User And Group Permissions dialog box automatically selects the Read Design check box as well.) If you don't want your users to see the design of your table or query, you can use the RunPermissions property of queries to restrict their access to this information.
The RunPermissions property determines whether Microsoft Access uses the query user's permissions or the query owner's permissions when checking the user-level security permissions for each of the underlying tables in a query. If the RunPermissions property is set to User's, then the users of a query have only their own permissions to view data in underlying tables. However, it is possible to override existing user permissions. If the owner of a query sets the RunPermissions property to Owner's, anyone who uses that query will have the same level of permissions to view data in the underlying tables as the query's owner.
The RunPermissions property allows you to create queries to display data to users who don't have permission to access the underlying tables. Using this feature, you can build different views of your data that provide record-level and field-level security for a table. For example, suppose you have a secure database with an Employees table and a Salary table. Using the RunPermissions property, you can build several views of the two tables: one that allows a user or group to view, but not update the Salary field; a second that allows a different user or group to view and update the Salary field; and a third that allows another user or group to view the Salary field only if its value is less than a certain amount.
To prevent users from viewing the design of underlying tables or queries
1 For the users or groups that you want to restrict, remove all permissions to the underlying tables or queries whose design you want to secure.
2 Build a new query that includes all the fields you want to include from those tables or queries. You can exclude access to a field by omitting that field. You can also limit access to a certain range of values by defining criteria for your query.
3 Make sure you or a secure group owns the new query.
4 In the query property sheet, set the RunPermissions property of the new query to Owner's.
Note You can also set the RunPermissions property in SQL view of the Query window by using the WITH OWNERACCESS OPTION declaration in the SQL statement.
5 Grant appropriate data permissions for the new query to the users and groups who you want to be able to update data, but not view the design of the table or query. This would typically be Read Design, Read Data, Update Data, Delete Data, and Insert Data permissions, but you should specify only the permissions you want to allow.
Tip You can base forms and reports on the new query.
Users can update data in the underlying tables or queries by using the new query or forms based on it. However, they won't be able to view the design of those tables or queries. If they try to view the design of the new query, they receive a message that they don't have permissions to view the source tables or queries.
Important By default, the user who creates a query is its owner, and only the owner of a query can save changes to it if the RunPermissions property is set to Owner's. Even members of the Admins group or users with Administer permission are prevented from saving changes to a query created by another user if the RunPermissions property is set to Owner's. However, anyone with Modify Design permission for the query can set the RunPermissions property to User's and then successfully save changes to the query.
Because the creator of a query owns it by default, having the RunPermissions property set to Owner's can create problems if you need to allow more than one user to work with the design of a query. To address this situation, the ownership of the query can be transferred to a group. To do this, create a group, change the owner of the query to this group on the Change Owner tab of the User And Group Permissions dialog box, and then add the users who need to modify the query to the new group. Any member of the new group will be able to edit the query and save changes.
Using Data Access Objects to Establish User-Level Security
As an alternative to using the Microsoft Access user interface to establish user-level security for your application, you can establish security by using Visual Basic code to manipulate the Data Access Objects (DAO) that control security.
For user-level security purposes, all Microsoft Access objectstables, queries, forms, reports, macros, and modulesare considered documents, and are manipulated through the Document object and the Documents collection. There are User and Group objects and Users and Groups collections. By manipulating the Users and Groups collections, you can manage which users belong to which groups. Permissions are properties of Document objects that are assigned to specific User and Group objects.
Using DAO objects, properties, and methods, you can:
Create user and group accounts.
Change or clear passwords.
Change ownership of objects.
Assign or remove permissions for objects.
In addition, you can use DAO to perform some operations that the user interface doesn't support. For example, you can write code to prevent users from creating a new database by using the security constant dbSecDBCreate. Or you can prevent users from creating new tables or queries within a database by revoking permissions on the Tables Container object with the dbSecCreate constant.
See Also For more information on how to prevent users from creating a new database, see "Preventing Users from Creating New Databases" later in this chapter. For more information on security constants, search the Help index for "data access constants."
Also, you can write code in an application to allow users to perform actions that they don't usually have permission to perform. You do this by opening new Workspace objects under the name and password of a user account (often a member of the Admins group of the workgroup information file in use when the database was created) who has greater permissions than the user currently logged on.
For example, you could add the following code to a command button on a form in a secured copy of the Northwind sample database. When a user clicks the button, Microsoft Access creates a new Workspace object using the name and password of a member of the Admins group, opens another copy of the database in that workspace, temporarily gives the current user full permissions to the form, changes the BackColor property of the form to Red, and then restores the user's normal permissions.
Private Sub ChangeBackcolor_Click()
On Error GoTo ChangeBackcolor_Err
Dim wsNew As Workspace
Dim dbs As Database
Dim ctr As Container
Dim doc As Document
Dim lngCurrentPermissions As Long
' Turn on hourglass icon and suppress screen updates.
DoCmd.Hourglass True
DoCmd.Echo False
' Create a new Workspace object using SteveB's name and password
' and open database in new workspace.
Set wsNew = DBEngine.CreateWorkspace("NewWorkspace", "SteveB", "32msaig54")
Set dbs = wsNew.OpenDatabase(CurrentDb.Name)
' Set Container object to Forms, and then set Document object to the current form.
Set ctr = dbs.Containers!Forms
Set doc = ctr.Documents(Me.Name)
' Specify the current user of the form, store that user's current permissions,
' and then give the user full rights.
doc.UserName = CurrentUser
lngCurrentPermissions = doc.Permissions
doc.Permissions = dbSecFullAccess
' Switch to Design view, change the Detail section backcolor to red, and save form.
DoCmd.DoMenuItem 0, 2, 0, , acMenuVer80
CodeContextObject.Section(0).BackColor = 255
DoCmd.Save acForm, CodeContextObject.Name
' Switch back to Form view, reset the user's permissions to original permissions,
' and close the database and new workspace.
DoCmd.DoMenuItem 3, 2, 1, , acMenuVer80
doc.Permissions = lngCurrentPermissions
dbs.Close
wsNew.Close
ChangeBackcolor_ErrCont:
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
ChangeBackcolor_Err:
MsgBox Err.Description
Resume ChangeBackcolor_ErrCont
End Sub
If you do this, make sure that the form 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 permission on the form.
The following sections provide examples of manipulating user-level security by using Visual Basic code to perform common operations.
See Also For more information on how to use Data Access Objects (DAO) code to work with user-level security, see the Microsoft Jet Database Engine Programmer's Guide.
Creating User and Group Accounts
You can use the User and Group objects to create and manage user and group accounts. A User is an object that represents an individual who can log on to the database, while a Group is a set of User objects.
You add users to a group by appending the User object to the Users collection of an existing group. Alternatively, you can append a Group object to the Groups collection in a User object to give that user the global permissions of that group.
Permissions set for a group are automatically given to all users who belong to that group, while permissions given to a user apply only to that user account. A user's security level is determined by the least restrictive of the permissions granted to that user or any group to which that user belongs. For example, if a user is given a higher permission level for a particular database object than is granted to the group of which the user is a member, the user permissions override the group permissions.
The following procedure creates new group and user accounts, and adds a new user to an existing group account:
Sub VPPromotion()
Dim ws As Workspace
Dim grpVp As Group, grpManager As Group
Dim usr As User
' Create a Vice Presidents group account.
Set ws = DBEngine.Workspaces(0)
Set grpVp = ws.CreateGroup("VicePres", "mmbhto101193")
ws.Groups.Append grpVp
ws.Groups.Refresh
' Create an instance of an existing user account, add the user to
' the new group account, then remove the user from an existing
' group account.
Set usr = grpVp.CreateUser("Andrew Fuller")
grpVp.Users.Append usr
gprVp.Users.Refresh
Set grpManager = ws.Groups("Managers")
grpManager.Users.Delete "Andrew Fuller"
grpManager.Users.Refresh
' Create a new user account for a new manager.
Set usr = grpManager.CreateUser("Mary McCreary", "mbw1011", "Pwd")
ws.Users.Append usr
ws.Users.Refresh
' Add the user to the Managers group. Because you've already added the
' user to the Users group, you must create a new instance of the
' user account before adding it to the Managers' Users group.
Set usr = grpManager.CreateUser("Mary McCreary")
grpManager.Users.Append usr
grpManager.Users.Refresh
End Sub
See Also For more information on users and groups, see "Users and Groups" earlier in this chapter.
Adding and Changing Passwords
You can use the NewPassword method of the User object to add, change, or clear the password of an existing user account. You can also set a password for a user account when you create it by using the CreateUser method of the User object, or you can do so by setting the Password property of the User object before you append it to the Users collection.
For example, the following procedure adds passwords to the Admin, OrdersAdmin and OrdersOwner user accounts, and creates two new user accounts with passwords:
Sub ChangePasswords()
Dim ws As Workspace, usr As User
' Add passwords to the Admin, OrdersAdmin, and OrdersOwner
' user accounts.
Set ws = DBEngine.Workspaces(0)
ws.Users("Admin").NewPassword "", "AdminPwd"
ws.Users("OrdersAdmin").NewPassword "", "OrdAdminPwd"
ws.Users("OrdersOwner").NewPassword "", "OrdOwnerPwd"
' Create a user account, specifying a PID and a password with
' the CreateUser method.
Set usr = ws.CreateUser("Tim Smith", "ilmj2d", "MyPwd")
ws.Users.Append usr
' Create a user account, specifying a password with the
' Password property.
Set usr = ws.CreateUser("Robert King")
usr.PID = "tdi3tcm"
usr.Password = "NewUserPwd"
ws.Users.Append usr
End Sub
See Also For more information on passwords, see "Setting a Database Password" earlier in this chapter.
Changing Ownership of Objects
Ownership entitles users to certain irrevocable rights to the database and objects that they create, including the ability to open the database. Owners can grant permissions on an object both to themselves and to other users or groups.
If you have the appropriate permissions, you can use the Owner property of a Document object to change the owner of an object. For example, the following procedure makes the OrdersOwner user account the owner of all objects in the current database. Note that the new owner for an object can also be a group account.
Sub ChangeOwnership()
On Error GoTo Err_ChangeOwnership
Dim dbs As Database, ctr As Container, doc As Document
Dim intDocCount As Integer, intCtrCount As Integer
Const conErrNoPermissions = 3033
' Assign the current database to the database variable.
Set dbs = CurrentDb
' Loop through all the objects in the database,
' changing their ownership to the OrdersOwner account.
For intDocCount = 0 To dbs.Containers.Count - 1
Set ctr = dbs.Containers(intDocCount)
For intCtrCount = 0 To ctr.Documents.Count - 1
Set doc = ctr.Documents(intCtrCount)
doc.Owner = "OrdersOwner"
Next intCtrCount
Next intDocCount
Bye_ChangeOwnership:
Exit Sub
Err_ChangeOwnership:
If Err = conErrNoPermissions Then
Resume Next
Else
MsgBox Err.Description
Resume Bye_ChangeOwnership
End If
End Sub
See Also For more information on ownership, see "Ownership" earlier in this chapter.
Assigning Permissions for Objects
You can use the UserName and Permissions properties of a Document object to assign permissions for an object. For example, you can modify the preceding ChangeOwnership procedure so that it assigns full permissions to the Managers group account for all objects in the Orders sample application.
Sub AssignPerms()
' Declare database and integer variables.
Dim dbs As Database, intDocCount As Integer, intCtrCount As Integer
' Assign the current database to the database variable.
Set dbs = CurrentDb
' Loop through all the document objects in the database,
' assigning full permissions to the Managers group account.
For intDocCount = 0 To dbs.Containers.Count - 1
For intCtrCount = 0 To dbs.Containers(intDocCount).Documents _
.Count - 1
dbs.Containers(intDocCount).Documents(intCtrCount) _
.UserName = "Managers"
dbs.Containers(intDocCount).Documents(intCtrCount) _
.Permissions = dbSecFullAccess
Next intCtrCount
Next intDocCount
End Sub
See Also For more information on permissions, see "Permissions" earlier in this chapter.
Determining If a User Has Read Data Permission for a Table
The AllPermissions property returns the set of permissions the user inherits from each of the groups the user belongs to (the user's implicit permissions), in addition to the explicit permissions granted directly to the individual user account. A user's security level is always the least restrictive of that user's explicit and implicit permissions.
The following function uses the AllPermissions property and the dbSecRetrieveData constant to determine if the specified user has Read Data permission for a particular table.
Function CheckAllReadPerms()
Dim dbs As Database, docTemp As Document, strUser As String, strObject As String
' Assign the current database to the database variable.
Set dbs = CurrentDb
' Prompt for user name and table name and assign to string variables.
strUser = InputBox("Enter a user's account name.", "Enter User")
strObject = InputBox("Enter a table to check for Read Data permission.", _
"Enter Table")
' Set document variable to the specified table and then specify the user
' of that table.
Set docTemp = dbs.Containers!Tables.Documents(strObject)
docTemp.UserName = strUser
' Check to see if user has either implicit or explicit Read Data permission.
If (docTemp.AllPermissions And dbSecRetrieveData) > 0 Then
MsgBox strUser & " has implicit or explicit Read Data permission for " _
& strObject & "."
Else
MsgBox strUser & " has no permissions for " & strObject & "."
End If
End Function
If you want to check only the explicit permissions granted to a user, you can use the Permissions property instead.
You can avoid having to worry about whether a user has less restrictive explicit permissions by using the User-Level Security Wizard to revoke permissions for all users and groups but the Admins group. Then create new groups and assign permissions only to groups and not to individual users. This way you can easily change permissions for a user by simply moving the user to a new group. You can also change permissions to an entire group in a single operation, which is much easier than changing them for each individual user.
Preventing Users from Creating New Databases
You can use the Or keyword to add permissions on top of the existing permissions for a user or a group, and you can use the And Not keyword to deny a user or a group account specific permissions. For example, when you use the And Not keyword with the security constant dbSecDBCreate for a user, you remove that user's permission to create new databases. The following procedure demonstrates how to use the dbSecDBCreate constant to remove the permission to create new databases from all users in the Users group:
Sub Remove_DBCreate()
Dim dbs As Database, ctr As Container, strSystemDatabase
' Retrieve path for your workgroup information file (system database).
strSystemDatabase = DBEngine.SystemDB
Set dbs = DBEngine(0).OpenDatabase(strSystemDatabase)
Set ctr = dbs.Containers!Databases
ctr.Username = "Users"
ctr.Permissions = ctr.Permissions And Not dbSecDbCreate
End Sub
See Also For more information on security constants, search the Help index for "security constants."
© 1996 Microsoft Corporation. All rights reserved.
