ACC: How to Use DAO to Assign or View Permissions

ID: Q112106

The information in this article applies to:

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:

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)

REFERENCES

For more information about using DAO to create group and user accounts, and about how to assign users to group accounts, please see the following articles in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q124240
   TITLE     : ACC2: Only Admins Group Members Can List Groups They
               Belong To

   ARTICLE-ID: Q112063
   TITLE     : ACC: How to Add a User to a Group with CreateUser Method

Keywords          : kbprg MdlDao ScrtPerm 
Version           : 2.0 7.0
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: August 30, 1997