· 9 years ago · Jan 18, 2017, 05:28 PM
1USE [master];
2GO
3IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseA')
4BEGIN
5 PRINT 'Dropping [DatabaseA] DB...';
6 ALTER DATABASE [DatabaseA] SET OFFLINE WITH ROLLBACK IMMEDIATE;
7 ALTER DATABASE [DatabaseA] SET ONLINE;
8 DROP DATABASE [DatabaseA];
9END;
10
11IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseB')
12BEGIN
13 PRINT 'Dropping [DatabaseB] DB...';
14 ALTER DATABASE [DatabaseB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
15 ALTER DATABASE [DatabaseB] SET ONLINE;
16 DROP DATABASE [DatabaseB];
17END;
18
19IF (SUSER_ID(N'JohnnyLunchbucket') IS NOT NULL)
20BEGIN
21 PRINT 'Dropping [JohnnyLunchbucket] Login...';
22 DROP LOGIN [JohnnyLunchbucket];
23END;
24
25IF (OBJECT_ID(N'tempdb..#CertInfo') IS NOT NULL)
26BEGIN
27 PRINT 'Dropping [#CertInfo] Temp Table...';
28 DROP TABLE #CertInfo;
29END;
30
31USE [master];
32
33EXECUTE AS LOGIN = N'sa';
34PRINT 'Creating databases...';
35CREATE DATABASE [DatabaseA] COLLATE Latin1_General_100_CI_AS_SC;
36CREATE DATABASE [DatabaseB] COLLATE Latin1_General_100_CI_AS_SC;
37REVERT;
38GO
39
40-- Default for both options should be OFF, but just to be sure:
41ALTER DATABASE [DatabaseA] SET DB_CHAINING OFF;
42ALTER DATABASE [DatabaseA] SET TRUSTWORTHY OFF;
43
44ALTER DATABASE [DatabaseB] SET DB_CHAINING OFF;
45ALTER DATABASE [DatabaseB] SET TRUSTWORTHY OFF;
46GO
47
48CREATE LOGIN [JohnnyLunchbucket] WITH PASSWORD = 'OhSoSecure;)';
49
50
51USE [DatabaseA];
52
53CREATE USER [JohnnyLunchbucket] FOR LOGIN [JohnnyLunchbucket];
54GO
55
56--DROP PROCEDURE dbo.InsertIntoTableWithoutTrigger;
57CREATE PROCEDURE dbo.InsertIntoTableWithoutTrigger
58(
59 @SomeValue NVARCHAR(50)
60)
61AS
62SET NOCOUNT ON;
63
64INSERT INTO [DatabaseB].[dbo].[TableWithoutTrigger] (SomeValue)
65VALUES (@SomeValue);
66GO
67
68GRANT EXECUTE ON dbo.InsertIntoTableWithoutTrigger TO [JohnnyLunchbucket];
69GO
70
71CREATE PROCEDURE dbo.InsertIntoTableWithTrigger
72AS
73SET NOCOUNT ON;
74
75INSERT INTO [DatabaseB].[dbo].[TableWithTrigger] (SomeOtherValue)
76VALUES (NEWID());
77GO
78
79GRANT EXECUTE ON dbo.InsertIntoTableWithTrigger TO [JohnnyLunchbucket];
80
81
82
83CREATE CERTIFICATE [PermissionsCert]
84 AUTHORIZATION [dbo]
85 ENCRYPTION BY PASSWORD = 'WeakPassword'
86 WITH SUBJECT = 'Used to test granting permissions to code',
87 EXPIRY_DATE = '2099-12-31';
88
89ADD SIGNATURE TO [dbo].[InsertIntoTableWithoutTrigger]
90 BY CERTIFICATE [PermissionsCert]
91 WITH PASSWORD = 'WeakPassword';
92
93ADD SIGNATURE TO [dbo].[InsertIntoTableWithTrigger]
94 BY CERTIFICATE [PermissionsCert]
95 WITH PASSWORD = 'WeakPassword';
96
97-- Save Certificate info in temporary table so we can recreate in DatabaseB
98SELECT CERTENCODED(CERT_ID(N'PermissionsCert')) AS [PublicKey],
99 CERTPRIVATEKEY(CERT_ID(N'PermissionsCert'), 'OtherPassword', 'WeakPassword')
100 AS [PrivateKey]
101INTO #CertInfo;
102GO
103
104USE [DatabaseB];
105
106DECLARE @SQL NVARCHAR(MAX);
107
108SELECT @SQL = N'CREATE CERTIFICATE [PermissionsCert] AUTHORIZATION [dbo] FROM BINARY = '
109 + CONVERT(NVARCHAR(MAX), [PublicKey], 1)
110 + N' WITH PRIVATE KEY (BINARY = '
111 + CONVERT(NVARCHAR(MAX), [PrivateKey], 1)
112 + N', DECRYPTION BY PASSWORD = N''OtherPassword'''
113 + N', ENCRYPTION BY PASSWORD = ''WeakPassword'');'
114FROM #CertInfo;
115
116PRINT @SQL;
117EXEC (@SQL);
118
119CREATE USER [PermissionsUser] FROM CERTIFICATE [PermissionsCert];
120
121--DROP TABLE dbo.[TableWithoutTrigger];
122CREATE TABLE dbo.[TableWithoutTrigger]
123(
124 [TableWithoutTriggerID] INT NOT NULL IDENTITY(1, 1)
125 CONSTRAINT [PK_TableWithoutTrigger] PRIMARY KEY,
126 [SomeValue] NVARCHAR(50)
127);
128
129GRANT INSERT ON [dbo].[TableWithoutTrigger] TO [PermissionsUser];
130
131
132CREATE TABLE dbo.[TableWithTrigger]
133(
134 [TableWithTriggerID] INT NOT NULL IDENTITY(1, 1)
135 CONSTRAINT [PK_TableWithTrigger] PRIMARY KEY,
136 [SomeOtherValue] NVARCHAR(50)
137);
138
139GRANT INSERT ON [dbo].[TableWithTrigger] TO [PermissionsUser];
140
141
142CREATE TABLE dbo.[TablePopulatedByTrigger]
143(
144 [TablePopulatedByTriggerID] INT NOT NULL IDENTITY(1, 1)
145 CONSTRAINT [PK_TablePopulatedByTrigger] PRIMARY KEY,
146 [DuplicatedValue] NVARCHAR(50)
147);
148GO
149
150CREATE TRIGGER dbo.CopySomeOtherValue
151ON dbo.[TableWithTrigger]
152AFTER INSERT
153AS
154BEGIN
155 SET NOCOUNT ON;
156
157 INSERT INTO dbo.[TablePopulatedByTrigger] ([DuplicatedValue])
158 SELECT ins.[SomeOtherValue]
159 FROM inserted ins;
160END;
161GO
162
163USE [DatabaseA];
164
165EXECUTE AS LOGIN = 'JohnnyLunchbucket';
166SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
167GO
168
169SELECT * FROM [DatabaseB].[dbo].[TableWithoutTrigger];
170SELECT * FROM [DatabaseB].[dbo].[TableWithTrigger];
171SELECT * FROM [DatabaseB].[dbo].[TablePopulatedByTrigger];
172INSERT INTO [DatabaseB].[dbo].[TableWithoutTrigger] ([SomeValue]) VALUES (N'test 0');
173USE [DatabaseB];
174/* -- All 5 statements above get the following error:
175Msg 916, Level 14, State 1, Line xxxxxx
176The server principal "JohnnyLunchbucket" is not able to access the database "DatabaseB" under the current security context.
177*/
178
179
180EXEC [dbo].[InsertIntoTableWithoutTrigger] @SomeValue = N'test A'; -- SUCCESS!!!
181
182EXEC [dbo].[InsertIntoTableWithTrigger]; -- ERROR:
183/*
184Msg 916, Level 14, State 1, Procedure CopySomeOtherValue, Line xxxxxx
185The server principal "JohnnyLunchbucket" is not able to access the database "DatabaseB" under the current security context.
186*/
187
188REVERT;
189SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
190
191
192SELECT * FROM [DatabaseB].[dbo].[TableWithoutTrigger];
193-- 1 test A
194
195USE [DatabaseB];
196
197ADD COUNTER SIGNATURE
198 TO dbo.[CopySomeOtherValue]
199 BY CERTIFICATE [PermissionsCert]
200 WITH PASSWORD = 'WeakPassword';
201GO
202
203
204USE [DatabaseA];
205
206EXECUTE AS LOGIN = 'JohnnyLunchbucket';
207SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
208GO
209
210INSERT INTO [DatabaseB].[dbo].[TableWithTrigger] ([SomeOtherValue]) VALUES (N'Test B');
211INSERT INTO [DatabaseB].[dbo].[TablePopulatedByTrigger] ([DuplicatedValue]) VALUES (N'Test B');
212/*
213Msg 916, Level 14, State 1, Line xxxxxx
214The server principal "JohnnyLunchbucket" is not able to access the database "DatabaseB" under the current security context.
215*/
216
217
218EXEC [dbo].[InsertIntoTableWithTrigger]; -- SUCCESS!!!
219
220REVERT;
221SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
222
223SELECT * FROM [DatabaseB].[dbo].[TableWithTrigger];
224SELECT * FROM [DatabaseB].[dbo].[TablePopulatedByTrigger];
225-- 2 968DB092-C3DE-4E4B-92B9-E21CA551A5FA
226-- 1 968DB092-C3DE-4E4B-92B9-E21CA551A5FA