Securing Objects

< Day Day Up >

Building tables, indexes, and relationships are activities that affect the schema of a database the design of the objects in the database. That's the "Data Definition" part of the ADOX library name. ADOX also offers facilities for manipulating database security. These facilities match well with the way that Access handles security. ADOX's security features enable you to perform these operations:

Create a group
Create a user
Change object ownership
Set object permissions

This section shows you the basic code to perform these security operations.

Creating a New Group

Access enables you to secure objects so that they are available only to individual users, or to groups of users. Let's start with groups, because it is often more convenient to manage security with groups. If you have many users for your database, you can set rights for groups and then just assign users to the appropriate groups.

To create a new security Group object using ADOX, you just give it a name and append it to the Groups collection of a catalog, as in this procedure:

 Sub CreateGroup()   ' Create a new security group   Dim cat As ADOX.Catalog   ' Set up a catalog pointing to this   ' database   Set cat = New ADOX.Catalog   Set cat.ActiveConnection = CurrentProject.Connection   ' Add a group named Management   cat.Groups.Append "Management"   ' And put the Admin user in this group   cat.Groups("Management").Users.Append "Admin" End Sub

CAUTION

The examples in this section make changes to your default Access workgroup when you actually run them on your computer. The Chapter18 module in the sample database includes a Cleanup procedure to reverse these changes.

After you create a group, you can add users to the group by appending them to the group's Users collection. The users must already exist in the catalog's Users collection. You see how to create users in the next section.

Creating a New User

The code to create a new user is very similar to the code to create a new group:

 Sub CreateUser()   ' Create a new security user   Dim cat As ADOX.Catalog   ' Set up a catalog pointing to this   ' database   Set cat = New ADOX.Catalog   Set cat.ActiveConnection = CurrentProject.Connection   ' Add a User named Mike   cat.Users.Append "Mike", "OriginalPW"   ' And put them in the Management group   cat.Users("Mike").Groups.Append "Management"   ' Change Mike's password   cat.Users("Mike").ChangePassword "OriginalPW", "Fish" End Sub

After you create users, you can put them in a group by adding that group to the users' Groups collection. The group, of course, must already exist. Alternatively, you can add the users to the group's Users collection. The effect is exactly the same.

When you create a new user, you need to include a password for the user. If you change your mind about the password, you can call the ChangePassword method of the User object to change the password. You must know the old password to use this method.

CAUTION

In an actual application, you shouldn't store the password in the code. Anyone who can get at the source code or the MDB file can read the password in this case. A more secure way to handle things is to prompt the users for their desired password at runtime.

Changing Object Ownership

Every object in Access is initially owned by the user who created the object. Changing the ownership of an object doesn't use the Groups or Users collections at all. Instead, you can retrieve ownership information or assign ownership to a different user with methods of the Catalog object. Here's an example:

 Sub ChangeOwner()   ' Change the owner of an object   Dim cat As ADOX.Catalog   Dim strOwner As String   ' Set up a catalog pointing to this   ' database   Set cat = New ADOX.Catalog   Set cat.ActiveConnection = CurrentProject.Connection   ' Show the current owner of the Clients table   strOwner = cat.GetObjectOwner("Clients", adPermObjTable)   Debug.Print "Clients is owned by " & strOwner   ' Change the ownership   cat.SetObjectOwner "Clients", adPermObjTable, "Mike"   Debug.Print "Clients is now owned by Mike" End Sub

Both the GetObjectOwner and SetObjectOwner methods take an object type parameter that indicates the type of database object to work with. When working with Access, this parameter can have any of these values:

adPermObjProcedure for a query with parameters
adPermObjTable for a table
adPermObjView for a query without parameters
adPermObjProviderSpecific for other objects
adPermObjColumn for a column
adPermObjDatabase for a database

If you specify adPermObjProviderSpecific, you must provide an additional parameter to the GetObjectOwner or SetObjectOwner method:

 cat.GetObjectOwner ObjectName, ObjectType, ObjectTypeId cat.SetObjectOwner ObjectName, ObjectType, UserName, ObjectTypeId

In this case, the ObjectTypeId is a GUID (globally unique ID) to refer to an object. You can use one of these GUIDs:

{C49C842E-9DCB-11D1-9F0A-00C04FC2C2E0} for a form
{C49C8430-9DCB-11D1-9F0A-00C04FC2C2E0} for a report
{C49C842F-9DCB-11D1-9F0A-00C04FC2C2E0} for a macro
{C49C8432-9DCB-11D1-9F0A-00C04FC2C2E0} for a module

This two-step process of first choosing adPermObjProviderSpecific, and then supplying a GUID is necessary because ADOX works with databases other than Access (such as SQL Server or Oracle). Those databases don't have objects such as forms or reports, so there's no need for ADOX to include them in its core set of constants.

Setting Object Permissions

To set the permissions on an object, you call the SetPermissions method of either a user or a group. Here's an example:

 Sub SetPermissions()   ' Set permissions on an object   Dim cat As ADOX.Catalog   ' Set up a catalog pointing to this   ' database   Set cat = New ADOX.Catalog   Set cat.ActiveConnection = CurrentProject.Connection   ' Grant Mike permissions on the Clients table   cat.Users("Mike").SetPermissions "Clients", adPermObjTable, _    adAccessSet, adRightFull End Sub

The SetPermissions method has four required and two optional arguments:

Name is the name of the object to manipulate.
ObjectType is the type of the object.
Action is adAccessDeny to remove the specified permissions, adAccessGrant to add the specified permissions to any already in place, adAccessRevoke to remove all permissions, or adAccessSet to set the exact permissions supplied.

Rights is a constant indicating which permissions to grant. Table 18.1 shows the possible values for this constant.

Table 18.1. Rights Constants for ADOX
Constant	Meaning
`adRightCreate`	Permission to create new objects of the specified type
`adRightDelete`	Permission to delete data
`adRightDrop`	Permission to delete objects of the specified type
`adRightExclusive`	Permission to lock the object exclusively
`adRightExecute`	Permission to execute
`adRightFull`	All possible permissions on the object
`adRightInsert`	Permission to insert data
`adRightMaximumAllowed`	All possible permissions, including provider-specific permissions
`adRightNone`	No permissions on the object
`adRightRead`	Permission to read data
`adRightReadDesign`	Permission to retrieve schema information
`adRightReadPermissions`	Permission to retrieve permission information
`adRightReference`	Permission to reference
`adRightUpdate`	Permission to edit existing data
`adRightWithGrant`	Permission to grant permissions
`adRightWriteDesign`	Permission to modify the design of the object
`adRightWriteOwner`	Permission to change object ownership
`adRightWritePermissions`	Permission to change the specified permissions

The optional Inherit parameter can be set to a constant to control whether objects contained within the specified object should inherit the specified permissions. You're unlikely to need this parameter.
The optional ObjectTypeId is used when the ObjectType is set to adPermObjProviderSpecific, just as it is with the ownership methods.

CASE STUDY: Creating a Data Dictionary

To demonstrate a practical use for ADOX, we've implemented a simple data dictionary in this chapter's case study. A data dictionary is a list of all the fields in a database together with some information about those fields. One of its best uses is to check for inconsistencies. For example, if you discover that some tables define CustomerID as a number, whereas others define a field with the same name as text, there's probably something wrong.

This particular data dictionary implementation shows you the column name, table name, and data type for every column in your database. It's built from a single table and a single form. You can use it in any database simply by importing the table and the form to the new database and then opening the form. Here's how you can build this data dictionary from scratch:

Create a new table containing three fields, TableName (text, 255 characters), ColumnName (text, 255 characters), and Type (text, 50 characters). Set TableName and ColumnName together to be a composite primary key for the table. Save the table as DataDictionary.
Create a new form and save it as Data Dictionary. Set the Record Selectors property to No, the Navigation Buttons property to No, and the Caption property to Data Dictionary.
Add a ListBox control to the form and name it lstColumns. Set the Column Count property of the control to 3 and set the Row Source property to SELECT DataDictionary.ColumnName, DataDictionary.TableName, DataDictionary.Type FROM DataDictionary ORDER BY DataDictionary.ColumnName, DataDictionary.TableName.

Add this code to the form's module:

Option Compare Database Option Explicit Private Sub Form_Open(Cancel As Integer) ' Rebuild the data dictionary on form open Dim cnn As ADODB.Connection Dim cmdDelete As ADODB.Command Dim rstDictionary As ADODB.Recordset Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim col As ADOX.Column ' Clear the existing dictionary Set cnn = CurrentProject.Connection Set cmdDelete = New ADODB.Command Set cmdDelete.ActiveConnection = cnn cmdDelete.CommandText = "DELETE * FROM DataDictionary" cmdDelete.Execute ' Now stock the new dictionary Set rstDictionary = New ADODB.Recordset rstDictionary.Open "SELECT * FROM DataDictionary", _ cnn, adOpenStatic, adLockOptimistic ' Set up a catalog pointing to this ' database Set cat = New ADOX.Catalog Set cat.ActiveConnection = CurrentProject.Connection ' Iterate through all tables and fields For Each tbl In cat.Tables ' Block system tables If Left(tbl.Name, 4) <> "MSys" And _ Left(tbl.Name, 4) <> "USys" And _ Left(tbl.Name, 4) <> "~TMP" Then For Each col In tbl.Columns rstDictionary.AddNew rstDictionary.Fields("TableName").Value = tbl.Name rstDictionary.Fields("ColumnName").Value = col.Name rstDictionary.Fields("Type").Value = TranslateType(col

.Type) rstDictionary.Update Next col End If Next tbl rstDictionary.Close End Sub Public Function TranslateType(intType As Integer) As String ' Get the Access data type corresponding to ' an ADOX data type Select Case intType Case adUnsignedTinyInt TranslateType = "Byte" Case adCurrency TranslateType = "Currency" Case adDate TranslateType = "Date/Time" Case adNumeric TranslateType = "Decimal" Case adDouble TranslateType = "Double" Case adLongVarWChar TranslateType = "Memo" Case adSmallInt TranslateType = "Integer" Case adInteger TranslateType = "Long Integer" Case adLongVarBinary TranslateType = "OLE Object" Case adGUID TranslateType = "Replication ID" Case adSingle TranslateType = "Single" Case adVarWChar TranslateType = "Text" Case adBoolean TranslateType = "Yes/No" End Select End Function Private Sub Form_Load() ' The underlying table has just been ' restocked, so requery the listbox lstColumns.Requery End Sub

This code might look complex, but it brings together ADO and ADOX techniques that you've already seen. When you open the form, it starts by using the Execute method of a Command object to clear out the DataDictionary table. This ensures that the table is refreshed every time that the form is opened. It then uses ADO to open a new Recordset on the now-empty table.

The code switches to ADOX to iterate through all the tables in the database, and through all the columns in each table. There's a rough-and-ready check designed to skip system tables and temporary objects, which starts with one of a small set of prefixes. For each column, the code adds a row to the Recordset.

The TranslateType function translates the ADOX column type constants into the more familiar Access data types. When the entire Recordset has been stocked, the form re-queries the listbox. Figure 18.2 shows the result.

Figure 18.2. The Data Dictionary in action.

graphics/18fig02.gif

TIP

One thing to notice about this technique is that there's no built-in feature or property that sorts an unbound listbox in Access. The easiest way to get a sorted list is to save your data to a table, and then retrieve that same data and sort it using an ORDER BY clause.