1

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.

1 Answer 1

3
+50

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
4
  • Hum, I see that is considerably easier than what I was planning. Though I have a slightly different scheme. I made an edit to the problem with a bit more detail. Thanks for the help. Jul 12, 2021 at 13:33
  • Any other ideas on the different data schema? Jul 20, 2021 at 14:37
  • 1
    See update to answer. Jul 20, 2021 at 17:18
  • Perfect thanks, I knew it was something with my poor choice or roles, classification entitlements. Jul 21, 2021 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.