Restricting Data Access with Row Level Security – Part 3

To this point in the series, the examples we’ve used are limited to the single Azure database for the Endless Timekeeping application. It’s great to define the concepts, but for an enterprise application, a few more tools need to be added to the tool belt. Endless Reporting isn’t large enough to really have good enterprise scenarios, yet, so we’ll rely on ACME Corporation, made famous by Wile E. Coyote. ACME has several divisions including Recreational Gear, Physical Security and Explosives, who receive materials and subcomponents from their vendors ACME Skates, ACME IronWorks and ACME Gun Powder, respectively. Each division produces their unique products, manage inventories, etc. The corporate accounting department is responsible for the finances of all their divisions. With the implementation of Just-in-Time inventory, the vendors have to be responsive to ACME’s needs.

Extending the Limits of Row Level Security

Mapping Users to Divisions

ACME is fortunate to use an Enterprise Resource Planning (ERP) system that allows each division to be split into separate “companies.” Knowing data security would be important (or maybe it was dumb luck) when the system was implemented, ACME did take advantage of this feature and laid out the divisions as follows.

• 01 – Physical Security
• 03 – Explosives
• 04 – Recreational Gear

Of course, Row Level Security cannot be implemented in the ERP system directly, because it’s a 3rd-party application that was not designed to handle Row Level Security. However, the data is loaded into a data warehouse each day. Our examples will work with the inventory data mart.

The Accounting department, of course, has access to all companies in the system, as do all internal users of ACME Corporation. The vendors, on the other hand, should only have access to the ERP company they work with. Users from each vendor have Active Directory accounts in the ACME domain. We can take the companies and users and map them together in table named UserSecurity.CompanyUserMapping. We can continue to add users to this table, which will be time-consuming but possible. Several users are listed here.

CompanyID	ADUserName
ALL	ACMECorpUS\AcctUser1
01	ACMECorpUS\AIUser01
03	ACMECorpUS\AGPUser01
04	ACMECorpUS\ASUser01

In the data warehouse, there is a table, Dim.Company, that have a matching column named CompanyID. It turns out this table will play a key role with data access. A predicate function, like udf_CompanyFilterPredicate, can be created to filter Dim.Company, which is used in nearly every query against the data warehouse.

CREATE FUNCTION Dim.udf_CompanyFilterPredicate
(
    @CompanyID char(3)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
	-- Internal users who have access to all data (CompanyID = 'ALL')
	SELECT 1
	FROM UserSecurity.CompanyUserMapping m
	WHERE m.ADUserName = USER_NAME()
		AND m.CompanyID = 'ALL'

	UNION
	-- External users who have access to specific companies
	SELECT 1
	FROM UserSecurity.CompanyUserMapping m 
	WHERE m.ADUserName = USER_NAME() 
                AND m.CompanyID = @CompanyID
)

Let’s bind this function to the Dim.Company table.

CREATE SECURITY POLICY [dbo].[FilterDimCompanyPolicy] 
       ADD FILTER PREDICATE Dim.udf_CompanyFilterPredicate(CompanyID) ON Dim.Company
WITH (STATE = ON, SCHEMABINDING = ON)
GO

Assuming CompanyIDs are surrogate keys, defined as an IDENTITY, this solution won’t restrict data in the Inventory fact table, but it will be harder to determine what’s what, if the user doesn’t have access to all companies. Dim.Company is a small table, so when the table is filtered for each and every query, that filtering will be quite fast, compared to filtering DimCompanyID in the fact table. Of course, filtering Fact.Inventory would be the ideal solution, but there could be some performance issues, depending on the size of the table.

Filtering Data by Active Directory Groups

We started to key in Active Directory accounts into the CompanyUserMapping table for every user at ACME Corp. and for all their vendors’ users who need access to the data warehouse. That could be a lot of users to keep track of and a maintenance nightmare for the database administrator. A better solution might be to map the companies to Active Directory user groups, such that every member of a group has access to one or more companies. Let’s rename the table CompanyGroupMapping.

CompanyID	ADGroup
ALL	ACMECorpUS\InternalUsers
01	ACMECorpUS\ACMEPhysicalSecurityUsers
03	ACMECorpUS\ACMEGunPowderUsers
04	ACMECorpUS\ACMESkatesUsers

The predicate function we wrote earlier needs just a couple changes. Instead of comparing the current user’s login to the ADUserName field, the IS_MEMBER() function can be used, which determines if the current user is a member of specified database role or Active Directory (or the database server’s) group. The management of user access has moved from the DBA to the Active Directory administrators, by adding user accounts to the respective groups.

CREATE FUNCTION Dim.udf_CompanyFilterPredicate
(
    @CompanyID char(3)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
	-- Internal users who have access to all data (CompanyID = 'ALL')
	SELECT 1
	FROM UserSecurity.CompanyGroupMapping m
	WHERE IS_MEMBER(m.ADGroup) = 1
		AND m.CompanyID = 'ALL'

	UNION
	-- External users who have access to specific companies
	SELECT 1
	FROM UserSecurity.CompanyGroupMapping m 
	WHERE IS_MEMBER(m.ADGroup) = 1
                AND m.CompanyID = @CompanyID
)

These examples are not perfect, but I don’t know if there is a perfect example. However, my goal is to give you some ideas on ways to implement Row Level Security in a manner that might work for your enterprise.

In the next blog, we’ll look at some query patterns to follow and to avoid with Row Level Security. Stay tuned…