如何限制Synapse无服务器登录的OPENROWSET权限仅访问指定视图？

一、限制Azure AD安全组登录仅能通过指定视图访问数据 #

要解决AD登录可直接执行任意OPENROWSET的问题，核心是切断其对数据湖的直接访问权限，仅通过受控视图提供数据访问：

1. 移除AD安全组的直接存储RBAC权限
   登录Azure门户，找到数据湖对应的存储账户，在「访问控制(IAM)」中移除JustADTest安全组的所有直接权限（如Storage Blob Data Reader、Contributor等）。这一步是关键——如果AD组保留存储RBAC权限，即使在Synapse中限制SQL权限，用户仍能通过自己的AD身份直接访问数据湖。

2. 创建基于托管身份/凭据的受控视图
   创建视图时，使用Synapse工作区的托管身份或预定义的数据库范围凭据访问存储，而非依赖登录用户的身份。示例代码：

-- 先确保已为Synapse托管身份授予存储账户的Storage Blob Data Reader权限
CREATE VIEW dbo.AuthorizedADView
AS
SELECT *
FROM OPENROWSET(
    BULK 'https://your-storage.dfs.core.windows.net/your-container/target-path/*.parquet',
    FORMAT = 'PARQUET',
    CREDENTIAL = (IDENTITY = 'Managed Identity') -- 使用Synapse托管身份
) AS data
-- 可添加WHERE子句进一步限制数据范围

3. 仅授予AD安全组视图的SELECT权限
   不要给AD组授予服务器级或数据库级的REFERENCES权限，仅对目标视图授权：

GRANT SELECT ON dbo.AuthorizedADView TO [JustADTest];

此时AD组用户只能通过该视图查询数据，直接执行OPENROWSET会因无存储权限失败。

二、SQL Server登录的类似限制实现 #

SQL登录本身无Azure AD身份，其OPENROWSET访问依赖数据库范围凭据，限制逻辑与AD登录类似：

1. 撤销SQL登录对存储凭据的直接权限
   若SQL登录默认能执行任意OPENROWSET，大概率是被授予了凭据的REFERENCES权限，需撤销：

REVOKE REFERENCES ON CREDENTIAL::YourStorageCredential FROM [JustTest];

2. 创建绑定凭据的受控视图
   用预定义的数据库范围凭据创建视图，确保视图能访问目标存储路径：

CREATE VIEW dbo.AuthorizedSQLView
AS
SELECT *
FROM OPENROWSET(
    BULK 'https://your-storage.dfs.core.windows.net/your-container/target-path/*.parquet',
    FORMAT = 'PARQUET',
    CREDENTIAL = [YourStorageCredential] -- 使用预先创建的存储凭据
) AS data

3. 仅授予SQL登录视图的SELECT权限

GRANT SELECT ON dbo.AuthorizedSQLView TO [JustTest];

完成后，SQL登录无法直接执行OPENROWSET（因无凭据访问权限），仅能通过授权视图查询数据。

关键注意事项 #

• 无论AD还是SQL登录，都避免授予服务器级的CONTROL或ALTER ANY CREDENTIAL权限，这类权限会绕过视图的权限限制。
• 视图使用的托管身份/凭据需拥有目标存储路径的访问权限，否则视图自身会无法读取数据。

内容的提问来源于stack exchange，提问作者bbb0777