Azure SQL Database - Row Level Security Walkthrough

According to De La Soul three is the Magic Number, so here's the third post on the new Azure SQL Database features released over the last couple of months.

Previously I looked at Dynamic Data Masking and Auditing, both features that deliver Azure-specific functionality (or that rely on Azure features). This post will cover Row-Level Security (RLS) which would be familiar to many on-prem SQL Server admins and until this release was a feature missing from Azure SQL Database.

As with my previous posts I'm going to work with the AdventureWorks sample database that I have loaded into Azure SQL Database.

Note that I'm not looking to take a "best practices" approach here to how I implement policies and permissions - that's why you can read MSDN or other SQL Server specific blogs 🙂.

Testing it out

The remainder of this post is mostly going to be SQL and some result sets - no need for pretty UI here!

The AdventureWorks database is a good sample as it has Sales information which includes quota, bonus, commission and year-to-date sales information. If we run the query below we get a good feeling for the data available.

SELECT PER.FirstName, PER.LastName, SP.SalesQuota, SP.Bonus, SP.CommissionPct, SP.SalesYTD
FROM Sales.SalesPerson AS SP
INNER JOIN Person.Person AS PER
ON SP.BusinessEntityID = PER.BusinessEntityID

The results of this query are shown below.

So.. some details there we don't want to necessarily share amongst the sales team!

I'm going to setup RLS to restrict this so that the person who can see all entries is someone logged in a dbo-equivalent user, with individual sales people only able to see their own row.

The below SQL shows how to set this up.

-- create a test user (we'll do it without a login for this demo)
CREATE USER LindaMitchell WITHOUT LOGIN;

-- grant them select permissions on the two tables in our query
GRANT SELECT ON Person.Person to LindaMitchell; GRANT SELECT ON Sales.SalesPerson to LindaMitchell;

-- create a 'security' schema as per Microsoft's recommendations
CREATE SCHEMA Security;
GO

-- create a table-valued function that will provide our filter functionality
CREATE FUNCTION Security.fn_securitypredicate(@FirstName AS nvarchar(50), @LastName AS nvarchar(50))
RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result
WHERE USER_NAME() = (@FirstName + @LastName) OR USER_NAME() = 'dbo';

-- now create security policy on the Person table, passing in the FirstName and LastName columns.
CREATE SECURITY POLICY SalesPersonFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(FirstName, LastName)
ON Person.Person WITH (STATE = ON);

Now that we have this setup we can re-run the previous query and we will still get the same output as shown above because we are allowing anyone with user name of 'dbo'.

However, what do we get when we run the query in the user context of 'LindaMitchell'? Let's find out.

EXEC AS USER = 'LindaMitchell';

SELECT PER.FirstName, PER.LastName, SP.SalesQuota, SP.Bonus, SP.CommissionPct, SP.SalesYTD
FROM Sales.SalesPerson AS SP
INNER JOIN Person.Person AS PER
ON SP.BusinessEntityID = PER.BusinessEntityID

REVERT;

Which results in:

Which is exactly what we wanted - Linda can only see her own data in this table!

So there we have the final piece of the major new features in Azure SQL Database - now an even better match for you migration to Azure.

Happy days!