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.
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.
|No access to the object
|Full access to the object
|Can delete the object
|Can read the object's security information
|Can change the object's security information
|Can change the ownership of the object
|Can create new Document objects (valid only with a Container object)
|Can read the table definition
|Can modify or change the table definition
|Can retrieve data from the Document object
|Can add records
|Can modify records
|Can delete records
|Assigns admin rights - can create replicas, change the database password, and set startup properties
|Can create new databases (valid only on the Databases container object in the Workgroup Information File)
|Can open the database exclusively
|Can open the database
|Can run the macro
|Can read the macro's definition
|Can modify the macro's definition
|Can open the form or report
|Can read the form's or report's definition and its module
|Can modify the form's or report's definition and its module
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:
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 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)
In this tutorial:
- Using DAO to Access Data
- Data Access Objects
- New Features in DAO
- Referring to DAO Objects
- The DBEngine Object
- Using Transactions
- The Errors Collection
- The Databases Collection
- The CurrentDb() Function
- Opening an External Database
- Closing and Destroying Database Object References
- DAO Property Types
- Setting and Retrieving Built-In Object Properties
- Setting and Retrieving SummaryInfo Properties
- Creating Schema Objects with DAO
- Creating Indexes
- Creating Relations
- Creating Multi-Value Lookup Fields
- Database Encryption with DAO
- Setting Encryption Options
- Managing Access (JET) Security with DAO
- Creating Security Objects
- Creating and Deleting Groups
- Managing Passwords
- Data Access with DAO
- Modifying a QueryDef
- Filtering and Ordering Recordsets
- Navigating Recordsets
- BOF, EOF
- Navigating Recordsets with Multi-Value Lookup Fields
- Bookmarks and Recordset Clones
- Finding Records
- Working with Recordsets
- Using Arrays with Recordsets
- Working with Attachment Fields
- Append Only Fields