New Security Features In SQL SERVER 2016 – PART 1

Data Security in any application is vital. Different business or application has their own security policy. SQL Server 2016 provides three new security features which helps to protect data.

Below there are new security features added in SQL Server 2016.

• Row-Level Security (RLS)
• Dynamic Data Masking (DDM)
• Always Encrypted

In this part of blog, we will see use case and implementation of Row-Level Security feature.

Predicate based access control is possible using . We can control access to rows in a table based on user characteristics. For example, a manager can only access employee data who are reporting to him.

It supports two types of security predicate.

• Filter predicates: it is transparent to user. It is applied for all read operation like SELECT, DELETE, UPDATE
• Block Predicates: Explicitly block write operation if it violates the predicate. It affects all write operations like AFTER INSERT, AFTER UPDATE, BEFORE UPDATE and BEFORE DELETE.

We can implement RLS using Security policy and inline table valued function. Let’s put things into action and impalement RLS where a manager can only see employee data whoever reporting to him.

First create 3 users where 2 are having manager role and one having Senior manager role.Manager can only see data for its employee and Senior manager see all employee’s data.

--Create users
CREATE USER SeniorManager WITHOUT LOGIN;  
CREATE USER Manager1 WITHOUT LOGIN;  
CREATE USER Manager2 WITHOUT LOGIN;  

--Create a table and inserts some records
CREATE TABLE Employees  
    (  
    Empid int,  
    EmpName varchar(10),  
    ManagerName sysname,  
    );

INSERT Employees VALUES   
(700, 'Emp_A', 'Manager1'),   
(701, 'Emp_B', 'Manager1'),   
(702, 'Emp_C', 'Manager2'),  
(703, 'Emp_D', 'Manager1'),   
(704, 'Emp_E', 'Manager2'),   
(705, 'Emp_F', 'Manager2'),
(706, 'Emp_G', 'Manager2'),   
(707, 'Emp_H', 'Manager1'),   
(708, 'Emp_I', 'Manager2'),  
(709, 'Emp_J', 'Manager1'),   
(710, 'Emp_K', 'Manager2'),   
(711, 'Emp_M', 'Manager2');
GO
--Grant all 3 users to read data from Employees table

GRANT SELECT ON Employees TO SeniorManager;  
GRANT SELECT ON Employees TO Manager1;  
GRANT SELECT ON Employees TO Manager2;

GO
--It is recommended to create a separate schema for the predicate function and security policy
 CREATE SCHEMA RLS;  
 GO  

--Create predicate function with schema binding. If function is not schema binding and your query is join with other table or calling any function, then the user will need select or execute permission on these additional tables and functions.
CREATE FUNCTION RLS.EmployeeSecurityFilter(@ManagerName AS sysname)  
 RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS EmployeeSecurityFilter_result   
WHERE @ManagerName = USER_NAME() OR USER_NAME() = 'SeniorManager';

--Create a security policy using the predicate function 

CREATE SECURITY POLICY EmployeeCheck
ADD FILTER PREDICATE RLS.EmployeeSecurityFilter(ManagerName)   
ON dbo.Employees  
WITH (STATE = ON);

-- run below query
EXECUTE AS USER = 'Manager1';  
SELECT * FROM Employees;   
REVERT;  

When we run the query against Employees as user Manager1, though we execute the query without any filter, SQL server finds there is security policy EmployeeCheck defined on table Employees so it applies the predicate filter check and will show only those employee rows who are reporting to manager1. Manager1 will not realize any filter is applied and can only see data applicable.

Now run the same query as user Manager2. When you execute the same query by user Manager2, it applies the filter predicate value as Manager2.

EXECUTE AS USER = 'Manager2';  
SELECT * FROM Employees;   
REVERT;  

If you see the filter predicate function checks if the user is SeniorManager it will return 1 for all rows resulting showing all rows when SeniorManager reads employees table.

EXECUTE AS USER = 'SeniorManager';  
SELECT * FROM Employees;   
REVERT;

We can disable the security policy on Empolyees table using below query.

ALTER SECURITY POLICY EmployeeCheck WITH (STATE = OFF);

Once you set the state of Security policy to off and user manager1 or manager2 run the same query, it will not apply the filter. User can see all rows.

However, only 8 are recognised by WHO as innovative https://antibioticspro.com which can improve the effectiveness of antimicrobial therapy.