具有執行方式、跨數據庫查詢和模組簽名的儲存過程安全性

January 4, 2018

我有一種情況，雖然我能夠解決它（正如重現將顯示的那樣），但我不明白。這裡是高點

兩個數據庫，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

你朝著正確的方向前進並且非常接近。現在您只需要將模組簽名視為替換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）。兩個簽名的儲存過程都按預期工作:-)。TRUSTWORTHY``OFF
測試 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


--========================================
模擬與模組簽名
我不明白的部分是為什麼在模擬下執行時，模組簽名不起作用。…模擬使模組簽名上下文更改為“僅拒絕”怎麼樣？
問題是這些問題的框架不正確。除了模擬之外，不應該使用模組簽名，而是作為它的替代；它們不是免費的功能。這裡的問題不是模擬如何影響模組簽名，而是模擬一般如何工作。原始測試腳本的結構（在問題中）是基於對模擬和模組簽名之間關係的這種誤解。它包括過早的模組簽名，以至於不能清楚地看到模擬的行為本身，從而導致誤導性的影響。
如果您執行上面發布的測試腳本，您應該會看到當 Impersonation 單獨使用時*（*即TRUSTWORTHY設置為OFF– 測試 1 和 2），那麼伺服器級別的“使用”為DENY ONLY. 含義：當您使用數據庫級模擬時，預設情況下，安全上下文被隔離到該特定數據庫。不允許上到伺服器級別，既不能獲取關聯登錄的伺服器級別權限，也不能返回到另一個數據庫。
這與模組簽名無關，因為尚未創建證書、登錄和使用者（假設您正在按順序逐步完成範例）。並且模組簽名——它確實添加了權限，並且可以允許跨數據庫訪問——不能覆蓋DENY因為DENY權限總是優先於GRANT權限。這DENY只能通過規避TRUSTWORTHY ON。
使用 Impersonation 時唯一可以刪除伺服器級DENY權限的方法是為源數據庫設置TRUSTWORTHY為。ON測試 3 和 4 顯示一旦TRUSTWORTHY啟用，則允許模擬在數據庫之間交叉。同樣，這與模組簽名無關，因為直到測試 4 之後才設置。模組簽名不是使整個場景正常工作所必需的；您所需要的只是模擬和 TRUSTWORTHY ON. 但是，如果您不想啟用，則需要模組簽名TRUSTWORTHY，在這種情況下，它取代了對模擬的需要。
下表顯示了各種方案及其要求：
    Scenario         --&gt;                 Requirements A               XOR   Requirements B
    ----------                ---------------------------------        |    --------------

Scope     Dynamic SQL --&gt; 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
希望很明顯，模組簽名可以完全取代對DB_CHAINING ON,Impersonation和TRUSTWORTHY ON. 考慮到同時具有跨數據庫功能和一些涉及動態 SQL 的場景，您的選擇是：
同時設置DB_CHAINING ON和TRUSTWORTHY ON：
這將允許您不使用模擬，除非由於使用了動態 SQL 而需要它。因此只有一些模組得到該EXECUTE AS子句。 2. 僅設置TRUSTWORTHY ON：
這要求所有模組都使用Impersonation（即有EXECUTE AS子句）。但是，您可以設置DB_CHAINING為OFF. 3. 僅使用模組簽名：
這需要在兩個數據庫中創建證書和使用者，並且源數據庫中的所有跨數據庫模組都得到簽名。但是，您可以同時設置DB_CHAINING 和！並且不需要模擬，即使是本地動態 SQL。此選項可以更乾淨、更安全地處理所有內容。TRUSTWORTHY``OFF
來自微軟的確認
在 SQL Server 中啟用跨數據庫訪問
動態 SQL
在執行動態創建的 SQL 語句的情況下，跨數據庫所有權連結不起作用，除非兩個數據庫中存在相同的使用者。您可以在 SQL Server 中通過創建訪問另一個數據庫中的數據的儲存過程並使用兩個數據庫中都存在的證書對該過程進行簽名來解決此問題。這使使用者可以訪問過程使用的數據庫資源，而無需授予他們數據庫訪問或權限。
使用 EXECUTE AS 擴展數據庫模擬
了解模擬範圍
…
但是，當使用 EXECUTE AS USER 語句或在數據庫範圍的模組中使用 EXECUTE AS 子句模擬主體時，預設情況下模擬的範圍僅限於數據庫。這意味著對數據庫範圍之外的對象的引用將返回錯誤。
此外，“Extending Database Impersonation by Using EXECUTE AS”MSDN 頁面（上面連結）上有很多很好的資訊，解釋了身份驗證器和這些規則背後的推理。
欲了解更多資訊，請參閱：
請，請，請停止使用模擬、可信和跨數據庫所有權連結
ModuleSigning.info

引用自：https://dba.stackexchange.com/questions/152931

具有執行方式、跨數據庫查詢和模組簽名的儲存過程安全性

相關問答

模擬使用者或登錄名映射到證書

通過 sys.fn_xe_file_target_read_file 授予使用者查看 XE 結果的權限

在數據庫和實例之間共享密碼加密的證書

將密碼加密的證書重新創建到另一個數據庫

創建數據庫的儲存過程的執行權限

SQL Server 模擬