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:
- Creating group accounts.
- Creating user accounts.
- Assigning users to groups.
- Assigning permissions on database objects to users and groups.
- 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)
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:
Keywords: kbhowto kbprogramming KB112106
