Configuring Data Warehouse Security - Row Level Security

Overview

When users are reporting from the data warehouse it is often necessary to restrict what they can see based on the data contained within a table. This type of security configuration is referred to as row-level security. This article covers the following:

What is Row-Level Security?

Row level Security is about constraining access to data for a database user at a very granular level.

By default the SQL Server relational engine can only limit user access to a certain level of granularity. For example SQL Server can limit access to a table and columns, but does not provide security within tables (i.e. based on the data contained within the table). Said another way, the grain of SQL Server Security is at the object level. What we want is access at a more granular level (at the row level).

Since SQL Server does not have native support for row-level Security we have to build a model to support the needs of the business. There are many different approaches to implementing row-level security, but the one we will cover in this article is through the use of custom views.

Configuration of Row-Level Security

This example provides the basics of how to configure row-level security within your project.

Scenario

Our organization has a business rule that states that each user should only see sales data from their respective company unless they are a manager. Managers are allowed to see data from all companies. For example:

Annette Hill should only see records from the Canadian company

Bart Duncan should o nly see records from the American company

Peter Saddow should see records from both the Canadian and American company

Building the Schema

To support the scenario above we will need to build the schema / model to support it.

Build User Access Table

1. Right click the Tables node select Add Table .

2. Assign a name for the table and click OK . In this example we named the table Access Rights.

3. Right click the table and select Add Fields .

In this example I added two fields called User Name and Company

If you have additional attributes that you would like to constrain on then you will have to add additional fields. In this example we only have a single condition.

4. Right click the table again → Advanced → Custom Data

This list contains the name of the user and the company name to which the user has access to. The ALL keyword signifies that Peter should have access to every company.

You will most likely need to format these names as they appear in your active directly using the Domain\Username format. In this example the domain is omitted as this example uses local users.

If a user needed access to multiple companies you could add another row with the company name. For example if Bart Duncan also needed to see data from the Canadian company you could add another row for Bart.

If there are a larger number of users that require restricted access it is recommended that you create an Excel sheet and then load this into your project as a second data source and drag the loaded table into the data warehouse. For more information see: Adding an Excel Data Source.

Create Custom View

We will use views to enforce row-level security. Views allow a predefined query to be presented to a user as if it were a table. Also, users can be granted access to a view, but denied access to the underlying tables. This prevents the user from bypassing the view and going straight to the base table. We will construct a view which applies all the necessary logic to enforce row-level security.

1. Create your view. Below is a sample view that you can use as a guide.

In this example we are performing a cross join on our fact table and our Access rights table. We are also stating several conditions in our WHERE clause. The logical OR operator allows us to give users access to all companies by adding the ALL string to the Access Rights table.

Granting Users Access to the View

2. Using SQL Server Management Studio, create a user in the data warehouse. Click User Mapping → tick the box for your data warehouse. In this example it is named JetNavDwh. Grant access to the public role.

3. Create a script that grants each user access to the view. In this example we have 3 separate GRANT statements for each user.

4. In the project right click Script Actions → Add Custom Step

5. Assign a name for the script and paste the code from step 3 into the window

6. Right click on the view → Advanced → Set Pre- and post scripts

7. From the Post Step drop down, select your script

You can also apply this script at the database level as opposed to the view level.