Eu gostaria de implementar a segurança em nível de linha em todas as minhas tabelas. Cada tabela tem uma coluna chamada data_classificationque possui uma lista de valores. Idealmente, gostaria de criar funções de banco de dados que tenham permissão para ver determinadas linhas. Isso permitirá escalabilidade quando eu adicionar novos usuários. Todos os exemplos de RLS que posso encontrar são para usuários.
Minha tentativa é a seguinte. Eu crio uma tabela com uma coluna para cada função e uma linha para cada tipo de permissão. Se uma função tiver acesso a essa permissão, o nome da permissão será preenchido, caso contrário, será Null. Dado um nome de usuário, eu uso
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')
Para selecionar todas as permissões que essa função possui. Mas não consigo encaixar isso em um predicado para filtrar, eu estava esperando algo assim
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)
Isso não funcionou e continuo recebendo erros de sintaxe. Qualquer ajuda seria muito apreciada. Alterar a tabela de permissões também é uma possibilidade se isso facilitar as coisas. Obrigado
======
EDIT: Eu tenho data_classifications a,b,c,d,ee roles role_x, role_y, role_z, role_xtem permissões para a,b,c,d,e, role_ytem permissão para ver a,c,de role_ztem permissão para ver b,c,e. Também gostaria de torná-lo extensível se for necessária uma nova função ou uma nova classificação de dados.
Eu também quero adicionar esse filtro em todas as tabelas, espero que com o mínimo de comandos possível.