Module: JetSecurity in Category Access/Jet Databases : Database from Total Visual SourceBook

Manage Jet security users and groups in a specified database in VBA and VB6.

Each of the procedures in this module require that you pass a workgroup name to identify the workgroup you want to work with. By default, Visual Basic doesn't open a workgroup information file (*.MDA, *.MDW) unless you specify the SystemDB property of the DAO DBEngine object. When you specify a value for this property, the default DAO Workspace object, also known as Workspaces(0) is mapped to the workgroup information file you specify. In such a case, specifying a blank value for the strWorkgroup parameter of the procedures in this module effectively uses whatever workgroup information file you are currently using.

Procedure List

Example Code for Using Module: JetSecurity

' Example of modJetSecurity
'
' To use this example, create a new module and paste this code into it.
' Then run the procedure by putting the cursor in the procedure and pressing:
'    F5 to run it, or
'    F8 to step through it line-by-line (see the Debug menu for more options)

Private Const mcstrSamplePath As String = "C:\Total Visual SourceBook 2013\Samples\"
Private Const mcstrWorkgroupPath As String = mcstrSamplePath & "Workgrp.mdw"
Private Const mcstrSampleDatabase As String = mcstrSamplePath & "SAMPLE.MDB"

Private Sub Example_modJetSecurity()
  ' Comments: Examples of the modJetSecurity module to manage Jet security users and groups in a specified database in VBA and VB6.

  ' This example assumes that the sample files are located in the folder named by the following constant.
  Const cstrSampleTable As String = "Customers"
  Const cstrUserName As String = "Beth"

  Dim fOK As Boolean
  Dim strRet As String
  Dim lngRet As Long
  Dim astrValues() As String
  Dim lngCounter As Long

  ' Important Note
  ' --------------
  ' This example code is provided to show how to use the modJetSecurity
  ' module in a hypothetical case. Do not run this code on your
  ' production workgroup files because the code makes changes to
  ' security. For this reason, the code is commented out. Before
  ' using this example, change the values for user and group names,
  ' and the path to the workgroup information file to a backup
  ' copy of your workgroup database. This way, you ensure that
  ' you don't inadvertently change security settings for production systems.

  ' Before using Jet security, we must tell the engine what workgroup to use
  DAO.DBEngine.SystemDB = mcstrWorkgroupPath

  ' See if a Group called Payroll exists and delete it if it does
  If GroupExists("", "Payroll") Then
    fOK = DropGroup("", "Payroll")
    If fOK Then
      Debug.Print "The Payroll group was deleted."
    Else
      Debug.Print "The Payroll group could not be deleted."
    End If
  Else
    fOK = True
  End If

  If fOK Then
    ' Add a group called 'Payroll' to the current workgroup information file.
    fOK = AddGroup("", "Payroll", "test1234")
    If fOK Then
      Debug.Print "Group 'Payroll' added to the current workgroup information file."
    Else
      Debug.Print "Group 'Payroll' could *not* be added."
    End If
  End If

  ' Create a new a user named in constant cstrUserName and add it to the 'Payroll' group.
  fOK = AddUser("", cstrUserName, "mypidABCDE")
  If fOK Then
    Debug.Print "User " & cstrUserName & " added."
  Else
    Debug.Print "User " & cstrUserName & " *not* added."
  End If

  ' Add user user to the Admins group.
  fOK = AddUserToGroup("", cstrUserName, "admins")
  If fOK Then
    Debug.Print "User " & cstrUserName & " added to the admins group."
  Else
    Debug.Print "User " & cstrUserName & " not added to the admins group."
  End If

  ' See if user can create a table in the sample database.
  fOK = CanUserCreateObject("", mcstrSampleDatabase, cstrUserName, sjotTable)
  If fOK Then
    Debug.Print "User " & cstrUserName & " can create a table"
  Else
    Debug.Print "User " & cstrUserName & " cannot create a table"
  End If

  ' Change user's password from '' to 'topsecret'.
  fOK = ChangeUserPassword("", cstrUserName, "", "topsecret")
  If fOK Then
    Debug.Print "Password changed."
  Else
    Debug.Print "Password not changed."
  End If

  ' Delete the 'Research' group from the current workgroup information file.
  fOK = DropGroup("", "Research")
  If fOK Then
    Debug.Print "The Research group was deleted."
  Else
    Debug.Print "The Research group was *not* deleted."
  End If

  ' Check for list of users
  ' Load the array with the names of users in the Admins group
  lngRet = UsersInGroupToArray("", "Admins", astrValues())
  For lngCounter = 0 To lngRet - 1
    Debug.Print "User " & lngCounter + 1 & " of " & lngRet & ": " & astrValues(lngCounter)
  Next lngCounter
  Debug.Print

  ' Delete user from the current workgroup information file.
  fOK = DropUser("", cstrUserName)
  If fOK Then
    Debug.Print "User " & cstrUserName & " was deleted."
  Else
    Debug.Print "User " & cstrUserName & " was *not* deleted."
  End If

  ' Find out who owns the Customers table
  strRet = ""
  strRet = GetOwner("", mcstrSampleDatabase, sjotTable, "Customers")
  Debug.Print "The Customers table is owned by " & strRet & "."

  ' See if a group called Employees exists.
  fOK = GroupExists("", "Employees")
  If fOK Then
    Debug.Print "The employees group exists."
  Else
    Debug.Print "The employees group does not exist."
  End If

  ' Determine if user is a member of the Admins group.
  fOK = IsUserMemberOfAdmins("", cstrUserName)
  If fOK Then
    Debug.Print cstrUserName & " is a member of the Admins group."
  Else
    Debug.Print cstrUserName & " is *not* a member of the Admins group."
  End If

  ' Determine if Scott is a member of the Payroll group."
  fOK = IsUserMemberOfGroup("", "Scott", "payroll")
  If fOK Then
    Debug.Print "Scott is a member of the Payroll group."
  Else
    Debug.Print "Scott is *not* a member of the Payroll group."
  End If

  ' Determine if user Luke has a password.
  fOK = UserHasPassword("", "Luke")
  If fOK Then
    Debug.Print "User Luke has a password in place."
  Else
    Debug.Print "User Luke does *not* have a password in place."
  End If

  ' Determine if user Maya had ReadData permission on the Customers table.
  fOK = UserHasPermission("", "Maya", mcstrSampleDatabase, cstrSampleTable, sjotTable, DAO.dbSecRetrieveData)
  If fOK Then
    Debug.Print "Maya can read data from the Customers table."
  Else
    Debug.Print "Maya *cannot* read data from the Customers table."
  End If

End Sub

Private Sub Example_modJetSecurityUsersAndGroups()
  ' Comments: Examples of the modJetSecurity module to get all users and groups

  Dim intGroups As Integer, intUsers As Integer
  Dim strGroups As String, strUsers As String
  Dim intCountGroup As Integer, intCountUser As Integer
  Dim astrGroups() As String, astrUsers() As String

  ' Before using Jet security, we must tell the engine what workgroup to use
  DAO.DBEngine.SystemDB = mcstrWorkgroupPath

  ' Get the list of groups in a single string
  intGroups = GroupsToString("", strGroups, ";")
  Debug.Print "There are " & intGroups & " groups: " & strGroups
  Debug.Print

  ' Load an array with a list of group names.
  intGroups = GroupsToArray("", astrGroups())
  For intCountGroup = 0 To intGroups - 1
    Debug.Print "Group " & intCountGroup + 1 & " of " & intGroups & ": " & astrGroups(intCountGroup)

    ' Load the user names of the group in one string
    intUsers = UsersInGroupToString("", astrGroups(intCountGroup), strUsers, "*")
    Debug.Print "There are " & intUsers & " users in the " & astrGroups(intCountGroup) & " group: " & strUsers

    ' Create an array with the names of users in the group
    intUsers = UsersInGroupToArray("", astrGroups(intCountGroup), astrUsers())
    If intUsers > 0 Then
      For intCountUser = 0 To intUsers - 1
        Debug.Print "  User " & intCountUser + 1 & " of " & intUsers & ": " & astrUsers(intCountUser)
      Next intCountUser
    Else
      Debug.Print "  No Users in group"
    End If
    Debug.Print
  Next intCountGroup

End Sub

Overview of Total Visual SourceBook

The source code in Total Visual SourceBook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Additional Resources

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Module Code from Total Visual SourceBook (Example of Date, Time and Age Calculations)