Microsoft KB Archive/112106

= ACC: How to Use DAO to Assign or View Permissions =

Article ID: 112106

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition

-



This article was previously published under Q112106



SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

In Microsoft Access version 7.0 for Windows 95 and Microsoft Access version 2.0, programmers can assign permissions to database objects programmatically using data access objects (DAO). Securing a database involves several steps, including:
 * 1) Creating group accounts.
 * 2) Creating user accounts.
 * 3) Assigning users to groups.
 * 4) Assigning permissions on database objects to users and groups.
 * 5) Encrypting the database, if desired.

This article demonstrates the fourth step, assigning permissions on database objects to users and groups.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications" manual.

NOTE: Visual Basic for Applications (used in Microsoft Access version 7.0 for Windows 95) is called Access Basic in version 2.0.



MORE INFORMATION
To assign permissions for an object, you must reference the UserName and Permissions properties of the object's document. All database objects can be treated as documents in a container. For example, given the DB variable assignment Dim DB As Database Set DB = DBEngine.Workspaces(0).Databases(0) a table called Table1 can be referred to using DAO as follows: DB.Containers("Tables").Documents("Table1") However, it is usually easier to assign and work with object variables as follows: Dim DB as Database, DOC as Document Set DB = DBEngine.Workspaces(0).Databases(0) Set DOC = DB.Containers("Tables").Documents("Table1") You can directly reference the UserName and Permissions properties of the Table1 document like this: UserNameVariable = DOC.UserName PermissionsVariable = DOC.Permissions -or- DOC.UserName = "MyUser" DOC.Permissions = DB_SEC_FULLACCESS

Reading Permissions Information
To read the permissions that a given user has on an object, set the UserName property of the document to the name of the user or group you want to inquire on, and then read the value of the Permissions property.

The following example shows how to read the permissions for the user John on form MyMainForm: Dim DB as Database, DOC as Document, HisPermissions as Long Set DB = DBEngine.Workspaces(0).Databases(0) Set DOC = DB.Containers("Forms").Documents("MyMainForm") DOC.UserName = "John" HisPermissions = DOC.Permissions This example shows how to read the permissions for the group Supervisors on

the report SalarySUMMARY
Dim DB as Database, DOC as Document, GroupPermissions as Long Set DB = DBEngine.Workspaces(0).Databases(0) Set DOC = DB.Containers("Reports").Documents("SalarySummary") DOC.UserName = "Supervisors" GroupPermissions = DOC.Permissions Note that whether you are inquiring on a group or a user, you still set the UserName property. There is no GroupName property for a document.

Assigning Permissions Information
To assign permissions for a user on an object, set the UserName property of the document to the name of the user or group, and then set the Permissions property of the document to the appropriate value.

Generally, you should use the predefined constants when you are assigning permissions. For a complete list of the predefined constants, search for "Constants, Intrinsic," and then "Security Constants" using the Microsoft Access Help menu.

The following example shows how to assign Full permissions for the user Martha on the table Employees: Dim DB as Database, DOC as Document Set DB = DBEngine.Workspaces(0).Databases(0) Set DOC = DB.Containers("Tables").Documents("Employees") DOC.UserName = "Martha" DOC.Permissions = DB_SEC_FULLACCESS The next example shows how to assign Open/Run and Modify Design permissions for the group Developers on the form Customers: Dim DB as Database, DOC as Document Set DB = DBEngine.Workspaces(0).Databases(0) Set DOC = DB.Containers("Forms").Documents("Customers") DOC.UserName = "Developers" DOC.Permissions = DB_SEC_FRMRPT_EXECUTE+DB_SEC_FRMRPT_WRITEDEF

Methods to Programmatically View and Assign Permissions
Method 1:

This method uses a sample subroutine called AssignPerms to update all the objects in a database. To assign permissions for a user on all the objects in a database, you can move through all the documents in all the collections in the database. The following sample subroutine demonstrates one way of moving through all the documents in a database and changing the permissions on them: Sub AssignPerms (GrpUsrName as String, NewPerm as Long) Dim DB as Database, I as Integer, J as Integer Set DB = DBEngine.Workspaces(0).Databases(0) For I = 0 to DB.Containers.Count - 1 For J = 0 to DB.Containers(I).Documents.Count - 1 DB.Containers(I).Documents(J).UserName = GrpUsrName DB.Containers(I).Documents(J).Permissions = NewPerm Next J     Next I  End Sub You can call this subroutine from within code, passing the group or user name and the Permissions value you want to assign.

For example, to revoke all permissions on all objects from the Guests group, call AssignPerms as follows: AssignPerms "Guests", DB_SEC_NOACCESS To assign full permissions on all objects to the Managers account, call AssignPerms as follows: AssignPerms "Managers", DB_SEC_FULLACCESS Method 2:

This method uses two sample functions, GetPermissions and SetPermissions, to display and assign the permissions for a user or group on the database object you specify.

To create the sample functions, follow these steps:   Type the following text in the Declarations section of a module: Option Compare Database Option Explicit Global Const SUCCESS_SUCCESS = 0   Type the following code in the module: ' ****************************************  ' FUNCTION: GetPermissions '  ' Inputs:  UserGrpName - name of a user or group account '         ObjClass    - name of an object container '         ObjName     - name of an object document '  ' Returns: Value of Permissions property or error number '         that was generated. ' ****************************************  Function GetPermissions& (UserGrpName$, ObjClass$, ObjName$)

On Error GoTo Err_GetPermissions

' Set DB to the current database, and set the DOC variable ' .. to the object specified in the arguments. Dim DB As Database, DOC As Document Set DB = DBEngine.Workspaces(0).Databases(0) Set DOC = DB.Containers(ObjClass).Documents(ObjName)

' Set the UserName property of the document to the ' .. user or group you want to obtain the permissions for. DOC.UserName = UserGrpName

' Get the permissions value. GetPermissions = DOC.Permissions

Bye_GetPermissions: Exit Function

Err_GetPermissions: ' If an error occurs, display the message and terminate the ' .. function, returning the error number. MsgBox Err & " " & Error$ GetPermissions = Err Resume Bye_GetPermissions

End Function

' ****************************************  ' FUNCTION: SetPermissions '  ' Inputs:  UserGrpName - name of a user or group account '         ObjClass    - name of an object container '         ObjName     - name of an object document '         NewPerm     - new Permissions value '  ' NewPerm will typically be set by adding together the constants ' predefined for the security options. For a list of the ' constants search Help on "Permissions Property." '  ' Returns: SUCCESS_SUCCESS or the error number that was generated. ' ****************************************  Function SetPermissions& (UserGrpName$, ObjClass$, ObjName$, _      NewPerm&)

On Error Goto Err_SetPermissions

' Set DB to the current database, and set the DOC variable ' to the object specified in the arguments. Dim DB As Database, DOC As Document Set DB = DBEngine.Workspaces(0).Databases(0) Set DOC = DB.Containers(ObjClass).Documents(ObjName)

' Set the UserName property of the document to the ' .. user or group you want to assign the permissions for. DOC.UserName = UserGrpName

' Set the permissions property to the value passed as     ' .. an argument to the function. DOC.Permissions = NewPerm

SetPermissions = SUCCESS_SUCCESS

Bye_SetPermissions: Exit Function

Err_SetPermissions: ' If an error occurs, display the message and terminate the ' .. function, returning the error number. MsgBox Err & " " & Error$ SetPermissions = Err Resume Bye_SetPermissions

End Function 

These functions can be called anywhere you can use an expression in Microsoft Access, including from within code, in the Field, Criteria, or Update To row of a query, or the ControlSource property of a form or report control.

The following example shows how to call the GetPermissions function from another function: Dim ObjProp as Long ObjProp = GetPermissions("User1","Tables","Table1") This function call returns the permissions value for User1 on the table object Table1.

The next example shows how to call the GetPermissions function from the ControlSource property of a control on a form or report with controls that contain the user name, object class, and object name that you want to list the permissions for: ControlName: ObjProp ControlSource: =GetPermissions(Me!UsrName, Me!ObjClass, Me!ObjName) To assign full permissions for the Admins group to a table called MyTable, you can call the SetPermissions function as follows: Dim RETVAL as Long RETVAL = SetPermissions("Admins","Tables","MyTable", DB_SEC_FULLACCESS)

