主页 / dba / 问题 / 312198
Accepted
Crescent
Asked: 2022-05-17 08:27:14 +0800 CST

尽管用户拥有 db_datareader，但仍将对新模式的访问限制为特定角色？

  • 772

我正在使用不是我设计的 SQL Server 2016 数据库，也无法从根本上更改安全结构。我知道这可能是垃圾/最差的做法，但我必须玩我目前处理的那手牌。我试图限制对模式的访问仅限于角色的成员，但每个人似乎都有 db_datareader 成员资格。以下是详细信息：

我们将 ETL 将一些新数据写入现有数据库中他们自己的表中。所有这些“新”表都需要访问限制，因此只有某些用户可以查看。这是我的想法（使用假名）：

  1. 创建名为“secret_schema”的新模式
  2. 创建 2 个新角色：'secret_schema_owner' (owner = dbo) 和 'secret_schema_reader' (owner = 'secret_schema_owner')
  3. 将“secret_schema”的所有者设置为“secret_schema_owner”角色
  4. GRANT SELECT ON SCHEMA::secret_schema TO secret_schema_reader

这里的目标是只有“secret_schema_reader”角色的成员才能查看“secret_schema”模式内的表中的数据。问题是这个数据库的前任管理员只是将用户“db_datareader”授予数据库，这似乎胜过我尝试的角色安全性。

除了对安全/权限结构进行全面改革外，我还有其他选择吗？

sql-server permissions

1 个回答

  1. Best Answer

    db_datareader 的成员实际上可以读取任何模式中的任何表/视图。这就是为什么如果您需要细粒度的访问控制，通常建议不要使用此角色。

    您可以创建一个新的“common_read”角色，然后将您的用户/组分配给该角色 - 并进行更严格的授权（即不授予对“secret_schema”的访问权限）

    要复制角色分配：

    -- execute this, copy the output to query window and execute
select concat('exec sp_addrole_member ''common_read'', ',rp.name,''';')
from sys.database_role_members rm
join sys.database_principals dp on (rm.role_principal_id = dp.principal_id)
join sys.database_principals rp ON (rm.member_principal_id = rp.principal_id)
where dp.name='db_datareader'

    在紧要关头，您始终可以拒绝访问模式。这将胜过任何 GRANT，因此创建一个角色，将 db_datareader 中的成员分配给“secret_schema”并拒绝访问。但这是一种倒退的方式，因为它需要管理角色成员两次。

    • 0

相关问题