In a previous tip (SQL Server 2016 Row Level Security Example) we have seen how Row Level Security works and its practical implications.In this tip we will see some additional enhancements made in SQL 2016 CTP 3.1 and explore these new features.As we have previously seen, Row Level Security presents FILTER predicates to restrict the access of data only to authorized personnel. insert, update or delete) on the table itself we could not restrict the user from modifying data.
In this tip we are going to create a sample table with data and code to demonstrate the SESSION_CONTEXT function which is introduced with CTP 3.1. Customer AFTER INSERT GO Execute as user ='APPS' EXEC sp_set_session_context N'Emp ID', 1 Update Customer set Status='active' where name='Mark' -- Output 1 Row(s) Updated Update Customer set Status='Inactive' where name='Dirk' --Output 0 Row(s) updated In the first case the data is updated.
In SQL Server 2016 we can store multiple key and value pairs which are accessible throughout the session. In the second case, the update cannot be completed. The security policy will not permit deleting data for another Emp ID.
The key and value pairs can be set by the sp_set_session_context system stored procedure and these set values can be retrieved one at a time by using the SESSION_CONTEXT function. CREATE TABLE CUSTOMER ( Customerid int identity(1,1) primary key, Name nvarchar(64), city nvarchar(20), Status nvarchar(64), Emp ID int DEFAULT CAST(SESSION_CONTEXT(N'Emp ID') AS int) -- This will automatically set Emp ID to the value in SESSION_CONTEXT ) --Sample Data Insert into customer(Name, City, Status, Empid ) values('Alex','London','Active',1) Insert into customer(Name, City, Status, Empid) values('Dirk','Slough','Active',2) Insert into customer(Name, City, Status, Empid) values('Mark','Slough','Inactive',1) Suppose in a call center each employee is assigned a few customers, so he should be only authorized to make any changes to specific customer data. It does not return an error, but will return the output as 0 rows updated i.e. If we look at the Actual Execution plan as shown below, we can see the predicate defined.
Also if a new customer in added, this employee should be able to add the record via their employee id. Now with Row Level Security we can now apply the security restrictions in a much more secure manner.
Now we will create predicate function with our basic logic above that is based on the session_context and Emp ID values stored in the table.
Applies To: SQL Server 2016Now that you have created the Products table, you are ready to insert data into the table by using the INSERT statement.After the data is inserted, you will change the content of a row by using an UPDATE statement.You will use the WHERE clause of the UPDATE statement to restrict the update to a single row. The basic syntax is: INSERT, table name, column list, VALUES, and then a list of the values to be inserted.The two hyphens in front of a line indicate that the line is a comment and the text will be ignored by the compiler.In this case, the comment describes a permissible variation of the syntax.Gain real-time insights across your transactional and analytical data with SQL Server 2016—a secure, scalable database platform that has everything built in, from advanced analytics to unparalleled in-memory performance.