SQL Server->> row-level Security

SQL Server CPT3 contains a new feature called Row level Security (RLS), which allows the database administrator to control the data rows that the client can access based on some of the characteristics of the client execution script according to the business needs, for example, We hope that the manager of the business department can only see the salary of the employees in his department. In the past, like to implement such a function, is to be implemented through the logical writing of the inner layer of the view. That was the way a project was done before. or by implementing it at the application layer, for example, by adding a WHERE clause condition to the query statement before committing the command to the database to filter the data. In this way, it is obvious that RLS is easier to implement row-level permission control.

So how does it come true? Combining security policy and inline functions

1) The SECURITY policy acts on the table, indicating the predicate type and the parameters of the predicate are passed in;

2) The definition of predicate is inside the inline function;

So you understand. Predicate's Chinese meaning is the predicate. People who understand the execution plan for SQL Server query statements are certainly not unfamiliar with the word. The representation of the filter criteria in the execution plan is the predicate (predicate). So, in fact, the implementation of RLS is through the table and one or more field properties as a reference (reference) condition, and the function of inline functions is simply encapsulated the predicate (predicate) definition. The definition itself is business-based, so there are developers who decide for themselves. But as a packaging container, or if someone needs to refer to it, it is security policy. So security policy becomes the link between the table and the predicate definition.

That is: Table <-SECURITY POLICY, Inline Function

RLS supports two types of predicate:1) FILTER predicate;2) BLOCK predicate. What is the difference between the two? The former is valid for data read operation, and the latter is effective for adding and deleting. The former filters out the data rows that violate the predicate, while the latter triggers an error when the data row that violates the predicate is added and deleted. In fact, filter predicate is effective for updates and deletions because the first step in updating and deleting is to read the data rows first.

What they each have in common is:

1) Other representations referenced in the inline function do not need to consider whether they have permission to view;

2) If the security policy is Stat=off, then the data row filter is invalid;

3) Even the dbo or db_owner will be affected by security policy;

4) because a schema binding is required to create an inline function for security policy, it triggers an error if the reference column is updated, but the other fields in the table do not, and the same modification of the inline function triggers the error;

5) A table can only have a predicate for a certain operation, such as can not define multiple before update block predicate;

For block predicate:

1) Although it is similar to trigger, it is actually different. Trigger records the field values of the row before and after the modification, and block predicate only knows the previous or subsequent values (depending on the before update or after update)

2) As long as the line that violates the predicate of the reference field is not able to operate;

3) for BULK insert also takes effect;

Speaking so much, the fact that RLS is equivalent to using a layer of where conditions secretly block out some data rows.

Microsoft's recommendations:

1) Create separate schemas for inline functions and security policy for RLS.

2) to create the secirity policy, you need to have alter any SECURITY policy permission.

3) Avoid data type conversion and result in inline function error.

4) Avoid too many table join queries in inline functions.

It is important to note that RLS can be applied to a view, but if a table with RLS is referenced by the view, it cannot create an indexed view.

RLS and Columnstore indexes, CDC, memory-optimized tables are compatible

A very typical example on MSDN is that the employee can only see his own salary, and the manager can see the salary of his subordinate employees.

The first step is to create the user and metadata first.

CREATE USERMartin without LOGIN;CREATE USERSara without LOGIN;CREATE USERAmy without LOGIN;CREATE TABLEdbo. Personnel (EmployeeIDINT, NameNVARCHAR( -), DepartmentNVARCHAR( -), PositionNVARCHAR( -), ReporttoINT, SalaryFLOAT);GOINSERT  intodbo. Personnel (EmployeeID, Name, Department, Position, Reportto, Salary)VALUES(1,'Martin','Accounting','Manager',NULL,10000);GOINSERT  intodbo. Personnel (EmployeeID, Name, Department, Position, Reportto, Salary)VALUES(2,'Sara','Accounting','Accountant',1, the);GOINSERT  intodbo. Personnel (EmployeeID, Name, Department, Position, Reportto, Salary)VALUES(3,'Amy','Accounting','Accountant',1, the);GO

creating schemas and inline functions

CREATE SCHEMASecurity;GOCREATE FUNCTIONSecurity.fn_securitypredicate (@Name  asSYSNAME)RETURNS TABLE withSCHEMABINDING as    RETURN SELECT 1  asFn_securitypredicate_resultWHERE @Name = user_name()OR user_name()= 'Martin';GO

Create Security Policy

CREATE SECURITY POLICY Secpol_personnelfilter ADD   on dbo. Personnelwith=on);

Permissions granted to three database users to access a table

GRANT SELECT   on  to [Amy] GRANT SELECT   on  to [Martin] GRANT SELECT   on  to [Sara]

Start testing how many rows of data a user Amy can access

EXECUTE  as USER = ' Amy ' ; SELECT user_name (),* from dbo. personnel; REVERT;

Results

Test user Martin

EXECUTE  as USER = ' Martin ' ; SELECT user_name (),* from dbo. personnel; REVERT;

Results

Test user Sara

EXECUTE  as USER = ' Sara ' ; SELECT user_name (),* from dbo. personnel; REVERT;

Results

RLS is indeed a useful feature of SQL Server 2016. But it also has some "shortcomings". In the above example, if you need to realize that the CEO can know the salary of the whole company, then the department manager can know the salary of the staff in the whole department, even more complicated. Then the inline function above becomes more complex, and you need to join some tables to determine the user's level of access. And we all know that adding a table reference to an inline function can easily cause performance problems. So it's not a good idea to do something like the one above. The real solution is to pass in the form of the user's security context as a variable of a string into the inline function, and then use the security context information inside the inline function to define the security control logic for the data access. This is provided in SQL Server 2016 with a session_context scalar function to implement this method. This approach is probably the best practices for future use of RLS in the real world after the release of SQL Server RTM.

Reference:

Row-level Security

ALTER SECURITY POLICY (Transact-SQL)
DROP SECURITY POLICY (Transact-SQL)
SYS.SECURITY_POLICIES (Transact-SQL)
SYS.SECURITY_PREDICATES (Transact-SQL)

SQL Server->> row-level Security