Row-Level Security (worth noting) of SQL Server 2016, 2016row-level

Row-Level Security (worth noting) of SQL Server 2016, 2016row-level

SQL Server 2016 has a new function called Row-Level Security, which generally refers to the Security policy of the Row version (I used to be an English scum _ (: Dirty "dirty )_)

Example. This function adds a function to a table as a filter rule so that users (or login names) with different conditions can only obtain qualified data. It provides a little convenience. Of course, it also increases the data security, which is equivalent to that every user can only see

Data that meets the rules (of course, the user here is just an example. In fact, you can write a filter function)

For example

There are three database users Sales1, Sales 2, and Manager, and then a Sales table is used to store their order records.

CREATE TABLE Sales ( OrderID int, SalesRep sysname, Product varchar(10), Qty int ); INSERT Sales VALUES (1, 'Sales1', 'Valve', 5), (2, 'Sales1', 'Wheel', 2), (3, 'Sales1', 'Valve', 4), (4, 'Sales2', 'Bracket', 2), (5, 'Sales2', 'Wheel', 5), (6, 'Sales2', 'Seat', 5); -- View the 6 rows in the table SELECT * FROM Sales; go

This is the data of all tables.

Then, add three users, namely Sales1 Sales2 Manager, and grant them the query and deletion permissions for the Sales table (used to test the deletion function.

CREATE USER Manager WITHOUT LOGIN; CREATE USER Sales1 WITHOUT LOGIN; CREATE USER Sales2 WITHOUT LOGIN;GRANT SELECT,delete ON Sales TO Manager; GRANT SELECT,delete ON Sales TO Sales1; GRANT SELECT,delete ON Sales TO Sales2; 

The following is the core part of this function. First, create a filter function and add the filter function to the security policy of the table to see the effect.

The function logic is very simple. Input a @ SalesRep name to match the current User_Name. Data is returned only after matching.

The following row creates a security policy and uses the fn_securitypredicate function in the Sales table as the table filter. The input function parameter is selected as the SalesRep field (that is, the sales representative field of the table we created)

CREATE FUNCTION fn_securitypredicate(@SalesRep AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager'; CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE dbo.fn_securitypredicate(SalesRep) ON dbo.Sales WITH (STATE = ON); 

Then let's take a look at the query results.

EXECUTE AS USER = 'Sales1'; SELECT * FROM Sales; REVERT; EXECUTE AS USER = 'Sales2'; SELECT * FROM Sales; REVERT; EXECUTE AS USER = 'Manager'; SELECT * FROM Sales; REVERT; 

This is the effect. Of course, if you want to set different filtering conditions and set different fields, you can set them through the code and security policies in the above functions. According to this filtering condition, if the login user is not one of Sales1 Sales2 Manager 3, nothing can be queried.

In addition, you cannot delete filtered data. For example, Sales1 cannot delete or modify data with OrderID = 3.

The emergence of recursive can also help us reduce the implementation of certain functions above ~

PS: The following describes the new features of SQL Server 2016.

1. PolyBase: more simple and efficient management of relational and non-relational T-SQL data;

2. AlwaysOn enhancement: the secondary feature is high availability and performance, including up to three synchronous replication, DTC support, and secondary Round-Robin load balancing;

3. Row Level Security (hierarchical Security control): enables customers to control data access based on user characteristics. The function has been built into the data and does not need to be modified;

4. Dynamic Data Masking: helps protect unencrypted Data;

5. Native JSON support: Easy parsing, storage, and relational data output;

6. Temporal Database Support: tracking historical data changes;

7. query the data history so that DBAs can locate the data accurately;

8. MDS enhancement: Provides the server management capability of the master data server;

9. Enhanced Azure hybrid backup: Faster backup and recovery can be achieved in Azure virtual machines.

Important improvements:

1. Always encrypt (Always Encrypted)

Data storage and use will be encrypted using technologies developed by Microsoft Research institutes;

2. Elastic Database (Stretch Database)

SQL Server users will be able to dynamically extend data to Microsoft Azure to enhance their performance;

3. Real-time business analysis and In-Memory central Transaction Processing (Real-time Operational Analytics & In-Memory OLTP)

This analysis technology was first introduced in 2014 and has now become more advanced (with higher real-time performance );

4. built-in Advanced analysis (Built-in Advanced Analytics), PolyBase, and Mobile BI

On SQL Server 2016, technologies such as data analysis and PolyBase are easier to use. In addition, Mobile BI will be used to display images on Mobile devices.

The above is the Row-Level Security (which is worth noting) New Function of SQL Server 2016 introduced by the editor. I hope it will help you. If you have any questions, please leave a message for me, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!