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
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.
This example provides the basics of how to configure row-level security within your project.
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
To support the scenario above we will need to build the schema / model to support it.
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
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.
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.
,Fact.[Sell to Customer No]
FROM [dbo].[Posted Sales Transactions_V] AS Fact
CROSS JOIN [dbo]._Access Rights_V] AS Rights
Fact.[Company] = Rights.[Company]
Rights .[Company] = 'ALL'
Rights. [User Name] = SYSTEM_USER
2. Once your view is created you will need to add it to the project. To do this Right click on 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 field enter the name of the script as it is in the view code. Once finished click OK .
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 on the top most node and select Deploy and Execute Modified Tables and Views .
5. Lastly, right-click the view and select Read View Fields . This will allow you to visually verify that the proper fields were added 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 → 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