tSQLt测试中的模拟和安全权限如何工作? [英] How does impersonation and security permissions in tSQLt tests work?

问题描述

我有一个tSQLt测试，我希望它会失败，但是可以成功运行.当我在过程外运行代码时，它按预期失败，但是使用Run执行测试时，不会发生错误.

I have a tSQLt test which I expect to fail but it runs with success. When I run the code outside the procedure, it fails as expected, but when executing the test with Run, no error occurs.

我已阅读问题 tSQLt测试SQL Server安全权限，但是接受的答案不能解决我的问题.

I have read the question tSQLt Testing SQL Server security permissions but the accepted answer does not solve my problem.

我的测试如下:

    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用户具有的唯一数据库角色成员身份.

The role has granted select permissions on the AFDK schema only and that is the only database role membership the SQL user has.

AFDK_Reader没有读取AWS模式的权限.

The AFDK_Reader has no permissions to read from the AWS schema.

有人可以告诉我如何进行调试吗?预先感谢.

Can anybody tell me how to get on with my debugging? Thanks in advance.

推荐答案

EXECUTE AS... REVERT命令的行为与您在存储过程中所期望的行为不同.这是SQL Server中存储过程安全性的一般功能.存储过程的一种常见用法是抽象权限. MS docs页面

EXECUTE AS... REVERT commands don't behave in the way you are expecting inside a stored procedure. This is a general feature of stored procedure security in SQL Server; one common use of stored procedures is to abstract permissions. The MS docs page Customizing Permissions with Impersonation in SQL Server says:

如果已存储，SQL Server不会检查调用方的权限 过程和表具有相同的所有者.

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

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

and that's effectively what's happening here. Even though the EXECUTE AS changes the security context, that security context isn't checked inside the stored procedure.

文档页面还显示:

但是，如果对象具有不同的所有权链接将不起作用 所有者，或者使用动态SQL.

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

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

One way to get the behaviour you're expecting would be to run the SELECT statement from inside a dynamic SQL statement, which means that the active security context is tested against the table permissions:

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和

A alternative (better?) solution might be to use the built-in permission functions to test permissions settings through metadata. Here's one way, still using EXECUTE AS... REVERT and 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

这篇关于tSQLt测试中的模拟和安全权限如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！