The Developer Workgroup File is Ready | Real World Microsoft Access Database Protection and Security

Thus far in the book, we have covered the steps to produce a developer workgroup file ”the most important concept that you need to grasp before we start securing the database is understanding where your security information ends up. So far in this chapter, I have discussed changes only to the workgroup file. From now on, our security discussions will involve both the workgroup file and the database itself.

Reflecting over a Cup of Coffee

Now is a good time to take a break and have a cup of coffee. While you are relaxing , reflect on the fact that you have made changes only to the workgroup file and haven't actually done anything with your database. It is also a good time to remember that every person is an anonymous Admin user and member of the Users group , which stops only if they join another workgroup file and log on by using another user account.

To make it easier to understand whether I am working on the workgroup file or the database permissions that we will cover soon, I like to modify the titles for the built-in user and security menu commands. The process that we have been using thus far (choosing Tools ˜ Security ˜ User and Group Accounts) only changes the workgroup file. Therefore, I change the command title to "User and Group Accounts WORKGROUP" (as shown in Figure 8-9) by using the following steps:

Right-click a toolbar and choose Customize.
When the Customize Toolbars dialog appears, choose Tools ˜ Security ˜ User and Group Accounts.
Right-click the "User and Group Accounts" command and choose Rename.
Change the name to include " WORKGROUP," then click Close. (See Chapter 7 for more instructions.)

Figure 8-9: Renamed user and security commands

Apart from helping me remember what I am working on, this new name acts as a visual reminder that I need to make sure that I am using the correct workgroup. The second command (Tools ˜ Security ˜ User and Group Permissions), which is located above the Workgroup command, changes only database permissions and database object permissions. I change this command to "User and Group Permissions DATABASE." These permissions apply to workgroup Users and Groups, and soon I will describe how they work.

Tip	Always know what account you are using and what workgroup file you are using before making security changes.

Before I discuss database ownership and permissions, I am going to digress into some VBA code that you can use to generate a developer workgroup file from scratch.

Building a Workgroup File in Code

While I was writing this chapter, I became very keen to find a way to guarantee that programmers could re-create the developer workgroup file. This capability concerned me because Microsoft recommends that you write down the owner information, user names , and PIDs. Because over the years I personally seem to be good at keeping electronic files and conversely seem to be useless at keeping and finding paper, creating a workgroup file from code appealed to me. In addition, because Access's workgroup security design precludes upgrades to a later file format, it would be better to create the workgroup file again from software in the latest file format. Therefore, when I was looking into integrating the Workgroup Administrator into Access 2002, I was excited to find that it was now possible to re-create a workgroup file from within Access. This discovery led me to write VBA code that would re-create the developer workgroup file and remove the Admin user from the Admins group. You will find this VBA code in a form called frmDevelopersWorkgroup in the grMap2002.mdb database. Developers may find this automation of the workgroup file creation to be very useful after reading the PID authentication concept in Chapter 10.

On the frmDevelopersWorkgroup form illustrated in Figure 8-10, you will find two buttons . To use the form, click button 1 to generate a workgroup file that will then be your default workgroup file. You then have to close and reopen Access, and this time you will be logged on automatically as the Admin user. Clicking button 2 on this form, which will work only if you joined this new workgroup file, then adds a password for the Admin user account, adds the Developer user account, and removes the Admin user from the Admins administration group. I encourage you to read the VBA code first before running the procedure. In particular, review the constants at the top of the module and the procedures, as these will be your security identifiers. Another thing to be aware of in Access 2002 or later is your default file format (found by choosing Tools ˜ Options ˜ Advanced tab), which defines the file format of your workgroup security file. Naturally, if your intended audience is Access 2002/2003 users, I advise you to go with the later version because it will preclude 2000 users from ever opening the workgroup file.

Figure 8-10: Building a developer workgroup file.

This discussion brings us to the specifics of the VBA code under button 1. Initially, a number of constants are declared, including the location of the workgroup file as a module constant common to both procedures. Then you will need to confirm the creation of the workgroup file and its Access file format (2000 or 2002/2003). Then, if all seems okay, the CreateNewWorkgroupFile and SetDefaultWorkgroupFile methods create the workgroup file by using the security details defined by the constants.

 Option Compare Database Option Explicit Const SECUREWRK = "C:\developer.mdw" Private Sub cmdDevelopWrkGrp_Click() On Error GoTo err_cmdDevelopWrkGrp ' This code makes a workgroup by using the Access 2002 default file format. Const FORMATMENU = "See menu (Options > Advanced > Default File format)" Const WRKGRPNAME = "Real World" Const WRKGRPCOMPANY = "Microsoft Access Database" Const WRKGRPID = "Protect and Secure" Const ACC2K = 9 Dim strFileFormat As String, intCreateWrk If Application.GetOption("Default File Format") = ACC2K Then    strFileFormat = "Access 2000" Else    strFileFormat = "Access 2002/2003" End If intCreateWrk = MsgBox("Would you like to create the " & SECUREWRK & _                       " workgroup file in " & strFileFormat & " file format", _                       vbYesNo, "Create and point to a new workgroup file") If intCreateWrk = vbYes Then    Application.CreateNewWorkgroupFile SECUREWRK, WRKGRPNAME, _                                       WRKGRPCOMPANY, WRKGRPID, False    Application.SetDefaultWorkgroupFile SECUREWRK    MsgBox "Next time you start Access, you will be using the " & _           SECUREWRK & " workgroup file." & vbCrLf & vbCrLf & _           "To secure the Workgroup file, restart Access " & _           ", Open this form again and click on button 2."    ' Disable button 2 because you need to start Access with the new workgroup.    cmdSecureWrkGrp.Enabled = False Else    MsgBox "If you want to change the file format, " & FORMATMENU End If Exit_cmdDevelopWrkGrp:    Exit Sub err_cmdDevelopWrkGrp:    Select Case Err.Number       Case Else          MsgBox "Error No. " & Err.Number & " -> " & Err.Description    End Select    Resume Exit_cmdDevelopWrkGrp End Sub

Caution

If you have run this procedure, you will join the new workgroup file. Do not forget to rejoin your old workgroup file. See the section "Preparing Your Development Workgroup File" earlier in this chapter for information on how to rejoin your old workgroup file.

At this stage, you will close and reopen Access so that you can log on (automatically) as the anonymous Admin user in the new workgroup file. Now we'll use some special SQL commands that Jet 4.0 (Access 2000) introduced. The purpose of these commands is to create a new user, make the user an administrator, set the Admin password, and remove the Admin user from the Admins group. These special SQL commands ” Create User , Add User xxx To Group , Drop User xxx From Group , and Alter User xxx Password ”are not available from the normal query window. Instead, you must use the Execute method of the ADO Connection object to run the SQL commands. I particularly like this approach because the VBA code is easy to read. It is also possible to do the same thing by using the DAO library. Yet another approach is the ADOX security library, but this falls down because you cannot specify the user's PID when creating the accounts. Instead, a random PID is generated for you, which makes rebuilding the workgroup impossible . One trick that I worked out by trial and error was using the Null qualifier (which acts as a blank password) in the SQL Alter User admin Password command string. This qualifier resolves the automatic logon into the workgroup by removing the blank password for the Admin account. To complete the VBA code, I have used the RunCommand method to launch the Users and Group Accounts dialog directly from the code. You can verify that the workgroup file is secure by verifying that the Admin account doesn't belong to the Admins group and that the Developer account does belong to the Admins group.

Note	The following code will also work in Access 2000, but you will need a new workgroup file before starting. Create one by using the manual instructions for the Workgroup Administrator executable as described earlier in the section "The Workgroup Administrator for Access 97 and 2000."

 Private Sub cmdSecureWrkGrp_Click() ' Secure the workgroup file to be used with the preceding module ' or in Access 2000, by using a newly created workgroup file. ' This subroutine requires a reference to the ' Microsoft ActiveX Data Objects 2.1 library (or later). Const SECUREUSER = "Developer" Const SECUREPWD = "Developer" Const SECUREPID = "RealWorldDeveloper" Dim cnn As ADODB.Connection On Error GoTo err_cmdSecureWrkGrp If SysCmd(acSysCmdGetWorkgroupFile) = SECUREWRK Then    Set cnn = CurrentProject.Connection    ' No error handling because the users may already exist or belong to the group.    On Error Resume Next    cnn.Execute "create user " & SECUREUSER & " " & SECUREPWD & " " & SECUREPID    cnn.Execute "add user " & SECUREUSER & " to Users"    cnn.Execute "add user " & SECUREUSER & " to Admins"    cnn.Execute "drop user Admin from Admins"    On Error GoTo 0    ' Add a password to the Admin account so that you are forced to log on.    cnn.Execute "alter user admin password " & SECUREPWD & " null;"    Set cnn = Nothing    MsgBox "You have created a new user with adminstration rights called " & _           SECUREUSER & " with a password of " & SECUREPWD & " and a PID of " & _           SECUREPID & vbCrLf & vbCrLf & ". The Admin account also has " & _           "the same password and now has been removed from the Admins group", _           vbInformation, "You Now Will See The Workgroup Users and Groups " & _           "Dialog Form"    RunCommand acCmdUserAndGroupAccounts Else    MsgBox "You have not logged into the " & SECUREWRK & " workgroup file yet", _           vbInformation, "No Action Taken" End If Exit_cmdSecureWrkGrp:   Exit Sub err_cmdSecureWrkGrp:    Select Case Err.Number       Case vbObjectError + 999          ' To see line immediately after the error line, press CTRL+BREAK,          ' drag yellow arrow to Resume Next, then prses F8 key.          Resume Next       Case Else          MsgBox "Error No. " & Err.Number & " -> " & Err.Description    End Select    Resume Exit_cmdSecureWrkGrpEnd Sub End Sub

So what are we going to do with this VBA code? I suggest that once you are happy with your security accounts and groups you create a database with this form in it, modify the workgroup security constants, and use this code as your documentation. Now you can use this code to regenerate the workgroup file in Access 2000 file format (using Access 2002) and any later version. You can also provide your clients with your version of this code so that they can re-create the workgroup file if you happen to step in front of the proverbial bus.