MS-Access / Getting Started

---

Creating Security Objects

When you create a new user or group account, either through the user interface or via code, you must supply a Personal IDentifier (PID). The PID is a case-sensitive 4-20 character string that Access combines with the user or group name to create a unique Security IDentifier (SID). The SID is a unique identifier, which is similar to a public security key. Once you create the account, you can never view or change the SID. But (and this is why the SID is notable) if you ever delete the user or group account, and later decide to re-create it, you must use the same PID because Access remembers it. If the resulting SID does not match, Access will not allow you to re-create the account. Therefore, whenever you create a new user or group account, save the PID offsite so you don't lose it.

When you create a new user account, you can also include a case-sensitive password of 1 to 20 characters, which the user must enter when logging on. The only user who can change the password is the user who owns it. However, members of the Admins group can clear any user's password.

Passwords and PIDs are encoded and stored in the workgroup file, and thus, cannot be viewed by anyone. The following sections demonstrate how to create and modify user and group accounts, and includes code to add SIDs and passwords.

Managing Users and Groups

The Workspace object contains a Groups collection and a Users collection. The Groups collection contains all the Group objects used in the workgroup. A Group, as its name suggests, is a collection of Users to whom you want to assign the same privileges. You can enumerate the users and groups using the following code:

Public Sub EnumUsersAndGroups()
    Dim wrk As DAO.Workspace
    Dim grp As DAO.Group
    Dim usr As DAO.User

    Set wrk = DBEngine(0)

    'Enumerate the groups
    Debug.Print "Groups..."
    For Each grp In wrk.Groups
	Debug.Print vbTab & grp.Name
    Next grp

    'Enumerate the users
    Debug.Print "Users..."
    For Each usr In wrk.Users
	Debug.Print vbTab & usr.Name
    Next usr

    Set grp = Nothing
    Set wrk = Nothing
End Sub

The preceding code simply lists all the users and groups that exist in the system, but it doesn't show the relationship between them. If you want to find out which users belong to a specific group, you need to enumerate the Users collection for that specific group:

Public Sub EnumGroupUsers(strGroup As String)
    Dim wrk As DAO.Workspace
    Dim varUser As Variant

    Set wrk = DBEngine(0)

    Debug.Print "Users belonging to the '" & strGroup & "' group..."
    For Each varUser In wrk.Groups(strGroup).Users
	Debug.Print vbTab & varUser.Name
    Next varUser

    Set wrk = Nothing
End Sub

Similarly, you can list all the groups that a specific user belongs to by enumerating the Groups collection for that user:

Public Sub EnumUserGroups(strUser As String)
    Dim wrk As DAO.Workspace
    Dim varGroup As Variant

    Set wrk = DBEngine(0)

    Debug.Print "Groups to which user '" & strUser & "' belongs..."
    For Each varGroup In wrk.Users(strUser).Groups
	Debug.Print vbTab & varGroup.Name
    Next varGroup

    Set wrk = Nothing
End Sub

The Current User

The current user is defined as the user who is currently logged on to the database application. For most security-related operations, you need to know the name of the current user. DAO provides a convenient way of obtaining this information using the Workspace object's UserName property:

strMyName = DBEngine(0).UserName

Using this property, you can create a User object for the current user, without having to know his name, as follows:

Dim usr As DAO.User
Set usr = DBEngine(0).Users(DBEngine(0).UserName)

The Access Application object also provides an easy way of obtaining the name of the user who is currently logged on, using a function appropriately named CurrentUser.