siliconvalve

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.

FirstName         LastName              SalesQuota   Bonus      CommissionPct  SalesYTD
----------------- --------------------- ------------ ---------- -------------- -------------
Stephen           Jiang                 NULL         0.00       0.00           559697.5639
Michael           Blythe                300000.00    4100.00    0.012          3763178.1787
Linda             Mitchell              250000.00    2000.00    0.015          4251368.5497
Jillian           Carson                250000.00    2500.00    0.015          3189418.3662
Garrett           Vargas                250000.00    500.00     0.01           1453719.4653
Tsvi              Reiter                300000.00    6700.00    0.01           2315185.611
Pamela            Ansman-Wolfe          250000.00    5000.00    0.01           1352577.1325
Shu               Ito                   250000.00    3550.00    0.01           2458535.6169
José              Saraiva               250000.00    5000.00    0.015          2604540.7172
David             Campbell              250000.00    3500.00    0.012          1573012.9383
Tete              Mensa-Annan           300000.00    3900.00    0.019          1576562.1966
Syed              Abbas                 NULL         0.00       0.00           172524.4512
Lynn              Tsoflias              250000.00    5650.00    0.018          1421810.9242
Amy               Alberts               NULL         0.00       0.00           519905.932
Rachel            Valdez                250000.00    75.00      0.018          1827066.7118
Jae               Pak                   250000.00    5150.00    0.02           4116871.2277
Ranjit            Varkey Chudukatil     250000.00    985.00     0.016          3121616.3202

(17 row(s) affected)

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:

FirstName         LastName              SalesQuota   Bonus      CommissionPct  SalesYTD
----------------- --------------------- ------------ ---------- -------------- -------------
Linda             Mitchell              250000.00    2000.00    0.015          4251368.5497

(1 row(s) affected)

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!