Configure Row-Level Data Warehouse Security

This article only applies to the data warehouse. For configuration of OLAP Security see: Configuring OLAP Security

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?
• Configuration of Row-Level Security
  • Build User Access Table
  • Create Custom View
  • Grant Users Access to the View
  • Scenario
  • Building the Schema

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

An 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've named the table Access Rights.

   

3. Right-click the table and select Add Fields.

   

   In this example, we have 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 and click Advanced > Custom Data.

   

   This list contains the name of the user and the company name to which the user has access. 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.

   View Code Example

         CREATE VIEW [dbo].[vRestrictedPostedSalesTransactions]				
         AS				
         SELECT				
         Fact.[Company]				
         ,Fact.[Document No]				
         ,Fact.[Sell-to Customer No]			
         ,Fact.[Sales]				
         FROM [dbo].[Sales Posted Transactions_V] AS Fact				
         CROSS JOIN [dbo].[Access Rights_V] AS Rights				
         WHERE				
         (				
         Fact.[Company] = Rights.[Company]				
         OR				
         Rights.[Company] = 'ALL'				
         )			
         AND				
         Rights.[User Name] = SYSTEM_USER				
   					

   If you have additional attributes that you would like to constrain on, make sure they are defined in the WHERE clause.

2. Once your view is created, you will need to add it to the project.  To do this, right-click the Views node in the data warehouse and select Add Custom View.

   

3. Paste your code into the window.  In the Name as in script, the field enters the name of the script as it is in the view code.  Once finished click OK

   

   For more information about creating custom views see:

   • Creating Custom Views

   • Create View Syntax

   If prompted with the following warning click Yes.

   

   If prompted with this message click Yes.

   

4. At this point deploy and execute all modified objects in your project.  One way to do this is to click the top-most node and select Deploy and Execute.

   

   This will open a window. Select Only the modified tables and views and click Start.

   

5. Right-click the view and select Read View FieldsThis will allow you to visually verify that the proper fields were added to the view.

   

Grant Users Access to the View

We now need to grant our users access to the view.

1. Using SQL Server Management Studio, create a SQL Server login for the user(s).

   

   

2. Using SQL Server Management Studio, create a user in the data warehouse. Click User Mapping and then select 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 the view and then select 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.