Setting up Row Level Security (RLS) on an Azure SQL DB/SQL MI by utilizing Azure AD groups

[Guest Tanayankar_Chakraborty]

Scenario:

 

We have 2 Azure AD groups (RLS_11 & RLS_12) , each containing 1 or more Azure AD users. We have to set up Row Level Security (RLS) on a table belonging to an Azure SQL DB.

 

 

 

Problem:

 

The Azure portal doesn`t provide a graphical interface on the database for you to set permissions needed to set up RLS.

 

Solution:

 

We can achieve our goal by using t-sql commands. For this test, please ensure you have an Azure AD ID that has enough elevated permissions to add the 2 Azure AD groups. Also needed are at least 2 Azure AD IDs that are added to the 2 Azure AD groups (1 in each AAD group). As noted above, the Azure AD groups used for this test are RLS_11 and RLS_12 and each of them have an Azure AD ID added.

 

 

 

Creating DB, Adding AAD groups as logins, creating users from those groups and adding datareader permissions.

 

All commands below were executed when I was logged in using my SQL authenticated login that has elevated permissions.

 

The DB created is – tanaysvrless

 

--Create a new schema ops and a table in it.

 

create schema ops

 

CREATE TABLE [ops].[test_RLSAccounts1](

 

[RLSAccountId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

 

[EmployerId] [int] NOT NULL)

 

-- populate the table

 

SET IDENTITY_INSERT [ops].[test_RLSAccounts1] Off

 

-- example values

 

Insert into [ops].[test_RLSAccounts1] (

 

[EmployerId])

 

values (1),

 

(2),

 

(3),

 

(4),

 

(5);

 

 

 

SET IDENTITY_INSERT [ops].[test_RLSAccounts1] on

 

 

 

-- add another column

 

ALTER TABLE [ops].[test_RLSAccounts1]

 

Add UScheck varchar(5) DEFAULT 'NONUS'; -- filter column

 

 

 

 

 

UPDATE [ops].[test_RLSAccounts1]

 

SET UScheck = 'US'

 

WHERE employerid = 3;

 

 

 

UPDATE [ops].[test_RLSAccounts1]

 

SET USCheck = 'NONUS'

 

WHERE USCheck IS NULL;

 

 

 

CREATE TABLE [ops].Lk_RLSAccounts

 

(username varchar(50),

 

UScheck varchar(5)

 

);

 

--populate Table

 

INSERT INTO [ops].Lk_RLSAccounts VALUES ('NonUSTest', 'NONUS');

 

INSERT INTO [ops].Lk_RLSAccounts VALUES ('USTest', 'US');

 

 

 

-- View the 2 rows in the table

 

SELECT * FROM [ops].Lk_RLSAccounts;

 

 

 

 

 

 

The command below must be executed while you are logged in using an Azure AD login having elevated permissions. The Azure AD login I used for this test was an AAD admin and hence had the necessary permissions. You must be logged in to the Master DB either using SQL Server management Studio (SSMS) or using Azure Data Studio.

 

USE MASTER;

 

CREATE LOGIN [RLS_11] FROM EXTERNAL PROVIDER -- non us

 

GO

 

CREATE LOGIN [RLS_12] FROM EXTERNAL PROVIDER -- us

 

GO

 

 

 

The commands after this can either be executed using the SQL authenticated login or the Azure AD login, provided they have the necessary permissions.

 

--Create DB principals from AAD Groups

 

USE [tanaysvrless]

 

GO

 

CREATE USER [All Non-US Employees] FOR LOGIN [RLS_11]

 

CREATE USER [All US Employees] FOR LOGIN [RLS_12]

 

-- Next step is to add AAD Groups to this

 

-- Once again, this table is not used in this test, but can be used in filtering function for RLS

 

INSERT INTO [ops].Lk_RLSAccounts VALUES ('All US Employees', 'US');

 

INSERT INTO [ops].Lk_RLSAccounts VALUES ('All Non-US Employees', 'NONUS');

 

-- Adding datareader permissions for user in order to read DB

ALTER ROLE db_datareader

 

ADD MEMBER [All Non-US Employees];

 

GO

 

 

 

ALTER ROLE db_datareader

 

ADD MEMBER [All US Employees];

 

GO

 

 

 

 

 

 

-- function will return results (whole table in this case) if the User from sys.user_token is 'All Non-US Employees' which was created from AAD Group login (in this case AAD group 'RLS_11').

 

 

 

CREATE FUNCTION ops.fn_securitypredicate()

 

RETURNS TABLE

 

AS

 

RETURN (select 1 as Result

 

from sys.user_token

 

where name = 'All Non-US Employees' and type = 'EXTERNAL GROUP');

 

 

 

CREATE SECURITY POLICY employeeidfilter

 

ADD FILTER PREDICATE ops.fn_securitypredicate()

 

ON [ops].[test_RLSAccounts1]

 

WITH (STATE = ON, SCHEMABINDING = OFF);

 

--To verify the successful creation of the security policy as well as its various properties, run the statement below:

 

select * from sys.security_policies

 

 

 

 

 

 

 

 

GRANT SELECT ON ops.fn_securitypredicate TO [All Non-US Employees];

 

GRANT SELECT ON ops.fn_securitypredicate TO [All US Employees];

 

 

 

GRANT SELECT ON [ops].[test_RLSaccounts1] TO [All Non-US Employees];

 

GRANT SELECT ON [ops].[test_RLSaccounts1] TO [All US Employees];

 

 

 

The tests after this point were conducted with the Azure AD user that is a member of RLS_11 (Azure AD group) and of the DB User- [All Non-US Employees]

 

Since the security function above was specified for [All Non-US Employees], the conditions outlined in it should be applicable to all Azure AD users that are a member of [All Non-US Employees] as shown below:

 

select * from ops.test_RLSAccounts1

 

select * from ops.Lk_RLSAccounts

 

select * from sys.user_token

 

 

 

 

The screenshot above indicates that we pass RLS filter and the Azure AD User that is a member of [All Non-US Employees] can view contents of the table [ops].[test_RLSaccounts1]

 

 

 

Now if we reverse the condition outlined in the security predicate, Azure AD users who are a member of the Azure AD group [All Non-US Employees] should ideally not be able to see the contents of the table [ops].[test_RLSaccounts1] and the members of [All Non-US Employees] should fail the RLS filter.

 

 

 

 

The screenshot above indicates the same and proves that RLS is working as expected.

 

 

 

Please note a few important things while setting up Row Level Security:

 

• While the example above was used only on 1 table, if you want to set this up for multiple tables, we could add the multiple tables in the same security policy as shown below:
  

 

CREATE SECURITY POLICY employeeidfilter1

 

ADD FILTER PREDICATE ops.fn_securitypredicate4(USCheck)

 

ON [ops].[test_RLSAccounts1],

 

ADD FILTER PREDICATE ops.fn_securitypredicate4(id)

 

ON [dbo].[t]

 

WITH (STATE = ON, schemabinding = OFF)

 

We can also use the same policy for multiple security predicates like Filter predicate & block predicate.

 

CREATE SECURITY POLICY rls.SecPol

 

ADD FILTER PREDICATE rls.tenantAccessPredicate(TenantId) ON dbo.Sales,

 

ADD BLOCK PREDICATE rls.tenantAccessPredicate(TenantId) ON dbo.Sales AFTER INSERT;

 

 

 

You may read more about this in this link: CREATE SECURITY POLICY (Transact-SQL) - SQL Server | Microsoft Learn

 

• If the output of select name from sys.user_token for the user has dbo, they are likely the AAD admin. You can also check the admin in the Azure Portal under the AAD blade. Avoid using an AAD admin to verify RLS as it may not provide results as expected.
  
• Although I used a simple condition while creating my security function, more complex select statements (With Joins, subqueries etc) can also be used if they are necessary to define your business rule. Here is an example : CREATE FUNCTION Sec.fn_securitypredicate4
  

 

(@UScheck AS varchar(5))

 

RETURNS TABLE

 

AS

 

RETURN ( SELECT 1 as Result

 

FROM [ops].[test_RLSAccounts1] f

 

INNER JOIN [ops].Lk_RLSAccounts s

 

ON s.UScheck = f.UScheck

 

WHERE ( f.UScheck = @UScheck

 

AND s.username IN (select name from sys.user_token)

 

)

 

OR USER_NAME() = 'USTest' ) ;

 

• While working with Azure AD groups, it is very important to note that the Azure AD user IDs that are a part of the participating (In Row level Security) AAD groups don’t inherit DB permissions from any other AAD groups that are already added to the DB and have a different level of permission. This could ruin the entire set up and the RLS set up may never work as expected.
  

 

 

 

References

 

Row-Level Security - SQL Server | Microsoft Learn

 

CREATE SECURITY POLICY (Transact-SQL) - SQL Server | Microsoft Learn

 

Continue reading...