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
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
|
ij> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
-- This script tests error cases where encryption of an un-encryped database
-- or re-encrption of an encrypted databases with new password/key should fail
-- when
-- 1) the database is booted read-only mode using jar subprotocol.
-- 2) the databases with log archive mode enabled. It should
--- succeed after disabling the log archive mode.
-- 3) when restoring from backup.
--------------------------------------------------------------------
-- Case : create a plain database, jar it up and then attempt
-- to encrypt using the jar protocol
connect 'jdbc:derby:endb;create=true';
ij> create table t1(a int ) ;
0 rows inserted/updated/deleted
ij> insert into t1 values(1) ;
1 row inserted/updated/deleted
ij> insert into t1 values(2) ;
1 row inserted/updated/deleted
ij> insert into t1 values(3) ;
1 row inserted/updated/deleted
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
ij> insert into t1 values(5) ;
1 row inserted/updated/deleted
ij> disconnect;
ij> connect 'jdbc:derby:endb;shutdown=true';
ERROR 08006: Database 'endb' shutdown.
ij> -- now create archive of the database.
connect 'jdbc:derby:wombat;create=true';
ij> create procedure CREATEARCHIVE(jarName VARCHAR(20), path VARCHAR(20), dbName VARCHAR(20))
LANGUAGE JAVA PARAMETER STYLE JAVA
NO SQL
EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.dbjarUtil.createArchive';
0 rows inserted/updated/deleted
ij> -- archive the "endb" and put in "ina.jar" with dbname as "jdb1".
call CREATEARCHIVE('ina.jar', 'endb', 'jdb1');
0 rows inserted/updated/deleted
ij> disconnect;
ij> -- try encrypting the database 'jdb1' using the jar protocol.
-- should fail
connect 'jdbc:derby:jar:(ina.jar)jdb1;dataEncryption=true;bootPassword=xyz1234abc';
ERROR XJ040: Failed to start database 'jar:(ina.jar)jdb1' with class loader XXXX, see the next exception for details.
ERROR XBCXQ: Cannot encrypt, re-encrypt or decrypt a read-only database.
ij> connect 'jdbc:derby:jar:(ina.jar)jdb1;dataEncryption=true;encryptionKey=6162636465666768';
ERROR XJ040: Failed to start database 'jar:(ina.jar)jdb1' with class loader XXXX, see the next exception for details.
ERROR XBCXQ: Cannot encrypt, re-encrypt or decrypt a read-only database.
ij> -- Case: create a a jar file of an encrypted database and
-- try re-encrypting it while boot it with the jar sub protocol
-- encrypt the databases.
connect 'jdbc:derby:endb;dataEncryption=true;bootPassword=xyz1234abc';
ij> insert into t1 values(6);
1 row inserted/updated/deleted
ij> insert into t1 values(7);
1 row inserted/updated/deleted
ij> disconnect;
ij> connect 'jdbc:derby:endb;shutdown=true';
ERROR 08006: Database 'endb' shutdown.
ij> -- create archive of encrypted database.
connect 'jdbc:derby:wombat';
ij> call CREATEARCHIVE('ina.jar', 'endb', 'jdb1');
0 rows inserted/updated/deleted
ij> disconnect;
ij> -- test the encrypted jar db
connect 'jdbc:derby:jar:(ina.jar)jdb1;dataEncryption=true;bootPassword=xyz1234abc;';
ij> select * from t1;
A
-----------
1
2
3
4
5
6
7
7 rows selected
ij> disconnect;
ij> connect 'jdbc:derby:;shutdown=true';
ERROR XJ015: Derby system shutdown.
ij> -- now finally attempt to re-encrypt the encrypted jar db with
-- a new boot password, it should fail.
connect 'jdbc:derby:jar:(ina.jar)jdb1;dataEncryption=true;bootPassword=xyz1234abc;newBootPassword=new1234xyz';
ERROR XJ040: Failed to start database 'jar:(ina.jar)jdb1' with class loader XXXX, see the next exception for details.
ERROR XBCXQ: Cannot encrypt, re-encrypt or decrypt a read-only database.
ij> -- Decrypting a read-only db should also fail.
connect 'jdbc:derby:jar:(ina.jar)jdb1;bootPassword=xyz1234abc;decryptDatabase=true';
ERROR XJ040: Failed to start database 'jar:(ina.jar)jdb1' with class loader XXXX, see the next exception for details.
ERROR XBCXQ: Cannot encrypt, re-encrypt or decrypt a read-only database.
ij> -- testing (re) encryption of a database
-- when the log arhive mode enabled -----
-- Case : configuring a un-encrypted database for
-- encryption should fail, when log archive mode is enabled.
connect 'jdbc:derby:wombat';
ij> create table emp(id int, name char (200));
0 rows inserted/updated/deleted
ij> insert into emp values (1, 'john');
1 row inserted/updated/deleted
ij> insert into emp values(2 , 'mike');
1 row inserted/updated/deleted
ij> insert into emp values(3 , 'robert');
1 row inserted/updated/deleted
ij> -- take a backup , this is used later.
call SYSCS_UTIL.SYSCS_BACKUP_DATABASE('extinout/mybackup');
0 rows inserted/updated/deleted
ij> -- enable the log archive mode and perform backup.
call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
'extinout/mybackup1', 0);
0 rows inserted/updated/deleted
ij> insert into emp select * from emp ;
3 rows inserted/updated/deleted
ij> insert into emp select * from emp ;
6 rows inserted/updated/deleted
ij> insert into emp select * from emp ;
12 rows inserted/updated/deleted
ij> disconnect;
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> -- attempt to configure the database for encryption using password.
connect 'jdbc:derby:wombat;dataEncryption=true;bootPassword=xyz1234abc;';
ERROR XJ040: Failed to start database 'wombat' with class loader XXXX, see the next exception for details.
ERROR XBCXS: Cannot encrypt, re-encrypt or decrypt a database when it is in the log archive mode.
ij> -- attempt to configure the database for encryption using key.
connect 'jdbc:derby:wombat;dataEncryption=true;encryptionKey=6162636465666768';
ERROR XJ040: Failed to start database 'wombat' with class loader XXXX, see the next exception for details.
ERROR XBCXS: Cannot encrypt, re-encrypt or decrypt a database when it is in the log archive mode.
ij> -- disable log archive mode and then reattempt encryption on
-- next boot.
connect 'jdbc:derby:wombat';
ij> select count(*) from emp ;
1
-----------
24
1 row selected
ij> call SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1);
0 rows inserted/updated/deleted
ij> disconnect;
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> -- Case: encrypt the database, with log archive mode disabled.
connect 'jdbc:derby:wombat;dataEncryption=true;bootPassword=xyz1234abc;';
ij> select count(*) from emp;
1
-----------
24
1 row selected
ij> create table t1(a int ) ;
0 rows inserted/updated/deleted
ij> insert into t1 values(1);
1 row inserted/updated/deleted
ij> -- enable log archive mode and perform backup.
call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
'extinout/mybackup2', 0);
0 rows inserted/updated/deleted
ij> insert into t1 values(2);
1 row inserted/updated/deleted
ij> insert into t1 values(3);
1 row inserted/updated/deleted
ij> disconnect;
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> -- attempt to re-encrypt the database , with log archive mode enabled.
-- it should fail.
connect 'jdbc:derby:wombat;dataEncryption=true;bootPassword=xyz1234abc;newBootPassword=new1234xyz';
ERROR XJ040: Failed to start database 'wombat' with class loader XXXX, see the next exception for details.
ERROR XBCXS: Cannot encrypt, re-encrypt or decrypt a database when it is in the log archive mode.
ij> -- Attempt to decrypt the database with log archive mode enabled.
-- It should fail.
connect 'jdbc:derby:wombat;bootPassword=xyz1234abc;decryptDatabase=true';
ERROR XJ040: Failed to start database 'wombat' with class loader XXXX, see the next exception for details.
ERROR XBCXS: Cannot encrypt, re-encrypt or decrypt a database when it is in the log archive mode.
ij> -- reboot the db and disable the log archive mode
connect 'jdbc:derby:wombat;bootPassword=xyz1234abc';
ij> select * from t1;
A
-----------
1
2
3
3 rows selected
ij> call SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1);
0 rows inserted/updated/deleted
ij> disconnect;
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> -- re-encrypt the database, with the log archive mode disabled.
-- it should pass.
connect 'jdbc:derby:wombat;dataEncryption=true;bootPassword=xyz1234abc;newBootPassword=new1234xyz';
ij> select * from t1;
A
-----------
1
2
3
3 rows selected
ij> select count(*) from emp;
1
-----------
24
1 row selected
ij> disconnect;
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> -- testing re-encryption with external key on a log archived database.
-- restore from the backup orignal un-encrypted database and
-- encrypt with a key.
connect 'jdbc:derby:wombat;restoreFrom=extinout/mybackup1/wombat';
ij> select count(*) from emp;
1
-----------
3
1 row selected
ij> call SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1);
0 rows inserted/updated/deleted
ij> disconnect;
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> -- encrypt with a key and enable the log archive mode.
connect 'jdbc:derby:wombat;dataEncryption=true;encryptionKey=6162636465666768';
ij> select count(*) from emp;
1
-----------
3
1 row selected
ij> create table t1(a int ) ;
0 rows inserted/updated/deleted
ij> insert into t1 values(1);
1 row inserted/updated/deleted
ij> -- enable log archive mode and perform backup.
call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
'extinout/mybackup2', 0);
0 rows inserted/updated/deleted
ij> insert into t1 values(2);
1 row inserted/updated/deleted
ij> insert into t1 values(3);
1 row inserted/updated/deleted
ij> disconnect;
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> -- attempt to re-encrypt the database with external key, with log archive mode enabled.
-- it should fail.
connect 'jdbc:derby:wombat;encryptionKey=6162636465666768;newEncryptionKey=5666768616263646';
ERROR XJ040: Failed to start database 'wombat' with class loader XXXX, see the next exception for details.
ERROR XBCXS: Cannot encrypt, re-encrypt or decrypt a database when it is in the log archive mode.
ij> -- reboot the db and disable the log archive mode
connect 'jdbc:derby:wombat;encryptionKey=6162636465666768';
ij> select * from t1;
A
-----------
1
2
3
3 rows selected
ij> call SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1);
0 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE('extinout/mybackup1');
0 rows inserted/updated/deleted
ij> disconnect;
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> -- now re-encrypt the database, with the log archive mode disbaled.
-- it should pass.
connect 'jdbc:derby:wombat;encryptionKey=6162636465666768;newEncryptionKey=5666768616263646';
ij> select * from t1;
A
-----------
1
2
3
3 rows selected
ij> select count(*) from emp;
1
-----------
3
1 row selected
ij> disconnect;
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> -- Finally, decrypt the database with log archive mode disabled.
-- It should pass.
connect 'jdbc:derby:wombat;encryptionKey=5666768616263646;decryptDatabase=true';
ij> select * from t1;
A
-----------
1
2
3
3 rows selected
ij> select count(*) from emp;
1
-----------
3
1 row selected
ij> disconnect;
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> -- restore from backup and attempt to configure database for encryption.
-- it shoud fail.
connect 'jdbc:derby:wombat;restoreFrom=extinout/mybackup/wombat;dataEncryption=true;bootPassword=xyz1234abc';
ERROR XJ081: Conflicting create/restore/recovery attributes specified.
ij> -- creating database from backup and attempting to configure database for encryption.
-- it shoud fail.
connect 'jdbc:derby:wombat_new;createFrom=extinout/mybackup/wombat;dataEncryption=true;bootPassword=xyz1234abc';
ERROR XJ081: Conflicting create/restore/recovery attributes specified.
ij> -- restore from backup and attempt to reEncrypt
-- it should fail.
connect 'jdbc:derby:wombat;restoreFrom=extinout/mybackup1/wombat;encryptionKey=6162636465666768;newEncryptionKey=5666768616263646';
ERROR XJ081: Conflicting create/restore/recovery attributes specified.
ij> -- restore from backup without re-encryption
-- it shoud boot.
connect 'jdbc:derby:wombat;restoreFrom=extinout/mybackup1/wombat;encryptionKey=6162636465666768';
ij> select count(*) from emp;
1
-----------
3
1 row selected
ij> disconnect;
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij>
|
