access和vba_在Access和VBA中使用Active Directory组




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.  

我最近解决了一个与在Access数据库应用程序中实现用户级安全性有关的问题。 您可能已经知道,Office 2007 引入的.accdb文件格式不支持 .mdb文件中可用的用户级安全性 。 此外,无法确保Access数据的整体安全性。 但是,如果您不太担心员工会撬入您的数据,而只关心阻止用户访问他们不应该访问的表单或报表,那么您可以采用自己的应用程序级安全性; 这就是我向提问者推荐的内容。

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.  

为了减轻这种麻烦,我建议发问者考虑使用Active Directory组来管理上述项目1-3。 当其他专家之一表示与VBA中的AD组合作是一项挑战时,我感到很惊讶,因为我已经从事了多年。 考虑一下,您的sysad已经创建了所有用户,他们创建AD组并将人员分配到这些组相对容易,因此,您真正需要处理的就是为当前登录的用户捕获此信息,然后通过基于这些角色授予对表单,控件和报表的权限,在您的应用程序中实现它。

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.




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.

尽管不是绝对必要的,但我还是想从对应用程序添加对“ Active DS类型库”的引用开始。 这使我可以访问IntelliSense,因此我可以立即确定哪些属性和方法可供我使用。 但是,一旦完成了应用程序的这一部分,就可以修改代码并删除引用。

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.

该过程的第一步是获取分配给用户的域的名称,然后创建用户对象(objUser)。 该对象包含有关用户的信息(您可能希望在应用程序中捕获和使用的某些字段为:FullName,LastLogin,PasswordExpirationDate)以及分配给用户的组。

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:

使用广告组时,通常会创建Tempvar(从Access 2007开始可用)来存储要在应用程序中使用的变量。 我创建这些值的数组,并使用以下代码将它们全部设置为FALSE:

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

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.

我使用此数组,以及希望应用程序搜索的GroupNames数组。 这两个数组必须使用相同数量的元素进行同步,并且组名数组中的值必须与tempvarNames数组中的值对齐。

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.

然后,我遍历分配给用户的组列表,并检查这些组名称是否与arrGroupNames()中的名称匹配。 如果是这样,我将关联的tempvar的值设置为true,以便可以在代码中使用该值。

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

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:

为了在我的应用程序中使用此信息,我经常使用Form_Load和Form_Current事件来启用或禁用表单上的控件。 例如,如果我只希望分配给“人力资源” AD组的用户有权访问“员工记录”表单,那么我将禁用在“初始表单”上打开该表单的按钮:

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

示例文件: 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.

附加的数据库包含两个模块(mod_ADGroups和mod_api_functions)。 要将它们添加到您的应用程序中,只需将两个模块复制到您的应用程序中,修改mod_ADGroups中GetAppSecurityGroups过程中用于填充两个数组(arrTempvarNames和arrGroupNames)的两行代码,然后运行过程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

作者: Dale Fye

Private Message Me




  • 0
  • 0
  • 0
  • 扫一扫,分享海报

评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页
钱包余额 0