Row-level security

Row-level security (RLS)
It is used to control access to rows.
RLS is made up of 3 elements:
1. Predicate function used to setup the logic of access control
2. Security predicate used to decide action of control (Filter or Block)
3. Security policy is a container for a set of security predicate.

Prepare for example
We also create a database and table for example

CREATE DATABASE Computer;
USE Computer;
CREATE TABLE Result (
ID INT PRIMARY KEY,
Profile INT,
Provider NVARCHAR(256),
Takeaway NVARCHAR(256)
);

-- INSERT SOME DATA
INSERT INTO Result(ID, Profile, Provider, Takeaway) VALUES(100,1000,'Slave','Manager')
INSERT INTO Result(ID, Profile, Provider, Takeaway) VALUES(101,1001,'Slave','Boss')
INSERT INTO Result(ID, Profile, Provider, Takeaway) VALUES(102,1002,'Slave','Boss')
INSERT INTO Result(ID, Profile, Provider, Takeaway) VALUES(103,1003,'Manager','Manager')
INSERT INTO Result(ID, Profile, Provider, Takeaway) VALUES(104,1004,'Manager','Boss')
INSERT INTO Result(ID, Profile, Provider, Takeaway) VALUES(105,1005,'Manager','Boss')
INSERT INTO Result(ID, Profile, Provider, Takeaway) VALUES(106,1006,'Boss','Boss')

-- CREATE three Users: Boss, Manager and Slave
CREATE USER Boss WITHOUT LOGIN;
CREATE USER Manager WITHOUT LOGIN;
CREATE USER Slave WITHOUT LOGIN;

-- ASSIGN USERS some permission
GRANT SELECT, UPDATE ON Result to Slave,Manager,Boss

Predicate function
Microsoft recommend create a separate schema for predicate function.

CREATE SCHEMA RLSEXAMPLE;

Within predicate function is the logic of our access control.
In the following, we will create a predicatre function named “AccessPredicate”
it depend on the value of “Provider” column.
User can view,update the row which is provided by themself.

-- @USERNAME reference to dbo.Reuslt's "Provider NVARCHAR(256)"
CREATE FUNCTION RLSEXAMPLE.AccessPredicate(@USERNAME sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS Access
FROM dbo.Result
WHERE @USERNAME = USER_NAME();

Create Security Policy

CREATE SECURITY POLICY RLSEXAMPLE.SecurityPolicy
ADD FILTER PREDICATE RLSEXAMPLE.AccessPredicate(Provider) ON dbo.Result,
ADD BLOCK PREDICATE RLSEXAMPLE.AccessPredicate(Provider) ON dbo.Result AFTER UPDATE;

How to test the policy
We can use “EXECUTE AS USER = ‘<USER’s name>’ to test the policy
Since we need to test the policy as this order. (Boss > Manager > Slave)
We need to grant permission to Boss to mimic Manager and Manager to mimic Slave.
If not, we need to use separate sessions to test the policy.

GRANT IMPERSONATE ON USER::Manager TO Boss;
GRANT IMPERSONATE ON USER::Slave TO Manager;

-- Run as "Boss", it will return 1 row
EXECUTE AS USER = 'Boss';
SELECT * FROM Result;
-- We try to update the Slave's record which is unable to access by Boss
UPDATE Result SET Profile = 2000 WHERE ID = 100
-- We try to update the Boss's record
UPDATE Result SET Profile = 2006 WHERE ID = 106
SELECT * FROM Result;

-- Run as "Manager", it will return 3 row
EXECUTE AS USER = 'Manager';
SELECT * FROM Result;

-- Run as "Slave", it will return 3 row
EXECUTE AS USER = 'Slave';
SELECT * FROM Result;

Joe's choice

Row-level security

Author: Joe Chan

Search Me

Recent Posts

Author: Joe Chan

Search Me

Recent Posts

Tag Cloud