Maintaining Object Ownership and Change of Ownership

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.