Using a Parent Child Hierarchy in SQL Server to Implement a Custom Security Scheme

By: Ray Barley | Read Comments (7) | Print Ray is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert. Related Tips: More

Problem
I have a requirement to implement a custom security scheme where roles and the user's place in the organization hierarchy are used to determine which customers a user can access.  In particular the requirements are that a sales person can only access their customers and any other role can access any customer in their level of the organization hierarchy and below.  We have a simple hierarchy that is made up of regions and offices.  Can you provide us with an example of how to do this?

Solution
Based on your requirements we can design a solution that leverages a parent-child hierarchy to determine what customers a user can access.  Our goal is to create a table that we can query that will return the list of customers that a user can access based on his role and level in the organization hierarchy.  In this tip we will walk through the following steps to achieve our goal:

• Review a T-SQL script to create the CustomerHierarchy table and its constraints
• Review a T-SQL script to populate the CustomerHierarchy table
• Review T-SQL queries to show the list of customers that a user can access

Create the CustomerHierarchy Table

We will use the following table to define the organization hierarchy, user roles, and customers:

CREATE TABLE [dbo].[CustomerHierarchy]
(
 [HierarchyKey] [int] IDENTITY(1,1) NOT NULL,
 [HierarchyType] [varchar](50) NOT NULL,
 [Description] [varchar](50) NOT NULL,
 [UserLogin] [varchar](50) NULL,
 [RoleName] [varchar](50) NULL,
 [CustomerKey] [int] NULL,
 [ParentHierarchyKey] [int] NULL
)
ALTER TABLE [dbo].[CustomerHierarchy]
ADD CONSTRAINT [PK_CustomerHierarchy] 
PRIMARY KEY CLUSTERED ([HierarchyKey] ASC)
ALTER TABLE [dbo].[CustomerHierarchy]
ADD CONSTRAINT [FK_CustomerHierarchy] 
FOREIGN KEY ([ParentHierarchyKey])
REFERENCES [dbo].[CustomerHierarchy] ([HierarchyKey])
ALTER TABLE [dbo].[CustomerHierarchy]
ADD CONSTRAINT [FK_CustomerHierarchy_Customer] 
FOREIGN KEY ([CustomerKey])
REFERENCES [dbo].[Customer] ([CustomerKey])

The following are the main points about the above script:

• HierarchyType determines the row type; it can be REGION, OFFICE, ROLE, CUSTOMER, or ROOT (i.e. the top level of the hierarchy)
• Description is based on HierarchyType; it could be a region name, office name, role name, customer name, or 'CUSTOMER-HIERARCHY' for the top level of the hierarchy
• UserLogin is the windows login in the form DOMAIN\USERNAME; it only appears in rows with a HierarchyType of ROLE
• RoleName is the user's role; it can be MANAGER, ASSOCIATE, or SALES; it only appears in rows with a HierarchyType of ROLE
• CustomerKey is a foreign key to the customer table; it only appears in rows with a HierarchyType of CUSTOMER
• ParentHierarchyKey links a row to its parent row via a foreign key; this is the parent-child hierarchy where the value of ParentHierarchyKey is equal to the HierarchyKey in another row in the same table

Populate the CustomerHierarchy Table

Let's manually populate the CustomerHierarchy table with some sample data so that we can execute some queries to show which customers a particular user can access.  The following T-SQL command inserts a row as the top level of the hierarchy:

INSERT [dbo].[CustomerHierarchy] (
 [HierarchyType]
, [Description]
)
VALUES (
 'ROOT'
, 'CUSTOMER HIERARCHY'
) 

Add regions by specifying a ParentHierarchyKey value of 1 which is the HierarchyKey value of the top level of the hierarchy:

INSERT [dbo].[CustomerHierarchy] (
 [HierarchyType]
, [Description]
, [ParentHierarchyKey]
)
SELECT 'REGION', 'RED', 1
UNION ALL
SELECT 'REGION', 'WHITE', 1
UNION ALL
SELECT 'REGION', 'BLUE', 1

Add offices to each region by specifying a ParentHierarchyKey value which is the HierarchyKey value of its region:

INSERT [dbo].[CustomerHierarchy] (
 [HierarchyType]
, [Description]
, [ParentHierarchyKey]
)
SELECT 'OFFICE', 'Pittsburgh, PA', 2
UNION ALL
SELECT 'OFFICE', 'New York, NY', 2
UNION ALL
SELECT 'OFFICE', 'Chicago, IL', 3
UNION ALL
SELECT 'OFFICE', 'St. Louis, MO', 3
UNION ALL
SELECT 'OFFICE', 'Dallas, TX', 4
UNION ALL
SELECT 'OFFICE', 'New Orleans, LA', 4

Add user roles to each region by specifying a ParentHierarchyKey value which is the HierarchyKey value of its region:

INSERT [dbo].[CustomerHierarchy] (
 [HierarchyType]
, [Description]
, [UserLogin]
, [RoleName]
, [ParentHierarchyKey]
)
SELECT 'ROLE', 'John Smith', 'DOMAIN\jsmith', 'MANAGER', 2
UNION ALL
SELECT 'ROLE', 'Mark Jones', 'DOMAIN\mjones', 'MANAGER', 3
UNION ALL
SELECT 'ROLE', 'Will Thomas', 'DOMAIN\wthomas', 'MANAGER', 4

Add user roles to an office by specifying a ParentHierarchyKey value which is the HierarchyKey value of its office:

INSERT [dbo].[CustomerHierarchy] (
 [HierarchyType]
, [Description]
, [UserLogin]
, [RoleName]
, [ParentHierarchyKey]
)
SELECT 'ROLE', 'Tim Willis', 'DOMAIN\twillis', 'ASSOCIATE', 6
UNION ALL
SELECT 'ROLE', 'Ron Lewis', 'DOMAIN\rlewis', 'SALES', 6
UNION ALL
SELECT 'ROLE', 'Bill Miller', 'DOMAIN\bmiller', 'SALES', 6

Connect customers to their sales person by specifying a ParentHierarchyKey value which is the HierarchyKey value of the sales person: 

INSERT [dbo].[CustomerHierarchy] (
 [HierarchyType]
, [Description]
, [CustomerKey]
, [ParentHierarchyKey]
)
SELECT 'CUSTOMER', 'A Bike Store', 1, 15
UNION ALL
SELECT 'CUSTOMER', 'Progressive Sports', 2, 15
UNION ALL
SELECT 'CUSTOMER', 'Advanced Bike Components', 3, 16
UNION ALL
SELECT 'CUSTOMER', 'Modular Cycle Systems', 4, 16

After running the T-SQL commands above, the following is the result set of SELECT * FROM dbo.CustomerHierarchy:

The above result set represents a parent-child hierarchy where each row's ParentHierarchyKey specifies the HierarchyKey of its parent and:

• ROOT is the parent of REGION rows
• REGION is the parent of OFFICE rows
• REGION and OFFICE can be the parent of ROLE rows
• ROLE is the parent of CUSTOMER rows (but only the SALES role has customers)

Query the CustomerHierarchy Table

We need to create queries to satisfy the following business rules:

• A user in the SALES role can access any customer that is a child of the SALES role row
• A user in any other role can access any customer in the same level of the organization hierarchy or below

Here is our first query that returns the list of customers that are directly connected to a user in the SALES role:

SELECT c.CustomerKey
FROM dbo.CustomerHierarchy p
JOIN dbo.CustomerHierarchy c 
ON c.ParentHierarchyKey = p.HierarchyKey
WHERE p.UserLogin = 'DOMAIN\rlewis'
AND p.RoleName = 'SALES'

The following is the result set from the above query:

The following are the main points about the above query:

• The p table alias is used to mean "parent"; the c table alias is used to mean "child"
• Get all rows from the parent where the UserLogin =  'DOMAIN\rlewis' and the RoleName = 'SALES'
• Join the parent rows to the child rows by joining the child's ParentHierarchKey to the parent's HierarchyKey
• Referring back to the result set from SELECT * FROM dbo.CustomerHierarchy, you can see that the UserLogin 'DOMAIN\rlewis' is in the SALES role in the New York office and he has two customers

Here is our second query that returns the list of customers that are either in the same level of the hierarchy or below based on the level of our user role:

; WITH customer_cte AS (
 SELECT p.* 
 FROM dbo.CustomerHierarchy c
 JOIN dbo.CustomerHierarchy p 
 ON p.HierarchyKey = c.ParentHierarchyKey
 WHERE c.UserLogin = 'DOMAIN\jsmith'
 AND c.RoleName NOT IN ('SALES')
 UNION ALL
 
 SELECT c.*
 FROM customer_cte p
 JOIN dbo.CustomerHierarchy c 
 ON c.ParentHierarchyKey = p.HierarchyKey 
) 
SELECT CustomerKey FROM customer_cte
WHERE HierarchyType = 'CUSTOMER' 

The following is the result set from the above query:

The following are the main points about the above query:

• The query uses a recursive common table expression
• The first SELECT statement retrieves the parents for all rows for a particular user login where the role name is not SALES; we exclude the SALES role because anyone in the SALES role can only see customers that are directly connected (we did this in the first query)
• The second SELECT statement retrieves all rows that are children of the rows returned by the first SELECT statement
• The recursive aspect of the common table expression is that the second SELECT statement essentially keeps going down the hierarchy until the CUSTOMER rows are returned; i.e. we reach the bottom of the hierarchy
• Referring back to the result set from SELECT * FROM dbo.CustomerHierarchy, you can see that the UserLogin 'DOMAIN\jsmith' is a MANAGER in the RED region so he can see all customers in that region; we have two customers connected to each person in the SALES role in the New York office which is in the RED region

Next Steps

• A parent-child hierarchy can be a great choice for implementing data-driven security; it can easily handle the scenario where a user role can access everything in the same level of the hierarchy and below.
• You can download a backup of the sample database and T-SQL scripts here and use it as a basis for your own testing and experimentation.
• In a future tip I will show how to leverage a parent-child hierarchy to implement dimension security in a cube with an MDX expression.

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Tuesday, February 16, 2010 - 5:07:29 PM - jcelko

Several years ago I designed a system like this.  I used the Nested Sets model and the code was much easier.  Privileges for a given employee (leaf node) were inherited from higher levels automaticall.  For example, the Engineering department has CAD access, so all the engineers got it; everyone inherited email from the corporation level, etc. Individual employees could be blocked (Fred does not have email since that porno-spam thing he did) or granted special access (John in Accounting gets CAD rights) on an exception basis.

Tuesday, February 16, 2010 - 7:53:44 PM - raybarley
Thanks for the tip. Now that you mention it I remember reading about that in your Trees and Hierarchies book so I will give it a try.

Monday, March 01, 2010 - 1:48:21 PM - R Glen Cooper
I had a similar problem, and recently submitted an article on row-oriented security using triggers that will appear in sqlservercentral.com in late Feb 2010.  This model automatically updates each person's security whenever the data (or filters) used to define that security changes. R Glen Cooper

Thursday, January 26, 2012 - 9:29:33 AM - Tom
"In a future tip I will show how to leverage a parent-child hierarchy to implement dimension security in a cube with an MDX expression." Did you ever post that tip ? Thank you! Tom

Thursday, January 26, 2012 - 9:36:32 AM - Ray Barley

No.  I got a little bogged down with the MDX expressions that you need to make it work. I have a tip that is part of the solution: http://www.mssqltips.com/sqlservertip/1773/how-to-enable-user-defined-hierarchies-in-sql-server-analysis-services-ssas/ which lets you create user-defined hierarchies.  This shows how to build user-defined hierarchies which could be leveraged to provide the data then you would need to set security such that only certain people can see certain hierarchies.

Friday, January 27, 2012 - 10:06:58 AM - Ray Barley
I did do a related tip: http://www.mssqltips.com/sqlservertip/1844/sql-server-analysis-services-ssas-dimension-security-stored-procedures In that one I call out to .NET code from SSAS to security trim a dimension.  Still not what I really want but works well in some instances.

Wednesday, February 01, 2012 - 7:27:36 AM - Tom
Thank you Ray. I will certainly read the tips you mention.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

Powerful SSAS Performance Dashboard Innovative Workload and Bottleneck Profiling Capture of all Heavy MDX, XMLA and DMX Aggregation, Partition, and Dimension Activity by Query SSAS Cache and Storage System Monitoring	SQL Server + Windows Performance Dashboard Real-Time & Historical Performance Analysis Disk Activity, Latency, and Capacity Monitoring Top SQL Analysis Highlights Heaviest Queries Graphical Blocking and Deadlock Analysis	Calendar Views of Top SQL, Blocks, Deadlocks & Jobs Calendar Views of SSAS, SQL Server, SSIS, and SSRS Easy Job Chaining and Queuing Robust Alerting and Response System
FREE TRIAL DOWNLOAD