Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give Feedback

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:

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

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.

 

View Code Example

CREATE VIEW
AS
SELECT
Fact.[Company]
,Fact.[Document No]
,Fact.[Sell to Customer No]
,Fact.[Sales Amount]
FROM [dbo].[Posted Sales 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 then 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 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 .

 

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 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.

Granting 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 → 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.
 
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments