Using Active Directory Groups in Access and VBA

Background:

I recently addressed a question relating to implementing user-level security in an Access database application.  As you are probably aware, the .accdb file format introduced in Office 2007 does not support user-level security that was available in .mdb files.  Furthermore, there is no way to ensure total security of your Access data. However, if you are not overly concerned that your employees will pry into your data and are only concerned with preventing your users from accessing forms or reports which they should not have access to, then you can roll your own application level security; this is what I recommended to the questioner.  

Creating your own application level security involves, at a minimum, defining tables and forms for editing those tables for:

1.  Users

2.  Roles

3.  User - Roles

4.  Assigning permission to various forms, controls, and reports based upon roles.

To alleviate some of the tedium of this, I recommended that the questioner consider using Active Directory Groups to manage items 1-3 above. I was surprised when one of the other experts indicated that working with AD groups in VBA is a challenge because I've been doing it for years. Think about it, your sysad has already created all of the users, and it is relatively easy for them to create AD groups and assign people to those groups, so all you really need to handle is capturing this information for the currently logged in user, and then implementing it within your application by granting permissions to forms, controls, and reports based upon those roles.  

This article focuses on how you determine what AD groups your user is assigned to, and leaves the implementation of the program and user interface logic to you.

References:

Although not absolutely necessary, I like to start out by adding a reference to the 'Active DS Type Library' to my application. This gives me access to IntelliSense so I can immediately identify which properties and methods are available to me.  However, once I'm done with this part of the application, I modify my code and remove the reference.

Step #1:

The first step in the process is to get the name of the Domain the user is assigned to and then create user object (objUser).  This object contains information about the user (some of the fields you might want to capture and use in your application are: FullName, LastLogin, PasswordExpirationDate), as well as the groups the user is assigned to.

The following code segment, along with the fUserNTDomain and fOSUserName functions defined in the attached database will let you accomplish this.

'Get the name of the domain the user is on, and the groups user is assigned to
Dim strDomainName As String
strDomainName = fUserNTDomain()
Set objUser = GetObject("WinNT://" & strDomainName & "/" & fOSUserName() & ",user")

Step #2:

When I use AD Groups, I generally create Tempvars (available since Access 2007) to store the variables I want to use within my application.  I create an array of these values and initially set them all to FALSE using the following code:

Dim arrTempvarNames() As String
arrTempvarNames = Split("IsAdmin,IsOpns,IsHR", ",")
'Create the tempvars with the names in the above array
For intLoop = LBound(arrTempvarNames) To UBound(arrTempvarNames)
    TempVars(arrTempvarNames(intLoop)) = False
Next

Dim arrGroupNames() As String
arrGroupNames = Split("MR Admin,Maestro FP Edit,Human Resources", ",")

I use this array, along with an array of GroupNames I want the application to search for.  These two arrays must be synchronized, with the same number of elements and the values in the array of group names must align with the values in my tempvarNames array.

Step #3:

Then I loop through the list of groups that the user is assigned to and check to see if any of these group names match with the names in arrGroupNames ().  If so, I set the value of the associated tempvar to true so that I can use that value in my code.

For Each objGroup In objUser.Groups
    For intLoop = 0 To UBound(arrGroupNames)
        If objGroup.Name = arrGroupNames(intLoop) Then
            TempVars(arrTempvarNames(intLoop)) = True
        End If
    Next
Next

Step #4:

To use this information in my application I frequently use the Form_Load and Form_Current event to enable or disable controls on my forms.  For example, if I only want users who are assigned to the 'Human Resources' AD Group to have access to the Employee Records form, then I would disable the button that opens that form on my Splash Form:

Private Sub Form_Load
    me.cmd_EmployeeRecords.Enabled = tempvars!IsHR
End Sub

Or in the EmployeeRecords form, I might hide the Salary field:

Private Sub Form_Load
    me.txt_Salary.Visible = tempvars!IsSalary
End Sub

Sample File:  ADGroup.accdb

The attached database contains two modules (mod_ADGroups and mod_api_functions).  To add these to your application, simply copy the two modules into your application, modify the two lines of code in the GetAppSecurityGroups procedure in mod_ADGroups which are used to fill the two arrays (arrTempvarNames and arrGroupNames), and then run the procedure GetAppSecurityGroups.

If you need to know what groups a particular user is assigned to, and you don't have time to wait for your sysadmin to provide this information to you, you could simply replace the call to fOSUserName( ) with an inputbox which would allow you to enter any users userid.

Author: Dale Fye

Private Message Me