SQL Server 2016 - How to Protect Your Data Using Row Level Security (RLS)

Views: 626
Reading Time: 10 minutes

Speak guys !!
In this article, I would like to introduce to you a very interesting security feature that Microsoft SQL Server has gained from the 2016 release, which is Row Level Security (RLS) or Row Level Security.

How Row Level Security (RLS) Works

View content
Row Level Security allows you to control access to rows in a table according to the user or group you are part of, as if a filter had been applied to the query WHERE to limit which records it can view. For the user querying the database, the data filtering process is completely transparent, so that he cannot realize that although he is executing a SELECT * FROM table, the query is not returning all data from the table. .

This simplifies the design and security of systems and applications by ensuring that, for example, employees can only access data lines that are relevant to their department or restrict customer access to only that customer's relevant business data. All of this, done at the database level, without making any changes to the application.

It is noteworthy that there are 2 types of RLS usage:

  • Line Filter (FILTER PREDICATE): Allows you to apply a row filter to queries made with SELECT, returning only the records that the user can view
  • BLOCK PREDICATE: Allows you to block certain row-level operations that the user in question could not perform (eg INSERT into a table using a different User_ID)


How to implement Row Level Security (RLS)

View content
To implement RLS on your tables, you will create a function to filter records and use the command CREATE SECURITY POLICY to force the use of this function in queries performed by the table.

Creation of test data for example

Result:

Creation of users used for testing and permissions
Now I will create some users in the database to test RLS and give them permission to access the Orders and Users tables.

"Rls" schema creation
As a good practice, I will also create a new schema (rls) to aggregate the functions that will be created for use with RLS. Because you will need to create 1 function for each criterion or filter that you want to apply RLS and 1 policy to each table, it is more organized that all these functions are in a dedicated schema.

Creation of the fncId_User function
Using the fncId_User function, I can apply a filter to all tables (if I create policies for it) that have a field with the user identifier. In this function, the User_ID that is in the table in question is informed and the function will search the user table for the identifier (User_ID) of the logged in user that is SELECTing the table.

Remember that the filter is applied even to sysadmin users. That is, even the sysadmin user will only be able to view records where the User_ID is the same as that user's Id (or he cannot view any records).

Policy rls creation.
With the command below, we will apply the policy rls. Orders using the fncId_User function and thus enable Row Level Security (RLS) in the Orders table.

Row Level Security Tests:
Now, let's get to the practice tests and see how row-level security really works.

Result:

Another example for Row Level Security (RLS)

View content
I ended up demonstrating a fairly common use of RLS, but poorly documented articles, which is applying RLS using an ID from another table. Using the same basis as before, I will quickly demonstrate a simpler use, which is using the user's own login without using a SELECT to retrieve some information.

Role and policy creation

Real RLS Tests

Result:

Using Row Level Security (RLS) BLOCK PREDICATE

View content
After demonstrating the use of FILTER PREDICATE, which allows you to filter records from a SELECT query, I will now demonstrate the use of BLOCK PREDICATE, which prevents a given DML operation (INSERT, UPDATE, and DELETE).

Remember that we can use the FILTER and BLOCK predicates at the same time, in the same policy.

Policy Source Code

Now, let's impersonate the user “dirceu.resende” and try to insert a user with another login into the table other than “dirceu.resende”:

Result:

Msg 33504, Level 16, State 1, Line 4
The attempted operation failed because the target object 'dirceuresende.dbo.Users' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the predicate block.
The statement has been terminated.

As you can see, it was not possible to insert the record, because BLOCK PREDICATE avoided inserting since the Cd_Usuario that was being inserted was not what the user “dirceu.resende” was allowed to insert.

The same happens when we try to perform an UPDATE:

Result:

Msg 33504, Level 16, State 1, Line 17
The attempted operation failed because the target object 'dirceuresende.dbo.Users' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the predicate block.
The statement has been terminated.

However, if we try to update the logged-in user's own records, we see that the operation is successful:

Result:

As we create more 2 conditions in the fncUsuario role to allow sysadmin and / or db_owner users to access the data, these 2 user profiles will also be able to enter / update data normally:

Best Practices for Row Level Security

View content
  • Avoid type conversions to predicate functions to avoid potential runtime errors.
  • Avoid using other functions within the filtering function to avoid performance degradation. The query optimizer will try to detect direct recursions, but there is no guarantee that it will find indirect recursions (that is, where a second function calls the predicate function).
  • Avoid using excess table joins in predicate functions to maximize performance.
  • Avoid creating filtering functions that depend on certain session settings that are applied by using the SET command (NULL Comparisons / Operations, Day First Week functions, and functions that depend on the current language or DATEFORMAT).

Row Level Security and its impact on performance

View content
As I said above, when using the Row Level Security (RLS) feature, we are applying a filter to our query through a policy and a filter function. This already causes the query execution plan to change and, depending on the logic of its function, can even cause performance issues according to the number of uses.

Execution plan WITHOUT RLS enabled

RLS COM execution plan enabled

How to disable Row Level Security (RLS)

View content
To disable RLS, simply use the syntax below:

And to delete RLS objects, you can use the commands below:

Row Level Security (RLS) and feature compatibility

View content
Overall, row-level security will work as expected between features. However, there are some exceptions. This section documents several notes and limitations for using row-level security with certain SQL Server features.

  • DBCC SHOW_STATISTICS: Statistics reports can display information that would be protected by a security policy. For this reason, to view an RLS-protected table statistics, the user must be the table owner or must be a member of the sysadmin, db_owner, or db_ddladmin role.
  • Filestream: RLS is not compatible with Filestream.
  • Polybase: RLS is not compatible with Polybase.
  • Memory-Optimized Tables: The function used as a security predicate in a memory optimized table must be defined using the WITH NATIVE_COMPILATION option. With this option, language features not allowed by memory-optimized tables will be banned and the appropriate error will be issued at creation time.
  • Indexed Views: In general, security policies can be created over views, and views can be created over tables that are joined by RLS. However, indexed views not they can be created over tables that have an RLS security policy because index row searches would bypass the policy.
  • Change Data Capture (CDC): CDC can allow whole line information to be leaked since, even using RLS, all changes made will be stored in the history table, and any user with permission in that table can view this information.
  • Change Tracking: Change Tracking can leak the primary row key that must be filtered out to users with SELECT and VIEW CHANGE TRACKING permissions. Actual data values ​​do not leak; just the fact that column A has been updated / inserted / deleted for row with primary key B. This is a problem if the primary key contains a confidential element, such as a Social Security Number. However, in practice, this CHANGETABLE is almost always joined to the original table for more recent data.
  • Full-Text Search: A drop in performance is expected in queries that use the Full-Text Search and Semantic Search functions due to an extra join presented to enforce row-level security and prevent the loss of row primary keys that must be filtered: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.
  • Columnstore Indexes: RLS supports clustered and nonclustered columnstore indexes. However, because row-level security applies to a function, it is possible that the optimizer can modify the query plan so that it does not use batch mode.
  • Partitioned Views: Locking predicates cannot be defined on partitioned views, and partitioned views cannot be created on tables that use locking predicates. Filter predicates support partitioned views.
  • Temporal tables: Time tables are RLS compliant. However, security predicates in the current table are not automatically replicated in the history table. To apply a security policy to the current and historical tables, you must individually add a security predicate to each table.

References: https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security

That's it folks!
A hug and see you next.