forked from tSQLt-org/tSQLt
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathRemoveAssemblyKeyTests.class.sql
More file actions
256 lines (204 loc) · 9.16 KB
/
Copy pathRemoveAssemblyKeyTests.class.sql
File metadata and controls
256 lines (204 loc) · 9.16 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
EXEC tSQLt.NewTestClass 'RemoveAssemblyKeyTests';
GO
CREATE PROCEDURE RemoveAssemblyKeyTests.RemoveKeyTestUser
AS
BEGIN
DECLARE @cmd NVARCHAR(MAX);
SET @cmd = 'IF(SUSER_ID(''RemoveAssemblyKeyTestsUser1'')) IS NOT NULL DROP LOGIN RemoveAssemblyKeyTestsUser1;';
EXEC master.sys.sp_executesql @cmd;
SET @cmd = 'IF(SCHEMA_ID(''RemoveAssemblyKeyTestsUser1'')) IS NOT NULL DROP SCHEMA RemoveAssemblyKeyTestsUser1;';
EXEC master.sys.sp_executesql @cmd;
EXEC sys.sp_executesql @cmd;
SET @cmd = 'IF(USER_ID(''RemoveAssemblyKeyTestsUser1'')) IS NOT NULL DROP USER RemoveAssemblyKeyTestsUser1;';
EXEC master.sys.sp_executesql @cmd;
EXEC sys.sp_executesql @cmd;
END;
GO
CREATE PROCEDURE RemoveAssemblyKeyTests.DropExistingItems
AS
BEGIN
IF SUSER_ID('tSQLtAssemblyKey') IS NOT NULL DROP LOGIN tSQLtAssemblyKey;
EXEC master.sys.sp_executesql N'IF ASYMKEY_ID(''tSQLtAssemblyKey'') IS NOT NULL DROP ASYMMETRIC KEY tSQLtAssemblyKey;';
EXEC master.sys.sp_executesql N'IF EXISTS(SELECT * FROM sys.assemblies WHERE name = ''tSQLtAssemblyKey'') DROP ASSEMBLY tSQLtAssemblyKey;';
DECLARE @cmd NVARCHAR(MAX);
DECLARE @ProductMajorVersion INT;
EXEC @ProductMajorVersion = tSQLt.Private_GetSQLProductMajorVersion;
IF(@ProductMajorVersion>=14)
BEGIN
-- sp_drop_trusted_assembly is new (and required) in 2017
DECLARE @TrustedHash NVARCHAR(MAX);
SELECT @TrustedHash = CONVERT(NVARCHAR(MAX),HASHBYTES('SHA2_512',PGEAKB.UnsignedEmptyBytes),1)
FROM tSQLt_testutil.GetUnsignedEmptyBytes() AS PGEAKB
SELECT @cmd =
'IF EXISTS(SELECT 1 FROM sys.trusted_assemblies WHERE hash = ' + @TrustedHash +')'+
'EXEC sys.sp_drop_trusted_assembly @hash = ' + @TrustedHash + ';';
EXEC master.sys.sp_executesql @cmd;
DECLARE @AssemblyKeyBytes VARBINARY(MAX);
EXEC tSQLt.Private_GetAssemblyKeyBytes @AssemblyKeyBytes = @AssemblyKeyBytes OUT;
SELECT @TrustedHash = CONVERT(NVARCHAR(MAX),HASHBYTES('SHA2_512',@AssemblyKeyBytes),1);
SELECT @cmd =
'IF EXISTS(SELECT 1 FROM sys.trusted_assemblies WHERE hash = ' + @TrustedHash +')'+
'EXEC sys.sp_drop_trusted_assembly @hash = ' + @TrustedHash + ';';
EXEC master.sys.sp_executesql @cmd;
END;
END;
GO
GO
CREATE PROCEDURE RemoveAssemblyKeyTests.[test removes tSQLtAssemblyKey assembly]
AS
BEGIN
EXEC RemoveAssemblyKeyTests.DropExistingItems;
DECLARE @cmd NVARCHAR(MAX);
DECLARE @ProductMajorVersion INT;
EXEC @ProductMajorVersion = tSQLt.Private_GetSQLProductMajorVersion;
IF(@ProductMajorVersion>=14)
BEGIN
-- sp_add_trusted_assembly is new (and required) in 2017
SELECT @cmd =
'EXEC sys.sp_add_trusted_assembly @hash = ' + CONVERT(NVARCHAR(MAX),HASHBYTES('SHA2_512',PGEAKB.UnsignedEmptyBytes),1) + ', @description = N''tSQLt Ephemeral'';'
FROM tSQLt_testutil.GetUnsignedEmptyBytes() AS PGEAKB
WHERE NOT EXISTS(SELECT 1 FROM sys.trusted_assemblies AS TA WHERE TA.hash = HASHBYTES('SHA2_512',PGEAKB.UnsignedEmptyBytes));
EXEC master.sys.sp_executesql @cmd;
END;
SELECT @cmd =
'CREATE ASSEMBLY tSQLtAssemblyKey AUTHORIZATION dbo FROM ' +
CONVERT(NVARCHAR(MAX),GUEB.UnsignedEmptyBytes,1) +
' WITH PERMISSION_SET = SAFE;'
FROM tSQLt_testutil.GetUnsignedEmptyBytes() AS GUEB;
EXEC master.sys.sp_executesql @cmd;
EXEC tSQLt.RemoveAssemblyKey;
IF(EXISTS(SELECT 1 FROM master.sys.assemblies WHERE name = 'tSQLtAssemblyKey'))
BEGIN
EXEC tSQLt.Fail 'Assembly tSQLtAssemblyKey not removed.';
END;
END;
GO
CREATE PROCEDURE RemoveAssemblyKeyTests.[test removes tSQLtAssemblyKey asymmetric key]
AS
BEGIN
EXEC RemoveAssemblyKeyTests.DropExistingItems;
DECLARE @cmd NVARCHAR(MAX);
SELECT @cmd = 'CREATE ASYMMETRIC KEY tSQLtAssemblyKey WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = '''+(SELECT PW FROM tSQLt_testutil.GenerateRandomPassword(NEWID()))+''';';
EXEC master.sys.sp_executesql @cmd;
EXEC tSQLt.RemoveAssemblyKey;
SELECT *
INTO #Actual
FROM master.sys.asymmetric_keys AS AK
WHERE AK.name = 'tSQLtAssemblyKey'
EXEC tSQLt.AssertEmptyTable @TableName = '#Actual'; --<-- this might blow up if the table isn't empty.
END;
GO
CREATE PROCEDURE RemoveAssemblyKeyTests.[test removes tSQLtAssemblyKey login]
AS
BEGIN
EXEC RemoveAssemblyKeyTests.DropExistingItems;
DECLARE @cmd NVARCHAR(MAX);
SELECT @cmd = 'CREATE LOGIN tSQLtAssemblyKey WITH PASSWORD = '''+(SELECT PW FROM tSQLt_testutil.GenerateRandomPassword(NEWID()))+''',CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;';
EXEC master.sys.sp_executesql @cmd;
EXEC tSQLt.RemoveAssemblyKey;
SELECT *
INTO #Actual
FROM master.sys.server_principals AS SP
WHERE SP.name = 'tSQLtAssemblyKey'
EXEC tSQLt.AssertEmptyTable @TableName = '#Actual'; --<-- this might blow up if the table isn't empty.
END;
GO
CREATE PROCEDURE RemoveAssemblyKeyTests.[test removes tSQLtAssemblyKey asymmetric key and login in correct order]
AS
BEGIN
EXEC RemoveAssemblyKeyTests.DropExistingItems;
DECLARE @cmd NVARCHAR(MAX);
SELECT @cmd = 'CREATE ASYMMETRIC KEY tSQLtAssemblyKey WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = '''+(SELECT PW FROM tSQLt_testutil.GenerateRandomPassword(NEWID()))+''';';
EXEC master.sys.sp_executesql @cmd;
SELECT @cmd = 'CREATE LOGIN tSQLtAssemblyKey FROM ASYMMETRIC KEY tSQLtAssemblyKey;';
EXEC master.sys.sp_executesql @cmd;
EXEC tSQLt.RemoveAssemblyKey;
SELECT *
INTO #Actual
FROM
(
SELECT AK.name
FROM master.sys.asymmetric_keys AS AK
WHERE AK.name = 'tSQLtAssemblyKey'
UNION ALL
SELECT SP.name
FROM master.sys.server_principals AS SP
WHERE SP.name = 'tSQLtAssemblyKey'
)A
EXEC tSQLt.AssertEmptyTable @TableName = '#Actual'; --<-- this might blow up if the table isn't empty.
END;
GO
CREATE PROCEDURE RemoveAssemblyKeyTests.[test non-sysadmin cannot execute procedure]
AS
BEGIN
EXEC RemoveAssemblyKeyTests.RemoveKeyTestUser;
DECLARE @cmd NVARCHAR(MAX);
SET @cmd = 'CREATE LOGIN RemoveAssemblyKeyTestsUser1 WITH PASSWORD='''+(SELECT PW FROM tSQLt_testutil.GenerateRandomPassword(NEWID()))+''';';
EXEC master.sys.sp_executesql @cmd;
SET @cmd = 'CREATE USER RemoveAssemblyKeyTestsUser1;ALTER ROLE db_owner ADD MEMBER RemoveAssemblyKeyTestsUser1;';
IF((SELECT I.SqlVersion FROM tSQLt.Info() AS I) < 11)
BEGIN
SET @cmd = 'CREATE USER RemoveAssemblyKeyTestsUser1;EXEC sys.sp_addrolemember @rolename = ''db_owner'', @membername = ''RemoveAssemblyKeyTestsUser1'';';
END
EXEC master.sys.sp_executesql @cmd;
EXEC sys.sp_executesql @cmd;
EXEC tSQLt.ExpectException @ExpectedMessage = 'Only principals with CONTROL SERVER permission can execute this procedure.';
EXECUTE AS LOGIN = 'RemoveAssemblyKeyTestsUser1';
EXEC tSQLt.RemoveAssemblyKey;
REVERT;--<--won't actually get executed...
END;
GO
CREATE PROCEDURE RemoveAssemblyKeyTests.[test login with control server permission can execute procedure]
AS
BEGIN
EXEC RemoveAssemblyKeyTests.DropExistingItems;
EXEC RemoveAssemblyKeyTests.RemoveKeyTestUser;
DECLARE @cmd NVARCHAR(MAX);
SET @cmd = 'CREATE LOGIN RemoveAssemblyKeyTestsUser1 WITH PASSWORD='''+(SELECT PW FROM tSQLt_testutil.GenerateRandomPassword(NEWID()))+''';';
EXEC master.sys.sp_executesql @cmd;
SET @cmd = 'GRANT CONTROL SERVER TO RemoveAssemblyKeyTestsUser1;';
EXEC master.sys.sp_executesql @cmd;
EXEC tSQLt.ExpectNoException;
EXECUTE AS LOGIN = 'RemoveAssemblyKeyTestsUser1';
EXEC tSQLt.RemoveAssemblyKey;
REVERT;
END;
GO
--[@tSQLt:MinSqlMajorVersion](14)
CREATE PROCEDURE RemoveAssemblyKeyTests.[test removes trusted assembly record if it exists]
AS
BEGIN
EXEC RemoveAssemblyKeyTests.DropExistingItems;
DECLARE @cmd NVARCHAR(MAX);
DECLARE @AssemblyKeyBytes VARBINARY(MAX);
DECLARE @TrustedHash NVARCHAR(MAX);
EXEC tSQLt.Private_GetAssemblyKeyBytes @AssemblyKeyBytes = @AssemblyKeyBytes OUT;
SELECT @TrustedHash = CONVERT(NVARCHAR(MAX),HASHBYTES('SHA2_512',@AssemblyKeyBytes),1);
SELECT @cmd = 'EXEC sys.sp_add_trusted_assembly @hash = ' + @TrustedHash +',@description = N''tSQLt Ephemeral'';'
EXEC master.sys.sp_executesql @cmd;
EXEC tSQLt.RemoveAssemblyKey;
IF(EXISTS(SELECT 1 FROM master.sys.trusted_assemblies WHERE hash = CONVERT(VARBINARY(MAX),@TrustedHash,1)))
BEGIN
EXEC tSQLt.Fail 'Trusted Assembly record for tSQLtAssemblyKey not removed.';
END;
END;
GO
--[@tSQLt:MinSqlMajorVersion](14)
CREATE PROCEDURE RemoveAssemblyKeyTests.[test doesn't remove trusted assembly record if it is not 'tSQLt Ephemeral']
AS
BEGIN
EXEC RemoveAssemblyKeyTests.DropExistingItems;
DECLARE @cmd NVARCHAR(MAX);
DECLARE @AssemblyKeyBytes VARBINARY(MAX);
DECLARE @TrustedHash NVARCHAR(MAX);
EXEC tSQLt.Private_GetAssemblyKeyBytes @AssemblyKeyBytes = @AssemblyKeyBytes OUT;
SELECT @TrustedHash = CONVERT(NVARCHAR(MAX),HASHBYTES('SHA2_512',@AssemblyKeyBytes),1);
SELECT @cmd = 'EXEC sys.sp_add_trusted_assembly @hash = ' + @TrustedHash +',@description = N''something else'';'
EXEC master.sys.sp_executesql @cmd;
EXEC tSQLt.RemoveAssemblyKey;
IF(NOT EXISTS(SELECT 1 FROM master.sys.trusted_assemblies WHERE hash = CONVERT(VARBINARY(MAX),@TrustedHash,1)))
BEGIN
EXEC tSQLt.Fail 'Trusted Assembly record for tSQLtAssemblyKey removed despite description <> ''tSQLt Ephemeral''.';
END;
END;
GO