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!
