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