SQL Server 2016 – Row Level Security

Dear Friends,

In conjunction to my previous blog, picked up another most awaited feature Row Level Security in short RLS. Current database world many RDBMS are emphasizing towards security as some or the other DB holding sensitive data. So far this kind of solution is available in SQL Server through T-SQL VIEWS/FUNCTION but restricting access to tables and limiting visibility to users. However that’s quite cumbersome and requires decent amount of T-SQL skills, understand of schemas, table structures etc. On top of it managing those VIEWS in timely fashion is just an overhead and once application moves from project phase to BAU, support team(s) chases tale of DBAs and DBAs push it back to developers and so on. So pretty much chasing each other’s tail to get out of woods.

With Row Level Security (RLS) feature coming with SQL Server 2016 lot of us irrespective of DBA/Developer/Project Managers/Infra Architects are/have started planning to onboard/leverage this feature in their respective environment(s). RLS will ease out quite a bit of pain where intended users can have access to table(s) without having access to all rows on that table. And this all comes with easy setup & very limited maintenance overhead. You can administer via SQL Server Management Studio or SQL Server Data Tools.

Row-level Security
Row-level Security

Woah! Cost effective solution – everyone loves on today’s cut throat market.

Let’s take a problem statement to demonstrate a need of Row Level Security.

Organisation’s compliance team wants a to see green flag (documented assurance) that credit card department workers can access only those data rows that are respective to their customers (Priority/Moderate/Low). Simple words, credit Card department head can see data for all customers holding different level of credit cards, particular Credit card Customer Service Manager can see their customer data but not all customers AND customer care representative can see data to certain customers holding particular type of credit card.

So compliance team raises request/query to Service Owner(s) or Stake holder(s). Down the line such action item(s) are going to be addressed by either Application team or DBA team, basically stakeholders of data/database(s). If organization is appearing first time for Audits such as ISO/SOX/HIPPA, story goes in different direction with quite a bit of additional steps. However let’s assume this is a kind of routine activity and IT team is pretty educated in terms of new technology stacks & always willing to leverage advance features to address current situation. So IT Team decides to move their hands off from traditional way of managing Row-Level Security (VIEW/FUNCTIONS etc.) in SQL Server DBs which are in SQL Server 2016 or going to be migrated in same. So minimal overhead to organization and carry some value in term of advancement and futuristic approach. Pretty much hitting two birds with single stone. More than happy to have such an advance an educated team, it’s definitely an asset to organization.

Answer to problem statement a RLS (Row Level Security) in SQL Server 2016.

Let’s pull over sleeves and get a feel how exactly Row Level Security (RLS) works in SQL Server 2016.

From a high level prospective, there are 3 new terms are associated with Row Level Security and whole concept roam around them. I’ll be explaining them high to moderate level (as I do most of time), so you can walkthrough concept and mimic to your environment wherever needs.

Predicate function – An inline table-valued function that implements access control logic.

Security Predicate – Not a new object but an inline table valued function (inline TVF) which contains the logic of filtering the rows.

Two types: filter predicates and blocking predicates

Security Policy – New object – can be CREATE’ed/ALTER’ed /DROP’ed. Imagine as a container of predicates which can be applied to tables. One policy can contain security predicate to many tables. A policy can be in an ON or OFF state.

Demo here I’m explaining is based upon my previous blog where DB name is none other than “Financle”. Don’t know for some reason I’m loving this name.

Step 1 – Create users in “Financle” database with R/O permission. You many create hundreds & thousands, for demo purpose I’ve set a limit to 3 users.

USE [master]
GO
CREATE LOGIN [F1User] WITH PASSWORD=N'pass123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE Finacle
GO
CREATE USER [F1User] FOR LOGIN [F1User]
GO
USE Finacle
GO
ALTER ROLE [db_datareader] ADD MEMBER [F1User]
GO
USE [master]
GO
CREATE LOGIN [F2User] WITH PASSWORD=N'pass123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE Finacle
GO
CREATE USER [F2User] FOR LOGIN [F2User]
GO
USE Finacle
GO
ALTER ROLE [db_datareader] ADD MEMBER [F2User]
GO

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

Step 2 – Create a table & push some data which contains information of Credit Card holders. Simply “Sensitive Information”.

Use Finacle
go
--Create a table with sensitive data
Create Table CustomerPII_Last
(
[CustomerId] [int] IDENTITY(1,1) NOT NULL,
[Salary] int,
[CarLoanAmount] int,
[TypeofCreditCard] varchar(9),
[AccessLevel] int
)
go

--DROP TABLE CustomerPII_Last

Insert Into CustomerPII_Last VALUES (2000000,800000,'BLACK',3)

Insert Into CustomerPII_Last VALUES (2500000,100000,'BLACK',3)

Insert Into CustomerPII_Last VALUES (3500000,100000,'BLACK',3)


Insert Into CustomerPII_Last VALUES (1000000,500000,'REGALIA',2)

Insert Into CustomerPII_Last VALUES (800000,300000,'REGALIA',2)

Insert Into CustomerPII_Last VALUES (700000,300000,'REGALIA',2)


Insert Into CustomerPII_Last VALUES (500000,100000,'PLATINUM',1)

Insert Into CustomerPII_Last VALUES (480000,150000,'PLATINUM',1)

Insert Into CustomerPII_Last VALUES (350000,50000,'PLATINUM',1)

Step 3 – Create a user table which contains limited information about particular user and their access level.

Create Table FXUsers
(
FxUName varchar(50),
FxUAccessLevel int,
)

Step 4 – Let’s run select query and see what’s visible there. Probably nothing as I’ve not pushed any data there.

Select *,USER_NAME(),SUSER_NAME() from FXUsers
--Sorry mates, here is script to push some user data.

INSERT INTO FXUsers VALUES ('F1User',1)
INSERT INTO FXUsers VALUES ('F2User',2)
INSERT INTO FXUsers VALUES ('F3User',3)

Step 5 – Another try to see upto where we’re.

SELECT *,USER_NAME() FROM CustomerPII_Last
GO
Execute as USER = 'dbo'
select *,user_name()
from CustomerPII_Last
revert;
GO

Execute as USER = 'F2User'
select *,user_name()
from CustomerPII_Last
revert;

Aforesaid queries don’t make any different, all rows are select to every user. L What’s wrong!

Damn! I’ve not created RLS security policies & functions yet. So let’s jump in there.

Step 6 – Below script is nothing just creating a Predicate function (part of RLS functions you’ve seen few lines above).

CREATE FUNCTION dbo.fn_AccessPerLevel(@AccessLevel int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS Acess_Result
from dbo.FXUsers
WHERE FxUName=User_Name() and FxUAccessLevel >= @AccessLevel;

Step 7 – Create Security Policy as last step of this whole implementation.

CREATE SECURITY POLICY dbo.DataSecurity
ADD FILTER PREDICATE dbo.fn_AccessPerLevel([AccessLevel]) ON dbo.CustomerPII_Last
WITH (STATE = ON);

This is kind of a switch, you can put on/off based upon need. Here is syntax for same.

Alter SECURITY POLICY dbo.DataSecurity WITH (State = OFF)

Ah! Finally we’re good with all implementation steps. However still not sure how to leverage it. Here you go, jump to step 5 again and kick off all steps listed there, you’re answer is there.

Glad! I could put forward another interesting topic of SQL Server 2016. Excited to continue my efforts in same direction by putting forward another wonderful topic in SQL Server Security & Encryption.

Stay tuned.

Happy Learning!

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Avanish Panchal on Twitter | Follow Avanish Panchal on FaceBook

Avatar

About Avanish Panchal

Avanish carries around 15 years of experience in IT industry. He is post graduate in Computer Science, followed by Masters in Business Administration. He has worked on multiple technologies like SQL Server, .net & Sybase with world largest bank(s)/IT consulting firm(s) like JPMorganChase, CapGemini, Datamatics etc. Currently holds position of Database Architect in BioPharma Global Leader. His area of focus are SQL Server DB Engine, Security, Storage, Virtualization & Infrastructure Consolidation. Passionate for delivering Database IT Infrastructure to satisfy and exceed the needs of the enterprise. You may find him active on various forums like SQLBangalore, SQLServerGeeks & many more. His SQL Server passion helped him to be the Core Team member of Asia's First SQL Server Conference in 2015 @ Bangalore (#SSGAS2015).

View all posts by Avanish Panchal →

Leave a Reply

Your email address will not be published. Required fields are marked *