MS-Access / Getting Started

---

Managing Passwords

To change a user's password, you execute the User object's NewPassword method. You must provide both the old and new passwords. However, if you are a member of the Admins groups and are changing the password of another user, the old password argument is ignored.

Public Sub ChangePassword(strUser As String, _
    strOldPassword As String, _
    strNewPassword As String)

    Dim wrk As DAO.Workspace
    Dim usr As DAO.User

    Set wrk = DBEngine(0)
    Set usr = wrk.Users(strUser)

    'Change the password
    usr.NewPassword strOldPassword, strNewPassword

    Set usr = Nothing
    Set wrk = Nothing
End Sub

The issue, of course, is that there is no way to view the password for any user.

Managing Permissions

Computer systems grant or deny access to objects and data based on the rights assigned to users or groups of users. These permissions are granted by the system administrator or owner of an object. Further, specific users can be granted or denied special access in addition to the rights of the group to which they belong.

In Access, you can grant permissions based on a specific user or group. Permissions can be removed from a user or group, but you cannot deny permissions in the same manner as you can in Windows or SQL Server. There is no explicit deny for permissions in a database.

In Access, user and group permissions are defined in two places. First, permissions relating to individual objects are stored in the Permissions property of Document objects. Second, permissions for objects that are created later are stored in the Permissions property of Container objects.

Depending on the specific object, different permissions can be granted. The following table describes those permissions and the constants that define them.

Object	Permission Constant	Value	Description
Container	dbSecNoAccess	0	No access to the object
	dbSecFullAccess	1048575	Full access to the object
	dbSecDelete	65536	Can delete the object
	dbSecReadSec	131072	Can read the object's security information
	dbSecWriteSec	262144	Can change the object's security information
	dbSecWriteOwner	524288	Can change the ownership of the object
Table	dbSecCreate	1	Can create new Document objects (valid only with a Container object)
	dbSecReadDef	4	Can read the table definition
	dbSecWriteDef	65548	Can modify or change the table definition
	dbSecRetrieveData	20	Can retrieve data from the Document object
	dbSecInsertData	32	Can add records
	dbSecReplaceData	64	Can modify records
	dbSecDeleteData	128	Can delete records
Database	dbSecDBAdmin	8	Assigns admin rights - can create replicas, change the database password, and set startup properties
	dbSecDBCreate	1	Can create new databases (valid only on the Databases container object in the Workgroup Information File)
	dbSecDBExclusive	4	Can open the database exclusively
	dbSecDBOpen	2	Can open the database
Macro	acSecMacExecute	8	Can run the macro
	acSecMacReadDef	10	Can read the macro's definition
	acSecMacWriteDef	65542	Can modify the macro's definition
	acSecFrmRptExecute	256	Can open the form or report
	acSecFrmRptReadDef	4	Can read the form's or report's definition and its module
	acSecFrmRptWriteDef	65548	Can modify the form's or report's definition and its module

Reading Permissions

As mentioned earlier, object permissions are stored in two main places: the Permissions property of Document objects, and the Permissions property of Container objects, the latter being where the permissions for future objects are defined. But before you get too carried away with this new found knowledge, you might be interested to know that object permissions are stored in a Long Integer bit field. To get at individual permissions, you need to perform a bitwise operation, which is not very difficult.

To determine the permissions that the current user has to Table1, for example, just read the Permissions property of its Document object:

Debug.Print dbs.Containers("Tables").Documents("Table1").Permissions

Be aware that the Permissions property returns only explicit permissions, which are those that are explicitly defined for that particular user. Implicit permissions, which are returned by the AllPermissions property, are the sum of all the permissions the user has, whether explicitly granted, or the ones they inherited by virtue of their membership of one or more groups.

For example, suppose that Fred Nurk belongs to a group called Data Entry, and the Data Entry group has dbSecInsertData and dbSecReplaceData permissions to Table1. In addition, the administrator has explicitly granted him dbSecDeleteData permissions, but accidentally revoked his individual dbSecReplaceData permissions to the same table. Because the Data Entry group has dbSecReplaceData permissions, Fred's total permissions are the sum of all permissions- dbSecInsertData + dbSecDeleteData + dbSecReplaceData.

To determine if the current user has particular permissions to an object, you must explicitly test for those permissions. The following example demonstrates this:

Public Function HasDeletePermissons(strTableName As String, _
    Optional strUser As String) As Boolean

    'Checks if the current user has Delete permissions to a specific table
    Dim dbs As DAO.Database
    Dim doc As DAO.Document

    Set dbs = CurrentDb

    'Set a reference to the table's Document
    Set doc = dbs.Containers!Tables.Documents(strTableName)

    'Specify the user
    If strUser <> "" Then doc.UserName = strUser

    'Test for explicit permissions only
    HasDeletePermissons = _
    ((doc.Permissions And dbSecDeleteData) = dbSecDeleteData)

    'To test for implicit permissions,
    'uncomment the following line
    'HasDeletePermissons = _
    ((doc.AllPermissions And dbSecDeleteData) = dbSecDeleteData)

    Set doc = Nothing
    Set dbs = Nothing
End Function

The more observant reader might have noticed that you can, in fact, specify the username. The default setting for the Document object's UserName property is that of the current user. If, however, you set the UserName property prior to reading the Permissions property, you can check the permissions for any user or group in the workgroup.

The following code shows how to determine the exact object permissions for a specific user or group:

Public Sub WhichPermissions(strTableName As String, Optional strUser As String)
    'Determines the specific permissions a
    'specific user has to a specific table
    Dim dbs As DAO.Database
    Dim doc As DAO.Document
    Dim lngPermission As Long

    Set dbs = CurrentDb

    'Set a reference to the table's Document
    Set doc = dbs.Containers!Tables.Documents(strTable)

    'Specify the user
    If strUser <> "" Then doc.UserName = strUser

    'Retrieve the permissions
    lngPermission = doc.AllPermissions

    'Determine the user's implicit permissions
    Debug.Print "Permissions granted to " & strUser & " for " & strTable
    If ((doc.AllPermissions And dbSecNoAccess) = dbSecNoAccess) Then
	Debug.Print vbTab & "dbSecNoAccess"
    End If

    If ((doc.AllPermissions And dbSecFullAccess) = dbSecFullAccess) Then
	Debug.Print vbTab & "dbSecFullAccess"
    End If

    If ((doc.AllPermissions And dbSecDelete) = dbSecDelete) Then
	Debug.Print vbTab & "dbSecDelete"
    End If

    If ((doc.AllPermissions And dbSecReadSec) = dbSecReadSec) Then
	Debug.Print vbTab & "dbSecReadSec"
    End If

    If ((doc.AllPermissions And dbSecWriteSec) = dbSecWriteSec) Then
	Debug.Print vbTab & "dbSecWriteSec"
    End If

    If ((doc.AllPermissions And dbSecWriteOwner) = dbSecWriteOwner) Then
	Debug.Print vbTab & "dbSecWriteOwner"
    End If

    Set doc = Nothing
    Set dbs = Nothing
End Sub

So far you've seen how to check the permissions for existing objects, but what about objects that will be created in the future? DAO provides a facility for this, too. You can retrieve the default permissions that have been set for any new objects by checking the Permissions property of the Document object's parent - the Container object:

Debug.Print dbs.Containers!Tables.AllPermissions
Debug.Print dbs.Containers!Tables.Permissions

Setting Permissions

Setting object permissions is similar to setting any other property. It is worth mentioning that you cannot set only the property, but can also simultaneously set multiple permissions, and add or remove one or more permissions.

To explicitly set the permissions for an object, you simply assign the permission to the object's Permission property. For example, to assign the permission for the current user to delete data from Table1:

Set doc = dbs.Containers!Tables.Documents!Table1
doc.Permissions = dbSecInsertData Or dbSecDeleteData

To add a permission to an object's existing permissions, use the bitwise Or operator with the existing permissions. For example, to add permission for the current user to delete data from Table1:

Set doc = dbs.Containers!Tables.Documents!Table1
doc.Permissions = doc.Permissions Or dbSecInsertData

To remove one or more permissions from the object's existing permissions, you make use of the And and Not operators. For example, to remove two permissions - the capabilities to modify and delete data - from Table1:

Set doc = dbs.Containers!Tables.Documents!Table1
doc.permissions = doc.Permissions And Not ( _
dbSecReplaceData Or dbSecDeleteData)