7.9. Managing Jet Security with ADO

Managing Access security in DAO was discussed in Chapter 6, and since the same methodology is used in ADO, we won't labor the point here. However, we will highlight the properties and methods available in ADO that aren't available in DAO.

The first thing to be aware of is that the ADO security objects are children of the Catalog object. For example, the Users and Groups collections are both accessed thus:

Dim cat As New ADOX.Catalog

cat.ActiveConnection = CurrentProject.Connection

Debug.Print cat.Groups(0).Name
Debug.Print cat.Users(0).Name

7.9.1. Creating Groups and Users

Once you've instantiated a Catalog object, you can create a new group in ADO by simply declaring its name as you append it to the Catalog object's Groups collection:

cat.Groups.Append "MyNewGroup"

To create a new user, all you need to do is to create a User object, set its name, and password, and then append it to the Catalog object's Users collection. The following example demonstrates just how easy it is to create a new group, a new user, and then add the user to a group.

Dim cat As ADOX.Catalog
Dim usr As ADOX.User

'Create the Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

'Create the new user
Set usr = New ADOX.User
usr.Name = "Doris Crockford"
usr.ChangePassword "", "sherbert_lemon"

To change the password at some later stage, just reissue the ChangePassword method using the following syntax:

userobject.ChangePassword oldpassword, newpassword

At this point we have a fully qualified User object. Now we append it to the Catalog object's Users collection.

'Append the new user to the collection
cat.Users.Append usr

A valid user account isn't much good to anyone if they don't belong to any groups, so let's add Doris to both Admins and Users groups.

'Add the new user to the Admins and Users groups
usr.Groups.Append "Admins"
usr.Groups.Append "Users"

'Clean up
Set cat.ActiveConnection = Nothing
Set cat = Nothing

How hard is that? Of course, to rename a user account, you must delete it, and then re-create it.

7.9.2. Managing Permissions

Setting permissions in ADO is even easier than it is in DAO. The Catalog object's Users and Groups collections manage permissions:

GroupOrUser.SetPermissions ObjectName, ObjectType, Action, Rights [,
Inherit] [, ObjectTypeID]

The ObjectTypeID parameter is an optional variant value that specifies the GUID for a provider object type that isn't defined by the OLE DB specification. This parameter is only used if you set ObjectType to adPermObjProviderSpecific. You should refer to the provider documentation for the specific GUID to use. The remaining parameter values for the SetPermisions method are listed in Appendix J. But, for now, let's take a look at how it works.

Dim cat As New ADOX.Catalog
Dim lngPermit As Long

'Create the Catalog
Set cat.ActiveConnection = CurrentProject.Connection

For this test, we want to remember the original permissions for the Customers table.

'Get the original permissions, so we can
'restore them when we've finished
lngPermit = cat.Users("Doris Crockford").GetPermissions( _
        "Customers", adPermObjTable)

Debug.Print "The original permissions were: " & CStr(lngPerm)

OK, so now we know what permissions Doris had. Let's play with her permissions for a while.

'Revoke all permissions to the Customers table
cat.Users("Doris Crockford").SetPermissions _
        "Customers", adPermObjTable, adAccessRevoke, adRightFull

Debug.Print "Permissions revoked. They are now: " & _
        CStr(cat.Users("Doris Crockford").GetPermissions( _
                "Customers", adPermObjTable))

'Now grant the Admin user full rights on the Customers table
cat.Users("Doris Crockford").SetPermissions _
        "Customers", adPermObjTable, adAccessSet, adRightFull

Debug.Print "Full permissions granted. They are now: " & _
        CStr(cat.Users("Doris Crockford").GetPermissions( _
                "Customers", adPermObjTable))

OK, she's had enough. She wants to get back to work, so let's restore her permissions for the table.

'Finally, restore the original permissions
cat.Users("Doris Crockford").SetPermissions _
        "Customers", adPermObjTable, adAccessSet, lngPermit

Debug.Print "Permissions restored: " & _
        CStr(cat.Users("Doris Crockford").GetPermissions( _
                "Customers", adPermObjTable))

'Clean up
Set cat.ActiveConnection = Nothing
Set cat = Nothing

Note: We can do exactly the same thing to an entire group, using exactly the same code, but replacing cat.Users with cat.Groups. Now isn't that cool?