Início / dba / Perguntas / 295484
Accepted
TheNumber23
Asked: 2021-07-10 14:42:16 +0800 CST

segurança em nível de linha usando funções no SQL Server

  • 772

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.

sql-server role

1 respostas

  1. Best Answer

    Claro, é mais simples que isso. por exemplo

    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

    saídas

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

(1 row affected)

    Você também pode armazenar os direitos (Role,DataClassification) em uma tabela e referenciar isso do seu predicado RLS, por exemplo:

    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
    • 3

relate perguntas