SqlQuantumLeap

T-SQL Module Signing demo: Dynamic SQL and Trigger

May 13th, 2019
116
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

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×