jump to navigation

Row Level Security in SQL Server 2016 December 10, 2016

Posted by fofo in Sql Server, SQL Server 2016.
Tags:
trackback

This is the third post in a series of posts that highlight the new features of SQL Server 2016. You can find the first two post in SQL Server 2016 (JSON support in SQL Server 2016, temporal tables) series here & here.

SQL Server 2016 was officially released back in June 2016.

There are many new features in SQL Server 2016 and in  my opinion the most important are:

  • Always Encrypted
  • Query Store
  • Row Level Security
  • Temporal Tables
  • Stretch Database
  • Dynamic Data Masking
  • PolyBase
  • JSON Support

I am going to discuss the “Row-Level Security” in SQL Server 2016 in this post.

I have installed SQL Server 2016 Enterprise edition in my machine but you can use the SQL Server 2016 Developer edition as well.

You can download the SQL Server 2016 Developer edition by joining the Visual Studio Dev Essentials which is free and simple. After joining you will be able to download SQL Server 2016 developer edition in your machine.

In many cases in our applications we have a multi-tenant database with a shared schema.

In modern applications there are certain situations where we need to restrict the access to data to our users.

We  have sales people that need to access only their data, (sales, reports, targets that apply only to them) in a web sales application.

We need students to only see their data (grades, stats) in an online web university application.

In all the scenarios above we need to prevent users/tenants from accessing data that does not belong to them.

The solution to the problems above was to write custom application code or to create SQL views.

The problem with SQL Views is that if we need to protect 3-4 tables we will spread the business logic all over the place. Obviously this kind of implementation soon becomes difficult to maintain, and the views are prone to run time errors.

We can also use custom code to implement access restrictions in the application.

This solution is not the best either. If you need to use the same access logic for a database, but in different applications, you may need to do the same work multiple times.

It’s also difficult to maintain, especially as the code base grows. But the most important reason is that in some situations the custom code is actually impossible to use, especially if you do not own the code.

Row-Level security in SQL Server 2016 was implemented to help us solve these kind of problems in a much more efficient way. Now we have a mechanism where we can control who reads and writes data from our database tables, based on criteria that we define.

We can define a plethora of criteria.Row-level security is transparent to the application code. We don’t need to make any changes in the code. All our data access logic is stored within the database.

Row-Level Security once implemented will apply a security predicate to all queries executed on a table. The predicate will dictate to what rows a specific user has access.

Let’s us start with our hands-on example.

We need to have Project Managers (supposedly we have a Projects Table) looking into only to the project details they are overlooking and are responsible for.

 

Type (copy paste) the following query in a new query window and execute it.

USE master;
GO

CREATE DATABASE RowLevelSecurity
GO
 
USE RowLevelSecurity
GO

CREATE TABLE dbo.Projects
 (
 ProjectID INT PRIMARY KEY IDENTITY,
 ProjectName NVARCHAR(50) ,
 ProjectManager NVARCHAR(50) ,
 budget DECIMAL(18,4),
 DateDue DATETIME

 );

INSERT dbo.Projects
VALUES ('NopCommerce', 'George', 12512, '12/12/2016'),
 ('Custom Asp.Net MVC', 'George', 24512, '11/11/2017'),
 ('Social Media Marketing', 'Mary', 3562, '03/03/2017'),
 ('Google Adwords Remarketing', 'Mary', 4512, '04/04/2017'),
 ('Google Adwords Search', 'Mary', 9218, '04/06/2017'),
 ('SQL Server Reporting Services', 'Tom', 8765, '04/03/2017'),
 ('SQL Server Upgrade to 2016', 'Tom', 8512, '04/08/2017')
; 


create user George without login;
create user Mary without login;
create user Tom without login;


GRANT SELECT ON dbo.Projects TO George
GRANT SELECT ON dbo.Projects TO Mary
GRANT SELECT ON dbo.Projects TO Tom
 

SELECT * FROM dbo.Projects

In the code above, I create a sample database.

Then I create a Projects table and then insert some sample data. Then I create users without logins and grant them select rights on the table Projects. Finally I query the table to see the results.

Type (copy paste) the following query in a new query window and execute it.


CREATE FUNCTION dbo.fn_securityProjects (@FilterColumnName AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
as
 RETURN SELECT 1 AS fn_securitypredicate_result 

where @FilterColumnName = user_name();


CREATE SECURITY POLICY FilterProjects
ADD FILTER PREDICATE dbo.fn_securityProjects(ProjectManager)
ON dbo.Projects
WITH (STATE = ON); 

In the code above I create an inline function and a security policy. My function will be used to filter out rows based on the database user.

The function code above accepts a parameter named @FilterColumnName, and then uses this parameter to filter out the rows based on the database user, using the user_name() function.

This function will be used to filter rows from my Projects table by associating this function with a security policy.

In the security policy  I added a FILTER PREDICATE that referenced my dbo.fn_securityProjects function.  By creating this security policy the SQL Server engine will make sure every time that a database user runs a SQL command that references the dbo.Projects table that the filter predicate  function will also be executed.

Now let’s try out our Row-Level Security functionality.

Type (copy paste) the following query in a new query window and execute it.

execute as user = 'George';
select * from dbo.Projects; 
revert;
 
execute as user = 'Mary';
select * from dbo.Projects; 
revert;
 
execute as user = 'Tom';
select * from dbo.Projects; 
revert;
 

Have a look at the picture below

users-db

As you can see, by looking at this output, the SELECT statement that was run when the database user was set to George returned 2 rows and each row had the value ‘George’ in the ProjectManager column. The user Mary returned 3 rows and Tom only had 2 rows associated with his user name.

This is a great new addition to SQL Server 2016 and help us having a fine-grained control over our data.

Hope it helps!!!

Comments»

1. Drop if Exists T-SQL Enhancement statement in SQL Server 2016 | DOT NET RULES - December 23, 2016

[…] A post on Row Level Security in SQL Server 2016 can be found here. […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: