"CLR strict security" and PERMISSION_SET

SET PARSEONLY ON;
GO
/*
 --------- Effect of "CLR strict security" on PERMISSION_SET ---------

Created By: Solomon Rutzky / Sql Quantum Leap
Created On: 2018-10-28
Updated On: n/a

Location:          https://pastebin.com/ZQ3Lfjrg
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/
----------------------------------------------------------------------
*/


--===========================================================================
--======== Confirm Environment ==============================================
--===========================================================================

-- Make sure we are using SQL Server 2017 (14.x) or newer:
SELECT @@VERSION;
/*
Microsoft SQL Server vNext (CTP2.0) - 15.0.1000.34 (X64)
    Sep 18 2018 02:32:04
    Copyright (C) 2018 Microsoft Corporation
    Enterprise Evaluation Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 17134: )
*/


-- Yes, "CLR strict security" is an "advanced option", but
-- "show advanced options" does not need to be "1" in order
-- to see the row in sys.configurations.

SELECT cfg.[name], cfg.[value_in_use]
FROM   sys.configurations cfg
WHERE  cfg.[configuration_id] = 1587; -- clr strict security
/*
name                   value_in_use
clr strict security    1
*/

-- EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
-- EXEC sp_configure 'CLR strict security', 1; RECONFIGURE;


--===========================================================================
--======== Test Setup =======================================================
--===========================================================================

-- Create test DB if it does not already exist:
USE [master];

IF (DB_ID(N'PermissionSetTest') IS NULL)
BEGIN
    CREATE DATABASE [PermissionSetTest] WITH TRUSTWORTHY OFF;
    ALTER DATABASE [PermissionSetTest] SET RECOVERY SIMPLE;
END;
GO


USE [PermissionSetTest];
GO

-- Install SQL# ( https://SQLsharp.com/free/ ) into the [PermissionSetTest] DB.
-- If "clr integration" is not enabled, the SQL# install script will enable it.
-- The SQL# installer does NOT disable "CLR strict security", and it does NOT
-- set TRUSTWORTHY to ON; it uses module signing ( https://ModuleSigning.info/ ).



--===========================================================================
--======== Check Run-time Restrictions ======================================
--===========================================================================

SELECT db.[name], db.[is_trustworthy_on]
FROM   sys.databases db
WHERE  db.[database_id] = DB_ID();
/*
name                 is_trustworthy_on
PermissionSetTest    0
*/


SELECT asm.[permission_set_desc]
FROM   sys.assemblies asm
WHERE  asm.[name] = N'SQL#.DB';
-- SAFE_ACCESS


CREATE TABLE ##TempDestination ([Col1] INT);
-- TRUNCATE TABLE ##TempDestination;


EXEC SQL#.DB_BulkCopy
    @SourceQuery = N'SELECT [object_id] FROM master.sys.objects;',
    @DestinationTableName = N'##TempDestination';
/*
The current PERMISSION_SET on the Assembly does not allow this operation (SqlClientPermission).
    Please execute the following SQL in order to fix this:

        EXEC SQL#.SQLsharp_SetSecurity 2, N'SQL#.DB';
*/


EXEC SQL#.SQLsharp_SetSecurity 2, N'SQL#.DB';

SELECT asm.[permission_set_desc]
FROM   sys.assemblies asm
WHERE  asm.[name] = N'SQL#.DB';
-- EXTERNAL_ACCESS


EXEC SQL#.DB_BulkCopy
    @SourceQuery = N'SELECT [object_id] FROM master.sys.objects;',
    @DestinationTableName = N'##TempDestination';

SELECT * FROM ##TempDestination;
-- 113 rows returned


----

SELECT asm.[permission_set_desc]
FROM   sys.assemblies asm
WHERE  asm.[name] = N'SQL#.OS';
-- SAFE_ACCESS


SELECT SQL#.OS_GenerateTone(1000, 1000);
/*
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All
The demanded resources were: UI
*/



EXEC SQL#.SQLsharp_SetSecurity 2, N'SQL#.OS';

SELECT asm.[permission_set_desc]
FROM   sys.assemblies asm
WHERE  asm.[name] = N'SQL#.OS';
-- EXTERNAL_ACCESS


SELECT SQL#.OS_GenerateTone(1000, 1000);
/*
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All
The demanded resources were: UI
*/



EXEC SQL#.SQLsharp_SetSecurity 3, N'SQL#.OS';

SELECT asm.[permission_set_desc]
FROM   sys.assemblies asm
WHERE  asm.[name] = N'SQL#.OS';
-- UNSAFE_ACCESS


SELECT SQL#.OS_GenerateTone(25000, 3000);
-- Success (well, no tone, but no error either)


--===========================================================================
--======== Check Creation Restrictions ======================================
--===========================================================================


ALTER DATABASE [PermissionSetTest] SET TRUSTWORTHY ON;



SELECT cfg.[name], cfg.[value_in_use]
FROM   sys.configurations cfg
WHERE  cfg.[configuration_id] = 1587; -- clr strict security
/*
name                   value_in_use
clr strict security    1
*/

SELECT db.[name], db.[is_trustworthy_on]
FROM   sys.databases db
WHERE  db.[database_id] = DB_ID();
/*
name                 is_trustworthy_on
PermissionSetTest    1
*/


-- C# code for Assembly:
/*
using Microsoft.SqlServer.Server;

public class StoredProcedures
{
    private static string CaughtByCreateAssemblyChecks = "Nope";

    [SqlProcedure]
    public static void PrintHello()
    {
        SqlContext.Pipe.Send("Hello");
    }
}
*/


-- Store Assembly definition in temp table so that it can be
-- used across multiple queries that are going to be executed
-- individually and not in a batch.
CREATE TABLE #AssemblyBits ([HexBytes] VARBINARY(MAX) NOT NULL);
-- TRUNCATE TABLE #AssemblyBits;

INSERT INTO #AssemblyBits ([HexBytes]) VALUES (0x\
4D5A90000300000004000000FFFF0000B8000000000000004000000000000000000000000000\
00000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014C\
CD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F\
64652E0D0D0A2400000000000000504500004C010300EB7DD75B0000000000000000E0000221\
0B010B00000600000008000000000000EE250000002000000040000000000010002000000002\
0000040000000000000006000000000000000080000000020000000000000300608500001000\
0010000000001000001000000000000010000000000000000000000094250000570000000040\
00001004000000000000000000000000000000000000006000000C0000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
000000200000080000000000000000000000082000004800000000000000000000002E746578\
74000000F4050000002000000006000000020000000000000000000000000000200000602E72\
7372630000001004000000400000000600000008000000000000000000000000000040000040\
2E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000\
004200000000000000000000000000000000D025000000000000480000000200050078200000\
1C05000001000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000042280A00000A72010000706F0B00000A\
2A2E720D00007080010000042A1E02280C00000A2A00000042534A4201000100000000000C00\
000076342E302E33303331390000000005006C00000094010000237E000000020000EC010000\
23537472696E677300000000EC03000018000000235553000404000010000000234755494400\
0000140400000801000023426C6F620000000000000002000001571400000900000000FA2533\
00160000010000000C0000000200000001000000030000000C00000008000000010000000200\
000000000A0001000000000006003B0034000600820070000600990070000600B20070000600\
CB0070000600E60070000600FF00700006003C011C0106005C011C010A00AD0192010A00C301\
92010A00CE0192010000000001000000000001000100010010001A0000000500010001001100\
42000A0050200000000096005F000D0001006D200000000086186A0011000100612000000000\
9118E4010D00010011006A00150019006A00150021006A00150029006A00150031006A001500\
39006A00150041006A001A0049006A00110051006A0011005900D60124006100DF0115000900\
6A00110020004B001F002E001B0086002E00130050002E000B0029002E00230097002E003300\
D0002E003B00DD002E004300E6000480000001000000010001000000000000007A0100000400\
0000000000000000000001002B00000000000400000000000000000000000100860100000000\
00000000003C4D6F64756C653E00556E7361666553747566662E646C6C0053746F7265645072\
6F63656475726573006D73636F726C69620053797374656D004F626A65637400436175676874\
4279437265617465417373656D626C79436865636B73005072696E7448656C6C6F002E63746F\
720053797374656D2E5265666C656374696F6E00417373656D626C795469746C654174747269\
6275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79\
50726F6475637441747472696275746500417373656D626C79436F7079726967687441747472\
696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C\
7946696C6556657273696F6E4174747269627574650053797374656D2E52756E74696D652E43\
6F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E73\
4174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574\
6500556E7361666553747566660053797374656D2E44617461004D6963726F736F66742E5371\
6C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053\
716C436F6E746578740053716C50697065006765745F506970650053656E64002E6363746F72\
0000000B480065006C006C006F0000094E006F0070006500000016298343E2FC2C4699D9FE26\
312562C50008B77A5C561934E08902060E0300000103200001042001010E0420010108040100\
000004000012312601002153514C434C522076732053514C2053657276657220323031372C20\
50617274203900003501003053716C205175616E74756D204C65617020282068747470733A2F\
2F53716C5175616E74756D4C6561702E636F6D2F202900001001000B556E7361666553747566\
66000038010033436F70797269676874203230313820536F6C6F6D6F6E205275747A6B792E20\
416C6C205269676874732052657365727665642E00000C010007312E302E322E320000080100\
0800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100\
0000BC2500000000000000000000DE2500000020000000000000000000000000000000000000\
00000000D02500000000000000000000000000000000000000005F436F72446C6C4D61696E00\
6D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000\
0000000000000000000001001000000018000080000000000000000000000000000001000100\
00003000008000000000000000000000000000000100000000004800000058400000B4030000\
0000000000000000B40334000000560053005F00560045005200530049004F004E005F004900\
4E0046004F0000000000BD04EFFE00000100000001000200020000000100020002003F000000\
0000000004000000020000000000000000000000000000004400000001005600610072004600\
69006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100\
740069006F006E00000000000000B00414030000010053007400720069006E00670046006900\
6C00650049006E0066006F000000F00200000100300030003000300030003400620030000000\
84003100010043006F006D00700061006E0079004E0061006D00650000000000530071006C00\
20005100750061006E00740075006D0020004C00650061007000200028002000680074007400\
700073003A002F002F00530071006C005100750061006E00740075006D004C00650061007000\
2E0063006F006D002F0020002900000000006C0022000100460069006C006500440065007300\
6300720069007000740069006F006E0000000000530051004C0043004C005200200076007300\
2000530051004C002000530065007200760065007200200032003000310037002C0020005000\
610072007400200039000000300008000100460069006C006500560065007200730069006F00\
6E000000000031002E0030002E0032002E003200000040001000010049006E00740065007200\
6E0061006C004E0061006D006500000055006E00730061006600650053007400750066006600\
2E0064006C006C0000008C00340001004C006500670061006C0043006F007000790072006900\
670068007400000043006F007000790072006900670068007400200032003000310038002000\
53006F006C006F006D006F006E0020005200750074007A006B0079002E00200041006C006C00\
20005200690067006800740073002000520065007300650072007600650064002E0000004800\
100001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000\
55006E007300610066006500530074007500660066002E0064006C006C00000038000C000100\
500072006F0064007500630074004E0061006D0065000000000055006E007300610066006500\
530074007500660066000000340008000100500072006F006400750063007400560065007200\
730069006F006E00000031002E0030002E0032002E0032000000380008000100410073007300\
65006D0062006C0079002000560065007200730069006F006E00000031002E0030002E003100\
2E00310000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000002000000C000000F03500000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000);


-----------------

-- Get Assembly definition from temp table:
GO
-- DROP ASSEMBLY [UnsafeStuff];
DECLARE @Assembly VARBINARY(MAX);
SELECT @Assembly = [HexBytes] FROM #AssemblyBits;

-- Create Assembly from that definition:
CREATE ASSEMBLY [UnsafeStuff]
AUTHORIZATION [dbo]
FROM @Assembly
WITH PERMISSION_SET = SAFE;
/*
Msg 6211, Level 16, State 1, Line XXXXX
CREATE ASSEMBLY failed because type 'StoredProcedures' in safe assembly
   'UnsafeStuff' has a static field 'CaughtByCreateAssemblyChecks'.
   Attributes of static fields in safe assemblies must be marked
   readonly in Visual C#, ReadOnly in Visual Basic, or initonly in
   Visual C++ and intermediate language.
*/

---

-- Get Assembly definition from temp table:
GO
DECLARE @Assembly VARBINARY(MAX);
SELECT @Assembly = [HexBytes] FROM #AssemblyBits;

-- Create Assembly from that definition:
CREATE ASSEMBLY [UnsafeStuff]
AUTHORIZATION [dbo]
FROM @Assembly
WITH PERMISSION_SET = UNSAFE;
-- Commands completed successfully.


GO
SET PARSEONLY OFF;
GO