daily pastebin goal
14%
SHARE
TWEET

"CLR strict security" and PERMISSION_SET

SqlQuantumLeap Oct 28th, 2018 (edited) 16 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SET PARSEONLY ON;
  2. GO
  3. /*
  4.  --------- Effect of "CLR strict security" on PERMISSION_SET ---------
  5.  
  6. Created By: Solomon Rutzky / Sql Quantum Leap
  7. Created On: 2018-10-28
  8. Updated On: n/a
  9.  
  10. Location:          https://pastebin.com/ZQ3Lfjrg
  11. Related blog post: https://SqlQuantumLeap.com/2018/10/30/sqlclr-vs-sql-server-2017-part-9-does-permission_set-still-matter-or-is-everything-now-unsafe/
  12. ----------------------------------------------------------------------
  13. */
  14.  
  15.  
  16. --===========================================================================
  17. --======== Confirm Environment ==============================================
  18. --===========================================================================
  19.  
  20. -- Make sure we are using SQL Server 2017 (14.x) or newer:
  21. SELECT @@VERSION;
  22. /*
  23. Microsoft SQL Server vNext (CTP2.0) - 15.0.1000.34 (X64)
  24.     Sep 18 2018 02:32:04
  25.     Copyright (C) 2018 Microsoft Corporation
  26.     Enterprise Evaluation Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 17134: )
  27. */
  28.  
  29.  
  30. -- Yes, "CLR strict security" is an "advanced option", but
  31. -- "show advanced options" does not need to be "1" in order
  32. -- to see the row in sys.configurations.
  33.  
  34. SELECT cfg.[name], cfg.[value_in_use]
  35. FROM   sys.configurations cfg
  36. WHERE  cfg.[configuration_id] = 1587; -- clr strict security
  37. /*
  38. name                   value_in_use
  39. clr strict security    1
  40. */
  41.  
  42. -- EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
  43. -- EXEC sp_configure 'CLR strict security', 1; RECONFIGURE;
  44.  
  45.  
  46. --===========================================================================
  47. --======== Test Setup =======================================================
  48. --===========================================================================
  49.  
  50. -- Create test DB if it does not already exist:
  51. USE [master];
  52.  
  53. IF (DB_ID(N'PermissionSetTest') IS NULL)
  54. BEGIN
  55.     CREATE DATABASE [PermissionSetTest] WITH TRUSTWORTHY OFF;
  56.     ALTER DATABASE [PermissionSetTest] SET RECOVERY SIMPLE;
  57. END;
  58. GO
  59.  
  60.  
  61. USE [PermissionSetTest];
  62. GO
  63.  
  64. -- Install SQL# ( https://SQLsharp.com/free/ ) into the [PermissionSetTest] DB.
  65. -- If "clr integration" is not enabled, the SQL# install script will enable it.
  66. -- The SQL# installer does NOT disable "CLR strict security", and it does NOT
  67. -- set TRUSTWORTHY to ON; it uses module signing ( https://ModuleSigning.info/ ).
  68.  
  69.  
  70.  
  71. --===========================================================================
  72. --======== Check Run-time Restrictions ======================================
  73. --===========================================================================
  74.  
  75. SELECT db.[name], db.[is_trustworthy_on]
  76. FROM   sys.databases db
  77. WHERE  db.[database_id] = DB_ID();
  78. /*
  79. name                 is_trustworthy_on
  80. PermissionSetTest    0
  81. */
  82.  
  83.  
  84. SELECT asm.[permission_set_desc]
  85. FROM   sys.assemblies asm
  86. WHERE  asm.[name] = N'SQL#.DB';
  87. -- SAFE_ACCESS
  88.  
  89.  
  90. CREATE TABLE ##TempDestination ([Col1] INT);
  91. -- TRUNCATE TABLE ##TempDestination;
  92.  
  93.  
  94. EXEC SQL#.DB_BulkCopy
  95.     @SourceQuery = N'SELECT [object_id] FROM master.sys.objects;',
  96.     @DestinationTableName = N'##TempDestination';
  97. /*
  98. The current PERMISSION_SET on the Assembly does not allow this operation (SqlClientPermission).
  99.     Please execute the following SQL in order to fix this:
  100.  
  101.         EXEC SQL#.SQLsharp_SetSecurity 2, N'SQL#.DB';
  102. */
  103.  
  104.  
  105. EXEC SQL#.SQLsharp_SetSecurity 2, N'SQL#.DB';
  106.  
  107. SELECT asm.[permission_set_desc]
  108. FROM   sys.assemblies asm
  109. WHERE  asm.[name] = N'SQL#.DB';
  110. -- EXTERNAL_ACCESS
  111.  
  112.  
  113. EXEC SQL#.DB_BulkCopy
  114.     @SourceQuery = N'SELECT [object_id] FROM master.sys.objects;',
  115.     @DestinationTableName = N'##TempDestination';
  116.  
  117. SELECT * FROM ##TempDestination;
  118. -- 113 rows returned
  119.  
  120.  
  121. ----
  122.  
  123. SELECT asm.[permission_set_desc]
  124. FROM   sys.assemblies asm
  125. WHERE  asm.[name] = N'SQL#.OS';
  126. -- SAFE_ACCESS
  127.  
  128.  
  129. SELECT SQL#.OS_GenerateTone(1000, 1000);
  130. /*
  131. System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.
  132.  
  133. The protected resources (only available with full trust) were: All
  134. The demanded resources were: UI
  135. */
  136.  
  137.  
  138.  
  139. EXEC SQL#.SQLsharp_SetSecurity 2, N'SQL#.OS';
  140.  
  141. SELECT asm.[permission_set_desc]
  142. FROM   sys.assemblies asm
  143. WHERE  asm.[name] = N'SQL#.OS';
  144. -- EXTERNAL_ACCESS
  145.  
  146.  
  147. SELECT SQL#.OS_GenerateTone(1000, 1000);
  148. /*
  149. System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.
  150.  
  151. The protected resources (only available with full trust) were: All
  152. The demanded resources were: UI
  153. */
  154.  
  155.  
  156.  
  157. EXEC SQL#.SQLsharp_SetSecurity 3, N'SQL#.OS';
  158.  
  159. SELECT asm.[permission_set_desc]
  160. FROM   sys.assemblies asm
  161. WHERE  asm.[name] = N'SQL#.OS';
  162. -- UNSAFE_ACCESS
  163.  
  164.  
  165. SELECT SQL#.OS_GenerateTone(25000, 3000);
  166. -- Success (well, no tone, but no error either)
  167.  
  168.  
  169. --===========================================================================
  170. --======== Check Creation Restrictions ======================================
  171. --===========================================================================
  172.  
  173.  
  174. ALTER DATABASE [PermissionSetTest] SET TRUSTWORTHY ON;
  175.  
  176.  
  177.  
  178. SELECT cfg.[name], cfg.[value_in_use]
  179. FROM   sys.configurations cfg
  180. WHERE  cfg.[configuration_id] = 1587; -- clr strict security
  181. /*
  182. name                   value_in_use
  183. clr strict security    1
  184. */
  185.  
  186. SELECT db.[name], db.[is_trustworthy_on]
  187. FROM   sys.databases db
  188. WHERE  db.[database_id] = DB_ID();
  189. /*
  190. name                 is_trustworthy_on
  191. PermissionSetTest    1
  192. */
  193.  
  194.  
  195. -- C# code for Assembly:
  196. /*
  197. using Microsoft.SqlServer.Server;
  198.  
  199. public class StoredProcedures
  200. {
  201.     private static string CaughtByCreateAssemblyChecks = "Nope";
  202.  
  203.     [SqlProcedure]
  204.     public static void PrintHello()
  205.     {
  206.         SqlContext.Pipe.Send("Hello");
  207.     }
  208. }
  209. */
  210.  
  211.  
  212. -- Store Assembly definition in temp table so that it can be
  213. -- used across multiple queries that are going to be executed
  214. -- individually and not in a batch.
  215. CREATE TABLE #AssemblyBits ([HexBytes] VARBINARY(MAX) NOT NULL);
  216. -- TRUNCATE TABLE #AssemblyBits;
  217.  
  218. INSERT INTO #AssemblyBits ([HexBytes]) VALUES (0x\
  219. 4D5A90000300000004000000FFFF0000B8000000000000004000000000000000000000000000\
  220. 00000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014C\
  221. CD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F\
  222. 64652E0D0D0A2400000000000000504500004C010300EB7DD75B0000000000000000E0000221\
  223. 0B010B00000600000008000000000000EE250000002000000040000000000010002000000002\
  224. 0000040000000000000006000000000000000080000000020000000000000300608500001000\
  225. 0010000000001000001000000000000010000000000000000000000094250000570000000040\
  226. 00001004000000000000000000000000000000000000006000000C0000000000000000000000\
  227. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  228. 000000200000080000000000000000000000082000004800000000000000000000002E746578\
  229. 74000000F4050000002000000006000000020000000000000000000000000000200000602E72\
  230. 7372630000001004000000400000000600000008000000000000000000000000000040000040\
  231. 2E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000\
  232. 004200000000000000000000000000000000D025000000000000480000000200050078200000\
  233. 1C05000001000000000000000000000000000000000000000000000000000000000000000000\
  234. 0000000000000000000000000000000000000000000042280A00000A72010000706F0B00000A\
  235. 2A2E720D00007080010000042A1E02280C00000A2A00000042534A4201000100000000000C00\
  236. 000076342E302E33303331390000000005006C00000094010000237E000000020000EC010000\
  237. 23537472696E677300000000EC03000018000000235553000404000010000000234755494400\
  238. 0000140400000801000023426C6F620000000000000002000001571400000900000000FA2533\
  239. 00160000010000000C0000000200000001000000030000000C00000008000000010000000200\
  240. 000000000A0001000000000006003B0034000600820070000600990070000600B20070000600\
  241. CB0070000600E60070000600FF00700006003C011C0106005C011C010A00AD0192010A00C301\
  242. 92010A00CE0192010000000001000000000001000100010010001A0000000500010001001100\
  243. 42000A0050200000000096005F000D0001006D200000000086186A0011000100612000000000\
  244. 9118E4010D00010011006A00150019006A00150021006A00150029006A00150031006A001500\
  245. 39006A00150041006A001A0049006A00110051006A0011005900D60124006100DF0115000900\
  246. 6A00110020004B001F002E001B0086002E00130050002E000B0029002E00230097002E003300\
  247. D0002E003B00DD002E004300E6000480000001000000010001000000000000007A0100000400\
  248. 0000000000000000000001002B00000000000400000000000000000000000100860100000000\
  249. 00000000003C4D6F64756C653E00556E7361666553747566662E646C6C0053746F7265645072\
  250. 6F63656475726573006D73636F726C69620053797374656D004F626A65637400436175676874\
  251. 4279437265617465417373656D626C79436865636B73005072696E7448656C6C6F002E63746F\
  252. 720053797374656D2E5265666C656374696F6E00417373656D626C795469746C654174747269\
  253. 6275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79\
  254. 50726F6475637441747472696275746500417373656D626C79436F7079726967687441747472\
  255. 696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C\
  256. 7946696C6556657273696F6E4174747269627574650053797374656D2E52756E74696D652E43\
  257. 6F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E73\
  258. 4174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574\
  259. 6500556E7361666553747566660053797374656D2E44617461004D6963726F736F66742E5371\
  260. 6C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053\
  261. 716C436F6E746578740053716C50697065006765745F506970650053656E64002E6363746F72\
  262. 0000000B480065006C006C006F0000094E006F0070006500000016298343E2FC2C4699D9FE26\
  263. 312562C50008B77A5C561934E08902060E0300000103200001042001010E0420010108040100\
  264. 000004000012312601002153514C434C522076732053514C2053657276657220323031372C20\
  265. 50617274203900003501003053716C205175616E74756D204C65617020282068747470733A2F\
  266. 2F53716C5175616E74756D4C6561702E636F6D2F202900001001000B556E7361666553747566\
  267. 66000038010033436F70797269676874203230313820536F6C6F6D6F6E205275747A6B792E20\
  268. 416C6C205269676874732052657365727665642E00000C010007312E302E322E320000080100\
  269. 0800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100\
  270. 0000BC2500000000000000000000DE2500000020000000000000000000000000000000000000\
  271. 00000000D02500000000000000000000000000000000000000005F436F72446C6C4D61696E00\
  272. 6D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000\
  273. 0000000000000000000001001000000018000080000000000000000000000000000001000100\
  274. 00003000008000000000000000000000000000000100000000004800000058400000B4030000\
  275. 0000000000000000B40334000000560053005F00560045005200530049004F004E005F004900\
  276. 4E0046004F0000000000BD04EFFE00000100000001000200020000000100020002003F000000\
  277. 0000000004000000020000000000000000000000000000004400000001005600610072004600\
  278. 69006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100\
  279. 740069006F006E00000000000000B00414030000010053007400720069006E00670046006900\
  280. 6C00650049006E0066006F000000F00200000100300030003000300030003400620030000000\
  281. 84003100010043006F006D00700061006E0079004E0061006D00650000000000530071006C00\
  282. 20005100750061006E00740075006D0020004C00650061007000200028002000680074007400\
  283. 700073003A002F002F00530071006C005100750061006E00740075006D004C00650061007000\
  284. 2E0063006F006D002F0020002900000000006C0022000100460069006C006500440065007300\
  285. 6300720069007000740069006F006E0000000000530051004C0043004C005200200076007300\
  286. 2000530051004C002000530065007200760065007200200032003000310037002C0020005000\
  287. 610072007400200039000000300008000100460069006C006500560065007200730069006F00\
  288. 6E000000000031002E0030002E0032002E003200000040001000010049006E00740065007200\
  289. 6E0061006C004E0061006D006500000055006E00730061006600650053007400750066006600\
  290. 2E0064006C006C0000008C00340001004C006500670061006C0043006F007000790072006900\
  291. 670068007400000043006F007000790072006900670068007400200032003000310038002000\
  292. 53006F006C006F006D006F006E0020005200750074007A006B0079002E00200041006C006C00\
  293. 20005200690067006800740073002000520065007300650072007600650064002E0000004800\
  294. 100001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000\
  295. 55006E007300610066006500530074007500660066002E0064006C006C00000038000C000100\
  296. 500072006F0064007500630074004E0061006D0065000000000055006E007300610066006500\
  297. 530074007500660066000000340008000100500072006F006400750063007400560065007200\
  298. 730069006F006E00000031002E0030002E0032002E0032000000380008000100410073007300\
  299. 65006D0062006C0079002000560065007200730069006F006E00000031002E0030002E003100\
  300. 2E00310000000000000000000000000000000000000000000000000000000000000000000000\
  301. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  302. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  303. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  304. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  305. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  306. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  307. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  308. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  309. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  310. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  311. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  312. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  313. 000000000000000000000000002000000C000000F03500000000000000000000000000000000\
  314. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  315. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  316. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  317. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  318. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  319. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  320. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  321. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  322. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  323. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  324. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  325. 0000000000000000000000000000000000000000000000000000000000000000000000000000\
  326. 000000000000000000000000000000000000000000000000000000000000);
  327.  
  328.  
  329. -----------------
  330.  
  331. -- Get Assembly definition from temp table:
  332. GO
  333. -- DROP ASSEMBLY [UnsafeStuff];
  334. DECLARE @Assembly VARBINARY(MAX);
  335. SELECT @Assembly = [HexBytes] FROM #AssemblyBits;
  336.  
  337. -- Create Assembly from that definition:
  338. CREATE ASSEMBLY [UnsafeStuff]
  339. AUTHORIZATION [dbo]
  340. FROM @Assembly
  341. WITH PERMISSION_SET = SAFE;
  342. /*
  343. Msg 6211, Level 16, State 1, Line XXXXX
  344. CREATE ASSEMBLY failed because type 'StoredProcedures' in safe assembly
  345.    'UnsafeStuff' has a static field 'CaughtByCreateAssemblyChecks'.
  346.    Attributes of static fields in safe assemblies must be marked
  347.    readonly in Visual C#, ReadOnly in Visual Basic, or initonly in
  348.    Visual C++ and intermediate language.
  349. */
  350.  
  351. ---
  352.  
  353. -- Get Assembly definition from temp table:
  354. GO
  355. DECLARE @Assembly VARBINARY(MAX);
  356. SELECT @Assembly = [HexBytes] FROM #AssemblyBits;
  357.  
  358. -- Create Assembly from that definition:
  359. CREATE ASSEMBLY [UnsafeStuff]
  360. AUTHORIZATION [dbo]
  361. FROM @Assembly
  362. WITH PERMISSION_SET = UNSAFE;
  363. -- Commands completed successfully.
  364.  
  365.  
  366. GO
  367. SET PARSEONLY OFF;
  368. 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