Security

So far in this chapter, then, we have looked at the way in which Access handles record locking, when multiple users are attempting to use the database at the same time. We will now take a look at another key issue that raises its head in a multi- user scenario - how Access enforces security.

Why Security?

The first question that needs to be answered is "Do I need to secure my database?" This is a question that you should ask when designing any database. Implementing security adds a maintenance overhead - so does your application merit it? Quite often the answer will be that it doesn't. The ease with which database applications can be created with Access means that they are now frequently used for fairly trivial functions, which don't always require security. However, if you are concerned about any of the following issues, you should consider implementing some method of securing your application:

• Your database contains confidential information that you don't want unauthorized personnel to view .Frequently, databases are used to store confidential information about personal or financial details. Such data needs to be protected from accidental or deliberate access by people who aren't authorized to view it. Your organization may even be legally required to do so.

• You want to protect your database objects from accidental change .It may have taken you a lot of time to create your database application. The last thing you want is for one of the users of the application to modify the design of a form or query so that the application no longer works.

• You want some of your users to be able to use certain functionality within your application but don't want this to be generally available .Often, your application will contain functionality that is only appropriate to a subset of users. For example, you may wish only Grade 3 managers to be able to use the application to approve expense checks.

• You want to implement an audit trail to monitor who has been doing what in your application. As well as building an audit trail specifically for security purposes, you may find it useful to build one for use during the application's testing cycle or initial rollout, so you can log how people have been using the application.

Security can be defined as a method of restricting the access that users have to a database and the objects within that database. You are probably already familiar with the methods of securing an Access database using the menu commands and standard Access dialogs. The main two ways of securing a database in this way involve:

• Creating a password for the database

• Establishing user-level security

Over 90% of the time you will perform these tasks through the user interface. These two options can be reached from the Security option on the Tools menu:

However, there may be occasions when it is preferable to administer these tasks from within VBA code. For example, you might wish to provide the users of your application with the ability to change the database password for themselves . Instead of expecting users to set the database password via the toolbar, you might wish to provide a simple password form that would allow users to enter the new database password. You could then add a procedure that programmatically changed the database password when the user hit the OK button on the form.

We will start by looking at the code that we would use to set a database password programmatically, and after that we'll examine the security model that Access employs and how it can be manipulated in VBA code.

Setting a Database Password from VBA

To set a database password from VBA, we simply apply the NewPassword method to a Database object representing the database. This method takes the existing password and the new password as its arguments.

   Set db = CurrentDB()     db.NewPassword "", "Valerie"   

The code above would add a password ( Valerie ) to a database that previously didn't have one. To change this password, we would use this syntax.

   Set db = CurrentDB()     db.NewPassword "Valerie", "Smith"   

To clear the password, we would specify an empty string for the new password:

   Set db = CurrentDB()     db.NewPassword "Smith", ""   

It should be noted that setting a password in this way is not recommended in a live application as it is possible for a hacker to extract the password from it relatively easily.

Protecting Your Code

Access 2002 also provides us with the capability to protect the code in the VBA project associated with our database. To set the VBA project password, select the Protection tab from the dialog that appears when you choose < ProjectName > Properties from the Tools menu in the VBE :

If you select the Lock project for viewing checkbox and enter a password, subsequent users of the database will not be allowed to view the VBA code for the database unless they enter the specified password.

If, instead, you enter a password but leave the Lock project for viewing checkbox unchecked, users will be able to view and amend the VBA code for the database, but will not be able to display the < ProjectName > Properties dialog.

In Access 2002 it is not possible to assign permissions to individual modules - you will need the Office XP Developer edition to achieve this.

The Access Security Model

So let's have a look now at how we can use user-level security to assign permissions to users and groups on individual database objects.

The Access security model consists of two elements:

1. The workgroup information file

2. User and group permissions

The workgroup information file (WIF) contains:

• The names of all users in the workgroup

• The names of all groups in the workgroup

• Information about which users belong to which groups

• Each user's password (in encrypted form)

• A unique SID (Security ID) for each user and group (in a binary format)

Using the User-Level Security Wizard

This wizard, new to Access 2002 (previously you had to find and use a less helpful separate utility WRKGADM.EXE ), allows you to simply and easily set up a security regime for your database. To initiate it select Tool, Security, and then User-Level Security Wizard :

Once started simply follow through each of the stages. They are pretty straightforward and self explanatory so we won't go through them here. Help is available via the Help button on the dialogue itself if you get stuck.

Once you have created your security regime you can inspect and modify it by selecting Tools, Security, and then User and Group Permissions or Accounts .

These wizards are useful and quick tools but we want to see how to do all this from VBA so we can manipulate users and groups from within our own programs. So, rather than taking you through each and every screen of the wizards we'll just dive into the VBA code detail. All the comments regarding Users, Groups, etc. apply equally to the WIF created by the wizard in any case.

Manually Making a Workgroup Information File (WIF)

OK, I know I just said we were going to look at how to do this stuff in VBA - but there is one operation that you are unlikely to need it for and that's setting up the WIF as this only needs doing once (except for very specialist applications). There are two options for doing this:

1. Use the User-Level Security Wizard and set basic user and groups up, then use VBA to modify them as required.

2. Use the Workgroup Administrator method explained below.

   If you really do have a need to use VBA code to change the WIF or create a new one please refer to the Visual Basic Help file and search for CreateNewWorkgroupFile, SetDefaultWorkgroupFile, and SystemDB.

   To manually create and/or set the location of the workgroup information file without going through the User-Level Security Wizard select Tools, Security, and then Workgroup Administrator:

   

You will then be asked whether you wish to create a new WIF or utilize an existing one:

If you select Create then you will be presented with the following dialogue:

Enter a name for your Workgroup under Workgroup ID and press OK . You will then be asked for a path and filename for the WIF. The default is:

 C:\Documents and Settings\[user name]\Application Data\Microsoft\Access\System.mdw 

It would make a lot of sense to change this to a location where all potential users had access to it. The same directory as the database itself would seem the obvious place.

Permissions for using individual data objects within a database are stored in the database that contains the objects. We'll look at how you modify these permissions a little later. First, we are going to look at how to modify the user and group information in the workgroup information file through VBA.

It is recommended that you make sure that the WIF is stored safely and periodically backed up as if it is lost or corrupted then you will have to re-create the User and Group Accounts with exactly the same Personal IDs that were originally assigned. If the new workgroup information file is not created exactly as the original file, you will not be able to open the database with the workgroup file.

Manipulating Users and Groups

The Data Access Object hierarchy, which we have already noted to be central to VBA coding, contains the following security objects:

The most striking feature of this hierarchy is its seemingly recursive structure. A User object contains a Groups collection and a Group object contains a Users collection. The hierarchy is implemented in this way to allow for the many-to-many relationship between Users and Groups . A user can belong to one or more groups, each of which can contain many users.

Notice also that the Users and Groups collections belong to the Workspace object, rather than the Database object. Although the properties for individual objects are contained within each database, users and groups are defined within the workgroup information file and are available to all databases that are opened in the security context ( Workspace ) defined by that workgroup information file. So we can think of the Workspace as being the object that is primarily responsible for access to user and group maintenance in VBA.

So, let's have a look at how we can use these data-access objects to perform the following tasks:

• Create new users and groups

• Add users to groups

• Change a user's password

  Important 

  Note that for the following examples to work, you must be logged in with permissions to allow the creation and modification of users. The same rules apply for creating and modifying users and groups in VBA as they do when you use the Security menu options. Normally it is only members of the Admins group who should modify security options.

Enumerating Users and Groups

To enumerate all of the users and groups in the current Workspace , we simply need to loop through the Users and Groups collections.

   Sub EnumGroupsAndUsers()     Dim grp As Group     Dim usr As User     For Each usr In DBEngine(0).Users     Debug.Print usr.Name     For Each grp In usr.Groups     Debug.Print vbTab; grp.Name     Next     Next     End Sub   

Note how we can use DBEngine(0) to refer to the default workspace. We can do that because Workspaces is the default collection of the DBEngine object, so that line of code is equivalent to:

   For Each usr In DBEngine.Workspaces(0).Users   

If we were to run this procedure in a database while using the default system.mdw workgroup information file that is installed with Access, we would see the following in the Immediate window:

As expected, we see the admin user. By default, Access will attempt to log on all users with a user name of admin with a blank password. If you want Access to display the logon dialog in order to allow users to log on with a different user name, you should first change the password of the admin user. This will cause the default login mechanism to fail and the login dialog box will be displayed.

As you can see, the admin user is a member of the two built-in groups, Admins and Users . We can verify this much if we switch to Access and display the User and Group Accounts dialog from the Tools Security menu.

But what of the two other users whose names we saw in the Immediate window? Creator and Engine are actually a couple of internal system accounts. They are used by JET and are not accessible to users and so do not appear when viewing and setting users and groups through the graphical interface. They are mentioned here not because we will use them at all, but simply to alert you of their presence. So, leaving aside the Creator and Engine user accounts we have one built-in user ( admin ) and two built-in groups ( Admins and Users ). Let's see how we programmatically add new users and groups.

Creating a New User

Creating a new user is very straightforward - you simply use the CreateUser method against the Workspace object. For example, the following piece of code can be used to create a user called Mark Fenton :

   Sub UserAdd()     Dim wks As Workspace     Dim usrMark As User     Dim strUsersPID As String     Set wks = DBEngine(0)     strUsersPID = "1234abcd"     'Start by creating a new User account     Set usrMark = wks.CreateUser("Mark Fenton")     'Now set the User's properties     usrMark.Password = "Doctor"     usrMark.PID = strUsersPID     'Append User to the Users collection of this workspace     wks.Users.Append usrMark     End Sub   

As before with the database password, I would not recommend using this technique in a live application. Placing passwords in source code means that they can be extracted by a hacker fairly easily.

As you can see, the first step is to create a new user object. We do this by applying the CreateUser method to the workspace object and supplying the name of the new user:

 Set usrMark = wks.CreateUser("Mark Fenton") 

The next step is to set the properties of the new User object. The properties you can set are the Password property, the PID property, and the Name property. Note that we set the Name property of the user object when we created it with the CreateUser method.

The PID property is a string of between four and twenty characters , which is used to uniquely identify a user:

 usrMark.PID = strUsersPID 

The Password property, a string of up to fourteen characters, corresponds to the password that the new user will need to enter. This is simply an additional way of verifying the user's identity and is not the same as the database password.

 usrMark.Password = "Doctor" 

After you have set these three properties, you are ready to append the User object to the collection of Users already defined in the current workspace. This will save the User , and its properties, in the workgroup information file.

 wks.Users.Append usrMark 

If you type the procedure into a new module and then run it, you can check the existence of the new user by going to the User and Group Accounts section of the Security submenu on the menu:

Notice, however, that the new user has not been added to any groups. That's not really surprising, as we never added the user to any groups in our code! We'll see next how we can create a new group and then add a user to that group.

Creating a New Group

A group is used to collect together users to whom you wish to assign the same permissions. For example, you may have thirty different registrars who will use your database, each of whom you want to define as a user in an Access workgroup. It would be very tiresome if you then had to assign permissions on every single database object to each of the thirty users. So, instead, you can create a group called Registrars , add the thirty users to it, and then assign database object permissions just to that group.

Important 

Wherever possible, you should assign permissions to groups rather than to individual users. Indeed, it is often better to create a group even if it currently only has one user in it - it makes it much easier to change the user or add a user at a later date without having to re-enter or copy permissions individually. It is also recommended that you create an Administration group rather than separately assigning administration permissions to individual users. Having a group to do this makes it easy to add or remove users from the group and also makes it a cinch to keep track of who currently has these important privileges.

Creating a group is a very similar process to the one we employed above for creating new users:

   Sub GroupAdd()     Dim wks As Workspace     Dim grpRegistrars As Group     Dim strGroupPID As String     Set wks = DBEngine(0)     strGroupPID = "5678"     'Start by creating a new Group account     Set grpRegistrars = wks.CreateGroup("Registrars")     'Now set the Group's properties     grpRegistrars.PID = strGroupPID     'Append Group to the Groups collection of this workspace     wks.Groups.Append grpRegistrars     End Sub   

First we create a group object within the current workspace:

 Set grpRegistrars = wks.CreateGroup("Registrars") 

Next we set the properties for the group. The only two properties of a group are the Name property and the PID property. We set the Name property when we create the group, just as we did when we created the new user.

 grpRegistrars.PID = strGroupPID 

Finally we save the group by appending it to the Groups collection in the current workspace:

 wks.Groups.Append grpRegistrars 

Adding a User to a Group

Once we have created the Registrars group, we want to add our new user to it. The following piece of code will achieve this:

   Sub AddUserToGroup()     Dim wks As WorkSpace     Dim usrMark As User     Set wks = DBEngine(0)     Set usrMark = wks.CreateUser("Mark Fenton")     wks.Groups("Registrars").Users.Append usrMark     End Sub   

First, we declare an object variable of type User and use it to reference the user object that we want to add to the Registrars group:

 Set usrMark = wks.CreateUser("Mark Fenton") 

All of the details concerning the user object Mark Fenton were set in the procedure UserAdd , so now it's just a case of appending the user object to the Users collection, which belongs to the Registrars group.

 wks.Groups("Registrars").Users.Append usrMark 

If you run the procedure and then look at the User and Group Accounts section again, you will see that Mark Fenton is now a member of the Registrars group.

Also, if you refer back to the DAO hierarchy, you will realize that we could have achieved this the other way round:

   Sub AddUserToGroup()     Dim wks As WorkSpace     Dim grpRegistrars As Group     Set wks = DBEngine(0)     Set grpRegistrars = wks.CreateGroup("Registrars")     wks.Users("Mark Fenton").Groups.Append grpRegistrars     End Sub   

In this example, we append the Registrars group object to the Groups collection that belongs to the user called Mark Fenton .

Changing a Password

To change our user's password we simply use the NewPassword method of the user object:

   Dim wks As WorkSpace     Set wks = DBEngine(0)     wks.Users("Mark Fenton").NewPassword "Doctor", "Nurse"   

To clear Mark Fenton's password, we supply an empty string as the new password:

   Dim wks As WorkSpace     Set wks = DBEngine(0)     wks.Users("Mark Fenton").NewPassword "Doctor", ""   

Important 

To change a user's password you must be logged on to that database either as that user or as a member of the Admins Group.

Now that we have got this far, we should be able to create a form that allows the users of our database to change their password.

Try It Out-Creating a 'Change Password' Form

Important 

I strongly recommend you read through this exercise thoroughly before entering any code, as you will be changing the Admin User's password - you wouldn't want to be locked out of your own database by mistake now would you? Indeed, I would urge you to backup your database before you proceed or to use a test database instead.

1. In the IceCream.mdb database, create a new blank form in Design view and set the following properties for it:

   

2. Add three TextBox controls. Call them txtOldPwd , txtNewPwd, and txtVerify and change the text of their labels to read Old Password , New Password, and Verify New Password .

3. Now select the three textboxes (hold down shift as you click on each item) and change them to password input boxes by changing their Input Mask property to Password .

   

4. Add the following code to the form's Load event handler:

      Private Sub Form_Load()     Me.Caption = Me.Caption & " (" & CurrentUser & ")"     End Sub   

5. Now add two command buttons . Call them cmdCancel and cmdChange and change their captions to Cancel and Change . Make sure that the Command Button Wizard isn't enabled when you create these buttons.

   

6. Next, add the following routines to the Click event handlers of each button. This code for the Cancel button:

      Private Sub cmdCancel_Click()     DoCmd.Close     End Sub   

7. and this code for the Change button:

      Private Sub cmdChange_Click()     Dim strOld As String     Dim strNew As String     Dim strVerify As String     Dim strMsg As String     strOld = Nz(txtOldPwd, "")     strNew = Nz(txtNewPwd, "")     strVerify = Nz(txtVerify, "")     If ChangePassword(strOld, strNew, strVerify) = True Then         If strNew = "" Then     strMsg = "Your password has been cleared"     Else     strMsg = "Your password has been changed"     End If         MsgBox strMsg, vbOKOnly         txtOldPwd = Null     txtNewPwd = Null     txtVerify = Null     End If     End Sub   

8. Next, write the code for the function that we referred to in the cmd_Change_Click procedure:

      Function ChangePassword(strOld As String, _     strNew As String, _     strVerify As String) As Boolean     Dim wks As Workspace     On Error GoTo ChangePassword_Err     Set wks = DBEngine(0)     If strNew <> strVerify Then     MsgBox "Your new password and the verification of your " & _     "new password do not match." & _     vbCrLf & vbCrLf & _     "Please try again.", vbExclamation     End If     wks.Users(CurrentUser).NewPassword strOld, strNew     ChangePassword = True     ChangePassword_Exit:     Exit Function     ChangePassword_Err:     MsgBox "Cannot change this password.  Please ensure that " & _     "you have typed the old password correctly.", _     vbExclamation     Resume ChangePassword_Exit     End Function   

9. Close the form and save it as frmPassword .

10. Finally, open the form and try it out!

    Important

Beware! If Access does not ask you for a password when you start it up, then Access will log you on as Admin with an empty password. Once you change the Admin password to something other than an empty password, Access will always ask for a login and password when it starts. So when you use the password form, if you are changing the password for the Admin user, make sure you write it down (remember that it's case-sensitive), because if you forget it you won't be able to get back in. To stop Access asking you for a login and password when it starts, set the password for Admin back to an empty string.

How It Works

If you followed the description of how to change passwords in the section above this example, you should have no problem understanding how the code works. The form simply allows a user to either enter a new password, change an existing one, or clear the current password.

The code behind the cmdCancel button shouldn't need any explanation.

The cmdChange button is used to change the password of the user who is currently logged on. The user must enter their current password in txtOldPwd and new password in txtNewPwd . The user must also enter it again in the txtVerify box to ensure that no mistake has been made. When the cmdChange button is clicked, the procedure in the Click event of the button starts by converting any null strings into empty strings:

 strOld = Nz(txtOldPwd, "") strNew = Nz(txtNewPwd, "") strVerify = Nz(txtVerify, "") 

We do this because Access treats textboxes that contain no text as if they contained the value Null . Now that might be appropriate for forms that are used to enter values into a database, but it is not appropriate in this situation. The NewPassword method of the User object, which we will use to change the user's password can only accept string values and Null is not a valid string value. If we want the user to have a blank password, we need to use an empty string ( "" ) instead.

The simplest way to convert Null values into any other value is to use the Nz ( N ull-to- z ero) function. This function accepts two arguments and checks whether the first is Null . If it is not Null , the function returns that first argument as its return value; but if the first argument is Null , the function returns the value of the second argument.

For example, if the value of txtNewPwd is Null , then Nz(txtOldPwd, "") returns an empty string. But, if the value of txtNewPwd is not Null , then Nz(txtOldPwd, "") returns txtNewPwd .

Once we have converted any Null values into empty strings, we call the ChangePassword function and determine whether it returns True or False .

 If ChangePassword(strOld, strNew, strVerify) = True Then 

If it returns True t hen we know that the password has been successfully changed, so we can display a message to the user stating that the password has either been changed or cleared (changed to a blank password). If it returns False then the attempt has failed for some reason. We should really do something about this (like maybe ask the user to try again) but for brevity we have not bothered here:

 If strNew = "" Then         strMsg = "Your password has been cleared"     Else         strMsg = "Your password has been changed"     End If          MsgBox strMsg, vbOKOnly 

Lastly we clear the values from the three textboxes on the form.

 txtOldPwd = Null     txtNewPwd = Null     txtVerify = Null 

Now that is simple enough, but how does the ChangePassword function work? Well, again it is fairly straightforward. The first task is to get hold of a reference to the current Workspace object.

 Set wks = DBEngine(0) 

Remember that the Workspace object is the object that gives us access to the Users collection and we need to use a User object if we are to change the current user's password.

We then perform a check to ensure that the new password and the confirmation of the new password have been entered correctly. If they are different, we display a warning message and exit the ChangePassword function.

 If strNew <> strVerify Then     MsgBox "Your new password and the verification of your " & _         "new password do not match." & _         vbCrLf & vbCrLf & _         "Please try again.", vbExclamation End If 

Once we have performed that check, we can try to change the password:

 wks.Users(CurrentUser).NewPassword txtOldPwd, txtNewPwd 

If the attempt is successful the function returns True :

 ChangePassword = True ChangePassword_Exit:     Exit Function 

However, if an error occurs, it is trapped in the error handler. An error here will almost certainly be caused by the user supplying an incorrect current password, so we display an appropriate message:

 ChangePassword_Err:     MsgBox "Cannot change this password.  Please ensure that " & _            "you have typed the old password correctly.", _            vbExclamation     Resume ChangePassword_Exit 

Important 

Notice that the ChangePassword function only returns True if the password has been successfully changed. That is because the value of the ChangePassword function is only set to True once the password has been changed. If the function exits before this point (either because the new password and confirmation are different or because of some other error) the function will return False . That is because the function returns a Boolean value and the initial value of all Boolean variables is False .

This form is still only very rudimentary in many ways, but it does illustrate how you can very quickly provide a simple interface to allow users to maintain their own passwords.

Setting Object Permissions with Visual Basic

Now that we have created users and groups and have learned how to assign them passwords, we shall take a look at how to give those users and groups permissions on objects. We said earlier that permissions for using specific objects within a database are held within each individual database. But how do we find out what those permissions are and how do we set them?

The keys to retrieving and setting permissions are Documents and Containers . As you may know, a Document is an object that contains information about a specific object in the database. A Container is an object that contains information about a collection of objects in the database. You will not be surprised to know that some of the information held by Document objects and Container objects is information about permissions.

Try It Out-Retrieving Permissions

1. In the IceCream.mdb database, add the following procedure to the Chapter 17 Code module:

      Sub ShowPerms()         Dim objContainer As Container     Dim objDoc As Document     For Each objContainer In CurrentDb.Containers     Debug.Print "--> Container: " & objContainer.Name     For Each objDoc In objContainer.Documents     Debug.Print "Document: " & objDoc.Name & "  ";     objDoc.UserName = "Admin"     Debug.Print "Perms: " & objDoc.AllPermissions     Next     Next     Debug.Print "Done"         End Sub   

2. Run the procedure from the Immediate window. You will get a large output similar to the following:

   

How It Works

The procedure loops through all the Container objects within the database and assigns a reference to each of them in turn to the variable objContainer . It then prints the Name of the Container object.

 For Each objContainer In CurrentDb.Containers     Debug.Print "--> Container: " & objContainer.Name 

It then loops through all of the Document objects within the Container object referenced by objContainer and assigns them in turn to the variable objDoc .

 For Each objDoc In objContainer.Documents 

It then displays the name of the Document object and the permissions in that Document object for the Admin user:

 Debug.Print "Document: " & objDoc.Name & "  "; objDoc.UserName = "Admin" Debug.Print "Perms: " & objDoc.AllPermissions 

To display the permissions for a specific user you must first set the UserName property of the Document object you are inspecting to the user's name. The semicolon at the end of the first Debug.Print s tatement tells Access not to move onto a new line for the next Debug.Print statement.

Note that we inspect the AllPermissions property of the document. The AllPermissions property reflects those permissions explicitly assigned to the user and those the user inherits from the group to which they belongs. The Permissions property only reflects the permissions that are explicitly and separately assigned to a user.

Analyzing the Output

Don't panic if you're a bit confused by the output you get in the Immediate window. We'll go through this now.

Containers and Documents

Containers are not the same as collections. For example, the Forms collection contains references to those forms that are currently open, and it contains information about the design and properties of those forms. The Forms container , by contrast, has a Document for every saved form in the database - whether it is loaded or not - and this Document contains information about the form's owner and permissions.

Each database will have within it the following nine containers: DataAccessPages , Databases , Forms , Modules , Relationships , Reports , Scripts , SysRel , and Tables .

Of these, three ( Databases , Tables, and Relations ) are defined by JET, whereas the other six ( DataAccessPages , Forms , Modules , Reports , Scripts , and SysRel ) are defined by the Access application.

Now the contents of most of these containers are fairly obvious but you might be confused by the Relationships and SysRel containers. Well, the Relationships container holds information about all the relationships that have been defined between the tables in the current database, while the other container object, SysRel , is used internally by Access to store information about the layout of the System Relationships window. We need not concern ourselves with that here. You should also note that there is no separate container for queries. Instead, these appear within the Tables container.

So much for containers, but what about the documents? If you look at the names of these Document objects, you will recognize most of them as saved objects within the database. There are a few oddities , however, such as AccessLayout , SummaryInfo, and UserDefined . These are all in the Databases container that refers to the current database.

The SummaryInfo Document has a Properties collection, which contains the properties on the Summary page of the Database Properties... dialog box, found on the File menu. Similarly, the UserDefined Document has a Properties collection containing the user-defined properties, found on the Custom page of the same dialog box. Finally, the AccessLayout Document is a system document used internally by Access.

So that explains the unfamiliar documents and containers. The rest of them apply to familiar objects and collections within the database.

Permissions

The next thing we must explain is the permission values. What does a Permissions property of 1048575 for frmCompany mean? To find out, we must use some intrinsic constants. Have a look at the following intrinsic constants which represent user permissions:

This constant...	...equals...	...and means that
dbSecNoAccess		The user can't access the object at all.
dbSecDelete	65536	The user is able to delete the object.
dbSecReadSec	131072	The user can read the security information about the object.
dbSecWriteSec	262144	The user is able to alter access permissions for the object.
dbSecWriteOwner	524288	The user can change the Owner property setting of the object.
dbSecFullAccess	1048575	The user has full access to the object.

You will no doubt have spotted that 1048575 is represented by the constant dbSecFullAccess , indicating that, in our previous example, Admin has full permissions for the form we created earlier, frmCriteria .

So, if we wanted, we could now alter our function so that it only shows whether or not a user has permission to, say, delete documents. In this case, it would read like this:

   Sub ShowNoDelPerms()     Dim objContainer As Container     Dim objDoc As Document     For Each objContainer In CurrentDb.Containers     Debug.Print "--> Container: " & objContainer.Name     For Each objDoc In objContainer.Documents     If (objDoc.AllPermissions And dbSecDelete) = dbSecDelete Then     Debug.Print "Can Delete Document: " & _     objDoc.Name & "  ";     objDoc.UserName = "Admin"     Debug.Print "Perms: " & objDoc.AllPermissions     Else     Debug.Print "Cannot Delete Document: " & _     objDoc.Name & "  ";     objDoc.UserName = "Admin"     Debug.Print "Perms: " & objDoc.AllPermissions     End If     Next     Next     Debug.Print "Done"     End Sub   

To check whether the user has permission to delete the object associated with a document, we compare the document's AllPermissions property with the constant dbSecDelete using the And operator:

 If (objDoc.AllPermissions And dbSecDelete) = dbSecDelete Then 

If the result of this expression is True , the user has permission to delete the document.

The main thing to remember here is not that you have to understand how these logical operators work at a low level but that you need to know how to use these operators to determine permission values.

To determine if a user has permissions represented by one of the security constants on a certain object we use the And operator. So the following expression will return True if the user does have the permission represented by the security constant and will return False if the user does not have that permission:

   (objDoc.AllPermissions And <security constant>) = <security constant>   

Setting Permissions

We can also set permissions as well as retrieve them. Suppose we want to make sure that one of our users, Mark Fenton, doesn't accidentally delete the frmPassword that we so carefully created earlier on. To do this, we can modify the Permissions property of the document for frmPassword . This is what the following procedure does:

   Sub ProtectItFromMark()     Dim db As Database     Dim Doc As Document     Set db = CurrentDb()     Set Doc = db.Containers("Forms").Documents("frmPassword")     Doc.UserName = "Mark Fenton"     Doc.Permissions = dbSecFullAccess And Not dbSecDelete     End Sub   

The first thing to notice about this piece of code is the way in which we select the document whose permissions we wish to alter. In the previous example, we simply looped through the container and document collections in turn. But if you want to, you can select a specific document or container by name. In this example, we are selecting the document for the object called frmPassword . Because this is a form, its document will be located in the container called Forms , hence the line:

 Set Doc = db.Containers("Forms").Documents("frmPassword") 

We then need to specify the user to whom these permissions should apply:

 Doc.Username = "Mark Fenton" 

Finally, we specify the permissions we want to give Mark. Here we are saying that we want Mark to be able to do everything but delete the form:

 Doc.Permissions = dbSecFullAccess And Not dbSecDelete 

Again, you don't need to understand exactly why And Not removes permissions. What you need to remember is that it works. The following code fragments show how to add and remove permissions for objects.

To replace all permissions on an object with a new single permission, use this:

   Set Doc.Permission = <security constant>   

To replace all permissions on an object with a new set of permissions, use this:

   Set Doc.Permission = <security constant> Or <security constant>   

To add a new single permission while retaining the existing permissions, use this:

   Set Doc.Permission = Doc.Permission Or <security constant>   

To add a new set of permissions while retaining the existing permissions, use this:

   Set Doc.Permission = Doc.Permission Or <security constant> Or _     <security constant>   

To remove a single permission from the existing permissions, use this:

   Set Doc.Permission = Doc.Permission And Not <security constant>   

To remove a set of permissions from the existing permissions, use this:

   Set Doc.Permission = Doc.Permission And Not (<security constant> Or _     <security constant>)   

There are many ways you can manipulate object permissions through VBA. After all, there are 25 security constants! But the principle is the same whichever you use. The full range of security constants is shown below:

acSecMacExecute

acSecMacReadDef

acSecMacWriteDef

acSecFrmRptExecute

acSecFrmRptReadDef

acSecFrmRptWriteDef

acSecModReadDef

acSecModWriteDef

dbSecCreate

dbSecDBAdmin

dbSecDBCreate

dbSecDBExclusive

dbSecDBOpen

dbSecDelete

dbSecDeleteData

dbSecFullAccess

dbSecInsertData

dbSecNoAccess

dbSecReadSec

dbSecReadDef

dbSecReplaceData

dbSecRetrieveData

dbSecWriteSec

dbSecWriteDef

dbSecWriteOwner

We don't have space here to go through every constant in detail (and in any case it would be deeply dull!) but you can get a full description of all of them by going to the Microsoft Developer's Network (MSDN) web site at http://msdn.microsoft.com and doing a search for the keywords " DAO Security Constants ".

Workspaces

Finally, a quick note about workspaces. Many of the code snippets in this last section have started with the following line:

   Set wks = DBEngine(0)   

This means that any code that follows that uses the wks workspace object will execute in the current workspace. In other words, it will run in the security context of the currently logged on user. There are times, however, when we might want to run some code in the security context of a different user. For example, our database may contain a function that lists the groups to which the user belongs. The problem is that we need to be logged on with administrative privileges to do this.

The way to impersonate another user in code is to create a new workspace, which we do like this:

   Set wks = DBEngine.CreateWorkspace("MyWorkspace", "Admin", "Glenfarclas")   

This has the effect of programmatically logging us on as the user called Admin with a password of Glenfarclas and creates a workspace called MyWorkspace . Any operations performed on users or groups within that workspace will be executed as if they were being performed by the user called Admin , irrespective of how the current user is currently logged on to Access.

In fact, if you want to try this out, you can have a go in one of the exercises at the end of this chapter!