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 Name Type Description
(Declarations) Declarations Declarations and private variables for the modJetSecurity module.
AddGroup Procedure Add a group to the workgroup. You must have administrator permissions on the workgroup information file to add a group.
AddUser Procedure Add a new user. Every user must belong to at least one group. Therefore, when you create a user, you must specify an initial group for the user to join by specifying a value for the strGroup parameter. You must have administrator permissions on the workgroup information file to add a user.
AddUserToGroup Procedure Add an existing user to an existing group.
CanUserCreateObject Procedure Determine if a user can create an object of the specified type.
ChangeUserPassword Procedure Change the user's password.
DropGroup Procedure Delete a group from the workgroup.
DropUser Procedure Delete a user from the workgroup.
GetOwner Procedure Get the owner of the specified object.
GroupExists Procedure Determine if the group exists.
GroupsToArray Procedure Populate the passed array with a list of groups.
GroupsToString Procedure Populate the passed string with a list of groups.
IsUserMemberOfAdmins Procedure Determine if a user is a member of the admins group.
IsUserMemberOfGroup Procedure Determine if a user is a member of the specified group.
UserHasPassword Procedure Determine if a user has a password set.
UserHasPermission Procedure Determine if a user has explicit permissions to the specified object.
UsersInGroupToArray Procedure Populate the passed array with a list of users.
UsersInGroupToString Procedure Populate the passed string with a list of users.
' 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

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.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!


View all FMS products for Microsoft Access All Our Microsoft Access Products

Reviews

Reader Choice Award for MS Access Source Code Library
Reader Choice

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

SourceBook Info

Additional Info

Question

 

 

Free Product Catalog from FMS