row level security using roles in SQL Server

Question

I would like to implement row level security on all of my tables. Each table has a column named data_classification that has a list of values. Ideally I would like to create database roles which are allowed to see certain rows. This will allow scalability when I add new users. All of the examples of RLS I can find are for users.

My attempt at this is as follows. I create a table with a column for each role, and a row for each permission type. If a role has access to that permission the permissions name is filled in, else it is Null. Given a username, I use

DECLARE @role_name nvarchar(50);
set @role_name = SELECT dp.name as role_name  
FROM sys.sysusers us right 
JOIN  sys.database_role_members rm ON us.uid = rm.member_principal_id
JOIN sys.database_principals dp ON rm.role_principal_id =  dp.principal_id
WHERE us.name = USER_NAME();
EXEC('SELECT ' + @role_name + ' FROM [admin].[data_permissions] WHERE ' + @role_name + ' IS NOT NULL')

To select all the permisions that role has. But I can't seem to fit this into a predicate to filter, I was expecting something like this

CREATE FUNCTION DataFilter.fn_data_classification(@data_classification AS varchar(20))  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS result   
WHERE 
    @data_classification in (Query above)

This didn't work and I keep getting syntax errors. Any help would be much appreciated. Changing the permissions table is also a possibility if that makes things easier. Thanks

======

EDIT: I have data_classifications a,b,c,d,e and roles role_x, role_y, role_z, role_x has permissions to a,b,c,d,e, role_y has permission to see a,c,d and role_z has permission to see b,c,e. I also would like to make it extendable if a new role is needed or a new data classification.

I also want to add this filter on all tables, hopefully with as few commands as possible.

Answer 1

Sure, it's simpler than that. eg

create table SomeTable(id int identity primary key, a int, DataClassification char(1))

insert into SomeTable(a,DataClassification) values (1,'a')
insert into SomeTable(a,DataClassification) values (2,'b')
go
CREATE SCHEMA Security;  
GO  
  
CREATE FUNCTION Security.tvf_securitypredicate(@DataClassification AS char(1))  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS tvf_securitypredicate_result
    WHERE IS_MEMBER(@DataClassification + '_role')=1
GO

CREATE SECURITY POLICY SomeTable_ClassifictionFilter  
ADD FILTER PREDICATE Security.tvf_securitypredicate(DataClassification)
ON dbo.SomeTable
WITH (STATE = ON);  

go

create role a_role
create role b_role

create user fred without login
grant select to fred
alter role a_role add member fred 
GO

execute as user='fred'
 select * from SomeTable
revert

outputs

id          a           DataClassification
----------- ----------- ------------------
1           1           a

(1 row affected)

You can also store the (Role,DataClassification) entitlements in a table and reference that from your RLS predicate, eg:

create table SomeTable(id int identity primary key, a int, DataClassification char(1))

insert into SomeTable(a,DataClassification) 
values 
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d'),
(5,'e')

go
CREATE SCHEMA Security;  
GO  

drop table if exists Security.Role_Classification
create table Security.Role_Classification
(
   Classification char(1),
   RoleName sysname   
   constraint pk_Role_Classification primary key (Classification,RoleName)
)

go 

insert into Security.Role_Classification(RoleName,Classification)
values 
       ('role_x','a'),
       ('role_x','b'),
       ('role_x','c'),
       ('role_x','d'),
       ('role_x','e'),
       ('role_y','a'),
       ('role_y','c'),
       ('role_y','d'),
       ('role_z','b'),
       ('role_z','c'),
       ('role_z','e')
GO

CREATE or alter FUNCTION Security.tvf_securitypredicate(@DataClassification AS char(1))  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN 
    SELECT 1 AS tvf_securitypredicate_result
    FROM Security.Role_Classification
    WHERE IS_MEMBER(RoleName)=1
     AND  Classification = @DataClassification
GO

CREATE SECURITY POLICY SomeTable_ClassifictionFilter  
ADD FILTER PREDICATE Security.tvf_securitypredicate(DataClassification)
ON dbo.SomeTable
WITH (STATE = ON);  

go

create role role_x
create role role_y
create role role_z

create user fred without login
grant select to fred
alter role role_y add member fred 
GO

execute as user='fred'
 select * from SomeTable
revert

GO
alter role role_z add member fred 
go
execute as user='fred'
 select * from SomeTable
revert