Manage Learn to apply best practices and optimize your operations.

SQL Server row-level security: How to implement in Azure SQL databases

An expert offers an example of how to institute row-level security, a preview feature introduced recently by Microsoft for Azure SQL databases.

Microsoft has introduced, as a preview feature, row-level security (RLS) in Azure SQL databases. 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).

Let's look at how you might implement RLS. This simple example expands on the one in Microsoft's online documentation of RLS. This example will help you understand the basics. You can find more information on the Microsoft Developer Network page on creating security policies.

First, we will create some tables in an Azure SQL database and add some data. We have an "Order" table and "Mapper" table (Figure 1). The purpose of the Mapper table is to associate certain types of orders with specific users.

order and mapper tables
Figure 1: Order and Mapper tables

Next, we will create several users and give them overall access to read the tables (Figure 2).

providing user access to tables

We will create an inline table valued function in a separate schema (Figure 3). The function will return "True" if the additional security column in the table (in this case "User") is equal to a prescribed USER_NAME or if the USER_NAME is equal to a specific user named "Manager." Note that we're defining a generic condition without reference to a specific table.

inline table valued function

Next we will create a new security policy that takes the function described above and add that filter to the Mapper table (Figure 4). The security policy is enabled with the "STATE = ON" portion of the command. In this snippet, the policy is being applied to a single specific table.  If there were multiple tables (in this case, with a "User" column defined) that could use this same filtering condition, those tables could also be affected by the same policy. It is considered best practice to keep the functions and policies in a separate schema for organizational reasons as well as to limit what a user could see based on security context.

creating a security policy

Let's try it out (Figure 5).

running the security policy

Now let's disable the security policy. This effectively disables RLS for this table. The first SELECT * statement will return all rows because the policy is not enabled. The second one will once again only return the headers (Figure 6).

disabling the security policy

In the Order table we created, we could add an additional User column, but we want to use the work we have already done on the Mapper table. So if we join the tables, the results will be filtered (Figure 7).  Try the query with the "Sales2" and "Manager" too.

joining the order and mapper tables

The main problem with this arrangement is that it requires us to change some of the code in our app to make sure that we always join the Order table to the Mapper table. Without joined Order and Mapper tables, we can't use RLS. In order to avoid this problem, the next step is to add a view that selects rows from both Order and Mapper (Figure 8).

adding a view

We can now use a view instead of a table for RLS (Figure 9). See it work for Sales2 and Manager users.

using a view

Having a view enables a more natural flow of coding than using a join to the Mapper table to have RLS enabled. However, the normal workarounds for using a view instead of a table also apply here.

At this point, it would appear that RLS still involves a lot of work. It is true that implementation requires some effort; however, the view option presented above is most likely the first option that you would consider. There is another option with Microsoft's implementation of RLS that makes this more efficient.

Next, we will establish security based on the "helper" table (Figure 10). In this case, it is the Mapper table. We need to create a security function based on this table.

establishing security with the mapper table

We will then apply this security function via policy to the Order table (Figure 11).

applying a security function to the order table

With this implementation of RLS and the appropriate security functions and policies, we can query the Order table without the need of a view. In other words, we still have RLS security even though we don't have a direct crosswalk from the Order table to the Mapper table (Figure 12). 

querying the order table without using a view


Next Steps

Get step-by-step instructions on how to improve OLTP with last year's hottest new feature -- in-memory

This SQL Server recovery tool retrieves individual tables instead of whole databases

SingleStone taps ObjectRocket for MongoDB on Azure

Dig Deeper on Microsoft SQL Server Tools and Utilities