Setting User Permissions for Containers

Now that you've learned how to grant permissions to a user for a specific object such as a table or query, you may want to know how to specify permissions for a whole set of objects such as tables, queries, forms, reports, and macros.

Each Database object has a Containers collection consisting of built-in Container objects. A Container object groups together similar types of Document objects. You can use the Containers collection to set security for all Document objects of a given type. You can set the permissions that users and groups will receive by default on all newly created objects in a database by passing in Null for the object name argument of the ADOX SetPermissions method, as shown in the example procedure in Hands-On 17-12. The code of this procedure gives the PowerUser account (recall that this account was created in Hands-On 17-2) the permission to design, read, update, insert, and delete data for all newly created tables and queries. Notice that Null is passed as the first argument of the SetPermissions method to indicate that permissions are to be set only on new objects of the type specified by the second argument of this method.

User arid Group Permissions

Pamissiorw Change Owner

User/Group Name:

Permissions |"1 Open/Run PI Open Exclusive lUi Modify Design □ Administer

Current User: Developer

Object Name:

User arid Group Permissions

Pamissiorw Change Owner

User/Group Name:

Object Name:

Admin

Developer

List: ©Users 0Groups

Object Type: l^-'AI.WM v

Permissions |"1 Open/Run PI Open Exclusive lUi Modify Design □ Administer

J Read Data Q Update Data HI Insert Data ODete^Data

J Read Data Q Update Data HI Insert Data ODete^Data

Creating and Manipulating Databases with ADO

©Hands-On 17-12: Setting User Permissions for Containers

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

Sub Set_UserContainerPermissions() Dim conn As ADODB.Connection Dim cat As ADOX.Catalog Dim strDB As String Dim strSysDb As String

On Error GoTo ErrorHandle strDB = "C:\BookProject\SpecialDb.mdb" strSysDb = "C:\BookProject\Security.mdw"

' 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

' add a user account cat.Users.Append "PowerUser", "star"

' Set permissions for PowerUser on the Tables Container cat.Users("PowerUser").SetPermissions Null, _ adPermObjTable, _ adAccessSet, _ adRightRead Or _ adRightInsert Or _ adRightUpdate Or _ adRightDelete, adInheritNone MsgBox "You have successfully granted permissions " & vbCrLf & _ "to PowerUser on the Tables Container."

ExitHere:

Set cat = Nothing conn.Close Set conn = Nothing Exit Sub ErrorHandle:

If Err.Number = -2147467259 Then

' because PowerUser user already exists ' we ignore this statement Resume Next

Part II

Else

MsgBox Err.Description Resume ExitHere End If End Sub

After executing the procedure above, the user account PowerUser has the permissions listed in Figure 17-21 on all newly created Table and Query objects.

User arid Group Permissions

PamissiorB Change Owner

User/Group Name:

O Groups

List: ©Users Permissions

I_I Open/Run

PI Read Design

□ Modify Design

□ Administer

Current User: Developer

Object Name:

User arid Group Permissions

PamissiorB Change Owner

User/Group Name:

Object Name:

Admin

Developa^^^^^^^^^^

Categories

Customers

Employees

Order Detdls

Orders

List: ©Users Permissions

I_I Open/Run

PI Read Design

□ Modify Design

□ Administer

Object Type:

E Read Data 0 Update Data 0 Insert Data B Delete Data

Object Type:

E Read Data 0 Update Data 0 Insert Data B Delete Data

Figure 17-21: The following settings are found in the User and Group Permissions window after running the Set_UserContainerPermissions procedure in Hands-On 17-12.

0 -1

Post a comment