SQL Server row-level security: How to implement in 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).

Download this free guide

Download: 60-Page Expert RDBMS Guide

Trends come and go, but your DB strategy shouldn’t be a flavor of the month. Learn why you shouldn’t get distracted by new DB technology, how Facebook is using a RDBMS to do the data slicing and dicing they can’t in Hadoop, and more.

Start Download

• Corporate E-mail Address:

  You forgot to provide an Email Address.

  This email address doesn’t appear to be valid.

  This email address is already registered. Please login.

  You have exceeded the maximum character limit.

  Please provide a Corporate E-mail Address.

• • I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

  Please check the box if you want to proceed.

• • I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.

  Please check the box if you want to proceed.

By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

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.

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

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.

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.

Let's try it out (Figure 5).

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).

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.

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).

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

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.

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

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).