With SQL Server 2016 literately just days away from being officially available to public for download (Get ready, SQL Server 2016 coming on June 1st). I wanted to highlight one important new key future called Row-Level Security. So what is Row Level Security? Row-Level Security enables customers to control access to rows in a database table based on the characteristics of the user executing a query (e.g., group membership or execution context).

sql-server-2016-row-level-security-04

Row-Level Security (RLS) simplifies the design and coding of security in your application. RLS enables you to implement restrictions on data row access. For example ensuring that workers can access only those data rows that are pertinent to their department, or restricting a customer’s data access to only the data relevant to their company.

The access restriction logic is located in the database tier rather than away from the data in another application tier. The database system applies the access restrictions every time that data access is attempted from any tier. This makes your security system more reliable and robust by reducing the surface area of your security system.

You implement RLS by using the CREATE SECURITY POLICY T-SQL statement, and predicates created as inline table valued functions.

To better understand SQL Server 2016 Row-Level Security let’s go through some code sample:

Create three user accounts that will demonstrate different access capabilities.

Create a quick table to hold data.

Populate the table with 10 rows of data, showing 5 orders for each sales person.

View the rows in the table

Grant read access on the table to each of the users.

Create a new schema, and an inline table valued function. The function returns 1 when a row in the SalesRep column is the same as the user executing the query (@SalesRep = USER_NAME()) or if the user executing the query is the Manager user (USER_NAME() = ‘Manager’).

Create a security policy adding the function as a filter predicate. The state must be set to ON to enable the policy.

Let’s now test the filtering predicate, by selected from the Orders table as each user. The Manager should see all 10 rows. The Sales1 and Sales2 users should only see their own sales.

To alter and disable the security policy.

In general, row-level security will work as expected across features. However, there are a few exceptions. The Cross-Feature Compatibility section documents several notes and caveats for using row-level security with certain other features of SQL Server. Details and information on RLS can be found here on the online documentation

Enjoy

Tagged with →