主页 / dba / 问题 / 152931
Accepted
Ben Thul
Asked: 2016-10-21 15:32:35 +0800 CST

具有执行方式、跨数据库查询和模块签名的存储过程安全性

  • 772

我有一种情况，虽然我能够解决它（正如重现将显示的那样），但我不明白。这里是高点

  • 两个数据库，ChainingSource 和 ChainDestination，它们都将跨数据库链接设置为 true
  • ChainingSource 中的存储过程通过EXEC(@sql)访问 ChainingDestination 中的表
  • 存储过程是用一个execute as子句定义的
  • 如果我尝试按原样执行该过程，则表示执行上下文的服务器主体无法访问 ChainingDestination
  • 因此，我将证书和代码签名添加到组合中。也就是说，我将证书映射登录添加到服务器，将用户映射到每个数据库，并相应地授予证书映射用户权限
  • 如果我保留该execute as子句，我会得到同样的错误。
  • 如果我删除该execute as子句，一切都很好。

这是我感到困惑的倒数第二点。或者，具体来说，为什么那个不起作用而最后一个起作用。

/******************************

            Setup

******************************/
USE [master];
go
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = 'ChainingSource')
BEGIN
    ALTER DATABASE [ChainingSource] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [ChainingSource] SET ONLINE;
    DROP DATABASE [ChainingSource];
END
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = 'ChainingDestination')
BEGIN
    ALTER DATABASE [ChainingDestination] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [ChainingDestination] SET ONLINE;
    DROP DATABASE [ChainingDestination];
END
GO

EXECUTE AS LOGIN = 'sa';
CREATE DATABASE [ChainingSource];
CREATE DATABASE [ChainingDestination];
GO
REVERT;
GO

ALTER DATABASE [ChainingSource] SET DB_CHAINING ON;
ALTER DATABASE [ChainingDestination] SET DB_CHAINING ON;

IF SUSER_ID('myAppUser') IS null
    CREATE LOGIN [myAppUser] WITH password = 'p@ssw0rd!23';

IF SUSER_ID('myAppUserEscalated') IS null
    CREATE LOGIN [myAppUserEscalated] WITH password = 'p@ssw0rd!23';

IF NOT EXISTS (
    SELECT * FROM sys.[symmetric_keys] AS [sk]
    WHERE name = '##MS_DatabaseMasterKey##'
)
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'f00bar!23';
    PRINT 'Created master key in databse [master]';
END

IF CERT_ID('myAppCert') IS NULL
    CREATE CERTIFICATE [myAppCert] AUTHORIZATION dbo FROM BINARY = 0x308201BD30820126A003020102021061AF3EB269776BB74629F44629EF9216300D06092A864886F70D0101050500301C311A301806035504031311436F6465205369676E696E6720436572743020170D3136313032303232303932335A180F32303939303130313030303030305A301C311A301806035504031311436F6465205369676E696E67204365727430819F300D06092A864886F70D010101050003818D0030818902818100BCCA7DC1CF2F4874F341AF3C586F0B023CAAD16986ADDAC2F7BFB3BCE590F2A952218F51298067CE3BE9ED695A229DADD029510F0927F30484A587024E0F58EC83924BE49D227D2FE1FCCA0C682528D6A0658AAA6CA5D9F2405AB6950B7D5BA672BB971910D71CEE3B77FF0A4EF59F010AAB445FD127944966C141F7CFC3D5790203010001300D06092A864886F70D010105050003818100011525EDD191767041659701F13F4370F803E6C981A6E33D68863FDACADE709926AB7E3BC8D618EDD07FC52058EFE42D96CA49961CF2936F446EDC4B7D55725FF2F1B37B326D564941CF6A7424551828FE198335AEFA0C892B375D3B676F35B708A48C67F80714643A34050CF9C557FBDD01274BC1ACCCA9A7AD3EC37DD2DA31 WITH PRIVATE KEY (BINARY = 0x1EF1B5B00000000001000000010000001000000054020000CF21B85A9464B60CDAB9F2E419B341490702000000A4000002E67BDF3CE02406E4D69A760D519B3BB6DA77FAFA7D710936EAE5267F072F98A1F7521110EB03955427B79FA386F7D70EDF6E977E92E59761DE0EB186F895AD975CE63C4D8A8B67BA487B9807EDB8B33C7C08EABCCD716E9505170A9729B6E165CE0ED0CA35B5C62D548367FEFC2C694060184D9185331466A0C64A9CB7BF8CA7AC0946A54091A4626978320C7290A784C6147BAF23FD866D9D1D4D1D79DA1B4D2DE213D11299F1417D8C421CC25A2E851FF9CEFA0ECA2006186C787692FDC28F2E702FCC7E76AFBEB95B954B50AA3697E60FC6928392664CE0EDB794AA392C1CC6326102B7CE8A02B367D2F416269DBBF4C16F096780517D32B4653E94DE5C24CE9D39EBC8E6A4EF1B9217F1B4F098F4F77F88CC11C40DDB312BE87CC1430C16AED8773E7691ADE8472BFC02B458B09B40404F61D2E02746AE576582DEE3EC5C09077E127BB4E996A9C4A840E6E0F59D85A3FC4E2844679927DBA6A571927A1F1C938716B8FC922B1D77FAA90BDBA49D1084081E4198A50506C5F6FE87F81B759EE0688428ABA7B2E8CC7D96AC6409DAE41937DB9C1E1CACCD7AE86A8F161316A07B05D523A116AB87022978312EE9853AE9FFA44FFF52114D084934D86D0FFD2D47B974769812BF0F4FE8276FD0DCE4069F11EC3915A68F4454166E3ABAAB9539530117597EE52213FEC7C87254634F10062C5C1D97CE5FEABB13252B22E210F56DB281FC1CE5432A7144FB4B89D00B4F8BC876C8C0F397DB9D22E15E2B07FBB44ADDDFBB6A75728917AC330E3A9F978847AC2D27913B3B6CBF54F1BAEF06072D15050ED1CA7BF9C5763A, DECRYPTION BY PASSWORD = 'f00bar!23')
IF SUSER_ID('myAppCert') IS NULL
    CREATE LOGIN [myAppCert] FROM CERTIFICATE [myAppCert];


USE [ChainingDestination];
CREATE USER [myAppUser];
CREATE USER [myAppUserEscalated];

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'f00bar!23';
CREATE CERTIFICATE [myAppCert] AUTHORIZATION dbo FROM BINARY = 0x308201BD30820126A003020102021061AF3EB269776BB74629F44629EF9216300D06092A864886F70D0101050500301C311A301806035504031311436F6465205369676E696E6720436572743020170D3136313032303232303932335A180F32303939303130313030303030305A301C311A301806035504031311436F6465205369676E696E67204365727430819F300D06092A864886F70D010101050003818D0030818902818100BCCA7DC1CF2F4874F341AF3C586F0B023CAAD16986ADDAC2F7BFB3BCE590F2A952218F51298067CE3BE9ED695A229DADD029510F0927F30484A587024E0F58EC83924BE49D227D2FE1FCCA0C682528D6A0658AAA6CA5D9F2405AB6950B7D5BA672BB971910D71CEE3B77FF0A4EF59F010AAB445FD127944966C141F7CFC3D5790203010001300D06092A864886F70D010105050003818100011525EDD191767041659701F13F4370F803E6C981A6E33D68863FDACADE709926AB7E3BC8D618EDD07FC52058EFE42D96CA49961CF2936F446EDC4B7D55725FF2F1B37B326D564941CF6A7424551828FE198335AEFA0C892B375D3B676F35B708A48C67F80714643A34050CF9C557FBDD01274BC1ACCCA9A7AD3EC37DD2DA31 WITH PRIVATE KEY (BINARY = 0x1EF1B5B00000000001000000010000001000000054020000CF21B85A9464B60CDAB9F2E419B341490702000000A4000002E67BDF3CE02406E4D69A760D519B3BB6DA77FAFA7D710936EAE5267F072F98A1F7521110EB03955427B79FA386F7D70EDF6E977E92E59761DE0EB186F895AD975CE63C4D8A8B67BA487B9807EDB8B33C7C08EABCCD716E9505170A9729B6E165CE0ED0CA35B5C62D548367FEFC2C694060184D9185331466A0C64A9CB7BF8CA7AC0946A54091A4626978320C7290A784C6147BAF23FD866D9D1D4D1D79DA1B4D2DE213D11299F1417D8C421CC25A2E851FF9CEFA0ECA2006186C787692FDC28F2E702FCC7E76AFBEB95B954B50AA3697E60FC6928392664CE0EDB794AA392C1CC6326102B7CE8A02B367D2F416269DBBF4C16F096780517D32B4653E94DE5C24CE9D39EBC8E6A4EF1B9217F1B4F098F4F77F88CC11C40DDB312BE87CC1430C16AED8773E7691ADE8472BFC02B458B09B40404F61D2E02746AE576582DEE3EC5C09077E127BB4E996A9C4A840E6E0F59D85A3FC4E2844679927DBA6A571927A1F1C938716B8FC922B1D77FAA90BDBA49D1084081E4198A50506C5F6FE87F81B759EE0688428ABA7B2E8CC7D96AC6409DAE41937DB9C1E1CACCD7AE86A8F161316A07B05D523A116AB87022978312EE9853AE9FFA44FFF52114D084934D86D0FFD2D47B974769812BF0F4FE8276FD0DCE4069F11EC3915A68F4454166E3ABAAB9539530117597EE52213FEC7C87254634F10062C5C1D97CE5FEABB13252B22E210F56DB281FC1CE5432A7144FB4B89D00B4F8BC876C8C0F397DB9D22E15E2B07FBB44ADDDFBB6A75728917AC330E3A9F978847AC2D27913B3B6CBF54F1BAEF06072D15050ED1CA7BF9C5763A, DECRYPTION BY PASSWORD = 'f00bar!23')
CREATE USER [myAppCert];
GO


CREATE TABLE [dbo].[topSecret] ([ID] INT IDENTITY, [Secrets] NVARCHAR(100));
INSERT INTO [dbo].[topSecret] ([Secrets]) VALUES ('Nuke Codes!');

GRANT SELECT ON [dbo].[topSecret] TO [myAppUserEscalated];
GRANT SELECT ON [dbo].[topSecret] TO [myAppCert];

GO

USE [ChainingSource];
GO
CREATE USER [myAppUser]
CREATE USER [myAppUserEscalated];

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'f00bar!23';
CREATE CERTIFICATE [myAppCert] AUTHORIZATION dbo FROM BINARY = 0x308201BD30820126A003020102021061AF3EB269776BB74629F44629EF9216300D06092A864886F70D0101050500301C311A301806035504031311436F6465205369676E696E6720436572743020170D3136313032303232303932335A180F32303939303130313030303030305A301C311A301806035504031311436F6465205369676E696E67204365727430819F300D06092A864886F70D010101050003818D0030818902818100BCCA7DC1CF2F4874F341AF3C586F0B023CAAD16986ADDAC2F7BFB3BCE590F2A952218F51298067CE3BE9ED695A229DADD029510F0927F30484A587024E0F58EC83924BE49D227D2FE1FCCA0C682528D6A0658AAA6CA5D9F2405AB6950B7D5BA672BB971910D71CEE3B77FF0A4EF59F010AAB445FD127944966C141F7CFC3D5790203010001300D06092A864886F70D010105050003818100011525EDD191767041659701F13F4370F803E6C981A6E33D68863FDACADE709926AB7E3BC8D618EDD07FC52058EFE42D96CA49961CF2936F446EDC4B7D55725FF2F1B37B326D564941CF6A7424551828FE198335AEFA0C892B375D3B676F35B708A48C67F80714643A34050CF9C557FBDD01274BC1ACCCA9A7AD3EC37DD2DA31 WITH PRIVATE KEY (BINARY = 0x1EF1B5B00000000001000000010000001000000054020000CF21B85A9464B60CDAB9F2E419B341490702000000A4000002E67BDF3CE02406E4D69A760D519B3BB6DA77FAFA7D710936EAE5267F072F98A1F7521110EB03955427B79FA386F7D70EDF6E977E92E59761DE0EB186F895AD975CE63C4D8A8B67BA487B9807EDB8B33C7C08EABCCD716E9505170A9729B6E165CE0ED0CA35B5C62D548367FEFC2C694060184D9185331466A0C64A9CB7BF8CA7AC0946A54091A4626978320C7290A784C6147BAF23FD866D9D1D4D1D79DA1B4D2DE213D11299F1417D8C421CC25A2E851FF9CEFA0ECA2006186C787692FDC28F2E702FCC7E76AFBEB95B954B50AA3697E60FC6928392664CE0EDB794AA392C1CC6326102B7CE8A02B367D2F416269DBBF4C16F096780517D32B4653E94DE5C24CE9D39EBC8E6A4EF1B9217F1B4F098F4F77F88CC11C40DDB312BE87CC1430C16AED8773E7691ADE8472BFC02B458B09B40404F61D2E02746AE576582DEE3EC5C09077E127BB4E996A9C4A840E6E0F59D85A3FC4E2844679927DBA6A571927A1F1C938716B8FC922B1D77FAA90BDBA49D1084081E4198A50506C5F6FE87F81B759EE0688428ABA7B2E8CC7D96AC6409DAE41937DB9C1E1CACCD7AE86A8F161316A07B05D523A116AB87022978312EE9853AE9FFA44FFF52114D084934D86D0FFD2D47B974769812BF0F4FE8276FD0DCE4069F11EC3915A68F4454166E3ABAAB9539530117597EE52213FEC7C87254634F10062C5C1D97CE5FEABB13252B22E210F56DB281FC1CE5432A7144FB4B89D00B4F8BC876C8C0F397DB9D22E15E2B07FBB44ADDDFBB6A75728917AC330E3A9F978847AC2D27913B3B6CBF54F1BAEF06072D15050ED1CA7BF9C5763A, DECRYPTION BY PASSWORD = 'f00bar!23')
CREATE USER [myAppCert];
GO

CREATE SYNONYM [dbo].[topSecret] FOR [ChainingDestination].[dbo].[topSecret];
GRANT SELECT ON [dbo].[topSecret] TO [myAppUserEscalated];
GRANT SELECT ON [dbo].[topSecret] TO [myAppCert];

GO

IF OBJECT_ID('[dbo].[getSecrets]') IS NOT null
    DROP PROCEDURE [dbo].[getSecrets]
GO

CREATE PROCEDURE [dbo].[getSecrets]
WITH EXECUTE AS 'myAppUserEscalated'
AS
BEGIN

    SELECT * FROM sys.login_token;
    SELECT * FROM sys.user_token;
    EXEC('SELECT * FROM [dbo].[topSecret] AS [ts];');
END
GO
GRANT EXECUTE ON [dbo].[getSecrets] TO [myAppUser];
GO

/******************************

            DEMO

******************************/

-- EXECUTE AS clause only
EXECUTE AS LOGIN = 'myAppUser';
GO
EXEC dbo.[getSecrets]
GO
REVERT;
GO

-- no bueno. let's try to add a signature!

ADD SIGNATURE TO [dbo].[getSecrets]
    BY CERTIFICATE [myAppCert];

EXECUTE AS LOGIN = 'myAppUser';
GO
EXEC dbo.[getSecrets]
GO
REVERT;
GO

-- still no bueno. 
-- let's take off the EXECUTE AS clause and sign

ALTER PROCEDURE [dbo].[getSecrets]
AS
BEGIN

    SELECT * FROM sys.login_token;
    SELECT * FROM sys.user_token;
    EXEC('SELECT * FROM [dbo].[topSecret] AS [ts];');
END
GO

ADD SIGNATURE TO [dbo].[getSecrets]
    BY CERTIFICATE [myAppCert];

EXECUTE AS LOGIN = 'myAppUser';
GO
EXEC dbo.[getSecrets]
GO
REVERT;
GO
 -- bueno
sql-server security

1 个回答

  1. Best Answer

    你朝着正确的方向前进并且非常接近。现在您只需要将模块签名视为替换EXECUTE AS而不是添加到其中的内容。完全删除EXECUTE AS和myAppUserEscalated完全依赖模块签名（以及基于证书的登录和相关用户）允许跨数据库权限并在所有权链接不起作用时维护权限（即动态 SQL），同时保持TRUSTWORTHY设置为OFF（和甚至保持DB_CHAINING设置为OFF）。

    下面是一个测试脚本，它基于问题中的脚本，但修改为从最小选项开始（即DB_CHAINING 未打开，并且未创建证书和基于证书的登录/用户）。它还具有 4 个存储过程，可以轻松测试以下各项的各种组合：

    • 默认（无模拟和无动态 SQL）
    • 模拟（但没有动态 SQL）
    • 动态 SQL（但没有模拟）
    • 模拟和动态 SQL

    脚本中有六个测试：

    • 测试 1 表明，默认情况下，所有组合都不起作用。由于所有权链接，存储过程getSecrets（无模拟或动态 SQL）比直接 SQL 更远，但由于没有跨数据库所有权链接，无法访问其他数据库。由于动态 SQL 破坏了所有权链，因此具有动态 SQL 的两个存储过程与直接 SQL 得到相同的错误。

    • 测试 2 表明，当 onlyDB_CHAINING设置为时ON，存储过程getSecrets（无模拟或动态 SQL）在数据库之间按需要工作。但是getSecretsWithDynamicSql由于动态 SQL 破坏了所有权链，存储过程失败，因此它无法从跨数据库所有权链中受益。

    • 测试 3 表明，当 onlyTRUSTWORTHY设置为ON（仅用于“源”数据库）时，使用模拟（即EXECUTE AS）的代码，无论是否使用动态 SQL，都可以在数据库之间按需要工作。但是不使用模拟的代码不起作用，与测试 1 相同。当然，我们不想这样做TRUSTWORTHY，ON因为它存在安全风险。该测试只是为了展示在模块签名之前的情况（即，TRUSTWORTHY在使用模拟时需要这样做，而在使用动态 SQL 时又需要这样做）。

    • 测试 4 表明，当DB_CHAINING 和 TRUSTWORTHY都设置为时ON，不使用动态 SQL 的代码无需模拟即可工作，并且任何使用模拟的代码，无论是否存在动态 SQL，都可以在数据库之间按需要工作。但同样，我们不想这样做TRUSTWORTHY，ON因为它是一个安全风险。这个测试只是为了展示在模块签名之前的情况。

    • 测试 5转回DB_CHAINING ，创建 Certificate 以及关联的 Login 和 Users，并对 不使用 Impersonation的两个存储过程进行签名（因为不再需要使用 Impersonation）。两个签名的存储过程都按预期工作:-)。TRUSTWORTHYOFF

    • 测试 6 删除了使用模拟的两个存储过程，甚至删除了“升级的”登录和被模拟的关联用户。再次运行测试 5 证明只需要模块签名（这就是为什么它是控制权限的超酷方式:-)。

    测试脚本：

    /******************************

            Setup

******************************/

/*************************  CLEANUP *************************************/

USE [master];
GO
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'ChainingSource')
BEGIN
    PRINT 'Dropping [ChainingSource] DB...';
    ALTER DATABASE [ChainingSource] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [ChainingSource] SET ONLINE;
    DROP DATABASE [ChainingSource];
END;

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'ChainingDestination')
BEGIN
    PRINT 'Dropping [ChainingDestination] DB...';
    ALTER DATABASE [ChainingDestination] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [ChainingDestination] SET ONLINE;
    DROP DATABASE [ChainingDestination];
END;

IF (SUSER_ID(N'myAppUser') IS NOT NULL)
BEGIN
  PRINT 'Dropping [myAppUser] Login...';
  DROP LOGIN [myAppUser];
END;

IF (SUSER_ID(N'myAppUserEscalated') IS NOT NULL)
BEGIN
  PRINT 'Dropping [myAppUserEscalated] Login...';
  DROP LOGIN [myAppUserEscalated];
END;
GO


/*************************  CREATE *************************************/

EXECUTE AS LOGIN = N'sa';
PRINT 'Creating databases...';
CREATE DATABASE [ChainingSource] COLLATE Latin1_General_100_CI_AS_SC;
CREATE DATABASE [ChainingDestination] COLLATE Latin1_General_100_CI_AS_SC;
REVERT;
GO


-- Set up Login/User: [myAppUser]
IF (SUSER_ID(N'myAppUser') IS NULL)
BEGIN
    EXEC(N'
      PRINT ''Creating [myAppUser]...'';
      USE [master];
      CREATE LOGIN [myAppUser] WITH PASSWORD = N''p@ssw0rd!23'';

      USE [ChainingDestination];
      CREATE USER [myAppUser];

      USE [ChainingSource];
      CREATE USER [myAppUser];
     ');
END;

-- Set up Login/User: [myAppUserEscalated]
IF (SUSER_ID(N'myAppUserEscalated') IS NULL)
BEGIN
    EXEC(N'
      PRINT ''Creating [myAppUserEscalated]...'';
      USE [master];
      CREATE LOGIN [myAppUserEscalated] WITH PASSWORD = N''p@ssw0rd!23'';

      USE [ChainingDestination];
      CREATE USER [myAppUserEscalated];

      USE [ChainingSource];
      CREATE USER [myAppUserEscalated];
     ');
END;
GO


USE [ChainingDestination];

CREATE TABLE [dbo].[topSecret] ([ID] INT IDENTITY, [Secrets] NVARCHAR(100));
INSERT INTO [dbo].[topSecret] ([Secrets]) VALUES (N'Nuke Codes!');

GRANT SELECT ON [dbo].[topSecret] TO [myAppUserEscalated];
GO


USE [ChainingSource];

CREATE SYNONYM [dbo].[topSecret] FOR [ChainingDestination].[dbo].[topSecret];

GRANT SELECT ON [dbo].[topSecret] TO [myAppUserEscalated];
GO

----
IF OBJECT_ID(N'[dbo].[getSecrets]') IS NOT NULL
    DROP PROCEDURE [dbo].[getSecrets]
GO

CREATE PROCEDURE [dbo].[getSecrets]
AS
BEGIN
    SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
    SELECT * FROM sys.login_token;
    SELECT * FROM sys.user_token;
    SELECT * FROM [dbo].[topSecret] AS [ts];
END
GO
GRANT EXECUTE ON [dbo].[getSecrets] TO [myAppUser];
GO
----
IF OBJECT_ID(N'[dbo].[getSecretsWithDynamicSql]') IS NOT NULL
    DROP PROCEDURE [dbo].[getSecretsWithDynamicSql]
GO

CREATE PROCEDURE [dbo].[getSecretsWithDynamicSql]
AS
BEGIN
    SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
    SELECT * FROM sys.login_token;
    SELECT * FROM sys.user_token;
    EXEC(N'SELECT * FROM [dbo].[topSecret] AS [ts];');
END
GO
GRANT EXECUTE ON [dbo].[getSecretsWithDynamicSql] TO [myAppUser];
GO
----
IF OBJECT_ID(N'[dbo].[getSecretsWithDynamicSqlAndImpersonation]') IS NOT NULL
    DROP PROCEDURE [dbo].[getSecretsWithDynamicSqlAndImpersonation]
GO

CREATE PROCEDURE [dbo].[getSecretsWithDynamicSqlAndImpersonation]
WITH EXECUTE AS N'myAppUserEscalated'
AS
BEGIN
    SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
    SELECT * FROM sys.login_token;
    SELECT * FROM sys.user_token;
    EXEC(N'SELECT * FROM [dbo].[topSecret] AS [ts];');
END
GO
GRANT EXECUTE ON [dbo].[getSecretsWithDynamicSqlAndImpersonation] TO [myAppUser];
GO
----
IF OBJECT_ID(N'[dbo].[getSecretsWithImpersonation]') IS NOT NULL
    DROP PROCEDURE [dbo].[getSecretsWithImpersonation]
GO

CREATE PROCEDURE [dbo].[getSecretsWithImpersonation]
WITH EXECUTE AS N'myAppUserEscalated'
AS
BEGIN
    SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
    SELECT * FROM sys.login_token;
    SELECT * FROM sys.user_token;
    SELECT * FROM [dbo].[topSecret] AS [ts];
END
GO
GRANT EXECUTE ON [dbo].[getSecretsWithImpersonation] TO [myAppUser];
GO

/******************************

            DEMO

******************************/

/******************  TEST 1 (both DB_CHAINING and TRUSTWORTHY OFF) ********************/

-- Default is OFF, but make resetting after running Tests 2 and 3 easier
ALTER DATABASE [ChainingSource] SET DB_CHAINING OFF;
ALTER DATABASE [ChainingDestination] SET DB_CHAINING OFF;
ALTER DATABASE [ChainingSource] SET TRUSTWORTHY OFF;


USE [ChainingSource];

EXECUTE AS LOGIN = 'myAppUser';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

SELECT * FROM [dbo].[topSecret]; -- error:
-- Msg 229, Level 14, State 5, Line XXXXX
-- The SELECT permission was denied on the object 'topSecret',
--    database 'ChainingSource', schema 'dbo'.

EXEC [dbo].[getSecrets]; -- error:
-- Msg 229, Level 14, State 5, Procedure getSecrets, Line XXXXX
-- The SELECT permission was denied on the object 'topSecret',
--    database 'ChainingDestination', schema 'dbo'.

EXEC [dbo].[getSecretsWithImpersonation]; -- error:
-- Msg 916, Level 14, State 1, Procedure getSecretsWithImpersonation, Line XXXXX
-- The server principal "myAppUserEscalated" is not able to access the database
--    "ChainingDestination" under the current security context.


EXEC [dbo].[getSecretsWithDynamicSqlAndImpersonation]; -- error:
-- Msg 229, Level 14, State 5, Line XXXXX
-- The SELECT permission was denied on the object 'topSecret',
--    database 'ChainingSource', schema 'dbo'.

EXEC [dbo].[getSecretsWithDynamicSql]; -- error:
-- Msg 229, Level 14, State 5, Line XXXXX
-- The SELECT permission was denied on the object 'topSecret',
--    database 'ChainingSource', schema 'dbo'.

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO


/******************  TEST 2 (DB_CHAINING ON ; TRUSTWORTHY OFF) ************************/

ALTER DATABASE [ChainingSource] SET DB_CHAINING ON;
ALTER DATABASE [ChainingDestination] SET DB_CHAINING ON;
ALTER DATABASE [ChainingSource] SET TRUSTWORTHY OFF;
GO


EXECUTE AS LOGIN = 'myAppUser';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

SELECT * FROM [dbo].[topSecret]; -- error: same as in Test 1
EXEC [dbo].[getSecretsWithImpersonation]; -- error: same as in Test 1
EXEC [dbo].[getSecretsWithDynamicSql]; -- error: same as in Test 1


EXEC [dbo].[getSecrets]; -- (different) success!

EXEC [dbo].[getSecretsWithDynamicSqlAndImpersonation]; -- (different) error:
-- Msg 916, Level 14, State 1, Line XXXXX
-- The server principal "myAppUserEscalated" is not able to access the database
--    "ChainingDestination" under the current security context.

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO


/******************  TEST 3 (DB_CHAINING OFF ; TRUSTWORTHY ON) **********************/

ALTER DATABASE [ChainingSource] SET DB_CHAINING OFF;
ALTER DATABASE [ChainingDestination] SET DB_CHAINING OFF;
ALTER DATABASE [ChainingSource] SET TRUSTWORTHY ON;
GO


EXECUTE AS LOGIN = 'myAppUser';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

SELECT * FROM [dbo].[topSecret]; -- error: same as in Tests 1 and 2
EXEC [dbo].[getSecrets]; -- error: same as in Test 1
EXEC [dbo].[getSecretsWithDynamicSql]; -- error: same as in Tests 1 and 2


EXEC [dbo].[getSecretsWithImpersonation]; -- (different) success!

EXEC [dbo].[getSecretsWithDynamicSqlAndImpersonation]; -- (different) success:

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO


/******************  TEST 4 (both DB_CHAINING and TRUSTWORTHY ON) *********************/

ALTER DATABASE [ChainingSource] SET DB_CHAINING ON;
ALTER DATABASE [ChainingDestination] SET DB_CHAINING ON;
ALTER DATABASE [ChainingSource] SET TRUSTWORTHY ON;
GO


EXECUTE AS LOGIN = 'myAppUser';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

SELECT * FROM [dbo].[topSecret]; -- error: same as in Tests 1, 2, and 3
EXEC [dbo].[getSecretsWithDynamicSql]; -- error: same as in Tests 1, 2, and 3

EXEC [dbo].[getSecrets]; -- success: same as in Test 2

EXEC [dbo].[getSecretsWithImpersonation]; -- success: same as in Test 3

EXEC [dbo].[getSecretsWithDynamicSqlAndImpersonation]; -- success: same as in Test 3

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO





/*********************************************************************/
/* BEGIN: set up Certificate and cert-based Users for module signing */
/*********************************************************************/

USE [ChainingDestination];

CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'f00bar!23';

CREATE CERTIFICATE [myAppCert]
  AUTHORIZATION [dbo]
  FROM BINARY = 0x\
308201BD30820126A003020102021061AF3EB269776BB74629F44629EF9216300D06092A864886F70D01\
01050500301C311A301806035504031311436F6465205369676E696E6720436572743020170D31363130\
32303232303932335A180F32303939303130313030303030305A301C311A301806035504031311436F64\
65205369676E696E67204365727430819F300D06092A864886F70D010101050003818D00308189028181\
00BCCA7DC1CF2F4874F341AF3C586F0B023CAAD16986ADDAC2F7BFB3BCE590F2A952218F51298067CE3B\
E9ED695A229DADD029510F0927F30484A587024E0F58EC83924BE49D227D2FE1FCCA0C682528D6A0658A\
AA6CA5D9F2405AB6950B7D5BA672BB971910D71CEE3B77FF0A4EF59F010AAB445FD127944966C141F7CF\
C3D5790203010001300D06092A864886F70D010105050003818100011525EDD191767041659701F13F43\
70F803E6C981A6E33D68863FDACADE709926AB7E3BC8D618EDD07FC52058EFE42D96CA49961CF2936F44\
6EDC4B7D55725FF2F1B37B326D564941CF6A7424551828FE198335AEFA0C892B375D3B676F35B708A48C\
67F80714643A34050CF9C557FBDD01274BC1ACCCA9A7AD3EC37DD2DA31;
-- no need for private key: nothing being signed in Destination


CREATE USER [myAppCert] FROM CERTIFICATE [myAppCert];

GRANT SELECT ON [dbo].[topSecret] TO [myAppCert];
GO


USE [ChainingSource];

CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'f00bar!23';

CREATE CERTIFICATE [myAppCert]
  AUTHORIZATION [dbo]
  FROM BINARY = 0x\
308201BD30820126A003020102021061AF3EB269776BB74629F44629EF9216300D06092A864886F70D01\
01050500301C311A301806035504031311436F6465205369676E696E6720436572743020170D31363130\
32303232303932335A180F32303939303130313030303030305A301C311A301806035504031311436F64\
65205369676E696E67204365727430819F300D06092A864886F70D010101050003818D00308189028181\
00BCCA7DC1CF2F4874F341AF3C586F0B023CAAD16986ADDAC2F7BFB3BCE590F2A952218F51298067CE3B\
E9ED695A229DADD029510F0927F30484A587024E0F58EC83924BE49D227D2FE1FCCA0C682528D6A0658A\
AA6CA5D9F2405AB6950B7D5BA672BB971910D71CEE3B77FF0A4EF59F010AAB445FD127944966C141F7CF\
C3D5790203010001300D06092A864886F70D010105050003818100011525EDD191767041659701F13F43\
70F803E6C981A6E33D68863FDACADE709926AB7E3BC8D618EDD07FC52058EFE42D96CA49961CF2936F44\
6EDC4B7D55725FF2F1B37B326D564941CF6A7424551828FE198335AEFA0C892B375D3B676F35B708A48C\
67F80714643A34050CF9C557FBDD01274BC1ACCCA9A7AD3EC37DD2DA31
  WITH PRIVATE KEY (
     BINARY = 0x\
1EF1B5B00000000001000000010000001000000054020000CF21B85A9464B60CDAB9F2E419B341490702\
000000A4000002E67BDF3CE02406E4D69A760D519B3BB6DA77FAFA7D710936EAE5267F072F98A1F75211\
10EB03955427B79FA386F7D70EDF6E977E92E59761DE0EB186F895AD975CE63C4D8A8B67BA487B9807ED\
B8B33C7C08EABCCD716E9505170A9729B6E165CE0ED0CA35B5C62D548367FEFC2C694060184D91853314\
66A0C64A9CB7BF8CA7AC0946A54091A4626978320C7290A784C6147BAF23FD866D9D1D4D1D79DA1B4D2D\
E213D11299F1417D8C421CC25A2E851FF9CEFA0ECA2006186C787692FDC28F2E702FCC7E76AFBEB95B95\
4B50AA3697E60FC6928392664CE0EDB794AA392C1CC6326102B7CE8A02B367D2F416269DBBF4C16F0967\
80517D32B4653E94DE5C24CE9D39EBC8E6A4EF1B9217F1B4F098F4F77F88CC11C40DDB312BE87CC1430C\
16AED8773E7691ADE8472BFC02B458B09B40404F61D2E02746AE576582DEE3EC5C09077E127BB4E996A9\
C4A840E6E0F59D85A3FC4E2844679927DBA6A571927A1F1C938716B8FC922B1D77FAA90BDBA49D108408\
1E4198A50506C5F6FE87F81B759EE0688428ABA7B2E8CC7D96AC6409DAE41937DB9C1E1CACCD7AE86A8F\
161316A07B05D523A116AB87022978312EE9853AE9FFA44FFF52114D084934D86D0FFD2D47B974769812\
BF0F4FE8276FD0DCE4069F11EC3915A68F4454166E3ABAAB9539530117597EE52213FEC7C87254634F10\
062C5C1D97CE5FEABB13252B22E210F56DB281FC1CE5432A7144FB4B89D00B4F8BC876C8C0F397DB9D22\
E15E2B07FBB44ADDDFBB6A75728917AC330E3A9F978847AC2D27913B3B6CBF54F1BAEF06072D15050ED1\
CA7BF9C5763A,
  DECRYPTION BY PASSWORD = N'f00bar!23');

CREATE USER [myAppCert] FROM CERTIFICATE [myAppCert];

GRANT SELECT ON [dbo].[topSecret] TO [myAppCert];
GO

/*********************************************************************/
/* END: set up Certificate and cert-based Users for module signing */
/*********************************************************************/

-- Sign the two stored procedures that are NOT using Impersonation.
-- Ignore the two stored procedures that ARE using Impersonation.
ADD SIGNATURE TO [dbo].[getSecrets]
    BY CERTIFICATE [myAppCert];

ADD SIGNATURE TO [dbo].[getSecretsWithDynamicSql]
    BY CERTIFICATE [myAppCert];
GO


/******************  TEST 5 (both DB_CHAINING and TRUSTWORTHY OFF) ********************/

ALTER DATABASE [ChainingSource] SET DB_CHAINING OFF;
ALTER DATABASE [ChainingDestination] SET DB_CHAINING OFF;
-- Trustworthy? We don't need no stinkin' trustworthy ;-)
ALTER DATABASE [ChainingSource] SET TRUSTWORTHY OFF;
GO


EXECUTE AS LOGIN = N'myAppUser';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

SELECT * FROM [dbo].[topSecret]; -- error: same as in Tests 1, 2, 3, and 4

EXEC [dbo].[getSecrets]; -- SUCCESS!!!

EXEC [dbo].[getSecretsWithDynamicSql]; -- SUCCESS!!!
GO


REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO


/************************  TEST 6 *************************************/
-- REMOVE Login/User: [myAppUserEscalated]
EXEC(N'
  USE [ChainingSource];
  IF (OBJECT_ID(N''[dbo].[getSecretsWithDynamicSqlAndImpersonation]'') IS NOT NULL)
  BEGIN
    DROP PROCEDURE [dbo].[getSecretsWithDynamicSqlAndImpersonation]
  END;
  IF (OBJECT_ID(N''[dbo].[getSecretsWithImpersonation]'') IS NOT NULL)
  BEGIN
    DROP PROCEDURE [dbo].[getSecretsWithImpersonation]
  END;
  IF (SUSER_ID(N''myAppUserEscalated'') IS NOT NULL)
  BEGIN
    DROP USER [myAppUserEscalated];
  END;


  USE [ChainingDestination];
  IF (SUSER_ID(N''myAppUserEscalated'') IS NOT NULL)
  BEGIN
    DROP USER [myAppUserEscalated];
  END;


  USE [master];
  IF (SUSER_ID(N''myAppUserEscalated'') IS NOT NULL)
  BEGIN
    DROP LOGIN [myAppUserEscalated];
  END;

');
GO

-- Now, re-run Test 5, just to be sure that it is only the module-signing that matters


--========================================

    模拟与模块签名

    我不明白的部分是为什么在模拟下运行时，模块签名不起作用。...模拟使模块签名上下文更改为“仅拒绝”怎么样？

    The problem is that these questions are framed incorrectly. Module signing isn't supposed to be used in addition to Impersonation, but as a replacement of it; they are not complimentary features. The issue here is not how Impersonation affects module signing, but how Impersonation works in general. The structure of the original test script (in the question) is based upon this misunderstanding of the relationship between Impersonation and module signing. It includes module signing too early such that the behavior of Impersonation, by itself, cannot be seen clearly, thus leading to misleading implications.

    If you run through the test script posted above, you should see that when Impersonation is used by itself (i.e. TRUSTWORTHY is set to OFF -- tests 1 and 2) then the server-level "usage" is DENY ONLY. Meaning: when you use Database-level Impersonation, the security context is, by default, quarantined to that particular database. It is not allowed to go up to the server level, neither to get server-level permissions of the associated Login, nor to go back down to another database.

    This has nothing to do with module signing since the Certificate, Login, and Users haven't even been created yet (assuming you are stepping through the example in order). And module signing -- which does add permissions, and which can allow for cross-database access -- cannot override the DENY since DENY permissions always take precedence over GRANT permissions. That DENY can only be circumvented by TRUSTWORTHY ON.

    The only thing that can remove the server-level DENY permission when using Impersonation is setting TRUSTWORTHY to ON for the source database. Tests 3 and 4 show that once TRUSTWORTHY is enabled, then Impersonation is allowed to cross between databases. And again, this has nothing to do with module signing since that does not get set up until after test 4. Module signing isn't necessary to get the overall scenario working; all you need is Impersonation and TRUSTWORTHY ON. However, module signing is required if you don't want to enable TRUSTWORTHY, in which case it replaces the need for the Impersonation.

    The following chart shows the various scenarios and what they require:

         Scenario         -->                 Requirements A               XOR   Requirements B
     ----------                ---------------------------------        |    --------------

Scope     Dynamic SQL --> DB_CHAINING    Impersonation   TRUSTWORTHY   XOR   Module Signing
Local      No               No               No              No         |        No
Local      YES              No               YES             No         |        YES

Cross-DB   No               YES              No              No         |        YES
Cross-DB   YES              No               YES             YES        |        YES

    Hopefully it is clear that module signing can completely replace the need for DB_CHAINING ON, Impersonation, and TRUSTWORTHY ON. Given the scenario of having both Cross-DB functionality, and some of that involving Dynamic SQL, your choices are:

    1. Set both DB_CHAINING ON and TRUSTWORTHY ON:

      This will allow you to not use Impersonation unless it is required due to Dynamic SQL being used. Hence only some modules get the EXECUTE AS clause.

    2. Set only TRUSTWORTHY ON:

      This requires that all modules use Impersonation (i.e. have the EXECUTE AS clause). But, you can set DB_CHAINING to OFF.

    3. Use only module signing:

      This requires that the Certificate and User be created in both DBs, and that all Cross-DB modules in the source DB get signed. But, you can set both DB_CHAINING and TRUSTWORTHY to OFF!! And there is no need for Impersonation, even for local Dynamic SQL. This option handles everything more cleanly and more securely.

    Confirmation from Microsoft

    • Enabling Cross-Database Access in SQL Server

      Dynamic SQL

      Cross-database ownership chaining does not work in cases where dynamically created SQL statements are executed unless the same user exists in both databases. You can work around this in SQL Server by creating a stored procedure that accesses data in another database and signing the procedure with a certificate that exists in both databases. This gives users access to the database resources used by the procedure without granting them database access or permissions.

    • Extending Database Impersonation by Using EXECUTE AS

      Understanding Impersonation Scope

      ...

      However, when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.

    Also, there is a lot of good information on the "Extending Database Impersonation by Using EXECUTE AS" MSDN page (linked above) that explains authenticators and the reasoning behind these rules.

    For more information, please see:

    • 7

相关问题