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.
- Annette Hill should only see records from the Canadian company
- Bart Duncan should only 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.
Right-click the Tables node select Add Table.
Assign a name for the table and click OK. In this example, we've named the table Access Rights.
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.
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. 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.
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.
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]
,Fact.[Sell-to Customer No]
FROM [dbo].[Sales Posted Transactions_V] AS Fact
CROSS JOIN [dbo].[Access Rights_V] AS Rights
Fact.[Company] = Rights.[Company]
Rights.[Company] = 'ALL'
Rights.[User Name] = SYSTEM_USER
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, the field enters 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.
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.
This will open a window allowing the user to select Only the modified tables and views and click Start.
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.
Using SQL Server Management Studio, create a SQL Server login for the user(s).
Using SQL Server Management Studio, create a user in the data warehouse. Click User Mapping and then tick the box for your data warehouse (in this example it is named JetNavDwh). Grant access to the public role.
Create a script that grants each user access to the view. In this example, we have 3 separate GRANT statements for each user.
In the project right click Script Actions → Add Custom Step
Assign a name for the script and paste the code from step 3 into the window
Right-click on the view and then select Advanced → Set Pre- and post scripts
From the Post Step drop-down, select your scriptYou can also apply this script at the database level as opposed to the view level.