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
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 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