Determining the Object Owner

The database, and every object in the database, has an owner. The owner is the user that created that particular object. The object owner has special privileges. He or she can always assign or revoke permissions for that object. To retrieve the name of the object owner, use the GetObjectOwner method of a Catalog object. This method takes two parameters: the object's name and type. For example, to determine the owner of a table, use the following syntax cat.GetObjectOwner(myObjName, adPermObjTable)

where cat is an object variable representing the ADOX Catalog object, myObjName is the name of a database table, and adPermObjTable is a built-in ADOX constant specifying the type of object. The constants for the Type parameter can be looked up in the Object Browser, as shown in Figure 17-17.

Set cat = Nothing conn.Close

Set conn = Nothing

MsgBox "Groups and Users are listed in the Immediate window." End Sub

Immediate

CI

Group Name: Admins

User Nam®; D®uelop«r

Group Name: Elite

There are no users in the Elite group.

Group Name: Users

User Name: admin

User Naftie: Deueloper

m

Creating and Manipulating Databases with ADO

Figure 17-17: The Object Browser displays the required constants for the Type parameter of the GetObjectOwner method.

© Hands-On 17-9: Retrieving the Name of the Object Owner

1. Insert a new module and, in the module's Code window, enter the Get_ObjectOwner procedure as shown below.

Sub Get_ObjectOwner()

Dim conn As ADODB.Connection Dim cat As ADOX.Catalog Dim strObjName As Variant Dim strDB As String Dim strSysDb As String strDB = "C:\BookProject\SpecialDb.mdb" strSysDb = "C:\BookProject\Security.mdw" strObjName = "Customers"

' Open connection to the database using ' the specified system database Set conn = New ADODB.Connection With conn

.Provider = "Microsoft.Jet.OLEDB.4.0" .Properties("Jet OLEDB:System Database") = strSysDb .Properties("User ID") = "Developer" .Properties("Password") = "chapter17" .Open strDB End With

' Open the catalog

Set cat = New ADOX.Catalog cat.ActiveConnection = conn

' Display the name of the table owner

MsgBox "The owner of the " & strObjName & " table is " & vbCr _ & cat.GetObjectOwner(strObjName, adPermObjTable) & "."

Part II

Set cat = Nothing conn.Close Set conn = Nothing End Sub

To set the ownership of an object by using ADOX, use the SetObjectOwner method of the Catalog object like this:

cat.SetObjectOwner("Customers", adPermObjTable, "PowerUser")

The above statement says that the ownership of the Customers table is to be transferred to the user named PowerUser. Note that currently there is no such user in the SpecialDb database. We created the PowerUser account in Hands-On 17-2 and deleted it in Hands-On 17-4. If you want to experiment with changing object ownership, you need to make appropriate changes in the example procedure using the information you have already learned.

0 0

Post a comment