sql-server - 如何在Azure数据仓库(Synapse)中授予架构级别权限？

Question

在我们的 Azure SQL 数据仓库 (Synapse) 中，我们有两个架构 dbo 和 staging。我想向我的 Azure Active Directory 组 用户仅授予“dbo”架构的架构级别权限。我正在寻找的预期行为是，授予此权限后，Azure Active Directory 组用户在 ssms 中打开数据库时不应看到暂存架构。我尝试了以下方法来创建用户并授予权限，但用户仍然在其 ssms 中看到staging 架构。

1. 我使用以下命令创建了用户并添加了 db_datareader 角色:

CREATE USER [Azure Active Directory Group] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo;
EXEC sp_addrolemember 'db_datareader', 'Azure Active Directory Group'

然后使用以下方法授予架构级别权限:

GRANT SELECT ON SCHEMA::dbo TO [Azure Active Directory Group]

以下查询返回:

select pr.name,
       pr.type_desc,
       pe.permission_name,
       pe.state_desc,
       pe.class_desc,
       object_name(pe.major_id)

FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id where name='Azure Active Directory Group Users';

有人知道我如何授予架构级别权限吗？我被困住了!

Answer 1

db_datareader :db_datareader 固定数据库角色的成员可以读取所有用户表中的所有数据。这意味着 db_datareader 角色可以查看所有架构。所以我们需要撤销这个权限。

有两种方法可以解决该问题:

1. 撤销 db_datareader 权限并向用户授予该权限。

EXEC sp_droprolemember 'db_datareader', 'Azure Active Directory Group'

GRANT SELECT ON SCHEMA::dbo TO [Azure Active Directory Group]

我们还可以添加自定义角色并向该角色授予权限。

CREATE ROLE <Role_Name>; 

GRANT SELECT ON schema::dbo TO <Role_Name>;

EXEC sp_addrolemember '<Role_Name>', 'Azure Active Directory Group'