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
|
--
-- 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';
create table t1(a int ) ;
insert into t1 values(1) ;
insert into t1 values(2) ;
insert into t1 values(3) ;
insert into t1 values(4) ;
insert into t1 values(5) ;
disconnect;
connect 'jdbc:derby:endb;shutdown=true';
-- now create archive of the database.
connect 'jdbc:derby:wombat;create=true';
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';
-- archive the "endb" and put in "ina.jar" with dbname as "jdb1".
call CREATEARCHIVE('ina.jar', 'endb', 'jdb1');
disconnect;
-- try encrypting the database 'jdb1' using the jar protocol.
-- should fail
connect 'jdbc:derby:jar:(ina.jar)jdb1;dataEncryption=true;bootPassword=xyz1234abc';
connect 'jdbc:derby:jar:(ina.jar)jdb1;dataEncryption=true;encryptionKey=6162636465666768';
-- 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';
insert into t1 values(6);
insert into t1 values(7);
disconnect;
connect 'jdbc:derby:endb;shutdown=true';
-- create archive of encrypted database.
connect 'jdbc:derby:wombat';
call CREATEARCHIVE('ina.jar', 'endb', 'jdb1');
disconnect;
-- test the encrypted jar db
connect 'jdbc:derby:jar:(ina.jar)jdb1;dataEncryption=true;bootPassword=xyz1234abc;';
select * from t1;
disconnect;
connect 'jdbc:derby:;shutdown=true';
-- 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';
-- Decrypting a read-only db should also fail.
connect 'jdbc:derby:jar:(ina.jar)jdb1;bootPassword=xyz1234abc;decryptDatabase=true';
-- 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';
create table emp(id int, name char (200));
insert into emp values (1, 'john');
insert into emp values(2 , 'mike');
insert into emp values(3 , 'robert');
-- take a backup , this is used later.
call SYSCS_UTIL.SYSCS_BACKUP_DATABASE('extinout/mybackup');
-- enable the log archive mode and perform backup.
call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
'extinout/mybackup1', 0);
insert into emp select * from emp ;
insert into emp select * from emp ;
insert into emp select * from emp ;
disconnect;
connect 'jdbc:derby:wombat;shutdown=true';
-- attempt to configure the database for encryption using password.
connect 'jdbc:derby:wombat;dataEncryption=true;bootPassword=xyz1234abc;';
-- attempt to configure the database for encryption using key.
connect 'jdbc:derby:wombat;dataEncryption=true;encryptionKey=6162636465666768';
-- disable log archive mode and then reattempt encryption on
-- next boot.
connect 'jdbc:derby:wombat';
select count(*) from emp ;
call SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1);
disconnect;
connect 'jdbc:derby:wombat;shutdown=true';
-- Case: encrypt the database, with log archive mode disabled.
connect 'jdbc:derby:wombat;dataEncryption=true;bootPassword=xyz1234abc;';
select count(*) from emp;
create table t1(a int ) ;
insert into t1 values(1);
-- enable log archive mode and perform backup.
call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
'extinout/mybackup2', 0);
insert into t1 values(2);
insert into t1 values(3);
disconnect;
connect 'jdbc:derby:wombat;shutdown=true';
-- attempt to re-encrypt the database , with log archive mode enabled.
-- it should fail.
connect 'jdbc:derby:wombat;dataEncryption=true;bootPassword=xyz1234abc;newBootPassword=new1234xyz';
-- Attempt to decrypt the database with log archive mode enabled.
-- It should fail.
connect 'jdbc:derby:wombat;bootPassword=xyz1234abc;decryptDatabase=true';
-- reboot the db and disable the log archive mode
connect 'jdbc:derby:wombat;bootPassword=xyz1234abc';
select * from t1;
call SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1);
disconnect;
connect 'jdbc:derby:wombat;shutdown=true';
-- re-encrypt the database, with the log archive mode disabled.
-- it should pass.
connect 'jdbc:derby:wombat;dataEncryption=true;bootPassword=xyz1234abc;newBootPassword=new1234xyz';
select * from t1;
select count(*) from emp;
disconnect;
connect 'jdbc:derby:wombat;shutdown=true';
-- 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';
select count(*) from emp;
call SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1);
disconnect;
connect 'jdbc:derby:wombat;shutdown=true';
-- encrypt with a key and enable the log archive mode.
connect 'jdbc:derby:wombat;dataEncryption=true;encryptionKey=6162636465666768';
select count(*) from emp;
create table t1(a int ) ;
insert into t1 values(1);
-- enable log archive mode and perform backup.
call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
'extinout/mybackup2', 0);
insert into t1 values(2);
insert into t1 values(3);
disconnect;
connect 'jdbc:derby:wombat;shutdown=true';
-- 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';
-- reboot the db and disable the log archive mode
connect 'jdbc:derby:wombat;encryptionKey=6162636465666768';
select * from t1;
call SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1);
call SYSCS_UTIL.SYSCS_BACKUP_DATABASE('extinout/mybackup1');
disconnect;
connect 'jdbc:derby:wombat;shutdown=true';
-- now re-encrypt the database, with the log archive mode disbaled.
-- it should pass.
connect 'jdbc:derby:wombat;encryptionKey=6162636465666768;newEncryptionKey=5666768616263646';
select * from t1;
select count(*) from emp;
disconnect;
connect 'jdbc:derby:wombat;shutdown=true';
-- Finally, decrypt the database with log archive mode disabled.
-- It should pass.
connect 'jdbc:derby:wombat;encryptionKey=5666768616263646;decryptDatabase=true';
select * from t1;
select count(*) from emp;
disconnect;
connect 'jdbc:derby:wombat;shutdown=true';
-- 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';
-- 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';
-- restore from backup and attempt to reEncrypt
-- it should fail.
connect 'jdbc:derby:wombat;restoreFrom=extinout/mybackup1/wombat;encryptionKey=6162636465666768;newEncryptionKey=5666768616263646';
-- restore from backup without re-encryption
-- it shoud boot.
connect 'jdbc:derby:wombat;restoreFrom=extinout/mybackup1/wombat;encryptionKey=6162636465666768';
select count(*) from emp;
disconnect;
connect 'jdbc:derby:wombat;shutdown=true';
|
