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.