Permissions, UserName Properties Example (MDB)

The following example adds a new user account by creating a User object and appending it to the Users collection of the default workspace. It then sets permissions for the new user for all tables in the database. The procedure first sets the UserName property of a Tables Container object to the name of the new user, then sets the Permissions property to the appropriate permissions.

Note that the And operator performs a bitwise comparison to determine whether an attribute is currently set.

Note   When programming security, you should avoid including actual password and PID information in your code. The following example is intended for demonstration purposes only.

Function SetPermissions(strName As String, strPID As String, _
        strPassword As String) As Boolean
    Dim dbs As Database, ctrTables As Container
    Dim wspDefault As Workspace, usrNew As User

    On Error GoTo ErrorSetPermissions
    ' Return reference to default workspace.
    Set wspDefault = DBEngine.Workspaces(0)
    ' Create User object, specifying name, PID, and password.
    Set usrNew = wspDefault.CreateUser(strName, _
            strPID, strPassword)
    ' Append to Users collection of default workspace.
    wspDefault.Users.Append usrNew
    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Tables container.
    Set ctrTables = dbs.Containers!Tables
    ctrTables.UserName = usrNew.Name
    ' Set new user's permissions for tables.
    ctrTables.Permissions = dbSecInsertData _
        And dbSecReplaceData And dbSecDeleteData
    SetPermissions = True

ExitSetPermissions
    Set dbs = Nothing
    Exit Function

ErrorSetPermissions
    MsgBox Err & ": " & Err.Description
    SetPermissions = False
    Resume ExitSetPermissions
End Function

The next procedure prompts the user to enter their name and a password and creates a PID value:

Sub NewUserPrompt()
    Dim strName As String, strPassword As String
    Dim intR As Integer, strPID As String
    Dim blnReturn As Boolean

    ' Prompt user for name and password.
    strName = InputBox("Enter your name.")
    strPassword = InputBox("Enter a password with " _
        & "up to 14 characters.")
    ' Seed random number generator.
    Randomize
    ' Generate PID string.
    For intR = 0 To 19
        strPID = strPID & Chr$(Int(256 * Rnd))
    Next intR
    ' Call SetPermissions function.
    blnReturn = SetPermissions(strName, strPID, strPassword)
End Sub