Row Level Security (RLS) on SSMS 2016

Today we are going to apply RLS security in SSMS 2016. Lower version of SSMS would not support RLS security.

To implement RLS we have under gone 4 steps given below.

Step 1: Schema Creation

We have to Create a separate Schema on database for this implementation.

Step 2: Table Creation

We have to create table, on which we are going to create RLS. Here we have created a table named 'ManagerHierarchy'. Table structure given below.

Step 3: Create predicate function:

We have to create a predicate function inside the schema.

IF EXISTS (SELECT * FROM sys.objects WHERE  object_id = OBJECT_ID

(N'[Security].fn_SecurityPredicateManagerHierarchy')

AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [Security].fn_SecurityPredicateManagerHierarchy

GO

CREATE FUNCTION [Security].fn_SecurityPredicateManagerHierarchy (

@EmpName AS SYSNAME,@Level1 AS SYSNAME,

@Level2 AS SYSNAME,@Level3 AS SYSNAME,@Level4 AS SYSNAME

)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN SELECT 1 AS SecurityPredicateManagerHierarchy_Result

FROM [dbo].[ManagerHierarchy]

WHERE @EmpName       =USER_NAME()

OR @Level1           = USER_NAME()

OR @Level2           = USER_NAME()

OR @Level3           = USER_NAME()

OR @Level4           = USER_NAME()

Step 4: Create security policy:

This is the last step to create RLS. Here we have to create a policy for RLS. This is new addition in SSMS 2016. Here we apply filter on ManagerHierarchy table. To activate the RLS we have to make 'STATE' option 'ON'.

Along with FILTER, we can restrict Insert or update data by unauthorized user through BLOCK PREDICATE. (It is required on OLTP Database)

CREATE SECURITY POLICY [Security].ManagerHierarchyFilter

ADD FILTER PREDICATE

[Security].fn_SecurityPredicateManagerHierarchy(EmpName,Level1,Level2,Level3,Level4)

ON [dbo].[ManagerHierarchy] WITH (STATE = ON)

GO

Now we have to test the RLS.

Create Sample Users without login and Grant Permission to them for this table

CREATE USER Subhadip WITHOUT LOGIN;

GO

CREATE USER Pritam WITHOUT LOGIN;

go

CREATE USER tanmoy WITHOUT LOGIN;

go

CREATE USER Sourav WITHOUT LOGIN;

GO

CREATE USER Titas WITHOUT LOGIN;

go

CREATE USER Anurag WITHOUT LOGIN;

go

GRANT SELECT ON [dbo].[ManagerHierarchy] TO Subhadip;

GO

GRANT SELECT ON [dbo].[ManagerHierarchy] TO Pritam;

GO

GRANT SELECT ON [dbo].[ManagerHierarchy] TO tanmoy;

GO

GRANT SELECT ON [dbo].[ManagerHierarchy] TO Sourav;

GO

GRANT SELECT ON [dbo].[ManagerHierarchy] TO Titas;

GO

GRANT SELECT ON [dbo].[ManagerHierarchy] TO Anurag;

GO

Test Cases:

EXECUTE AS USER = 'Subhadip';

SELECT * FROM [dbo].[ManagerHierarchy];

REVERT;

GO

EXECUTE AS USER = 'Pritam';

SELECT * FROM [dbo].[ManagerHierarchy];

REVERT;

GO

EXECUTE AS USER = 'tanmoy';

SELECT * FROM [dbo].[ManagerHierarchy];

REVERT;

GO

EXECUTE AS USER = 'Sourav';

SELECT * FROM [dbo].[ManagerHierarchy];

REVERT;

GO

EXECUTE AS USER = 'Anurag';

SELECT * FROM [dbo].[ManagerHierarchy];

REVERT;

GO

EXECUTE AS USER = 'tanmoy';

SELECT * FROM [dbo].[ManagerHierarchy];

REVERT;

GO