SHARE
TWEET

T-SQL Module Signing demo: Dynamic SQL and Trigger

SqlQuantumLeap May 13th, 2019 (edited) 7 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.  ---- Proc Inserts via Dynamic SQL into Table with Trigger that Inserts into Other Table ----
  3.  
  4. Created By: Solomon Rutzky / Sql Quantum Leap
  5. Created On: 2019-05-10
  6. Updated On: n/a
  7.  
  8. Location:          https://pastebin.com/ALgLuZAP
  9. Related blog post: https://SqlQuantumLeap.com/2017/12/30/please-please-please-stop-using-impersonation-execute-as/
  10. ---------------------------------------------------------------------------------------------
  11. */
  12.  
  13. -- =========================================================================
  14. -- ====                                                                 ====
  15. -- ====  This script is a step-by-step walk-through of a common         ====
  16. -- ====  scenario in which additional permissions are needed due to     ====
  17. -- ====  the use of Dynamic SQL. But, instead of using EXECUTE AS       ====
  18. -- ====  (i.e. impersonation), we will use Module Signing to get only   ====
  19. -- ====  one or two additional permissions. As you will see, setting    ====
  20. -- ====  up the Certificate is fairly easy to do.                       ====
  21. -- ====                                                                 ====
  22. -- ====  For more info, please visit: https://ModuleSigning.Info/       ====
  23. -- ====                                                                 ====
  24. -- =========================================================================
  25.  
  26. GO
  27. -- https://SqlQuantumLeap.com/2018/12/28/prevent-full-script-execution-understanding-and-using-parseonly-and-noexec/
  28. SET PARSEONLY ON;
  29. GO
  30.  
  31. ----------------------------------------------------------------
  32. -- SETUP
  33. ----------------------------------------------------------------
  34. USE [tempdb];
  35.  
  36. -- Initial target table:
  37. IF (OBJECT_ID(N'dbo.Step2') IS NULL)
  38. BEGIN
  39.     PRINT 'Creating table [Step2]...';
  40.     CREATE TABLE dbo.Step2
  41.     (
  42.         [Step2ID]    INT NOT NULL IDENTITY(1, 1)
  43.                          CONSTRAINT [PK_Step2] PRIMARY KEY,
  44.         [SomeValue]  VARCHAR(50) NOT NULL,
  45.         [InsertTime] DATETIME NOT NULL
  46.                          CONSTRAINT [DF_Step2_InsertTime] DEFAULT (GETDATE())
  47.     );
  48. END;
  49.  
  50.  
  51. -- Destination / audit table:
  52. IF (OBJECT_ID(N'dbo.Step4') IS NULL)
  53. BEGIN
  54.     PRINT 'Creating table [Step4]...';
  55.     CREATE TABLE dbo.Step4
  56.     (
  57.         [Step4ID]    INT NOT NULL IDENTITY(1, 1)
  58.                          CONSTRAINT [PK_Step4] PRIMARY KEY,
  59.         [TableName]  sysname,
  60.         [Operation]  CHAR(1) NOT NULL,
  61.         [ID]         INT NOT NULL,
  62.         [NewValue]   VARCHAR(50) NULL,
  63.         [InsertTime] DATETIME NOT NULL
  64.                          CONSTRAINT [DF_Step4_InsertTime] DEFAULT (GETDATE())
  65.     );
  66. END;
  67.  
  68.  
  69. -- Audit Trigger on dbo.Step2 that inserts into dbo.Step4:
  70. IF (OBJECT_ID(N'dbo.Step3') IS NULL)
  71. BEGIN
  72.     PRINT 'Creating trigger [Step3]...';
  73.     -- DROP TRIGGER dbo.[Step3];
  74.     EXEC (N'
  75. CREATE TRIGGER dbo.Step3 ON dbo.Step2
  76. AFTER INSERT, UPDATE, DELETE
  77. AS
  78. SET NOCOUNT ON;
  79.  
  80. IF (EXISTS(SELECT * FROM inserted))
  81. BEGIN
  82.  
  83.    IF (EXISTS(SELECT * FROM inserted WHERE [SomeValue] = ''dynamic''))
  84.    BEGIN
  85.        -- Dynamic SQL cannot see "inserted" pseudo-table
  86.        SELECT *
  87.        INTO   #Inserted
  88.        FROM   inserted;
  89.  
  90.        -- Break ownership-chain (again!)
  91.        EXEC sp_executesql
  92.            N''INSERT INTO dbo.Step4 ([TableName], [Operation], [ID], [NewValue])
  93.            SELECT @TableName_tmp, @Operation_tmp, ins.[Step2ID], ins.[SomeValue]
  94.            FROM   #Inserted ins;'',
  95.            N''@TableName_tmp sysname, @Operation_tmp CHAR(1)'',
  96.            @TableName_tmp = N''dbo.Step2'',
  97.            @Operation_tmp = ''I'';
  98.    END;
  99.    ELSE
  100.    BEGIN
  101.        INSERT INTO dbo.Step4 ([TableName], [Operation], [ID], [NewValue])
  102.            SELECT N''dbo.Step2'', ''I'', ins.[Step2ID], ins.[SomeValue]
  103.            FROM   inserted ins;
  104.    END;
  105. END;
  106. ');
  107. END;
  108.  
  109.  
  110. -- Stored Procedure that inserts into dbo.Step2 (in Dynamic SQL):
  111. IF (OBJECT_ID(N'dbo.Step1') IS NULL)
  112. BEGIN
  113.     PRINT 'Creating stored procedure [Step1]...';
  114.     EXEC (N'
  115. CREATE PROCEDURE dbo.Step1
  116. (
  117.    @SomeValue VARCHAR(50),
  118.    @ExtraRows TINYINT = 0 -- test set-based inserts
  119. )
  120. AS
  121. SET NOCOUNT ON;
  122.  
  123. -- Dynamic SQL breaks ownership chain:
  124. EXEC sp_executesql
  125.    N''INSERT INTO dbo.Step2 ([SomeValue]) VALUES (@SomeValue_tmp);'',
  126.    N''@SomeValue_tmp VARCHAR(50)'',
  127.    @SomeValue_tmp = @SomeValue;
  128.  
  129. IF (@ExtraRows > 0)
  130. BEGIN
  131.    EXEC sp_executesql
  132.        N''INSERT INTO dbo.Step2 ([SomeValue])
  133.            SELECT TOP (@ExtraRows_tmp) CONVERT(VARCHAR(50), NEWID())
  134.            FROM   master.sys.columns;'',
  135.        N''@ExtraRows_tmp TINYINT'',
  136.        @ExtraRows_tmp = @ExtraRows;
  137. END;
  138. ');
  139. END;
  140.  
  141.  
  142. -- Test Login:
  143. IF (SUSER_ID(N'PermissionsTestLogin') IS NULL)
  144. BEGIN
  145.     PRINT 'Creating login [PermissionsTestLogin]...';
  146.     CREATE LOGIN [PermissionsTestLogin] WITH PASSWORD = 'A So-So Password 1234';
  147. END;
  148.  
  149.  
  150. -- Test User:
  151. IF (DATABASE_PRINCIPAL_ID(N'PermissionsTestUser') IS NULL)
  152. BEGIN
  153.     PRINT 'Creating user [PermissionsTestUser]...';
  154.     CREATE USER [PermissionsTestUser] FOR LOGIN [PermissionsTestLogin];
  155. END;
  156.  
  157.  
  158. GO
  159. ----------------------------------------------------------------
  160. -- TEST 1: ensure no extra security results in an error
  161. ----------------------------------------------------------------
  162.  
  163. EXECUTE AS LOGIN = N'PermissionsTestLogin';
  164.  
  165. SELECT ORIGINAL_LOGIN() AS [OriginalLogin],
  166.        SUSER_NAME() AS [CurrentLogin],
  167.        SESSION_USER AS [SessionUser],
  168.        DATABASE_PRINCIPAL_ID() AS [DBPrincipalID];
  169. -- OriginalLogin       CurrentLogin             SessionUser            DBPrincipalID
  170. -- ALBRIGHT\Solomon    PermissionsTestLogin     PermissionsTestUser    5
  171.  
  172.  
  173. INSERT INTO dbo.Step2 ([SomeValue]) VALUES ('should error');
  174. /*
  175. Msg 229, Level 14, State 5, Line XXXXX
  176. The INSERT permission was denied on the object 'Step2', database 'tempdb', schema 'dbo'.
  177. */
  178.  
  179.  
  180.  
  181. INSERT INTO dbo.Step4 ([TableName], [Operation], [ID], [NewValue])
  182. VALUES ('should error', 'e', -1, 'error');
  183. /*
  184. Msg 229, Level 14, State 5, Line XXXXX
  185. The INSERT permission was denied on the object 'Step4', database 'tempdb', schema 'dbo'.
  186. */
  187.  
  188.  
  189. EXEC dbo.Step1 'this should error';
  190. /*
  191. Msg 229, Level 14, State 5, Procedure dbo.Step1, Line XXXXX [Batch Start Line YYYYY]
  192. The EXECUTE permission was denied on the object 'Step1', database 'tempdb', schema 'dbo'.
  193. */
  194.  
  195.  
  196. REVERT;
  197. SELECT SESSION_USER AS [SessionUser];
  198. -- dbo
  199.  
  200.  
  201. GO
  202. ----------------------------------------------------------------
  203. -- TEST 2: grant permission to the stored procedure
  204. ----------------------------------------------------------------
  205.  
  206. GRANT EXECUTE ON dbo.Step1 TO [PermissionsTestUser];
  207.  
  208.  
  209. EXECUTE AS LOGIN = N'PermissionsTestLogin';
  210.  
  211. SELECT ORIGINAL_LOGIN() AS [OriginalLogin],
  212.        SUSER_NAME() AS [CurrentLogin],
  213.        SESSION_USER AS [SessionUser],
  214.        DATABASE_PRINCIPAL_ID() AS [DBPrincipalID];
  215. -- OriginalLogin       CurrentLogin             SessionUser            DBPrincipalID
  216. -- ALBRIGHT\Solomon    PermissionsTestLogin     PermissionsTestUser    5
  217.  
  218.  
  219. -- Same outcome as before:
  220. INSERT INTO dbo.Step2 ([SomeValue]) VALUES ('should error');
  221. /*
  222. Msg 229, Level 14, State 5, Line XXXXX
  223. The INSERT permission was denied on the object 'Step2', database 'tempdb', schema 'dbo'.
  224. */
  225.  
  226.  
  227. -- Same outcome as before:
  228. INSERT INTO dbo.Step4 ([TableName], [Operation], [ID], [NewValue])
  229. VALUES ('should error', 'e', -1, 'error');
  230. /*
  231. Msg 229, Level 14, State 5, Line XXXXX
  232. The INSERT permission was denied on the object 'Step4', database 'tempdb', schema 'dbo'.
  233. */
  234.  
  235.  
  236. -- DIFFERENT:
  237. EXEC dbo.Step1 'this should also error';
  238. /*
  239. Msg 229, Level 14, State 5, Line XXXXX
  240. The INSERT permission was denied on the object 'Step2', database 'tempdb', schema 'dbo'.
  241. */
  242.  
  243.  
  244. REVERT;
  245. SELECT SESSION_USER AS [SessionUser];
  246. -- dbo
  247.  
  248.  
  249. GO
  250. ----------------------------------------------------------------
  251. -- TEST 3: use module signing to allow proc to insert into dbo.Step2
  252. ----------------------------------------------------------------
  253.  
  254.  
  255. ------------------------------
  256. IF (CERT_ID(N'PermissionsTestCert') IS NULL)
  257. BEGIN
  258.     PRINT 'Creating certificate [PermissionsTestCert]...';
  259.     CREATE CERTIFICATE [PermissionsTestCert]
  260.         ENCRYPTION BY PASSWORD = 'simple cert password, y0!'
  261.         WITH SUBJECT = 'avoid impersonation / TRUSTWORTHY ON / cross-DB ownership chaining',
  262.         EXPIRY_DATE = '2099-10-31';
  263. END;
  264.  
  265.  
  266. IF (DATABASE_PRINCIPAL_ID(N'PermissionsTestCertUser') IS NULL)
  267. BEGIN
  268.     PRINT 'Creating user [PermissionsTestCertUser]...';
  269.     CREATE USER [PermissionsTestCertUser] FROM CERTIFICATE [PermissionsTestCert];
  270. END;
  271.  
  272. GRANT INSERT ON dbo.Step2 TO [PermissionsTestCertUser];
  273.  
  274.  
  275. -- Associate the insert permission (given to [PermissionsTestCertUser]) with the stored procedure:
  276. IF (NOT EXISTS(
  277.     SELECT *
  278.     FROM   sys.crypt_properties sig
  279.     INNER JOIN sys.certificates crt
  280.             ON crt.[thumbprint] = sig.[thumbprint]
  281.     WHERE  sig.[major_id] = OBJECT_ID(N'dbo.Step1')
  282.     AND    crt.[name] = N'PermissionsTestCert'
  283.     ))
  284. BEGIN
  285.     PRINT 'Adding signature to [Step1]...';
  286.     ADD SIGNATURE TO dbo.[Step1]
  287.         BY CERTIFICATE [PermissionsTestCert]
  288.         WITH PASSWORD = 'simple cert password, y0!';
  289. END;
  290. ------------------------------
  291.  
  292.  
  293. EXECUTE AS LOGIN = N'PermissionsTestLogin';
  294.  
  295. SELECT ORIGINAL_LOGIN() AS [OriginalLogin],
  296.        SUSER_NAME() AS [CurrentLogin],
  297.        SESSION_USER AS [SessionUser],
  298.        DATABASE_PRINCIPAL_ID() AS [DBPrincipalID];
  299. -- OriginalLogin       CurrentLogin             SessionUser            DBPrincipalID
  300. -- ALBRIGHT\Solomon    PermissionsTestLogin     PermissionsTestUser    5
  301.  
  302.  
  303. -- Same outcome as before:
  304. INSERT INTO dbo.Step2 ([SomeValue]) VALUES ('should error');
  305. /*
  306. Msg 229, Level 14, State 5, Line XXXXX
  307. The INSERT permission was denied on the object 'Step2', database 'tempdb', schema 'dbo'.
  308. */
  309.  
  310.  
  311. -- Same outcome as before:
  312. INSERT INTO dbo.Step4 ([TableName], [Operation], [ID], [NewValue])
  313. VALUES ('should error', 'e', -1, 'error');
  314. /*
  315. Msg 229, Level 14, State 5, Line XXXXX
  316. The INSERT permission was denied on the object 'Step4', database 'tempdb', schema 'dbo'.
  317. */
  318.  
  319.  
  320. -- DIFFERENT:
  321. EXEC dbo.Step1 'this should work';
  322. -- SUCCESS!!
  323.  
  324.  
  325. -- DIFFERENT:
  326. EXEC dbo.Step1 'this should also work', 3;
  327. -- SUCCESS!!
  328.  
  329.  
  330. SELECT * FROM dbo.[Step2];
  331. /*
  332. Msg 229, Level 14, State 5, Line XXXXX
  333. The SELECT permission was denied on the object 'Step2', database 'tempdb', schema 'dbo'.
  334. */
  335.  
  336.  
  337. SELECT * FROM dbo.[Step4];
  338. /*
  339. Msg 229, Level 14, State 5, Line XXXXX
  340. The SELECT permission was denied on the object 'Step4', database 'tempdb', schema 'dbo'.
  341. */
  342.  
  343.  
  344. REVERT;
  345. SELECT SESSION_USER AS [SessionUser];
  346. -- dbo
  347.  
  348.  
  349. SELECT * FROM dbo.[Step2];
  350. -- 6 rows
  351.  
  352.  
  353. SELECT * FROM dbo.[Step4];
  354. -- 6 rows
  355.  
  356.  
  357. -- =========================================================================
  358. -- ====                          CONCLUSION #1                          ====
  359. -- ====                                                                 ====
  360. -- ====  Bare-minimum requirements:                                     ====
  361. -- ====  1) Sign Stored Procedure                                       ====
  362. -- ====  2) GRANT signature-based User INSERT on main Table             ====
  363. -- ====                                                                 ====
  364. -- ====  IF Trigger uses static SQL then no additional requirements as  ====
  365. -- ====     Ownership-Chaining implicitly grants DML permissions on     ====
  366. -- ====     objects owned by the same database principal that owns      ====
  367. -- ====     the Trigger.                                                ====
  368. -- ====                                                                 ====
  369. -- ====  IF Trigger uses Dynamic SQL then additional steps are needed   ====
  370. -- ====     as Dynamic SQL breaks Ownership-Chaining. Proceed with      ====
  371. -- ====     following tests for details.                                ====
  372. -- ====                                                                 ====
  373. -- =========================================================================
  374.  
  375. GO
  376. ----------------------------------------------------------------
  377. -- TEST 4: check extent / scope of signature-based permissions
  378. ----------------------------------------------------------------
  379.  
  380.  
  381. EXECUTE AS LOGIN = N'PermissionsTestLogin';
  382.  
  383. SELECT ORIGINAL_LOGIN() AS [OriginalLogin],
  384.        SUSER_NAME() AS [CurrentLogin],
  385.        SESSION_USER AS [SessionUser],
  386.        DATABASE_PRINCIPAL_ID() AS [DBPrincipalID];
  387. -- OriginalLogin       CurrentLogin             SessionUser            DBPrincipalID
  388. -- ALBRIGHT\Solomon    PermissionsTestLogin     PermissionsTestUser    5
  389.  
  390.  
  391.  
  392. -- DIFFERENT:
  393. EXEC dbo.Step1 'dynamic';
  394. /*
  395. Msg 229, Level 14, State 5, Line XXXXX
  396. The INSERT permission was denied on the object 'Step4', database 'tempdb', schema 'dbo'.
  397. */
  398.  
  399.  
  400. REVERT;
  401. SELECT SESSION_USER AS [SessionUser];
  402. -- dbo
  403.  
  404.  
  405. SELECT * FROM dbo.[Step2];
  406. -- 6 rows
  407.  
  408.  
  409. SELECT * FROM dbo.[Step4];
  410. -- 6 rows
  411.  
  412.  
  413. GO
  414. ----------------------------------------------------------------
  415. -- TEST 5: grant permission to certificate
  416. ----------------------------------------------------------------
  417.  
  418.  
  419. ------------------------------
  420. GRANT INSERT ON dbo.Step4 TO [PermissionsTestCertUser];
  421. ------------------------------
  422.  
  423.  
  424. EXECUTE AS LOGIN = N'PermissionsTestLogin';
  425.  
  426. SELECT ORIGINAL_LOGIN() AS [OriginalLogin],
  427.        SUSER_NAME() AS [CurrentLogin],
  428.        SESSION_USER AS [SessionUser],
  429.        DATABASE_PRINCIPAL_ID() AS [DBPrincipalID];
  430. -- OriginalLogin       CurrentLogin             SessionUser            DBPrincipalID
  431. -- ALBRIGHT\Solomon    PermissionsTestLogin     PermissionsTestUser    5
  432.  
  433.  
  434.  
  435. -- DIFFERENT:
  436. EXEC dbo.Step1 'dynamic';
  437. /*
  438. Msg 229, Level 14, State 5, Line XXXXX
  439. The INSERT permission was denied on the object 'Step4', database 'tempdb', schema 'dbo'.
  440. */
  441.  
  442.  
  443. REVERT;
  444. SELECT SESSION_USER AS [SessionUser];
  445. -- dbo
  446.  
  447.  
  448. SELECT * FROM dbo.[Step2];
  449. -- 6 rows
  450.  
  451.  
  452. SELECT * FROM dbo.[Step4];
  453. -- 6 rows
  454.  
  455.  
  456. GO
  457. ----------------------------------------------------------------
  458. -- TEST 6: add signature to trigger to handle Dynamic SQL
  459. ----------------------------------------------------------------
  460.  
  461.  
  462. ------------------------------
  463. -- Associate the insert permission (given to [PermissionsTestCertUser]) with the trigger:
  464. IF (NOT EXISTS(
  465.     SELECT *
  466.     FROM   sys.crypt_properties sig
  467.     INNER JOIN sys.certificates crt
  468.             ON crt.[thumbprint] = sig.[thumbprint]
  469.     WHERE  sig.[major_id] = OBJECT_ID(N'dbo.Step3')
  470.     AND    crt.[name] = N'PermissionsTestCert'
  471.     ))
  472. BEGIN
  473.     PRINT 'Adding signature to [Step3]...';
  474.     ADD SIGNATURE TO dbo.[Step3]
  475.         BY CERTIFICATE [PermissionsTestCert]
  476.         WITH PASSWORD = 'simple cert password, y0!';
  477. END;
  478. ------------------------------
  479.  
  480.  
  481.  
  482. EXECUTE AS LOGIN = N'PermissionsTestLogin';
  483.  
  484. SELECT ORIGINAL_LOGIN() AS [OriginalLogin],
  485.        SUSER_NAME() AS [CurrentLogin],
  486.        SESSION_USER AS [SessionUser],
  487.        DATABASE_PRINCIPAL_ID() AS [DBPrincipalID];
  488. -- OriginalLogin       CurrentLogin             SessionUser            DBPrincipalID
  489. -- ALBRIGHT\Solomon    PermissionsTestLogin     PermissionsTestUser    5
  490.  
  491.  
  492.  
  493. -- DIFFERENT:
  494. EXEC dbo.Step1 'dynamic';
  495. -- SUCCESS!!
  496.  
  497.  
  498. REVERT;
  499. SELECT SESSION_USER AS [SessionUser];
  500. -- dbo
  501.  
  502.  
  503. SELECT * FROM dbo.[Step2];
  504. -- 7 rows
  505.  
  506.  
  507. SELECT * FROM dbo.[Step4];
  508. -- 7 rows
  509.  
  510.  
  511. -- =========================================================================
  512. -- ====                          CONCLUSION #2                          ====
  513. -- ====                                                                 ====
  514. -- ====  Additional requirements (ONLY IF Trigger uses Dynamic SQL):    ====
  515. -- ====  3) Sign Trigger                                                ====
  516. -- ====  4) GRANT signature-based User INSERT on secondary Table        ====
  517. -- ====                                                                 ====
  518. -- =========================================================================
  519.  
  520. GO
  521. ----------------------------------------------------------------
  522. -- CLEAN UP
  523. ----------------------------------------------------------------
  524.  
  525. /* -- Highlight to execute:
  526.  
  527. DROP PROCEDURE dbo.Step1;
  528. DROP TABLE dbo.Step2;
  529. DROP TABLE dbo.Step4;
  530.  
  531. DROP USER [PermissionsTestCertUser];
  532. DROP CERTIFICATE [PermissionsTestCert];
  533.  
  534. */
  535.  
  536.  
  537. GO
  538. SET PARSEONLY OFF;
  539. RAISERROR('
  540.     This script is not meant to run all at once.
  541.     Please highlight each section individually to execute.', 16, 1);
  542. GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top