11/12/2018

Getting Started with Row Level Security in Azure SQL Database

Azure SQL Database offers Row Level Security out of the box.  To get started we can view this link.

So in order to get started, we first provision a SQL Database in Azure:



Next, we create our database for this example:





Once our database is provisioned, we open our SQL Server Management Studio 17:

When logging in, must provide credentials and Server Name which can be found in Azure SQL Database main page.  SSMS prompted to add our IP Address to the Firewall Rule to allow connection from remote machine.


Once connected we can begin the sample scripts:



Create our test users:




Create our dbo.Sales table:




Insert our data, 6 rows:






Grant privileges to our 3 users:



Create a Security Schema:



Create a predicate function:




Create a Security Policy:




Execute SQL Select query with different Users:



Here's the Results:


What does that indicate?

The first query was run by user "Sales1".  Sales1 can only see data where SalesRep field contains "Sales1".  Therefore, from the entire dataset of 6 rows, this user is allowed to see only 2 rows, which match the data "Sales1" as indicated in the Predicate statement.

Next, "Sales2" User can see 3 rows.

Lastly, "Manager" can see everything.

Using Row Level Security, out of the box, within Azure, is as simple as adding a few lines of code.

Here's the entire script:

drop USER if exists  Manager;
drop USER if exists  Sales1;
drop USER if exists  Sales2;

CREATE USER Manager WITHOUT LOGIN; 
CREATE USER Sales1 WITHOUT LOGIN; 
CREATE USER Sales2 WITHOUT LOGIN;
---------------------
CREATE TABLE dbo.Sales 
    ( 
    OrderID int, 
    SalesRep sysname, 
    Product varchar(10), 
    Qty int 
    ); 
---------------------
INSERT dbo.Sales VALUES  
(1, 'Sales1', 'Valve', 5),  
(2, 'Sales1', 'Wheel', 2),  
(3, 'Sales1', 'Valve', 4), 
(4, 'Sales2', 'Bracket', 2),  
(5, 'Sales2', 'Wheel', 5),  
(6, 'Sales2', 'Seat', 5); 
-- View the 6 rows in the table 
SELECT * FROM dbo.Sales; 
---------------------
GRANT SELECT ON dbo.Sales TO Manager; 
GRANT SELECT ON dbo.Sales TO Sales1; 
GRANT SELECT ON dbo.Sales TO Sales2;
---------------------
CREATE SCHEMA Security; 
GO 
---------------------
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname) 
    RETURNS TABLE 
WITH SCHEMABINDING 
AS 
    RETURN SELECT 1 AS fn_securitypredicate_result  
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
-----------------------
CREATE SECURITY POLICY SalesFilter 
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)  
ON dbo.Sales 
WITH (STATE = ON); 
-----------------------
EXECUTE AS USER = 'Sales1'; 
SELECT * FROM dbo.Sales;  
REVERT; 
EXECUTE AS USER = 'Sales2'; 
SELECT * FROM dbo.Sales;  
REVERT; 
EXECUTE AS USER = 'Manager'; 
SELECT * FROM dbo.Sales;  
REVERT; 
-----------------------
--ALTER SECURITY POLICY SalesFilter 
--WITH (STATE = OFF); 
-----------------------
--DROP SECURITY POLICY SalesFilter 

Thanks for reading~!