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
role_y has permission to see
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.