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