Problem
We have a requirement to implement security in our data warehouse to limit what data a user can see on a report. As an initial step we have created tables for users and roles; we also have a user role table where we specify the role(s) that a user is assigned. We would like to update the user, role, and user role tables automatically from Active Directory. Ideally we’d like an SSIS package that we could schedule and/or run on demand to take care of the update. Can you provide an example of how to do this?
Solution
An SSIS package is a convenient choice to synchronize your security tables with Active Directory. From a database standpoint, let’s assume your security tables are as shown in the following schema diagram:
The DimUser table has a row for each user that is allowed to see any data in the warehouse. The DimRole table contains our list of roles that a user may be assigned; there is a one-to-one relationship between a role and an Active Directory group. The DimUserRole table contains the users and their roles via the foreign key relationships to the DimUser and DimRole tables. You query the DimUserRole table to determine what role(s) the user belongs to then use that to filter the data that the user can see. If you’re familiar with .NET programming you might ask why not just call the method WindowsPrincipal.IsInRole() to check whether a user is in a particular role? While you certainly could do that, there are a number of reasons why having the users and roles in tables is beneficial:
- You can create table-valued functions (TVF) that you can join with other tables to filter based on role and/or other rules; I’ll give an example in this tip.
- Sometimes the “current user” is different than the the “calling user”; e.g. in SQL Server Reporting Services you may configure a report to run as a particular user but filter the data based on the global variable User!UserID (the calling user).
- You often come up with additional user or role data that isn’t in Active Directory and it’s not a simple task to extend the Active Directory schema; you can easily incorporate this data into your own SQL Server tables. For instance users in certain roles can see all Key Performance Indicators (KPIs) for all offices; users in other roles can only see the company-wide KPI (no office-level detail).
As shown above the schema is very simple. Let’s continue on with the details on how to retrieve users and their groups from Active Directory then implement an SSIS package to perform the update.
.NET Code to Query Active Directory
In order to retrieve the list of users and their groups from Active Directory, we will need to write some .NET code. The following method shows the steps to write out every user and their group memberships to a tab delimited file:
The main points about the above code are:
- Step 1 sets up the parameters for the Active Directory search. DirectoryEntry is a class in the System.DirectoryServices namespace that you use to specify where in Active Directory to begin the search. In my case I used “LDAP://dc=vs,dc=local” as the path variable value to get all users in the domain since my domain is vs.local. DirectorySearcher is used to perform the actual search; it is also in the System.DirectoryServices namespace. The SearchScope property is set to search objects and their hierarchies. You specify the attributes you want to retrieve by calling the PropertiesToLoad Add method. The Filter property is set to return any object that represents a person. The PageSize property sets the chunk size for retrieving items from the Active Directory. Without specifying a PageSize you will only get the first 1,000 items.
- Step 2 executes the search by calling the FindAll method on the DirectorySearcher object, which returns a collection of objects.
- Step 3 creates the flat file to output the results.
- Step 4 iterates through the result collection. Each item is a collection itself.
- Step 5 iterates through each item collection pulling out either a single value (e.g. samaccountname) or multiple values (e.g. memberof).
- The samaccountname attribute is the user’s login name.
- The memberof attribute is a multi-valued collection which contains each Active Directory group that the user is a member of.
- The above code is part of a class called User contained in a class library called ADHelper.DLL. By packaging the code in a class library rather than embedding it in the SSIS package, we can call it from the SSIS package as well as any .NET code.
Note that there are two requirements for deploying the above code so that it can be called from an SSIS package:
- ADHelper.DLL must be deployed to the Global Assembly Cache (GAC). You can use the GACUTIL utility to do this or simply drag and drop the DLL to the \Windows\Assembly folder.
- ADHelper.DLL must be copied to the folder \Program Files\Microsoft SQL Server\90\SDK\Assemblies
SSIS Package Control Flow
We will use the following SSIS package control flow to synchronize our security tables with Active Directory:
Extract Users and Group Memberships from AD
Extract Users and Group Memberships from AD is a Script task that retrieves users and their group memberships from Active Directory and writes out a tab delimited file. The Script task contains the following code:
Dim user As New ADHelper.User
Dim filename As String
filename = Dts.Variables("ADUserGroupsFileName").Value.ToString()
user.GetUserGroups("VS", "LDAP://dc=vs,dc=local", filename)
Dts.TaskResult = Dts.Results.SuccessADUserGroupFileName is a package variable that holds the full path to the tab delimited file where the results are stored. The code in the Script task is minimal since the GetUserGroups method contains the majority of the code. You need to add a reference to the ADHelper DLL in the Script task.
Truncate stg_UserGroupList
Truncate stg_UserGroupList is an Execute SQL task that truncates the stg_UserGroupList table used during processing. The stg_UserGroupList table is created with the following script:
CREATE TABLE [dbo].[stg_UserGroupList](
[Domain] [nvarchar](50) NOT NULL,
[AccountName] [nvarchar](50) NOT NULL,
[Group] [nvarchar](50) NOT NULL,
[DomainUser] AS (([Domain]+'\')+[AccountName]),
[FK_DimUser] [int] NULL,
[FK_DimRole] [int] NULL
)
The Domain column is populated by the value of the domain parameter passed in to the GetUserGroups method described above. The AccountName and Group columns are set from the Active Directory attributes samaccountname and memberof. The DomainUser computed column concatenates the Domain and AccountName columns in the DOMAIN\ACCOUNT format. The FK_DimUser and FK_DimRole columns will be set to the primary key values of the respective tables.
Import Users and Groups into stg_UserGroupList
Import Users and Groups into stg_UserGroupList is a Data Flow task that loads the stg_UserGroupList table from the tab delimited file created in the initial Script task.
Synchronize DimUser and DimUserRole
Synchronize DimUser and DimUserRole is an Execute SQL task that calls the stored procedure that updates our security tables from the stg_UserGroupList table. The update is essentially a merge operation that inserts rows for any new users and their group memberships, and deletes users and their group memberships that are no longer in the Active Directory. The stored procedure is shown below:
-- Step 1
UPDATE dbo.stg_UserGroupList
SET FK_DimUser = u.DimUserID
FROM dbo.stg_UserGroupList f
JOIN dbo.DimUser u ON u.UserName = f.DomainUser
UPDATE dbo.stg_UserGroupList
SET FK_DimRole = r.DimRoleID,
FK_DimPlan = r.FK_DimPlan
FROM dbo.stg_UserGroupList f
JOIN dbo.DimRole r ON r.Role = f.[Group]
-- Step 2
INSERT INTO dbo.DimUser (UserName)
SELECT DISTINCT DomainUser
FROM dbo.stg_UserGroupList
WHERE FK_DimRole IS NOT NULL
AND FK_DimUser IS NULL
-- Step 3
UPDATE dbo.stg_UserGroupList
SET FK_DimUser = u.DimUserID
FROM dbo.stg_UserGroupList f
JOIN dbo.DimUser u ON u.UserName = f.DomainUser
-- Step 4
INSERT INTO dbo.DimUserRole (FK_DimUser, FK_DimRole)
SELECT f.FK_DimUser, f.FK_DimRole
FROM dbo.stg_UserGroupList f
LEFT JOIN dbo.DimUserRole r ON r.FK_DimUser = f.FK_DimUser
AND r.FK_DimRole = f.FK_DimRole
WHERE f.FK_DimUser IS NOT NULL
AND f.FK_DimRole IS NOT NULL
AND r.DimUserRoleID IS NULL
-- Step 5
DELETE FROM dbo.DimUser
WHERE DomainUser NOT IN (
SELECT DISTINCT DomainUser
FROM dbo.stg_UserGroupList
)
-- Step 6
DELETE FROM dbo.DimUserRole
FROM dbo.DimUserRole r
LEFT JOIN dbo.stg_UserGroupList f ON f.FK_DimUser = r.FK_DimUser
AND f.FK_DimRole = r.FK_DimRole
WHERE f.AccountName IS NULL
The main points about the above stored procedure are:
- Step 1 looks up the primary key values for the DimUser and DimRole tables and saves them in the FK_DimUser and FK_DimRole columns. When the FK_DimUser column is NULL we have a new user; when the column isn’t NULL we have an existing user.
- Step 2 inserts any new users into the DimUser table. Note that we only insert new users if they are in a role in the DimRole table.
- Step 3 looks up the primary key value for the DimUser table and saves it in the FK_DimUser column. This is done to get the primary key of any users that were added in Step 2.
- Step 4 inserts any new user role assignments into the DimUserRole table. Note that a LEFT JOIN is used because we only want to insert rows that are not already in the table.
- Step 5 deletes any rows from the DimUser table that have been removed from Active Directory; i.e. any user not in the staging table.
- Step 6 deletes any rows from the DimUserRole table where the user is no longer in the Active Directory group.
Implementing KPI Security
Let’s finish up by implementing a common security requirement. Assume we want to only allow users in a certain role to see KPI values in detail. All roles can see the KPI values for the company, but only users in a certain role can see KPIs at the office level of detail. We can implement this security by creating a table-valued function that will take the user as a parameter, lookup his role, then return the list of KPI values that the user is allowed to see.
We’ll use the following schema to implement the KPI values:
The FK_DimOffice column in the FactKPIValue table will have a value of 1 when the row is the KPI value for the entire company.
We will use the following table-valued function to implement the KPI security:
CREATE FUNCTION [dbo].[udf_get_filtered_kpi]
(
@username NVARCHAR(256)
)
RETURNS @kpi_t TABLE (
FactKPIID INT
, FK_DimKPI INT
, FK_DimOffice INT
, KPIValue DECIMAL(18,2)
)
AS
BEGIN
DECLARE @UserID INT
DECLARE @ShowDetails BIT
SELECT @UserID = DimUserID
FROM dbo.DimUser
WHERE DomainUser = @username
IF @UserID IS NULL
RETURN
IF EXISTS (
SELECT *
FROM dbo.DimUserRole u
JOIN dbo.DImRole r ON r.DimRoleID = u.FK_DimRole
WHERE u.FK_DimUser = @UserID
AND r.[Role] = 'Senior Leadership'
) SET @ShowDetails = 1 ELSE SET @ShowDetails = 0
-- everyone gets to see the kpi values for the company
INSERT INTO @kpi_t
(FactKPIID, FK_DimKPI, FK_DimOffice, KPIValue)
SELECT FactKPIID, FK_DimKPI, FK_DimOffice, KPIValue
FROM dbo.FactKPIValue
WHERE FK_DimOffice = 1
-- 'Senior Leadership' role can see the detailed kpis
IF @ShowDetails = 1
INSERT INTO @kpi_t
(FactKPIID, FK_DimKPI, FK_DimOffice, KPIValue)
SELECT FactKPIID, FK_DimKPI, FK_DimOffice, KPIValue
FROM dbo.FactKPIValue
WHERE FK_DimOffice > 1
RETURN
END
Main points about the above function:
- The @username parameter must be formatted as DOMAIN\USERNAME. This is the format of the User!UserID global variable in a SQL Server Reporting Services report.
- Every user can see the company KPI values.
- If the user is in the Senior Leadership role then he can see the KPI details at the office level.
Here is the sample output from executing the function with a user that is in the Senior Leadership role and another user that isn’t in the role:
select * from dbo. udf_get_filtered_kpi ('VS\jones')
select * from dbo. udf_get_filtered_kpi ('VS\smith')
Next Steps
- SSIS can be used for all sorts of tasks as this tip has shown.
- Take a look at the sample code here to experiment on your own. Here is a revised version of the code.
- Keep in mind that the Script task can execute code that is packaged in an external DLL in addition to executing VB.NET code embedded in the task.
