Building an LDAP/AD Group Membership Report in Guardium

 by John Haldeman, Practice Lead


Guardium Entitlement Reports are a useful feature that help you determine what privileges have been assigned in your databases. It's primary value is in helping you create standardized reporting for entitlements based on the database catalog information in each database without you having to create custom scripts.

This being said, that's all Guardium does - query the database catalogs of the databases you register and shows you that information. Certain database types, MS SQL Server for instance, may obscure who the end user that has a certain privilege is because the database catalog just has a listing for the groups assigned, not the users in those groups. An example is show in figure 1. A role for a MS SQL Server database is shown to be assigned to a WINDOWS_GROUP. Invariably, the next question becomes: who is in that Windows group, and can I see that information in the same report set and environment I am getting delivered to me anyway rather than having to look up the information in my corporate directory server separately.

Figure 1: A Guardium Entitlement Report showing a role assigned to two groups: TESTDIR\testgroup1 and TESTDIR\testgroup2 - Who are the users in those groups?

Building a report on that group membership is what this post is all about. You should be warned though: Guardium is not very good at this. In this post you will see mechanisms to try and help make this happen but keep in mind that these mechanisms were not originally designed to fulfill this specific use case. So, it may start to feel a little awkward in making this happen.



Before we go into specifics, here is an overview of what needs to be done. Figure 2 outlines the process.

Figure 2: Process for Active Directory and LDAP Group Membership Reporting in Guardium

This example is built for MS SQL Server and Active Directory, but the same principles would apply for other database and directory server types. The first task to to take the entitlement report that you are seeing the groups listed in and create a special custom column to flip the domain and group names in the reported values. This will seem strange, but the reasons will become apparent later. Then populate a "bindValues" group to contain all of the groups you are interested in. This group can be populated automatically from the entitlement report using Guardium's "Populate From Query" function and the specialized column you created to switch the domain and group name. Next, populate a regular group to use the bindValues group and set it up to populate from LDAP. The end results will be members of that group that will show the group name following by a backslash ("\") followed by the member of the group. Finally build a report on that group to show what end user is in each active directory group.

Step 1: The Entitlement Report and Custom Column

For this example, let's use the "mssql2005/8 Role Granted To User And Role" built-in entitlement report. That is the same report pictured previously in Figure 1. Build a custom computed column on that entitlement report using the following grdapi call:

grdapi create_computed_attribute expression="concat(substring_index(Grantee, CHAR(92), -1), CHAR(92), substring_index(Grantee, CHAR(92), 1))" entityLabel="MSSQL2005/8 Role Granted To User And Role" attributeLabel=GroupUserFlip

All the expression does is, for values that contain a backslash, have the values before and after the slash trade places (eg: "DOMAIN\group_name" becomes "group_name\DOMAIN"). This creates a new column in the "MSSQL2005/8: Role Granted To User And Role" domain called GroupUserFlip. Next, create a query that includes that new column and limits the output to just WINDOWS_GROUP values (see Figure 3). You can create a report like this by accessing as admin: Tools --> Report Building --> Custom Query Buider --> MSSQL2005/8: Role Granted To User And Role --> New, or by cloning and modifying the existing entitlement report built off that domain.

Figure 3: Report With New Custom Computed Column and WINDOWS_GROUP Filter

Step 2: Create and Populate the bindValues Group

bindValues groups are special groups that allow you to run multiple LDAP/AD queries in one operation rather than creating multiple groups. They also have a function to allow you to insert the domain into the group members before a blackslash. We will actually be using that function, normally used to insert domain names, to insert the group name that was looked up into the group members retrieved.

Create and Configure a new group as an OBJECTS type group. Call the group "- AD Group Membership_bindValues". Next, in the group builder, select the group and click "Populate From Query". Then configure that population process according to what is shown in Figure 4. You will populate the group based on the query and new custom column you created in Step 1.

Figure 4: Populating the bindValues Group Based on the Query and Customer Computed Attribute Created in Step 1

Note that the value of From Date parameter does not matter as long as it is a value that is prior to when you first started running entitlement reports. Also, note that you can safely ignore the "Column Type" warning that appears in Orange. Finally, if you are automatically refreshing your entitlement reports, set a schedule to run this after your entitlement reports have been repopulated.

Step 3: Configure the LDAP/AD Import

Now that the bindValues group is created, you can create another group that will use that group's content as parameters in it's LDAP/AD queries. Create this new group with a type of "USERS" and name it "- AD Group Membership" (note that it has the same name as the group in Step 2, without the "_bindValues" prefix - if you are using your own group names, just follow that convention). Configure the LDAP Import for this group similarly to what is pictured in Figure 4.

Figure 4: LDAP Group Import Configuration

Since it is cut off in the screenshot, the full search filter in or lab environment is:

memberof=CN=:2,CN=Users,DC=:1,DC=infoinsightsllc,DC=com

Of course, your configuration is likely to be different, but the important part in the search filter is that ":2" is located where you would normally put in a group name for an LDAP import, and ":1" is located where you normally specify the Active Directory domain you are working with. If you specify the search filter correctly, and run the import, it results in entries similar to Figure 5, showing the AD group names before the backslash in each entry, and the AD group member after the backslash.

Figure 5: LDAP Group Import Results showing group names on the left (testgroup1, testgroup2), and group membership on the right (testuser1 belonging to both, and testuser2 belonging to just testgroup2)

Similarly to Step 2, if you were automating the reporting of the group membership, you would schedule this LDAP import to happen on a regular basis - this time after the populate from query process in Step 2, but before your audit processes to report on the data run.

Step 4: Reporting on the Imported Group Membership Information

The final step is to build a report on those imported group members. To do that, build a report on group membership by logging in as a user that is assigned admin and navigating to Tools --> Report Building --> Group Tracking. Create the query similar to that shown in Figure 6.

Figure 6: Report on Group Membership

Figure 7 is the result that you can then apply filters to and/or schedule to put into an audit process for regular delivery with the rest of your entitlement reports.

Figure 7: The Final Result Showing Active Directory Group Membership for the Groups in Our Original Entitlement Report