/*
 ---- Proc Inserts via Dynamic SQL into Table with Trigger that Inserts into Other Table ----

Created By: Solomon Rutzky / Sql Quantum Leap
Created On: 2019-05-10
Updated On: n/a

Location:          https://pastebin.com/ALgLuZAP
Related blog post: https://SqlQuantumLeap.com/2017/12/30/please-please-please-stop-using-impersonation-execute-as/
---------------------------------------------------------------------------------------------
*/

-- =========================================================================
-- ====                                                                 ====
-- ====  This script is a step-by-step walk-through of a common         ====
-- ====  scenario in which additional permissions are needed due to     ====
-- ====  the use of Dynamic SQL. But, instead of using EXECUTE AS       ====
-- ====  (i.e. impersonation), we will use Module Signing to get only   ====
-- ====  one or two additional permissions. As you will see, setting    ====
-- ====  up the Certificate is fairly easy to do.                       ====
-- ====                                                                 ====
-- ====  For more info, please visit: https://ModuleSigning.Info/       ====
-- ====                                                                 ====
-- =========================================================================

GO
-- https://SqlQuantumLeap.com/2018/12/28/prevent-full-script-execution-understanding-and-using-parseonly-and-noexec/
SET PARSEONLY ON;
GO

----------------------------------------------------------------
-- SETUP
----------------------------------------------------------------
USE [tempdb];

-- Initial target table:
IF (OBJECT_ID(N'dbo.Step2') IS NULL)
BEGIN
    PRINT 'Creating table [Step2]...';
    CREATE TABLE dbo.Step2
    (
        [Step2ID]    INT NOT NULL IDENTITY(1, 1)
                         CONSTRAINT [PK_Step2] PRIMARY KEY,
        [SomeValue]  VARCHAR(50) NOT NULL,
        [InsertTime] DATETIME NOT NULL
                         CONSTRAINT [DF_Step2_InsertTime] DEFAULT (GETDATE())
    );
END;


-- Destination / audit table:
IF (OBJECT_ID(N'dbo.Step4') IS NULL)
BEGIN
    PRINT 'Creating table [Step4]...';
    CREATE TABLE dbo.Step4
    (
        [Step4ID]    INT NOT NULL IDENTITY(1, 1)
                         CONSTRAINT [PK_Step4] PRIMARY KEY,
        [TableName]  sysname,
        [Operation]  CHAR(1) NOT NULL,
        [ID]         INT NOT NULL,
        [NewValue]   VARCHAR(50) NULL,
        [InsertTime] DATETIME NOT NULL
                         CONSTRAINT [DF_Step4_InsertTime] DEFAULT (GETDATE())
    );
END;


-- Audit Trigger on dbo.Step2 that inserts into dbo.Step4:
IF (OBJECT_ID(N'dbo.Step3') IS NULL)
BEGIN
    PRINT 'Creating trigger [Step3]...';
    -- DROP TRIGGER dbo.[Step3];
    EXEC (N'
CREATE TRIGGER dbo.Step3 ON dbo.Step2
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON;

IF (EXISTS(SELECT * FROM inserted))
BEGIN

    IF (EXISTS(SELECT * FROM inserted WHERE [SomeValue] = ''dynamic''))
    BEGIN
        -- Dynamic SQL cannot see "inserted" pseudo-table
        SELECT *
        INTO   #Inserted
        FROM   inserted;

        -- Break ownership-chain (again!)
        EXEC sp_executesql
            N''INSERT INTO dbo.Step4 ([TableName], [Operation], [ID], [NewValue])
            SELECT @TableName_tmp, @Operation_tmp, ins.[Step2ID], ins.[SomeValue]
            FROM   #Inserted ins;'',
            N''@TableName_tmp sysname, @Operation_tmp CHAR(1)'',
            @TableName_tmp = N''dbo.Step2'',
            @Operation_tmp = ''I'';
    END;
    ELSE
    BEGIN
        INSERT INTO dbo.Step4 ([TableName], [Operation], [ID], [NewValue])
            SELECT N''dbo.Step2'', ''I'', ins.[Step2ID], ins.[SomeValue]
            FROM   inserted ins;
    END;
END;
');
END;


-- Stored Procedure that inserts into dbo.Step2 (in Dynamic SQL):
IF (OBJECT_ID(N'dbo.Step1') IS NULL)
BEGIN
    PRINT 'Creating stored procedure [Step1]...';
    EXEC (N'
CREATE PROCEDURE dbo.Step1
(
    @SomeValue VARCHAR(50),
    @ExtraRows TINYINT = 0 -- test set-based inserts
)
AS
SET NOCOUNT ON;

-- Dynamic SQL breaks ownership chain:
EXEC sp_executesql
    N''INSERT INTO dbo.Step2 ([SomeValue]) VALUES (@SomeValue_tmp);'',
    N''@SomeValue_tmp VARCHAR(50)'',
    @SomeValue_tmp = @SomeValue;

IF (@ExtraRows > 0)
BEGIN
    EXEC sp_executesql
        N''INSERT INTO dbo.Step2 ([SomeValue])
            SELECT TOP (@ExtraRows_tmp) CONVERT(VARCHAR(50), NEWID())
            FROM   master.sys.columns;'',
        N''@ExtraRows_tmp TINYINT'',
        @ExtraRows_tmp = @ExtraRows;
END;
');
END;


-- Test Login:
IF (SUSER_ID(N'PermissionsTestLogin') IS NULL)
BEGIN
    PRINT 'Creating login [PermissionsTestLogin]...';
    CREATE LOGIN [PermissionsTestLogin] WITH PASSWORD = 'A So-So Password 1234';
END;


-- Test User:
IF (DATABASE_PRINCIPAL_ID(N'PermissionsTestUser') IS NULL)
BEGIN
    PRINT 'Creating user [PermissionsTestUser]...';
    CREATE USER [PermissionsTestUser] FOR LOGIN [PermissionsTestLogin];
END;


GO
----------------------------------------------------------------
-- TEST 1: ensure no extra security results in an error
----------------------------------------------------------------

EXECUTE AS LOGIN = N'PermissionsTestLogin';

SELECT ORIGINAL_LOGIN() AS [OriginalLogin],
       SUSER_NAME() AS [CurrentLogin],
       SESSION_USER AS [SessionUser],
       DATABASE_PRINCIPAL_ID() AS [DBPrincipalID];
-- OriginalLogin       CurrentLogin             SessionUser            DBPrincipalID
-- ALBRIGHT\Solomon    PermissionsTestLogin     PermissionsTestUser    5


INSERT INTO dbo.Step2 ([SomeValue]) VALUES ('should error');
/*
Msg 229, Level 14, State 5, Line XXXXX
The INSERT permission was denied on the object 'Step2', database 'tempdb', schema 'dbo'.
*/



INSERT INTO dbo.Step4 ([TableName], [Operation], [ID], [NewValue])
VALUES ('should error', 'e', -1, 'error');
/*
Msg 229, Level 14, State 5, Line XXXXX
The INSERT permission was denied on the object 'Step4', database 'tempdb', schema 'dbo'.
*/


EXEC dbo.Step1 'this should error';
/*
Msg 229, Level 14, State 5, Procedure dbo.Step1, Line XXXXX [Batch Start Line YYYYY]
The EXECUTE permission was denied on the object 'Step1', database 'tempdb', schema 'dbo'.
*/


REVERT;
SELECT SESSION_USER AS [SessionUser];
-- dbo


GO
----------------------------------------------------------------
-- TEST 2: grant permission to the stored procedure
----------------------------------------------------------------

GRANT EXECUTE ON dbo.Step1 TO [PermissionsTestUser];


EXECUTE AS LOGIN = N'PermissionsTestLogin';

SELECT ORIGINAL_LOGIN() AS [OriginalLogin],
       SUSER_NAME() AS [CurrentLogin],
       SESSION_USER AS [SessionUser],
       DATABASE_PRINCIPAL_ID() AS [DBPrincipalID];
-- OriginalLogin       CurrentLogin             SessionUser            DBPrincipalID
-- ALBRIGHT\Solomon    PermissionsTestLogin     PermissionsTestUser    5


-- Same outcome as before:
INSERT INTO dbo.Step2 ([SomeValue]) VALUES ('should error');
/*
Msg 229, Level 14, State 5, Line XXXXX
The INSERT permission was denied on the object 'Step2', database 'tempdb', schema 'dbo'.
*/


-- Same outcome as before:
INSERT INTO dbo.Step4 ([TableName], [Operation], [ID], [NewValue])
VALUES ('should error', 'e', -1, 'error');
/*
Msg 229, Level 14, State 5, Line XXXXX
The INSERT permission was denied on the object 'Step4', database 'tempdb', schema 'dbo'.
*/


-- DIFFERENT:
EXEC dbo.Step1 'this should also error';
/*
Msg 229, Level 14, State 5, Line XXXXX
The INSERT permission was denied on the object 'Step2', database 'tempdb', schema 'dbo'.
*/


REVERT;
SELECT SESSION_USER AS [SessionUser];
-- dbo


GO
----------------------------------------------------------------
-- TEST 3: use module signing to allow proc to insert into dbo.Step2
----------------------------------------------------------------


------------------------------
IF (CERT_ID(N'PermissionsTestCert') IS NULL)
BEGIN
    PRINT 'Creating certificate [PermissionsTestCert]...';
    CREATE CERTIFICATE [PermissionsTestCert]
        ENCRYPTION BY PASSWORD = 'simple cert password, y0!'
        WITH SUBJECT = 'avoid impersonation / TRUSTWORTHY ON / cross-DB ownership chaining',
        EXPIRY_DATE = '2099-10-31';
END;


IF (DATABASE_PRINCIPAL_ID(N'PermissionsTestCertUser') IS NULL)
BEGIN
    PRINT 'Creating user [PermissionsTestCertUser]...';
    CREATE USER [PermissionsTestCertUser] FROM CERTIFICATE [PermissionsTestCert];
END;

GRANT INSERT ON dbo.Step2 TO [PermissionsTestCertUser];


-- Associate the insert permission (given to [PermissionsTestCertUser]) with the stored procedure:
IF (NOT EXISTS(
    SELECT *
    FROM   sys.crypt_properties sig
    INNER JOIN sys.certificates crt
            ON crt.[thumbprint] = sig.[thumbprint]
    WHERE  sig.[major_id] = OBJECT_ID(N'dbo.Step1')
    AND    crt.[name] = N'PermissionsTestCert'
    ))
BEGIN
    PRINT 'Adding signature to [Step1]...';
    ADD SIGNATURE TO dbo.[Step1]
        BY CERTIFICATE [PermissionsTestCert]
        WITH PASSWORD = 'simple cert password, y0!';
END;
------------------------------


EXECUTE AS LOGIN = N'PermissionsTestLogin';

SELECT ORIGINAL_LOGIN() AS [OriginalLogin],
       SUSER_NAME() AS [CurrentLogin],
       SESSION_USER AS [SessionUser],
       DATABASE_PRINCIPAL_ID() AS [DBPrincipalID];
-- OriginalLogin       CurrentLogin             SessionUser            DBPrincipalID
-- ALBRIGHT\Solomon    PermissionsTestLogin     PermissionsTestUser    5


-- Same outcome as before:
INSERT INTO dbo.Step2 ([SomeValue]) VALUES ('should error');
/*
Msg 229, Level 14, State 5, Line XXXXX
The INSERT permission was denied on the object 'Step2', database 'tempdb', schema 'dbo'.
*/


-- Same outcome as before:
INSERT INTO dbo.Step4 ([TableName], [Operation], [ID], [NewValue])
VALUES ('should error', 'e', -1, 'error');
/*
Msg 229, Level 14, State 5, Line XXXXX
The INSERT permission was denied on the object 'Step4', database 'tempdb', schema 'dbo'.
*/


-- DIFFERENT:
EXEC dbo.Step1 'this should work';
-- SUCCESS!!


-- DIFFERENT:
EXEC dbo.Step1 'this should also work', 3;
-- SUCCESS!!


SELECT * FROM dbo.[Step2];
/*
Msg 229, Level 14, State 5, Line XXXXX
The SELECT permission was denied on the object 'Step2', database 'tempdb', schema 'dbo'.
*/


SELECT * FROM dbo.[Step4];
/*
Msg 229, Level 14, State 5, Line XXXXX
The SELECT permission was denied on the object 'Step4', database 'tempdb', schema 'dbo'.
*/


REVERT;
SELECT SESSION_USER AS [SessionUser];
-- dbo


SELECT * FROM dbo.[Step2];
-- 6 rows


SELECT * FROM dbo.[Step4];
-- 6 rows


-- =========================================================================
-- ====                          CONCLUSION #1                          ====
-- ====                                                                 ====
-- ====  Bare-minimum requirements:                                     ====
-- ====  1) Sign Stored Procedure                                       ====
-- ====  2) GRANT signature-based User INSERT on main Table             ====
-- ====                                                                 ====
-- ====  IF Trigger uses static SQL then no additional requirements as  ====
-- ====     Ownership-Chaining implicitly grants DML permissions on     ====
-- ====     objects owned by the same database principal that owns      ====
-- ====     the Trigger.                                                ====
-- ====                                                                 ====
-- ====  IF Trigger uses Dynamic SQL then additional steps are needed   ====
-- ====     as Dynamic SQL breaks Ownership-Chaining. Proceed with      ====
-- ====     following tests for details.                                ====
-- ====                                                                 ====
-- =========================================================================

GO
----------------------------------------------------------------
-- TEST 4: check extent / scope of signature-based permissions
----------------------------------------------------------------


EXECUTE AS LOGIN = N'PermissionsTestLogin';

SELECT ORIGINAL_LOGIN() AS [OriginalLogin],
       SUSER_NAME() AS [CurrentLogin],
       SESSION_USER AS [SessionUser],
       DATABASE_PRINCIPAL_ID() AS [DBPrincipalID];
-- OriginalLogin       CurrentLogin             SessionUser            DBPrincipalID
-- ALBRIGHT\Solomon    PermissionsTestLogin     PermissionsTestUser    5



-- DIFFERENT:
EXEC dbo.Step1 'dynamic';
/*
Msg 229, Level 14, State 5, Line XXXXX
The INSERT permission was denied on the object 'Step4', database 'tempdb', schema 'dbo'.
*/


REVERT;
SELECT SESSION_USER AS [SessionUser];
-- dbo


SELECT * FROM dbo.[Step2];
-- 6 rows


SELECT * FROM dbo.[Step4];
-- 6 rows


GO
----------------------------------------------------------------
-- TEST 5: grant permission to certificate
----------------------------------------------------------------


------------------------------
GRANT INSERT ON dbo.Step4 TO [PermissionsTestCertUser];
------------------------------


EXECUTE AS LOGIN = N'PermissionsTestLogin';

SELECT ORIGINAL_LOGIN() AS [OriginalLogin],
       SUSER_NAME() AS [CurrentLogin],
       SESSION_USER AS [SessionUser],
       DATABASE_PRINCIPAL_ID() AS [DBPrincipalID];
-- OriginalLogin       CurrentLogin             SessionUser            DBPrincipalID
-- ALBRIGHT\Solomon    PermissionsTestLogin     PermissionsTestUser    5



-- DIFFERENT:
EXEC dbo.Step1 'dynamic';
/*
Msg 229, Level 14, State 5, Line XXXXX
The INSERT permission was denied on the object 'Step4', database 'tempdb', schema 'dbo'.
*/


REVERT;
SELECT SESSION_USER AS [SessionUser];
-- dbo


SELECT * FROM dbo.[Step2];
-- 6 rows


SELECT * FROM dbo.[Step4];
-- 6 rows


GO
----------------------------------------------------------------
-- TEST 6: add signature to trigger to handle Dynamic SQL
----------------------------------------------------------------


------------------------------
-- Associate the insert permission (given to [PermissionsTestCertUser]) with the trigger:
IF (NOT EXISTS(
    SELECT *
    FROM   sys.crypt_properties sig
    INNER JOIN sys.certificates crt
            ON crt.[thumbprint] = sig.[thumbprint]
    WHERE  sig.[major_id] = OBJECT_ID(N'dbo.Step3')
    AND    crt.[name] = N'PermissionsTestCert'
    ))
BEGIN
    PRINT 'Adding signature to [Step3]...';
    ADD SIGNATURE TO dbo.[Step3]
        BY CERTIFICATE [PermissionsTestCert]
        WITH PASSWORD = 'simple cert password, y0!';
END;
------------------------------



EXECUTE AS LOGIN = N'PermissionsTestLogin';

SELECT ORIGINAL_LOGIN() AS [OriginalLogin],
       SUSER_NAME() AS [CurrentLogin],
       SESSION_USER AS [SessionUser],
       DATABASE_PRINCIPAL_ID() AS [DBPrincipalID];
-- OriginalLogin       CurrentLogin             SessionUser            DBPrincipalID
-- ALBRIGHT\Solomon    PermissionsTestLogin     PermissionsTestUser    5



-- DIFFERENT:
EXEC dbo.Step1 'dynamic';
-- SUCCESS!!


REVERT;
SELECT SESSION_USER AS [SessionUser];
-- dbo


SELECT * FROM dbo.[Step2];
-- 7 rows


SELECT * FROM dbo.[Step4];
-- 7 rows


-- =========================================================================
-- ====                          CONCLUSION #2                          ====
-- ====                                                                 ====
-- ====  Additional requirements (ONLY IF Trigger uses Dynamic SQL):    ====
-- ====  3) Sign Trigger                                                ====
-- ====  4) GRANT signature-based User INSERT on secondary Table        ====
-- ====                                                                 ====
-- =========================================================================

GO
----------------------------------------------------------------
-- CLEAN UP
----------------------------------------------------------------

/* -- Highlight to execute:

DROP PROCEDURE dbo.Step1;
DROP TABLE dbo.Step2;
DROP TABLE dbo.Step4;

DROP USER [PermissionsTestCertUser];
DROP CERTIFICATE [PermissionsTestCert];

*/


GO
SET PARSEONLY OFF;
RAISERROR('
	This script is not meant to run all at once.
	Please highlight each section individually to execute.', 16, 1);
GO