Advanced Dimension Data Security with SQL Server 2008

 

On a recent project, my client asked if we could secure members of a dimension by Active Directory groups.  For example, in a dimension named “Region”, there might be a “Southeast” region.  Members of the A/D group named Southeast should only be able to view the “Southeast” member. Below is an example of this, built using the AdventureWorks database.  In this example, the Geography dimension is secured.

The Dimensional Model

The goal is that when a client application (like Excel) issues the below query, Analysis Services returns only specific geographies the user is authorized to view.

SELECT
[Measures].[Sales Amount] ON COLUMNS,
[Geography].[City].MEMBERS ON ROWS
FROM [Adventure Works DW2008]

To secure the Geography dimension, two additional tables are added to the model: FactGeographySecurity and DimUser.   Here’s the DDL:

CREATE TABLE [dbo].[DimUser](
	[UserKey] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](50) NULL,
	CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED 
	(
		[UserKey] ASC
	)
)

CREATE TABLE [dbo].[FactGeographySecurity](
	[UserKey] [int] NOT NULL,
	[GeographyKey] [int] NOT NULL,
	CONSTRAINT [PK_FactDistrictSecurity] PRIMARY KEY CLUSTERED 
	(
		[UserKey] ASC,
		[GeographyKey] ASC
	)
)

Now insert some test data into the new tables.  The below DML adds my user account, and then grants me access to cities in Great Britain and Canada.

INSERT DimUser (UserName) VALUES('blackd820\black')

INSERT FactGeographySecurity (UserKey, GeographyKey)
	SELECT 
		(SELECT UserKey FROM DimUser WHERE UserName = 'blackd820\black')
		,GeographyKey
	FROM DimGeography
	WHERE CountryRegionCode IN ('GB', 'CA')

In BIDS, add the DimGeography, DimReseller, and FactResellerSales tables to your Data Source View from the AdventureWorksDW database.  Also add the two new tables, DimUser and FactGeographySecurity.

 

Create database/cube dimensions for Geography, Reseller, and User.  Create Reseller Sales and Geography Security measure groups.  Your cube structure should now look like the below:

 

Applying Security

In BIDS, create a new role.  Mine is named “DimensionSecurity”.   On the “Membership” Tab add your user account (I added my “black” account).  On the “Dimension Data” Tab, open the “Advanced” sub-tab.  Select the “Geography” dimension, and the “City” attribute.  Then type the below MDX expression into the “Allowed member set” text box.   You can also check “Enable Visual Totals” here if totals should be tallied based on the filtered set, not the entire set.

Exists([Geography].[City].MEMBERS,StrToMember("[User].[User Name].&[" + Username + "]"), "Geography Security")

The exists function returns a set that is based on filtering the first set by members of the second set.  And because the “Geography Security” measure group is specified, the second set, [User].[User Name].&[black], must also have associated rows in the FactGeographySecurity fact table.  Now it should make sense why we added FactGeographySecurity as a measure group, and DimUser as a dimension. 

 Note: this only secures the City attribute.  For each attribute in the drop-down list, type in an allowed member set similar to the one above.  Only the first parameter would change.  Ex., for the Country-Region attribute, the first parameter becomes [Geography].[Country-Region].MEMBERS.

Testing in BIDS

In BIDS, go to the cube browser, and click the “Change User” icon .  Select the “Roles” option, and choose “DimensionSecurity” from the drop-down list.  Expand Geography and drag the City attribute into the rows area.  Drag any measure from Reseller Sales into the totals area.  You should see, as in the below, that only cities from Great Britain and Canada are displayed.

 

Testing via MDX

Run the below MDX script in SSMS.  Notice how the “Allowed member set” MDX from above is used on the row axis, with the username hardcoded.  This might be useful if you just want to verify that the dimension/fact data is setup correctly. 

SELECT 
[Measures].[Sales Amount] ON COLUMNS, 
Exists([Geography].[City].MEMBERS,StrToMember("[User].[User Name].&[blackd820\black]"), "Geography Security") ON ROWS 
FROM [Adventure Works DW2008]

 

Testing in Excel 2007

When connecting via Excel, you might expect it to just work.  After all, your account is in the “DimensionSecurity” role.  But if you are a server administrator, you will still see all of the City members, not just ones for Great Britain and Canada. But similar to the way you explicitly chose “DimensionSecurity” in the BIDS cube browser, you can test other roles by listing them (comma-delimited if more than one) in the connection string:

Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Data Source=blackd820;Initial Catalog=SSAS Security;Roles=DimensionSecurity

To do this, (I’m using Excel 2007), go to the “Data” tab and select “Properties”.  You’ll find the connection string on the “Definition” tab.  Add the part highlighted in yellow above.

Note: If you’ve only set security on the “City” attribute, expand “More fields” and select “City” from the PivotTable Field List pane.

Populate the security table from Active Directory

You probably noticed that security is defined in the FactGeographySecurity table at the grain of the individual user, instead of the Active Directory role.  This is because there is no IsUserInRole MDX function.  So now we have to maintain data in the FactGeographySecurity table to keep it in sync with Active Directory.  To do this, we need a way to query an A/D group and return a list of users. 

A utility application scheduled to execute nightly could be written to accomplish this.  The code snippet below retrieves a list of users in the specified A/D group; this list could be used to insert records into FactGeographySecurity. 

        /// 

        /// Gets a list of members in the specified group.
        /// 
        public static List GetGroupMembers(string groupName)
        {
            List users = new List();

            string domain = "somedomain.com";
            string filter = string.Format("(&(ObjectClass={0})(sAMAccountName={1}))", "group", groupName); // principal);
            string[] properties = new string[] { "fullname" };
            DirectoryEntry adRoot = new DirectoryEntry("LDAP://" + domain, null, null, AuthenticationTypes.Secure);
            DirectorySearcher searcher = new DirectorySearcher(adRoot);
            searcher.SearchScope = SearchScope.Subtree;
            searcher.ReferralChasing = ReferralChasingOption.All;
            searcher.PropertiesToLoad.AddRange(properties);
            searcher.Filter = filter;
            SearchResult result = searcher.FindOne();

            if (result != null)
            {

                DirectoryEntry directoryEntry = result.GetDirectoryEntry();
                foreach (object dn in directoryEntry.Properties["member"])
                {
                    DirectoryEntry member = new DirectoryEntry("LDAP://" + dn);

                    //Add users from other groups within this group (only go 1 level deep).
                    if (!IsGroup(member))
                    {
                        users.Add(member);
                    }
                }
            }
            return users;

        }

        /// 

        /// Determine whether the object is a group.
        /// 
        private static bool IsGroup(DirectoryEntry de){
            return de.Properties["objectClass"][1].ToString() == "group";
        }

Finally

Using this approach, each dimension can be secured by creating only a single role.  This satisfied my client,  who did not want to manually create a role per geography, then go into the role definition and manually select dimensions available to that role.

Hope this helps!