SQL Server 2016 new features Row-level security (worth paying attention) _mssql

SQL Server 2016 has a new feature called Row-level Security, which probably means the line version of the safety policy (originally I am an English slag _ (: З"∠) _)

Directly on the example. This function is rather to add a function to the table as a filtering rule, so that users with different conditions (or logins), and so on, can only get the data that meets the criteria. In contrast, it provides a bit of convenience, of course, increases the security of the data, equivalent to each user connected to see only

Data that conforms to the rules (of course, the user here is just an example.) Can actually be done by writing a filter function.

As an example

There are three users Sales1, Sales 2, Manager 3 database users, and then use a Sales table to register their order records

CREATE TABLE Sales 
( 
OrderID int, 
salesrep sysname, 
Product varchar (), 
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

Here's a screenshot of the data for all the tables.

Then add 3 users, respectively, to the Sales1 Sales2 Manager 3 users, respectively, their permissions to the sales table for queries and deletions (for testing the deletion feature).

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 the Sales to Sales1; 

Then the following is a central part of this function. First we create a filter function, then add this filter function to the table's security policy, we can see the effect.

The function logic is simple, passing in a @salesrep name to match the current user_name. Matches before returning data

The following line then creates a security policy and uses function Fn_securitypredicate as a filter for the table in the sales table. The parameters passed into the function are selected as the SalesRep field (that is, the Sales rep field that we built the table)

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 

And then we look at the results of the query

EXECUTE as USER = ' Sales1 '; 
SELECT * from Sales; 
REVERT; 
EXECUTE as USER = ' Sales2 '; 
SELECT * from Sales; 
REVERT; 
EXECUTE as USER = ' Manager '; 
SELECT * from Sales; 

The effect is this, of course, if you want to set different filter conditions, set different fields, it can be through the function of the above code and security policy to set. According to this filter, if the logged-on user is not Sales1 Sales2 Manager 3 One of them, nothing can be queried.

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

The appearance of RLS is also able to help us to reduce the implementation of a certain function ~

PS: Below look at SQL Server 2016 new feature features

1, Polybase: More simple and efficient management of relational and non-relational T-SQL data;

2, AlwaysOn Enhancements: Achieve high availability and performance second, including up to 3 synchronous replication, DTC support and secondary round-robin load balancing;

3, Row level security (Layer safety control): so that customers based on user characteristics control data access, functionality has been built into the data, no need to modify the application;

4, Dynamic Data masking: To help protect unencrypted data;

5, native JSON support: to achieve easy parsing and storage, as well as the output of relational data;

6, Temporal database support: can track historical data changes;

7, Data History query so that the DBA can accurately locate;

8, MDS Enhancement work: Provides the server management ability of the main data server;

9, Enhanced Azure Hybrid backup feature: Faster backup and recovery in azure virtual machines.

Important improvements:

1. Always encrypt (Always Encrypted)

The storage and use of data will be encrypted using technology developed by Microsoft Research.

2. Flexible database (Stretch)

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

3. Real-time business analysis and online transaction processing in memory (real-time operational Analytics & In-memory OLTP)

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

4, built-in advanced analysis (built-in Advanced Analytics), polybase and mobile BI

In SQL Server 2016, technologies such as data analysis and polybase are easier to use. In addition, mobile BI will be used for graphical display on mobile devices.

The above is a small set to introduce the SQL Server 2016 new features of the Row-level security (pay attention), I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!