GRANT SELECT权限问题：如何让用户访问视图而不授权底层对象？

我有一个SQL视图[schemaA].[ViewNameA]，它基于以下不同架构的视图构建：

• [schemaB].[ViewNameB]
• [schemaC].[ViewNameC]
• [schemaD].[ViewNameD]

我执行了以下语句给功能账号USERXYZ授予该视图的访问权限：

GRANT SELECT ON [schemaA].[ViewNameA] to USERXYZ

但使用USERXYZ登录查询[schemaA].[ViewNameA]时，出现错误：

The SELECT permission was denied on the object 'ViewNameB', database 'db1', schema 'schemaB'.

该账号USERXYZ在数据库db1中拥有public角色。我需要在不授予[schemaB].[ViewNameB]等底层视图SELECT权限的前提下，让其访问[schemaA].[ViewNameA]。之前尝试用ALTER AUTHORIZATION管理架构权限，但导致用户获得了schemaB下所有视图的权限。

1. 利用所有权链（推荐，前提是视图所有者可统一） #

如果[schemaA].[ViewNameA]和所有底层视图（schemaB、schemaC、schemaD下的视图）的所有者相同，SQL Server会自动触发所有权链机制：此时只需授予USERXYZ对[schemaA].[ViewNameA]的SELECT权限，用户就能正常查询，无需单独授权底层对象。

若所有者不同，可修改视图所有者使其与底层视图一致：

-- 将schemaA.ViewNameA的所有者改为与schemaB.ViewNameB相同的用户/角色
ALTER AUTHORIZATION ON OBJECT::[schemaA].[ViewNameA] TO [OwnerOfSchemaBViews];

注意：确保这个所有者对所有底层视图拥有SELECT权限。

2. 使用证书签名的存储过程（适用于所有权链不适用的场景） #

如果无法统一视图所有者，可通过证书签名的存储过程封装查询逻辑，让存储过程拥有访问底层视图的权限，用户只需获得执行存储过程的权限即可：

步骤1：在master数据库创建并备份证书 #

USE master;
CREATE CERTIFICATE ViewAccessCert
ENCRYPTION BY PASSWORD = 'StrongPassword123!'
WITH SUBJECT = 'Certificate for ViewNameA Access';
BACKUP CERTIFICATE ViewAccessCert TO FILE = 'C:\Temp\ViewAccessCert.cer';

步骤2：在db1数据库导入证书 #

USE db1;
CREATE CERTIFICATE ViewAccessCert
FROM FILE = 'C:\Temp\ViewAccessCert.cer';

步骤3：创建证书对应的用户并授予底层权限 #

CREATE USER ViewAccessUser FOR CERTIFICATE ViewAccessCert;
GRANT SELECT ON [schemaB].[ViewNameB] TO ViewAccessUser;
GRANT SELECT ON [schemaC].[ViewNameC] TO ViewAccessUser;
GRANT SELECT ON [schemaD].[ViewNameD] TO ViewAccessUser;
GRANT SELECT ON [schemaA].[ViewNameA] TO ViewAccessUser;

步骤4：创建封装查询的存储过程 #

CREATE PROCEDURE GetViewNameAData
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM [schemaA].[ViewNameA];
END;

步骤5：用证书签名存储过程 #

ADD SIGNATURE TO GetViewNameAData BY CERTIFICATE ViewAccessCert
WITH PASSWORD = 'StrongPassword123!';

步骤6：授予USERXYZ执行权限 #

GRANT EXECUTE ON GetViewNameAData TO USERXYZ;

之后USERXYZ只需执行EXEC GetViewNameAData;即可获取视图数据，无需直接访问底层视图。

3. 使用EXECUTE AS子句（注意权限风险） #

可在视图或存储过程中指定拥有底层权限的用户作为执行上下文，但需注意权限泄露风险：

选项A：修改视图使用EXECUTE AS #

ALTER VIEW [schemaA].[ViewNameA]
WITH EXECUTE AS 'UserWithUnderlyingPermissions'
AS
-- 原视图定义
SELECT ... FROM [schemaB].[ViewNameB] JOIN ...;

确保UserWithUnderlyingPermissions对所有底层视图有SELECT权限，同时保留USERXYZ对该视图的SELECT权限。

选项B：创建带EXECUTE AS的存储过程 #

CREATE PROCEDURE GetViewNameAData
WITH EXECUTE AS 'UserWithUnderlyingPermissions'
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM [schemaA].[ViewNameA];
END;
GRANT EXECUTE ON GetViewNameAData TO USERXYZ;

注意：若指定的是登录用户，可能带来跨数据库权限风险，建议优先使用证书签名方案。

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