ACC: How to Use DAO to Assign or View Permissions

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:

1. Type the following text in the Declarations section of a module:

      Option Compare Database
      Option Explicit
      Global Const SUCCESS_SUCCESS = 0

2. 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)