SQL with Manoj

In my [previous post] I discussed about Row Level Security, its introduction, and a detailed demo on how to implement it.

There I discussed about the Filter Predicate option available in the CTP 2.x release. With this option we can restrict read access for a user to avoid unauthorized access of rows.

Filter Predicates filters the read operations like SELECT, UPDATE and DELETE, but do not filter INSERT operations, and thus a user can INSERT any row (associate to another user also).
 

Here in this post I will talk about the new Block Predicate option available in the CTP 3.0 release. With this option we can restrict write access for specific users.

Block Predicates block all write operations like:

– AFTER INSERT and AFTER UPDATE

– BEFORE UPDATE

– and BEFORE DELETE

To know more about these write operations check MS BoL here.
 

I’m using the same script I used in my [previous post] about RLS with Filter Predicates.

–> Step #1. Create some test accounts and test data:

-- Let's create some test accounts:

-- Three users for The CEO, HR & Finance department admin users.
CREATE USER userCEO WITHOUT LOGIN;
GO
CREATE USER userHR WITHOUT LOGIN;
GO
CREATE USER userFin WITHOUT LOGIN;
GO
CREATE USER userAdmin WITHOUT LOGIN;
GO

-- Create a sample table [dbo].[Employee]: with a self-referencing Manager ID column.
CREATE TABLE dbo.Employee (
	[EmpCode] NVARCHAR(50),  -- Employee ID
	[EmpName] NVARCHAR(250), -- Employee/Manager Full Name
	[Salary]  MONEY,		 -- Fictious Salary
	[MgrCode] NVARCHAR(50)   -- Manager ID
);
GO

-- Now insert some test records:

-- Top Boss CEO
INSERT INTO dbo.Employee VALUES ('userCEO' , 'CEO Top Boss'  , 800, NULL)

-- Next 2 levels under CEO
INSERT INTO dbo.Employee VALUES ('userHR'  , 'HR User'		 , 700, 'userCEO');
INSERT INTO dbo.Employee VALUES ('userFin' , 'Finance User'  , 600, 'userCEO');

-- Employees under Kevin
INSERT INTO dbo.Employee VALUES ('manojp'  , 'Manoj Pandey'  , 100, 'userHR');
INSERT INTO dbo.Employee VALUES ('saurabhs', 'Saurabh Sharma', 400, 'userHR');
INSERT INTO dbo.Employee VALUES ('deepakb' , 'Deepak Biswal' , 500, 'userHR');

-- Employees under Amy
INSERT INTO dbo.Employee VALUES ('keshavk' , 'Keshav K'		 , 200, 'userFin');
INSERT INTO dbo.Employee VALUES ('viveks'  , 'Vivek S'		 , 300, 'userFin');
GO

-- Let's check the records before applying "Row Level Security":
SELECT * FROM dbo.Employee; -- 8 rows
GO

Output: As a normal SEELCT and without RLS, it just ignores my Execution Context and execute the Query and return all the 8 rows.
 

–> Step #2. Grant only SELECT to the CEO-User, and Grant SELECT, INSERT,UPDATE, DELETE access on the dbo.Employee table to all 3 users:

GRANT SELECT ON dbo.Employee TO userCEO;
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Employee TO userHR;
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Employee TO userFin;
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Employee TO userAdmin;
GO

The new “Row Level Security” feature lets you:

– apply this security at the database level

– create two predicate functions, Filter & Block Predicates.

– and there is no need to apply the WHERE clause to filter out unauthorized users.
 

–> Step #3a. Create an Inline Table-Valued Function to create a Filter Predicate function:

CREATE FUNCTION dbo.fn_SecurityFilterPredicateEmployee (@mgrCode AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_SecurityPredicateEmployee_result 
	-- Predicate logic
	WHERE @mgrCode = USER_NAME() 
	OR USER_NAME() IN ('userCEO', 'userAdmin'); -- the CEO and Admin should see all rows
GO

–> Step #3b. Create an Inline Table-Valued Function to create a Block Predicate function:

CREATE FUNCTION dbo.fn_SecurityBlockPredicateEmployee (@mgrCode AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_SecurityPredicateEmployee_result 
	-- Predicate logic
	WHERE @mgrCode = USER_NAME() 
	OR USER_NAME() = 'userAdmin'; -- the Admin should have SELECT, INSERT, UPDATE, DELETE access to all rows
GO

Both the functions returns value 1 when a row in the MgrCode (Manager ID) column is the same as the user executing the query (@@mgrCode = USER_NAME()) or if the user executing the query is the Top Boss user USER_NAME() IN (‘userCEO’, ‘userAdmin’) and (USER_NAME() = ‘userCEO’) respectively.
 

–> Step #4. Create a security policy adding both the functions for Filter & Block predicates:

CREATE SECURITY POLICY ManagerFilter
ADD FILTER PREDICATE 
	dbo.fn_SecurityFilterPredicateEmployee(MgrCode) ON dbo.Employee, 
ADD BLOCK PREDICATE 
	dbo.fn_SecurityBlockPredicateEmployee(MgrCode) ON dbo.Employee 
WITH (STATE = ON); -- The state must be set to ON to enable the policy.
GO

-- Now let's again check the records after applying "Row Level Security":
SELECT * FROM dbo.Employee; -- 0 rows, because my used does not have any access.
GO

Finally with the above 4 steps we’ve configured RLS with both Filter & Block predicates, to restrict unauthorized access and modification of data.
 

Note: to check and validate RLS with Filter Predicate while retrieving data (or SELECT) please refer my [previous post].
 

–> Now, let’s check and validate RLS with Block Predicate while Updating data (INSERT, UPDATE, DELETE):

Usage #1. First we will check our top boss ‘userCEO’ account which has just read/SELECT access:

EXECUTE AS USER = 'userCEO';
	SELECT * FROM dbo.Employee; -- 8 rows

-- 1. Insert:
	INSERT INTO dbo.Employee (EmpCode, EmpName, Salary, MgrCode) 
	VALUES ('akashm' , 'Akash M'  , 550, 'userCEO') -- error
/*
Msg 229, Level 14, State 5, Line 106
The INSERT permission was denied on the object 'Employee', database 'TestManDB', schema 'dbo'.
*/

-- 2. Update:
	UPDATE [dbo].[Employee] SET Salary = 900 WHERE EmpCode = 'akashm' -- error
/*
Msg 229, Level 14, State 5, Line 111
The UPDATE permission was denied on the object 'Employee', database 'TestManDB', schema 'dbo'.
*/

-- 3. Delete:
	DELETE FROM [dbo].[Employee] WHERE EmpCode = 'akashm' -- error
/*
Msg 229, Level 14, State 5, Line 118
The DELETE permission was denied on the object 'Employee', database 'TestManDB', schema 'dbo'.
*/
REVERT;
GO

As you can see above for ‘userCEO’ account all INSERT/UPDATE/DELETE operations failed on the Employee table, as it only has read/SELECT access.
nbsp;

Usage #2. Now let’s check the ‘userHR’ account which as all permissions on his data:

EXECUTE AS USER = 'userHR';
	SELECT * FROM dbo.Employee; -- 3 rows

-- 1. Insert:
	INSERT INTO dbo.Employee (EmpCode, EmpName, Salary, MgrCode) 
	VALUES ('akashm' , 'Akash M'  , 550, 'userHR')
	SELECT * FROM dbo.Employee; -- 4 rows

-- 2: Update:
	UPDATE [dbo].[Employee] SET Salary = 900 WHERE EmpCode = 'akashm'
	SELECT * FROM dbo.Employee; -- 4 rows, with Salary = 900

-- 3: Delete:	
	DELETE FROM [dbo].[Employee] WHERE EmpCode = 'akashm'
	SELECT * FROM dbo.Employee; -- 3 rows

-- 4. Update another user's row:
	UPDATE [dbo].[Employee] SET Salary = 1000 WHERE EmpCode = 'viveks'
	-- (0 row(s) affected)
REVERT;
GO

So, the ‘userHR’ account can modify his records in all 3 INSERT/UPDATE/DELETE operations. But the 4th UPDATE operation fails where he tries to update another user (‘userFin’) record. However this does not displays any error message, but the query output message clearly shows no records were updated.
 

Usage #3. Execute as our Admin who can insert record for any User:

EXECUTE AS USER = 'userAdmin';
	SELECT * FROM dbo.Employee; -- 8 rows

-- 1. Insert row for another user HR:
	INSERT INTO dbo.Employee (EmpCode, EmpName, Salary, MgrCode) 
	VALUES ('akashm' , 'Akash M'  , 550, 'userHR')
	SELECT * FROM dbo.Employee; -- 9 rows

-- 2. Insert row for another user Fin:
	INSERT INTO dbo.Employee (EmpCode, EmpName, Salary, MgrCode) 
	VALUES ('shantis' , 'Shanti S'  , 200, 'userFin')
	SELECT * FROM dbo.Employee; -- 10 rows

-- 3. Update other user's rows (HR & Fin):
	UPDATE [dbo].[Employee] SET Salary = 900 WHERE EmpCode IN ('akashm', 'shantis')
	SELECT * FROM dbo.Employee; -- 10 rows total, last 2 rows with Salary = 900

-- 4. Delete other user's rows (HR & Fin)::
	DELETE FROM [dbo].[Employee] WHERE EmpCode IN ('akashm', 'shantis')
	SELECT * FROM dbo.Employee; -- 8 rows
REVERT;
GO

And here you can see the ‘userAdmin’ account is able to update all rows belonging to other users as well, as it is configured to behave like that, and this logic is built into the Security function that we build above in Step 3.b, where the Predicate logic looks like this:

    -- Predicate logic
    WHERE @mgrCode = USER_NAME() 
    OR USER_NAME() = 'userAdmin';

–> Thus, by using Row Level Security feature in SQL Server 2016 you can build your own customized Security feature for Read & Write operations for Tables by creating Inline Table-Valued Functions with Filter & Block predicates which are linked to the Security Policy for respective tables.
 

–> Final Cleanup

USE [TestManDB]
GO
DROP SECURITY POLICY [dbo].[ManagerFilter]
GO
DROP FUNCTION [dbo].[fn_SecurityFilterPredicateEmployee]
GO
DROP FUNCTION [dbo].[fn_SecurityBlockPredicateEmployee]
GO
DROP TABLE [dbo].[Employee]
GO

Check the same demo on YouTube:

Check & Like my FB Page