Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- ---- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement