sql-server - tSQLt 测试中的模拟和安全权限如何工作？

Question

我有一个 tSQLt 测试，我预计它会失败，但它运行成功。当我在过程外运行代码时，它按预期失败了，但是当使用 Run 执行测试时，没有发生错误。

我已阅读问题 tSQLt Testing SQL Server security permissions但接受的答案并不能解决我的问题。

我的测试是这样的:

    CREATE PROCEDURE TestSecurity.[test AFDK_Reader cannot read from AWS schema]
    AS 
    BEGIN
        --EXEC tSQLt.ExpectException
        EXECUTE AS USER = 'AFDK_Reader'

        select *
        from sys.user_token

        SELECT * FROM fn_my_permissions('AWS', 'SCHEMA')   
        ORDER BY subentity_name, permission_name ;   

        SELECT *
        FROM [AWS].[ADRESSEPUNKT_HISTORIK]
        REVERT
    END

该角色仅授予对 AFDK 架构的选择权限，这是 SQL 用户拥有的唯一数据库角色成员身份。

AFDK_Reader 没有从 AWS 架构中读取的权限。

谁能告诉我如何进行调试？提前致谢。

Answer 1

EXECUTE AS...REVERT 命令在存储过程中的行为方式与您期望的不同。这是 SQL Server 中存储过程安全性的一般特征；存储过程的一种常见用途是抽象权限。 MS 文档页面 Customizing Permissions with Impersonation in SQL Server说:

SQL Server does not check the permissions of the caller if the stored procedure and tables have the same owner.

这实际上就是这里发生的事情。即使 EXECUTE AS 更改了安全上下文，存储过程内部也不会检查该安全上下文。

文档页面还说:

However, ownership chaining doesn't work if objects have different owners or in the case of dynamic SQL.

获得您期望的行为的一种方法是从动态 SQL 语句内部运行 SELECT 语句，这意味着针对表权限测试事件安全上下文:

CREATE PROCEDURE TestSecurity.[test AFDK_Reader cannot read from AWS schema]
AS 
BEGIN
    EXEC tSQLt.ExpectException
    EXECUTE AS USER = 'AFDK_Reader'

    EXEC ('SELECT * FROM [AWS].[ADRESSEPUNKT_HISTORIK]')
    REVERT
END

另一种(更好的？)解决方案可能是使用内置权限函数通过元数据测试权限设置。这是一种方法，仍然使用 EXECUTE AS...REVERT 和 sys.fn_my_permission :

CREATE PROCEDURE TestSecurity.[test AFDK_Reader cannot read from AWS schema]
AS 
BEGIN
    EXECUTE AS USER = 'AFDK_Reader'
    DECLARE @permissionCount int = (SELECT COUNT(*) FROM sys.fn_my_permissions('[AWS].[ADRESSEPUNKT_HISTORIK]', 'OBJECT') WHERE permission_name = 'SELECT' AND subentity_name = '')
    REVERT
    EXEC tSQLt.AssertEquals 0, @permissionCount
END