Real World Microsoft Access Database Protection and Security

Once you have transferred ownership by importing all the objects into a blank database, you probably should not concern yourself with object ownership for a while. When you do, you may find that you have a problem with mixed ownership. In addition, you may wonder how good your permissions (covered in the next section) are at stopping the anonymous Admin account from reversing the ownership changes. To help you with these issues, I have developed an Access form called frmCheckandChangeOwnership (shown in Figure 8-17).

Figure 8-17: The frmCheckandChangeOwnership demonstration form.

The purpose of this form is to check and list ownership of all the objects (database, tables, forms, and so on) in your database. The output of this software, demonstrated as follows , displays in the Immediate window (which you can open by pressing CTRL+G). The software will also change the ownership of the object to the current user , if required.

>>> Start Of List of Other Owners of Objects @ 9:20:59 AM --- DATABASES Container--- AccessLayout ... admin MSysDb ... admin SummaryInfo ... admin UserDefined ... admin --- FORMS Container--- Categories ... admin Customer Labels Dialog ... admin Startup ... admin Suppliers ... admin --- MODULES Container--- Startup ... admin Utility Functions ... admin --- RELATIONSHIPS Container--- {1F8E2C3D-2471-4FFE-8F53-7DAD3224ADC8} ... admin {2B241988-7600-49E9-9686-A12B933079BA} ... admin --- REPORTS Container--- Alphabetical List of Products ... admin Catalog ... admin Summary of Sales by Quarter ... admin Summary of Sales by Year ... admin --- SCRIPTS Container--- Customer Labels Dialog ... admin Sample Autokeys ... admin Suppliers ... admin --- SYSREL Container--- --- TABLES Container--- Order Details Extended ... admin Order Subtotals ... admin Orders ... admin Product Sales for 1995 ... admin Products ... admin Ten Most Expensive Products ... admin <<< End of List of Other Owners of Objects 9:20:59 AM

To produce this output and change ownership, if required, I have used the Microsoft DAO library, which allows the code to list all objects that are not owned by the current user account. To produce this output, the VBA code iterates through all the document collections. If you are interested in how the ownership is changed, look through the code that follows for the line that includes doc.Owner . The VBA code that produces this output follows:

Private Sub cmdFindOwners_Click() ' Find all the owners of the database objects. ' If requested, change the ownership of foreign objects to ' the current user. ' This subroutine requires a reference to the ' Microsoft DAO 3.6 or 3.51 library. On Error GoTo err_cmdFindOwners Dim dbs As DAO.Database, cnt As DAO.Container, doc As DAO.Document Dim objName As String, strDocName As String, strCurrUser Dim i As Integer, intChangeUser As Integer Dim strDocOwner As String strCurrUser = CurrentUser intChangeUser = MsgBox("Do you want to attempt to change the ownership of " & _ "all objects in this database to " & strCurrUser & _ " (Yes)" & vbCrLf & vbCrLf & "or list the other " & _ "owners of objects in this database (No)", _ vbYesNoCancel + vbDefaultButton2, _ "List Other Owners (and change ownership)") If intChangeUser <> vbCancel Then Debug.Print vbCrLf & ">>> Start Of List Of Other Owners Of Objects @ " & Time Set dbs = CurrentDb() For Each cnt In dbs.Containers ' Cycle through all the containers. Debug.Print vbCrLf & "--- " & UCase(cnt.Name) & " Container---" & vbCrLf For Each doc In cnt.Documents If intChangeUser = vbYes Then ' Change the owner of this document to the current owner. ' Will occur only if current user has appropriate permissions. On Error Resume Next doc.Owner = strCurrUser On Error GoTo err_cmdFindOwners End If strDocOwner = doc.Owner If strDocOwner <> "Engine" And _ LCase(strDocOwner) <> LCase(strCurrUser) Then ' Only look at nonsystem objects that are not owned by this user. strDocName = doc.Name Debug.Print " " & strDocName & Chr(9) & " ... " & strDocOwner End If Next doc Next cnt ' Open the debug window. ' Warning: Databases with large numbers of objects may have their lists trimmed. Debug.Print vbCrLf & "<<< End of List Of Other Owners Of Objects " & Time RunCommand acCmdDebugWindow End If Exit_cmdFindOwners: On Error Resume Next Set doc = Nothing Set cnt = Nothing Set dbs = Nothing Exit Sub err_cmdFindOwners: Select Case Err.Number ' Problems with unload process Case vbObjectError + 999 ' To see line immediately after the error line, press CTRL+BREAK, ' drag yellow arrow to Resume Next, then press F8. Resume Next Case Else MsgBox "Error No. " & Err.Number & " -> " & Err.Description End Select Resume Exit_cmdFindOwners End Sub

This form will also prove useful when you have completed the next section of the chapter on permissions, because you may want to test that the Admin account cannot regain permissions for objects because they are the owner of the objects. To undertake this test, take a copy of the database, import the frmCheckandChangeOwnership form into the database, and run this code. If this form can change the ownership of an object back to the anonymous Admin account, you will need to check your permissions.

Now that we have seen how to view, manipulate, and test ownership by using automated techniques, the next stage in the path to securing your database involves assigning and removing database permissions.