主页 / dba / 问题 / 295484
Accepted
TheNumber23
Asked: 2021-07-10 14:42:16 +0800 CST

在 SQL Server 中使用角色的行级安全性

  • 772

我想在我的所有表上实现行级安全性。每个表都有一个名为的列data_classification，其中包含一个值列表。理想情况下，我想创建允许查看某些行的数据库角色。当我添加新用户时，这将允许可扩展性。我能找到的所有 RLS 示例都是针对用户的。

我的尝试如下。我创建了一个表，其中每个角色都有一个列，每个权限类型都有一个行。如果角色有权访问该权限，则填写权限名称，否则为 Null。给定一个用户名，我使用

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')

选择角色拥有的所有权限。但是我似乎无法将其放入要过滤的谓词中，我期待这样的事情

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)

这不起作用，我不断收到语法错误。任何帮助将非常感激。如果这使事情变得更容易，更改权限表也是可能的。谢谢

======

编辑：我有 data_classificationsa,b,c,d,e和 roles role_x, role_y, role_z，role_x有权限a,b,c,d,e，role_y有权限查看a,c,d和role_z有权限查看b,c,e。如果需要一个新角色或一个新的数据分类，我也想让它可扩展。

我还想在所有表上添加此过滤器，希望使用尽可能少的命令。

sql-server role

1 个回答

  1. Best Answer

    当然，它比这更简单。例如

    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

    输出

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

(1 row affected)

    您还可以将 (Role,DataClassification) 权利存储在一个表中，并从您的 RLS 谓词中引用它，例如：

    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

相关问题