Steve Jones

19 July 2016

1 Comment

• SQL Compare
• Database Builds and Deployments

Using SQL Compare with Row Level Security

SQL Server 2016 brings with it a host of new features that will be both useful and appreciated by many customers. Quite a few of these features relate to providing additional security for databases, one of which is Row Level Security (RLS). This is a feature that has been available in Azure SQL Databases for about a year, and was released to on-premise SQL Server instances in June of 2016.

The idea behind RLS is one that many people have implemented in their databases for a long time. However, the development overhead to ensure all queries included the security logic was high and problematic. It became easy to forget the additional code, especially in applications that assembled queries at runtime. As developers left and joined application teams, knowledge was sometimes not transferred, resulting in security holes that might not be discovered for quite some time.

The addition of RLS in Azure and SQL Server was designed to simplify the development of row-based security rules, and ensure they are consistently implemented in applications, without requiring additional developer awareness. This method allows administrators to configure security rules based on the data stored in tables, freeing up the developer from worrying about managing security.

However, since the RLS configuration isn’t something developers are aware of, it is possible that both developers and administrators might assume the objects associated with RLS (security predicate functions and security policies) are consistently deployed across environments along with other application objects. This may not be the case, and there need to be assurances that deployment scripts contain the RLS objects, as well as periodic audits to ensure the rules are correctly implemented. SQL Compare can help with both of these tasks, as we will see below.

Implementing Row Level Security

The initial setup for RLS is fairly simple. There are two items an administrator needs to create and link to the data inside the database. These are:

• The security predicate function
• The security policy

This article will not cover the complete setup for RLS, but will just show the code for the objects in your development database. If you want to learn more about Row Level Security, we have a collection of resources at SQLServerCentral.

In this case, we have a security predicate function that is defined like this:

In this function, a 1 is returned for those rows that are allowed in a query. As you can see, the USER_NAME() of the session is gathered and compared to the value contained in the dbo.SalesPeople table. The rows in the dbo.SalesPeople table must also match the SalesPersonID with the value passed into the function. This value is automatically passed in, based on the RLS security policy definition.

The security policy determines how the predicate function is applied to a particular table. In this case, we have a dbo.OrderHeader table that contains a SalesPersonID. This table is the one we want to secure with RLS. We define our Security Policy as shown here:

Now that we’ve created these objects, let’s ensure they work. There are seven rows in the dbo.OrderHeader table that reside in my development database. Of these, three have a SalesPersonID = 2. The dbo.SalesPeople table has three rows, one of which is for the salesperson with username = ‘sjones’. We can see the SalesPerson table below.

If the user, sjones, queries this table, they should only see the rows with a SalesPersonID = 2. This is easily tested with a simple query:

Using SQL Compare to Deploy

SQL Compare will help us deploy these changes to another database. In this case, I have a database named EncryptionDemo_Production, which has a dbo.SalesPeople table as well as a dbo.OrderHeader table. The dbo.SalesPeople table has only three rows. We see this, noting the database and row count at the bottom of the image.

However, the dbo.OrderHeader table has many more. We have 1003 rows, and our user, pmanning, can access all of them.

No RLS has been implemented in this database as of yet. We can check the Security Policies folder in Object Explorer to verify this.

Now let’s run SQL Compare. I’m using the new SQL Compare 12 beta, so the first thing we see is a redesigned and much cleaner interface.

We want to compare our databases, so let’s do that. I’ll select Database as both the Source and Target. I’ll also choose the appropriate database for each. In this case, EncryptionDemo is the Source and EncryptionDemo_Production is the target. We want to deploy our RLS from the first to the second.

Pressing Compare Now will start the comparison of the two databases. We see the familiar comparison step progress, though again, with a redesigned user interface.

Once this is done, we can examine the differences. There are a few table differences, which relate to Primary Keys (PK) being named differently in development from production. Note, this is one reason to explicitly name your objects.

If we look, we can see that our security predicate function, RLS_SalesPerson_OrderCheck, is only in the source, as is our security policy, RLS_SalesPeople_Orders_Policy. SQL Compare has detected both. The function is a normal User-Defined Function, as we would have had in prior versions of both SQL Compare and SQL Server. The Security Policy, however, is new to SQL Server 2016 and SQL Compare.

The users and encryption keys are objects we would not deploy from development to production, so we will ignore those. Let’s check the two RLS objects and click Deploy at the top.

Once we do that, we see the familiar options to deploy using SQL Compare or create a script. I’m going to let SQL Compare perform the deployment here.

After I click Next, we get the Dependencies screen, where I can see that each of these objects depends on something else.

In this case, I can review the objects, and this makes perfect sense. Certainly in a more complex scenario I might want to actually save the script, double-check dependencies detected and possibly ignore them rather than deploy them.

I click Next and I see the script that will be deployed. I scroll down and see both my function and security policy are being deployed. This is what I expect, so I click Deploy Now.

I get a confirmation, which I agree to with a click.

Once that is done, a new comparison is run (because I had the Recompare after deployment checkbox ticked above). When I do that, I can see that my objects have been deployed.

However, did this work? Let’s examine the target database and see what’s there. I refresh the Object Explorer and I see both.

What about the application of the security rules for RLS? Are they being followed correctly? If you remember, when my user, pmanning, queried the dbo.OrderHeader table, he returned 1003 rows. Let’s run the same query.

Notice above that while we see many results, we only see 342 rows. This is because RLS has been applied to the table and that is the count of rows in OrderHeader that have a SalesPeopleID = 1.

Now watch a demo

I’ve shown you how Row Level Security works – and how you can use SQL Compare to ensure it’s implemented correctly. If you’d like to see a demo of it in action, I’ve created a short video. It’s only three minutes long, so why not watch it now?

Conclusion

As we can see, SQL Compare has additional functionality to work with Row Level Security in SQL Server 2016. This means that, as you test your RLS configuration in various environments, you can easily script out and deploy those changes to other databases with SQL

This functionality is built into the SQL Compare engine, which is also used in other Redgate tools, such as SQL Source Control and the DLM Automation cmdlets. In fact, any product or process built on SQL Compare will benefit, ensuring that, as you migrate to SQL Server 2016, all your code will also migrate as expected.

Try it now free or ‘Check for Updates’ under the Help menu in-product to make sure you’re on the latest version.