SQL 2016 Row-Level Security Demo: Users with various clearance levels

SQL 2016 is right around the corner and one of the new security enhancements promised is Row-Level security for tables.  It’s a great new feature and pretty easy to implement.  I have created a simple demo that gives various users access to data based on specific clearance level to the data.  Feel free to modify the code and play around with it how to see fit.  There are many different ways to setup Row-Level security and this is just one scenario.  One of the things you’ll notice if you go through the scripts below is that the dbo user does not have access to the data after the the security policy is applied.  This is key for many environments where customers do not want administrators to have access to sensitive data.  Of course anyone with good coding skills and the proper permissions could circumvent that, but that’s why we put auditing measures in place 🙂

I’ve broken up the code into three sections.  The first is for setting up the database and permissions.  The second section creates the tables in the database and puts test data in them.  The third section is for the creation of the function and security policy which enables Row-Level Security. After creating the function and security policy, go back to the second section and re-run the select statements to see the security policy in action.  This demo was created on SQL 2016 CTP 2.2.  If you are interested in learning more about Row-Level Security and to see some other demos please refer to this webinar from PASS.

First let’s start with the database, login, and database user creation:

Use Master
Drop database RowLevelSecurity
Create Database RowLevelSecurity

USE [master]
GO
CREATE LOGIN [user1] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [RowLevelSecurity]
GO
CREATE USER [user1] FOR LOGIN [user1]
GO
USE [RowLevelSecurity]
GO
ALTER ROLE [db_datareader] ADD MEMBER [user1]
GO

USE [master]
GO
CREATE LOGIN [user2] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [RowLevelSecurity]
GO
CREATE USER [user2] FOR LOGIN [user2]
GO
USE [RowLevelSecurity]
GO
ALTER ROLE [db_datareader] ADD MEMBER [user2]
GO


USE [master]
GO
CREATE LOGIN [user3] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [RowLevelSecurity]
GO
CREATE USER [user3] FOR LOGIN [user3]
GO
USE [RowLevelSecurity]
GO
ALTER ROLE [db_datareader] ADD MEMBER [user3]
GO


USE [master]
GO
CREATE LOGIN [user4] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [RowLevelSecurity]
GO
CREATE USER [user4] FOR LOGIN [user4]
GO
USE [RowLevelSecurity]
GO
ALTER ROLE [db_datareader] ADD MEMBER [user4]
GO

Here is section #2 of the demo code:

Use RowLevelSecurity
go
---Create new schema for tables/functions related to rowlevel security **recommended**
Create Schema RowSec
go
--Create a table with sensative data
Create Table ImportantData
(
UserID Int Identity(10000,1),
FirstName varchar(50),
LastName varchar(50),
SSN varchar(9),
ClearanceLevelNumber int
)
go


---Insertion of random data

Insert into ImportantData values ('FName'+cast(scope_identity() as varchar(5)),'LName'+cast(SCOPE_IDENTITY() as varchar(5)),CAST(RAND()*1000000000 as int),3)
go 20


---updating sensative data to have different levels of clearance
Update ImportantData
set ClearanceLevelNumber = 2
where UserID < 10005

Update ImportantData
set ClearanceLevelNumber = 1
where UserID = 10007

Update ImportantData
set ClearanceLevelNumber = 0
where UserID > 10013


--Check data
Select *
from ImportantData


---Create a table with user and user clearance information.
---Login name represents the database user id
Create Table RowSec.Users
(
LoginName varchar(50),
ClearanceLevel varchar(50),
ClearanceLevelNumber int
)

---Inserting user information and clearance levels
Insert into RowSec.Users Values('User1','Top Secret',3),('User2','Secret',2),('User3','Confidential',1), ('User4','None',0)


---verify that the user name matches the username in the security table
Select *,USER_NAME(),SUSER_NAME()
from RowSec.Users

select *,User_name()
from ImportantData

---Run these scripts before and after creating the security policy

--Results: before security policy is applied this should bring back ALL data
--Results: after security policy is applied this should bring back NO data
--**** Even DBO does not have access to the data since row-level security is based on the users in the security table****
Execute as USER = 'dbo'
select *,user_name()
from ImportantData
revert;

--Results: before security policy is applied this should bring back ALL data
--Results: after security policy is applied this should bring back data that the user has clearance for. 
--Since this user has Secret clearance, only data that is Secret level or below should be returned.
Execute as USER = 'User1'
select *,user_name()
from ImportantData
revert;

--Results: before security policy is applied this should bring back ALL data
--Results: after security policy is applied this should bring back data that the user has clearance for. 
--Since this user has Confidential clearance, only data that is Confidential level or below should be returned.
Execute as USER = 'User2'
select *,user_name()
from ImportantData
revert;

--Results: before security policy is applied this should bring back ALL data
--Results: after security policy is applied this should bring back data that the user has clearance for. 
--Since this user has NO clearance, only data that is Not Sensative should be returned.
Execute as USER = 'User3'
select *,user_name()
from ImportantData
revert;

--Results: before security policy is applied this should bring back ALL data
--Results: after security policy is applied this should bring back NO data
Execute as USER = 'User4'
select *,user_name()
from ImportantData
revert;


---clean up
Drop table dbo.ImportantData
Drop table RowSec.Users

The final section of the demo code:

Use RowLevelSecurity
go

CREATE FUNCTION RowSec.fn_SecretClearance(@ClearanceLevel int)
 RETURNS TABLE
WITH SCHEMABINDING
AS
 RETURN SELECT 1 AS Acess_Result
 from RowSec.Users 
WHERE LoginName=User_Name() and ClearanceLevelNumber >= @ClearanceLevel;


----create after the function is created

CREATE SECURITY POLICY RowSec.SensitiveData
ADD FILTER PREDICATE RowSec.fn_SecretClearance(ClearanceLevelNumber) ON dbo.ImportantData
WITH (STATE = ON);


---Allows for security policy to be turned off without dropping related objects
Alter SECURITY POLICY RowSec.SensitiveData
WITH (State = OFF)


---Clean up
Drop Security Policy RowSec.SensitiveData
Drop Function RowSec.fn_SecretClearance