لطفا منتظر باشید ...
Assigning and Revoking Permissions to Objects Using Code
NOTEYou will see the terms permissions and rights used interchangeably throughout this chapter.
 | Often, you will want to assign and revoke object permissions using code. Once again, you can easily accomplish this by using ADO code. The frmTableRights form in Figure 28.5 enables administrative users to assign rights to tables, and is located in the CHAP28EX.MDB database. |
Figure 28.5. This form enables administrative users to assign rights to tables.
The code in Listing 28.21 assigns view rights for the table selected in the Select a Table list box to the group selected in the Group Name drop-down list in Figure 28.5.
Listing 28.21 Assigning View Rights
Private Sub cmdViewRights_Click()
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
cat.Groups(Me.cboGroupName.Value).SetPermissions _
Me.lstTables.Value, _
ObjectType:=adPermObjTable, _
Action:=adAccessSet, _
Rights:=adRightRead
End Sub
The SetPermissions method of the Groups object is used to establish the permissions for the table. The rights for the table are granted to the group specified in the cboGroupName combo box. The table to which the rights are granted is designated in the lstTables list box. The object type to which the rights are assigned is designated in the ObjectType parameter. The constant adPermObjTable is used to specify the object type as a table. The Action parameter is used to designate the type of action being taken. The constant adAccessSet is used to indicate that the rights are being established. The Rights parameter is used to specify the rights being granted. The constant adRightRead is used to assign read rights to the table. Table 28.1 lists some of the permission constants for queries and tables.
| Permission Constant | Grants Permission To |
|---|---|
| adRightDelete | Delete rows from the table or query. |
| adRightInsert | Insert new rows into the table or query. |
| adRightReadDesign | Read the definition of the table or query. |
| adRightUpdate | Modify table or query data. |
| adRightRead | Read data stored in the table or query. Also, implicitly grants read permission to the definition of the table or query. |
| adWriteDesign | Alter the definition of the table or query. |
| adRightWithGrant | Grant permissions for the object. |
| adRightAll | Grant all rights to the object. |
Listing 28.22 Modifying User Rights
Private Sub cmdModifyRights_Click()
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
cat.Groups(Me.cboGroupName.Value).SetPermissions _
Me.lstTables.Value, _
ObjectType:=adPermObjTable, _
Action:=adAccessSet, _
Rights:=adRightRead Or adRightUpdate
End Sub
Determining Whether a Group Has Permissions to an Object
It is useful to be able to determine whether a user has permissions to an object. This is easy to accomplish using the ADOX object library. The code appears in Listing 28.23.
Listing 28.23 Determining User Permissions to an Object
Private Sub cmdHasPermission_Click()
Dim boolCanRead As Boolean
Dim cat As ADOX.Catalog
Dim grp As ADOX.Group
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Determine if the Group selected in the cboGroupName combo box
'has the permissions selected in the cboPermissions combo box
'to the table selected in the lstTables list box
Set grp = cat.Groups(Me.cboGroupName.Value)
boolCanRead = ((grp.GetPermissions(Me.lstTables.Value, _
adPermObjTable) _
And Val(Me.cboPermissions.Value)) = _
Val(Me.cboPermissions.Value))
MsgBox boolCanRead
End Sub
The code points a Group object at the group selected in the cboGroupName combo box. It then uses the GetPermissions method of the Group object to retrieve the permissions the group has on the specified table. The GetPermissions method receives the name of the object whose permissions you want to retrieve, as well as a constant designating the type of object. GetPermissions returns a long integer indicating all the permissions for an object. Each bit of the integer indicates a different permission or right. You evaluate a specific permission using a bitwise AND, along with a constant for that particular permission. This masks off the bits for the specific permission that you are interested in. When compared to the same bits, the expression evaluates True if the group has the permissions and False if it does not. Refer to Table 28.1 for a list of some of the permissions that you can test for.The code in Listing 28.23 evaluates whether a group has one type of rights to the selected table. You can evaluate one type of rights at a time, or you can test for several rights simultaneously. The code in Listing 28.24 evaluates the Users group to see whether members have Read, Update, Insert, and Delete rights to the table selected in the lstTables list box.
Listing 28.24 Determining Whether a Group Has Multiple Permissions to an Object
Private Sub cmdHasMultiple_Click()
Dim boolRights As Boolean
Dim cat As ADOX.Catalog
Dim grp As ADOX.Group
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Determine if the Group selected in the cboGroupName combo box
'has Read, Update, Insert, and Delete permissions
'to the table selected in the lstTables list box
Set grp = cat.Groups(Me.cboGroupName.Value)
boolRights = ((grp.GetPermissions(Me.lstTables.Value, _
adPermObjTable) _
And (adRightRead Or _
adRightUpdate Or _
adRightInsert Or _
adRightDelete)) = _
(adRightRead Or _
adRightUpdate Or _
adRightInsert Or _
adRightDelete))
MsgBox boolRights
End Sub
The example combines rights using a bitwise OR. The expression returns True only if the selected group has all rights designated (Read, Update, Insert, and Delete).
Determining Whether a User Has Permissions to an Object
The code in Listings 28.23 and 28.24 showed how you can evaluate an object to determine whether a particular group has rights to it. The process to determine whether an individual user has rights to an object is almost identical, and is shown in Listing 28.25.
Listing 28.25 Determining Whether a User Has Permissions to an Object
Private Sub cmdUserHasPermission_Click()
Dim boolCanRead As Boolean
Dim cat As ADOX.Catalog
Dim usr As ADOX.User
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Determine if the User selected in the cboUserName combo box
'has the permissions selected in the cboPermissions combo box
'to the table selected in the lstTables list box
Set usr = cat.Users(Me.cboUserName.Value)
boolCanRead = ((usr.GetPermissions(Me.lstTables.Value, _
adPermObjTable) _
And Val(Me.cboPermissions.Value)) = _
Val(Me.cboPermissions.Value))
MsgBox boolCanRead
End Sub
The code in Listing 28.25 points a User object at the user selected in the cboUserName combo box. It then uses the GetPermissions method of the User object to retrieve the permissions the user has on the specified table. The GetPermissions method receives the name of the object whose permissions you want to retrieve, as well as a constant designating the type of object whose permissions you want to retrieve. GetPermissions returns a long integer indicating all the permissions for an object. Each bit of the integer indicates a different permission or right. You evaluate a specific permission using a bitwise AND, along with a constant for that particular permission. This masks off the bits for the specific permission that you are interested in. When compared to the same bits, the expression evaluates True if the user has the permissions and False if she does not.
Determining Whether a User Has Implicit Rights to an Object
If a user is not explicitly assigned rights to an object, he might have implicit rights to the object. Implicit rights exist if a group that the user belongs to has rights to the object. The code in Listing 28.26 evaluates whether a user has implicit rights to the object.
Listing 28.26 Determining Whether a User Has Implicit Rights to an Object
Private Sub cmdImplicit_Click()
Dim boolCanRead As Boolean
Dim cat As ADOX.Catalog
Dim usr As ADOX.User
Dim grp As ADOX.Group
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Determine if the User selected in the cboUserName combo box
'has the permissions selected in the cboPermissions combo box
'to the table selected in the lstTables list box
Set usr = cat.Users(Me.cboUserName.Value)
boolCanRead = ((usr.GetPermissions(Me.lstTables.Value, _
adPermObjTable) _
And Val(Me.cboPermissions.Value)) = _
Val(Me.cboPermissions.Value))
'If the user does not have permissions, see if any group she
'belongs to has permissions
If Not boolCanRead Then
For Each grp In usr.Groups
boolCanRead = ((grp.GetPermissions(Me.lstTables.Value, _
adPermObjTable) _
And Val(Me.cboPermissions.Value)) = _
Val(Me.cboPermissions.Value))
If boolCanRead Then
Exit For
End If
Next grp
End If
MsgBox boolCanRead
End Sub
The code first evaluates whether the user has the designated rights for the object. If the user does not have rights to the object, the code loops through each group that the user is a member of. If it finds the designated rights for any group that the user is a member of, it exits the loop, returning True.
Setting Permissions to New Objects
Earlier in the chapter, you learned how to set permissions for existing objects. You might also want to programmatically assign the permissions a user is granted for new objects. The process is shown in Listing 28.27.
Listing 28.27 Programmatically Assigning Rights Assigned to New Objects
Private Sub cmdNewObjects_Click()
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Uses the SetPermissions method with a zero-length
'string for the Name parameter, and adInheritObjects
'for the Inherit parameter to assign Read rights to all
'new tables
cat.Groups(Me.cboGroupName.Value).SetPermissions _
", _
ObjectType:=adPermObjTable, _
Action:=adAccessGrant, _
Rights:=adRightRead, _
Inherit:=adInheritObjects
End Sub
The code uses the SetPermissions method of the Group object to assign permissions to the group. Notice that the first parameter to the SetPermissions object, the Name parameter, is a zero-length string. The Name parameter, along with the adInheritObjects value for the Inherit parameter, dictates that the rights being assigned apply to new objects.
Manipulating Database Permissions
I've covered how to assign permissions to the objects in a database. Often you will want to programmatically grant or remove rights to the database. You can programmatically determine whether a user or group can open the database, open the database exclusively, and more. The code in Listing 28.28 assigns rights for the database to the group selected in the cboGroupName combo box.
Listing 28.28 Manipulating Database Permissions
Private Sub cmdDatabaseRights_Click()
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Uses the SetPermissions method with an
'ObjectType of adPermObjDatabase, an
'Action of adAccessGrant, and Rights of
'adRightRead to assign Open rights for the group
cat.Groups(Me.cboGroupName.Value).SetPermissions _
", _
ObjectType:=adPermObjDatabase, _
Action:=adAccessGrant, _
Rights:=adRightRead
End Sub
The code uses the SetPermissions method of the group object to assign rights for the database to the group selected in the cboGroupName combo box. Notice the name parameter is a zero-length string. The ObjectType parameter is adPermObjDatabase, and the Rights are set to adRightRead. These parameters grant open rights for the database to the selected group.
