SQL Server 2016 row-level permission control

Original: SQL Server 2016 row-level permission control

Background

If we have key data stored in a table, such as the staff table contains employee, department and salary information. Only allow users to access information from their respective departments, but they cannot access other departments. Generally we are in the terminal implementation of this function, and after sqlserver2016 can also be directly on the database side to implement this function.

Solve

Security is already a core data issue, each generation of MS Database has new security features, then in SQL Server 2016, there are many upgrades in this area, such as ' Row level Security ', ' Always Encrypted ', ' Dynamic data masking ', and ' enhancement of Transparent Data encryption ', and so on, will play a security role. In this article, I will describe the row level security (rls--row Class), which controls access to rows in a table. RLS enables us to control the underlying data based on the properties of the queried person, helping us to easily make transparent access to the data for different users. Row-level security enables customers to control rows in a database based on the characteristics of the user who executes the query.

In order to implement RLS we need to prepare for the following three areas:

1. predicate functions
2. Security predicates
3. Security Policy

Describe each of the above three aspects individually

predicate functions

A predicate function is a built-in table-valued function that checks whether a user executes a query that accesses data based on its logical definition. This function returns one to indicate that the user can access it.

Security predicates

The security predicate is the binding of the predicate function to the table, and RLS provides two security predicates: the filter predicate and the BLOCK predicate. Filtering predicates are simply filtering data when querying data using Select, UPDATE, and DELETE statements without error. The BLOCK predicate is a display error when using data that violates the predicate logic and prevents the user from using after inserts, after update, before update, before DELETE, and so on.

Security Policy

Security policy objects are created specifically for row-level security, grouping all security predicates that involve predicate functions.

Instance

Example we create a person table and test data, and finally we let the user access the information of their department, the code is as follows:

Create Tabledbo. Person (PersonIdINT IDENTITY(1,1), PersonNamevarchar( -), Departmentvarchar( -), SalaryINT, User_accessvarchar( -))GOINSERT  intoPerson (PersonName, Department, Salary, user_access)SELECT 'Ankit','CS',40000,'User_cs'UNION  AllSELECT 'Sachin','EC',20000,'User_ec'UNION  AllSELECT 'Kapil','CS',30000,'User_cs'UNION  AllSELECT 'Ishant','IT',50000,'User_it'UNION  AllSELECT 'Aditya','EC',45000,'User_ec'UNION  AllSELECT 'Sunny','IT',60000,'User_it'UNION  AllSELECT 'Rohit','CS',55000,'User_cs'GO

Now that the table has been created and the test data is inserted, execute the following statement to retrieve the records that are there:

SELECT * FROM person

As shown, there are currently three Departments department (Cs,ec,it), and the user_access column represents the respective user groups. Let's create an account statement for three test user data as follows:

-- For CS Department CREATE USER User_cs without LOGIN -- For EC Department CREATE USER User_ec without LOGIN -- For IT Department CREATE USER User_it without LOGIN

After creating the user group, grant the Read permission to the new user above, and execute the following statement:

-- -Grant SELECT permission to all users GRANT SELECT  on  to User_cs GRANT SELECT  on  to User_ec GRANT SELECT  on  to User_it

Now we create a predicate function that is not visible to the query user.

----create functionCREATE FUNCTIONdbo. Personpredicate (@User_Access  as varchar( -) )RETURNS TABLE withSCHEMABINDING asRETURN SELECT 1  asAccessrightWHERE @User_Access = user_name()GO

This function returns only rows, and if the name of the user executing the query matches the user_access column, then the user allows access to the specified row. After you create the function, you also need to create a security policy that uses the predicate function personpredicate above to filter the logical binding of the table, with the following script:

-- Security Policy CREATE SECURITY POLICY Personsecuritypolicy ADD  on dbo. person with=on)

State (status) is on to be policy effective, and if you plan to close the policy, you can change the status to OFF.

Let's take a look at the query results:

This query does not return any rows, which means that after the definition of the predicate function and the creation of the policy, the user query needs to have the appropriate permissions to return the row, and then use a different user to query the data, first, we use the user User_cs to query the results:

EXECUTE  as USER = ' User_cs ' SELECT *  from dbo. Personrevert

As shown, we see only three rows of data data that the user, User_cs, has retrieved. Therefore, the filtering function filters out other data that is not part of the user group.

In fact, the process of executing this query is to invoke the predicate function inside the database as follows:

SELECT * FROM dbo. Person

WHERE user_name () = ' User_cs '

The query results for the other two groups of users are similar and are not demonstrated here.

Therefore, we can see that the execution query gets the specified data that belongs to the specified user group according to the different use. That's what we want to achieve.

So far, we've demonstrated the filtering predicates, and we'll show you how to block predicates. Execute the following statement to authorize the DML operation permissions to the user.

-- granting DML Permissions GRANT INSERT UPDATE DELETE  on  to User_cs GRANT INSERT UPDATE DELETE  on  to User_ec GRANT INSERT UPDATE DELETE  on  to User_it

We execute the INSERT statement with user User_it, and insert the user group as Usercs, with the following statement:

EXECUTE  as USER = ' User_it ' INSERT  into Person (PersonName, Department, Salary, user_access) SELECT ' Soniya ' ' CS ' 35000 ' User_cs ' REVERT

But, unexpectedly no error, inserted successfully.

Let's check the situation of user data insertion:

EXECUTE  as USER = ' User_it ' SELECT *  from dbo. Personrevert

Strangely, the new insert row was not inserted into the user group ' User_it '. Instead, it appears in the user group data of ' User_cs ' .

-- insert data appears in different groups of users EXECUTE  as USER = ' User_cs ' SELECT *  from dbo. Personrevert

The above example shows that the filter predicate does not prevent the user from inserting data, so there is no error because the blocking predicate is not defined in the security policy. Let's join the BLOCK predicate to show the error, there are four blocking predicates after the INSERT, after update, before update, and before delete can be used. We test here using the after INSERT predicate. This verb prevents users from inserting records into a group of data users who do not have permission to view them.

Add the security policy that the predicate blocks, with the following code:

-- To add a blocking verb ALTER SECURITY POLICY Personsecuritypolicy ADD BLOCK predicate dbo. Personpredicate (user_access)onINSERT

Now let's try again with a similar code, whether we can insert the data:

EXECUTE  as USER = ' User_cs ' INSERT  into Person (PersonName, Department, Salary, user_access) SELECT ' Sumit ' ' IT ' 35000 ' User_it ' REVERT

Wipe, sure enough this time the error appears, blocking the user's insertion of different permissions. So we can say that by adding a blocking predicate, the DML operations of an unauthorized user are limited.

Note: In the example, there is only one user in each department. If a department contains multiple users, we need to create a branch login to assign the required permissions to each user because the predicate function is applied to the user base and the security policy depends on the predicate function.

line-level security restrictions

Here are a few line-level security restrictions:

1. predicate functions must be prefixed with the WITH schemabinding keyword, which throws an exception if the function does not have the keyword to bind the security policy.
2. You cannot create an indexed view on a table that has row-level security enforced.
3. Memory data table not supported
4. Full-text indexing does not support

Summarize

SQLServer2016 with row-level security, we can implement permission control for data logging without application-level code modifications. Row-level security by using predicate functions and security policy implementations, you do not need to modify the various DML codes to be implemented with existing code.

SQL Server 2016 row-level permission control