Início / dba / Perguntas / 152931
Accepted
Ben Thul
Asked: 2016-10-21 15:32:35 +0800 CST

Segurança de procedimento armazenado com executar como, consultas cruzadas de banco de dados e assinatura de módulo

  • 772

Estou com uma situação que, embora tenha conseguido contornar (como a reprodução mostrará), não entendo. Aqui estão os pontos altos

  • Dois bancos de dados, ChainingSource e ChainDestination, ambos com encadeamento entre bancos de dados definido como verdadeiro
  • Um procedimento armazenado em ChainingSource acessa, por meio de um EXEC(@sql), acessa uma tabela em ChainingDestination
  • O procedimento armazenado é definido com uma execute ascláusula
  • Se eu tentar executar o procedimento como está, ele diz que o servidor principal do contexto de execução não pode acessar o ChainingDestination
  • Então, adiciono um certificado e uma assinatura de código à mistura. Ou seja, eu adiciono um login mapeado por certificado ao servidor, usuário mapeado para cada um dos bancos de dados e concedo permissões ao usuário mapeado por certificado de acordo
  • Se eu deixar a execute ascláusula no lugar, recebo o mesmo erro.
  • Se eu remover a execute ascláusula, está tudo bem.

É o penúltimo ponto que me deixa confuso. Ou, especificamente, por que aquele não funciona e o último sim .

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

            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 respostas

  1. Best Answer

    Você estava indo na direção certa e estava muito perto. Agora você só precisa visualizar a assinatura do módulo como uma substituição em EXECUTE ASvez de algo que é adicionado a ele. Remover EXECUTE ASe myAppUserEscalatedtotalmente, e confiar apenas na assinatura do módulo (e no Login baseado em certificado e Usuários relacionados) permite permissões entre bancos de dados e mantém permissões quando o encadeamento de propriedade não funciona (ou seja, SQL dinâmico), mantendo tudo TRUSTWORTHYdefinido para OFF(e mesmo mantendo DB_CHAININGdefinido como OFF).

    Abaixo está um script de teste baseado no script da pergunta, mas modificado para iniciar com as opções mínimas (ou seja , DB_CHAINING não ativado e o Certificado e Login com base em certificado / Usuários não criados). Ele também possui 4 procedimentos armazenados para testar facilmente as várias combinações de:

    • Padrão (sem representação e sem SQL dinâmico)
    • Representação (mas sem SQL dinâmico)
    • SQL dinâmico (mas sem representação)
    • Representação e SQL dinâmico

    Há seis testes no script:

    • O teste 1 mostra que, por padrão, nenhuma das combinações funciona. O procedimento armazenado, getSecrets(sem representação ou SQL dinâmico), vai além do SQL direto devido ao encadeamento de propriedade, mas não pode acessar o outro banco de dados devido à falta de encadeamento de propriedade entre bancos de dados. Os dois procedimentos armazenados com SQL dinâmico obtêm o mesmo erro que o SQL direto devido ao SQL dinâmico quebrar a cadeia de propriedade.

    • O Teste 2 mostra que, quando apenas DB_CHAININGestá definido como ON, o procedimento armazenado getSecrets(sem representação ou SQL dinâmico) funciona conforme desejado entre os bancos de dados. Mas o getSecretsWithDynamicSqlprocedimento armazenado falha devido ao SQL dinâmico quebrar a cadeia de propriedade, portanto, não pode se beneficiar do encadeamento de propriedade entre bancos de dados.

    • O teste 3 mostra que, quando apenas TRUSTWORTHYestá definido como ON(apenas para o banco de dados "Fonte"), o código usando representação (ou seja EXECUTE AS, ), com ou sem SQL dinâmico, funciona conforme desejado entre os bancos de dados. Mas o código que não usa a representação não funciona, como no Teste 1. É claro que não queremos , pois é um risco à segurança TRUSTWORTHY. ONEste teste é apenas para mostrar como as coisas costumavam ser, antes da assinatura do módulo (ou seja, isso TRUSTWORTHYé necessário ao usar a representação, que por sua vez é necessária ao usar o SQL dinâmico).

    • O teste 4 mostra que quando ambos DB_CHAINING e TRUSTWORTHY estão definidos como ON, o código que não usa SQL dinâmico funciona sem precisar de representação e qualquer código que usa representação, com ou sem SQL dinâmico, funciona como desejado entre os bancos de dados. Mas, novamente, não queremos TRUSTWORTHYser ON, pois é um risco de segurança. Este teste é apenas para mostrar como as coisas costumavam ser, antes da assinatura do módulo.

    • O Teste 5 vira DB_CHAINING e TRUSTWORTHY volta para OFF, cria o Certificado e o Login e Usuários associados e assina os dois procedimentos armazenados que não estão usando Representação (porque não há mais necessidade de usar Representação). Ambos os procedimentos armazenados assinados funcionam como pretendido :-).

    • O Teste 6 remove os dois procedimentos armazenados que usaram representação e até remove o logon "escalado" e os usuários associados que estavam sendo representados. A execução do Teste 5 novamente prova que tudo o que é necessário é a assinatura do módulo (e é por isso que é uma maneira super legal de controlar as permissões :-).

    O roteiro de teste:

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

            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


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

    Representação vs. assinatura de módulo

    A parte que não entendo é por que, ao executar sob representação, a assinatura do módulo não funciona. ... E a representação faz com que o contexto de assinatura do módulo mude para "negar apenas"?

    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

relate perguntas