Friday, August 19, 2016

How Row-Level Security works in SQL Server 2016

Row-Level Security is a centralized security feature within the database which is helping us to maintain a consistent data access policy and reduce the risk of accidental data leakage. Most amazing thing is that RLS works transparently and does not require changing our application code because it has an ability to control both read and write operations to specific rows of data. It supports flexible access criteria means provides Fine-Grained Access Control on our database.
How Row-Level Security works?
To understand the RLS, we need to take a look on the necessary contents for applying this feature on our database table. So, we are using our Demo database and creating a customer table without RLS where each customer belong a territory. We will create another table called Employees table with RLS where each customer belong a territory. With the help of Employees table, we can apply RLS feature on Customers table also because both tables are linked each other with territory column as given below:
Use Demo
Go

------- Customer Table without RLS
CREATE TABLE [dbo].[Customers]
(
                [CustID] [int] IDENTITY(1,1) NOT NULL Primary Key,
                [FirstName] [varchar](100) NULL,
                [LastName] [varchar](100) NULL,
                [PhoneNo] [varchar](12) NULL,
                [Email] [varchar](100) NULL,
                [TerritoryId] [int] NULL
)

----- Employee Table with RLS
CREATE TABLE [dbo].[Employees]
(
                [UserID] [int] IDENTITY(1,1) NOT NULL Primary Key,
                [UserName] [varchar](100) NULL,
                [TerritoryId] [int] NULL
)

 ----- Insert values into Customers table 
INSERT [Customers] (FirstName, LastName, PhoneNo, Email, TerritoryId) VALUES  
('Ryan', 'Arjun', '555-123-4567', 'ryan.arjun@gmail.com',1), 
('Kimmy', 'Wang', '555-936-4568', 'kimmy,wang@yahoo.com.co',2), 
('Black', 'Smith', '932-123-0569', 'black.smith@aol.com',3),
('John', 'Ray', '932-025-0523', 'john.ray@rediffmail.com',2); 

 ----- Insert values into Employees table 
INSERT [Employees] (UserName, TerritoryId) VALUES  
('RyanArjun',1), 
('KimmyWang',2); 

---- Pull values from Customers table
SELECT CustId, FirstName, LastName, PhoneNo, Email, TerritoryId
FROM [dbo].[Customers]
CustId
FirstName
LastName
PhoneNo
Email
TerritoryId
1
Ryan
Arjun
555-123-4567
ryan.arjun@gmail.com
1
2
Kimmy
Wang
555-936-4568
kimmy,wang@yahoo.com.co
2
3
Black
Smith
932-123-0569
black.smith@aol.com
3
4
John
Ray
932-025-0523
john.ray@rediffmail.com
2

---- Pull values from Employees table
SELECT UserId, UserName,TerritoryId
FROM [dbo].[Employees]
UserId
UserName
TerritoryId
1
RyanArjun
1
2
KimmyWang
2
With the help of territory column we can apply RLS feature on customer table because Sales Res can access results from their assigned territory.

Inline table-valued function -
We need to create inline table-valued function (predicate function) that defines user access criteria. This function is going to apply all queries to against our data table and play an important role to authenticate the user for row level data access. This function at least has a parameter that corresponds to a column from our data table and returns one cell underlying our data table will be accessible.
In a very simple way, if this function returns true value against the requested user than data raw will be visible to him as given below:
Use Demo
Go

----- Create RLS policy Schema
CREATE SCHEMA Security
GO
---- create inline table-valued function (predicate function)
CREATE FUNCTION Security.CustomerAccessPredicates(@TerritoryId Int)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN SELECT AccessResult=1 From [dbo].[Employees]
WHERE
----- Sales Res can access results from their assigned territory
(UserName=USER_NAME() And TerritoryId=@TerritoryId)
OR
----- Database admin can access all rows
IS_MEMBER('db_owner')=1
Go

In the above function, we are using territory as parameter to the function and will be used for the row-level security for corresponding data table Customer.

Security Policy
This is another term of RLS feature because security policy will bind with our inline table-valued function and adds a security predicates on our data table. So, we need to create a security policy and add it with our predicate function. At the same time, we can use the predicate function as block predicate to restrict users for DDL operations as given below:
Use Demo
Go

---- Create Security policy
CREATE SECURITY POLICY Security.CustomerAccessPolicy
----- Filter Predicates to Customer Table
ADD FILTER PREDICATE Security.CustomerAccessPredicates(TerritoryId) on [dbo].[Customers],
----- Filter Predicates to Customer Table
ADD BLOCK PREDICATE Security.CustomerAccessPredicates(TerritoryId) on [dbo].[Customers]

Authenticated Users to the database
We need to create various users to verify the functionality of Row-Level Security as given below:
Use Demo
Go

----- Create Users
----- Territory=1: Create Users
CREATE USER RyanArjun WITHOUT LOGIN; 
----- Territory=2: Create Users
CREATE USER KimmyWang WITHOUT LOGIN; 

Pull Records after applying RLS feature
After creating the users for RLS, we can pull the data by authenticated them as given below:
Use Demo
Go

------RLS Territory=1: RyanArjun
EXECUTE AS USER = 'RyanArjun'; 
SELECT CustId, FirstName, LastName, PhoneNo, Email, TerritoryId
 FROM [dbo].[Customers];  
REVERT; 
CustId
FirstName
LastName
PhoneNo
Email
TerritoryId
1
Ryan
Arjun
555-123-4567
ryan.arjun@gmail.com
1

------RLS Territory=2: KimmyWang
EXECUTE AS USER = 'KimmyWang'; 
SELECT CustId, FirstName, LastName, PhoneNo, Email, TerritoryId
 FROM [dbo].[Customers];  
REVERT;
CustId
FirstName
LastName
PhoneNo
Email
TerritoryId
2
Kimmy
Wang
555-936-4568
kimmy,wang@yahoo.com.co
2
4
John
Ray
932-025-0523
john.ray@rediffmail.com
2

---- Pull values from Customers table with db_owner credentials
SELECT CustId, FirstName, LastName, PhoneNo, Email, TerritoryId
FROM [dbo].[Customers]
CustId
FirstName
LastName
PhoneNo
Email
TerritoryId
1
Ryan
Arjun
555-123-4567
ryan.arjun@gmail.com
1
2
Kimmy
Wang
555-936-4568
kimmy,wang@yahoo.com.co
2
3
Black
Smith
932-123-0569
black.smith@aol.com
3
4
John
Ray
932-025-0523
john.ray@rediffmail.com
2
If user wants to do other DDL operations then he can work around in his assigned territory area only and block predicate policy will not allow him to access other territories.

No comments:

Post a Comment

Popular Posts