User-Level Security

To imagine user-level security, pretend you are a playwright. Your play has a number of characters, each having his or her own function in the play. You then hire actors and assign them to the characters in the play.

As you will be seeing, user-level security is very similar in that you set up roles that need to be performed in the database, and then you assign people to those roles.

When you work with user-level security, you will be working with two files: the standard .mdb file that tracks the objects and permissions of the database, and an .mdw file that contains identification information for users.

Using the play analogy, the characters are called a workgroup. Each workgroup needs an administrator.

You begin by starting a new Access session without opening any databases and selecting Tools | Security | Workgroup Administrator. You will be presented with the window shown in Figure 16-3. Notice that the workgroup information is being held under System.mdw. This is the default information.

Figure 16-3: Workgroup Administrator

Click on Create to create a new group. The Workgroup Owner Information dialog box, shown in Figure 16-4, appears. When you first bring up the screen, it should have the default information with which you installed Access. Before you make any changes, note the default information.

Figure 16-4: Workgroup Owner Information

In order to form the new workgroup, you need to give the group a name, organization, and ID. The ID must be between 4 and 20 characters and is case sensitive. Again, the users will need this information to join the workgroup, so please keep good notes with this information.

After selecting OK, you will be prompted to enter the folder and name of the .mdw file that will contain the information. Again, record the default information before changing it. Do not add the .mdw; it will be added automatically.

Once you select OK, you will get one final chance to review the information, as shown in Figure 16-5. Select OK to accept the changes.

Figure 16-5: Confirm Workgroup dialog box

You now need to create an administrator. This is a user who will be assigned to this role. So you need to create a user by selecting Tools | Security | User and Group Accounts. In Figure 16-6, a new user called John Smith has been created. By default, each workgroup only has two initial groups: Admins and Users.

Figure 16-6: User and Group Accounts

If you want to assign John Smith as an administrator, you select Admins and then click the Add button. John will now be a member of Admins and Users.

Initially, the Admin has no password. You can assign one here by clicking on the Change Logon Password tab.

Let’s review something here for clarity. Initially, you created a new workgroup called Group11. Then, within Group11, you have two groups: Users and Admins. You create new users and, if necessary, assign them to the Admins group (or any group necessary within Group11).

The password assigned by the Change Logon Password tab is for the admin and not for the Group11 password, which we assume was assigned earlier.

Once all of this has been completed, you now need to assign the permissions. You need to have the database open and then select Tools | Security | User and Group Permissions. This dialog box is shown in Figure 16-7.

Figure 16-7: User and Group Permissions

Here you can set the permissions for John Smith. As an example, you may want to give John all permissions for the tblCustomer object but only a Read Data permission for the tblInventory object. Or if John is assigned to a group created earlier, you may want to assign the permissions to the entire group. John would then have those permissions because he is a member of that group. In summary, you can assign permissions to either an individual or a group.

Changing Users Programmatically

Up to this point, we created the groups and granted the permissions manually. (We are assuming that the database you are connecting to is remote from where the code is located.) You can accomplish the same thing programmatically using VBA. In order to accomplish this, you need to use ADOX with ADOB.

A note is in order here. As I have stated several times throughout this book, ADO is a collection of prebuilt objects that facilitate the connection to, and manipulation of, databases. It is divided into libraries. The ADOB library is the core library for making connections and accessing the data inside the database. The ADOX library handles security issues in addition to its ability to build and change structures within the database.

Please note that the code exhibits shown here are models. If you do test them, please do so in a noncritical environment because they could change system files.

Let’s assume that you set up a secure database manually, and now you want to open it programmatically. When you create the connection code, you must reference the .mdw file and provide for the password as follows:

con1.Provider = “Microsoft.Jet.OLEDB.4.0” con1.Properties(“Jet OLEDB:System database”) = "C:\BegVBA\demo.mdw” con1.Open “Data Source=C:\BegVBA\thecornerbookstore.mdb; “ & _   “User ID = JohnSmith;Password =;”

Here you can see the reference to the .mdw file that contains the information for John Smith. Since John is the administrator, due to our earlier assignment, you want to make him the focus of the code to add and delete users and permissions.

When you want to add or delete users, you would employ the following code using the ADOX.Catalog object:

Sub addUser()   Dim con1 As ADOX.Catalog   Dim newUser As ADOX.User   Dim userName As String   Dim newPassword As String   Set con1 = New ADOX.Catalog   con1.ActiveConnection = _   "Provider = Microsoft.Jet.OLEDB.4.0;" & _   "Data Source=C:\BegVBA\thecornerbookstore.mdb;" & _   "Jet OLEDB:System database=C:\begvba\demo.mdw;" & _   "User id=johnsmith;Password=;"   Set newUser = New ADOX.User   newUser.Name = userName   con1.Users.Append newUser   con1.Users(newUser.Name).changePassword "", newPassword End Sub

We made the connection to the data source as discussed earlier. We then used the Name attribute of the ADOX object called newUser to Append that name to the .mdx file of the active database. Finally, we set a password that is associated with that name.

This is a pretty straightforward code example. However, in my opinion, there is even an easier way to add and delete users, by using Jet SQL. As a beginner to VBA programming, you may find the following example, which does the same thing, a bit easier to follow because it does not employ the ADOX library:

Sub addUser()   Dim con1 As ADODB.Connection   Dim newUser As ADOX.User   Dim strSQL As String     Set con1 = New ADODB.Connection   With con1   .Provider = "Microsoft.Jet.OLEDB.4.0"   .Properties("Jet OLEDB:System database") = _   "C:\BegVBA\demo.mdw"   .Open "Data Source=c:|BegVBA\thecornerbookstore.mdb;" & _   "User ID=JohnSmith;Password=;"   strSQL = "CREATE USER janedoe [mycat] NULL"   .Execute (strSQL)   End With End Sub

Here we used Jet SQL to create a new user named janedoe and gave her a password of mycat. Since she is new, the old password was NULL.

If you were going to drop janedoe as a user, you could have simply made the SQL string as follows:

strSQL = “DROP USER janedoe”

I think you will agree that this is a little more straightforward and easier to follow than the previous code block. Jet SQL can also be used to grant the permissions to SELECT, INSERT, DELETE, and UPDATE.

As an example, I could have declared a second string variable, strGrants, and entered the following code for janedoe:

strGrants = "GRANT SELECT, UPDATE, INSERT, DELETE" & _ "ON TABLE tblCustomer TO janedoe" .Execute (strGrants) 

If you wanted to revoke her delete permission, you could use the following SQL string:

“REVOKE UPDATE ON TABLE tblCustomer FROM janedoe”

As I stated before, this could be an easier coding approach than using the ADOX library. However, this will only work with the Jet 4.0 engine. This engine ships with Access 2003 or if you are using an earlier version, can be updated at the Microsoft website. You will also find a complete list of permissions and syntax on the same site.