Column encryption is one of the oldest encryption method in this product, I think deprecated should be the right position in encryption level in these days. Anyway – it can be really handy to use it for storing sensitive data.
I´m continuing with my DB2 install described before. Let´s try to recreate acc_test table with encrypted credit cards numbers. First of all, we need to drop old table:
db2 => drop table acc_test
DB20000I The SQL command completed successfully.
Let´s have a file /home/db2inst/enc_cc.sql containing:
connect to sample;
SET ENCRYPTION PASSWORD =’@w4!_ui‘;
create table acc_test (no smallint,accno varchar(50) for bit data);
INSERT INTO acc_test VALUES (01,ENCRYPT(‚4255-8613-9716-5076‘));
INSERT INTO acc_test VALUES (02,ENCRYPT(‚4531-2930-4967-2232‘));
INSERT INTO acc_test VALUES (03,ENCRYPT(‚4548-7929-1826-4899‘));
INSERT INTO acc_test VALUES (04,ENCRYPT(‚4691-3746-9407-0405‘));
INSERT INTO acc_test VALUES (05,ENCRYPT(‚4649-1103-3295-2551‘));
INSERT INTO acc_test VALUES (06,ENCRYPT(‚4997-5257-8004-9774‘));
INSERT INTO acc_test VALUES (07,ENCRYPT(‚4692-4078-2653-8573‘));
INSERT INTO acc_test VALUES (08,ENCRYPT(‚4044-8832-9347-1412‘));
INSERT INTO acc_test VALUES (09,ENCRYPT(‚4688-8299-4664-4385‘));
INSERT INTO acc_test VALUES (10,ENCRYPT(‚4339-4136-0219-4499‘));
INSERT INTO acc_test VALUES (11,ENCRYPT(‚4016-7845-2950-8098‘));
INSERT INTO acc_test VALUES (12,ENCRYPT(‚4961-6591-9364-9979‘));
INSERT INTO acc_test VALUES (13,ENCRYPT(‚4787-0768-3223-3820‘));
INSERT INTO acc_test VALUES (14,ENCRYPT(‚4582-2114-8843-1397‘));
INSERT INTO acc_test VALUES (15,ENCRYPT(‚4549-3001-8407-0658‘));
INSERT INTO acc_test VALUES (16,ENCRYPT(‚4464-3676-8558-3399‘));
INSERT INTO acc_test VALUES (17,ENCRYPT(‚4804-1261-5366-8044‘));
INSERT INTO acc_test VALUES (18,ENCRYPT(‚4284-1059-8455-5702‘));
INSERT INTO acc_test VALUES (19,ENCRYPT(‚4127-1856-7120-0292‘));
INSERT INTO acc_test VALUES (20,ENCRYPT(‚4633-7926-0863-2443‘));
commit;
terminate;
terminate;
And we can run it, massive output is expected:
db2inst1@debian:~$ db2 -tvsf /home/db2inst1/enc_cc.sql
connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 10.5.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
SET ENCRYPTION PASSWORD =’@w4!_ui‘
DB20000I The SQL command completed successfully.
create table acc_test (no smallint,accno varchar(50))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (01,ENCRYPT(‚4255-8613-9716-5076‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (02,ENCRYPT(‚4531-2930-4967-2232‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (03,ENCRYPT(‚4548-7929-1826-4899‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (04,ENCRYPT(‚4691-3746-9407-0405‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (05,ENCRYPT(‚4649-1103-3295-2551‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (06,ENCRYPT(‚4997-5257-8004-9774‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (07,ENCRYPT(‚4692-4078-2653-8573‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (08,ENCRYPT(‚4044-8832-9347-1412‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (09,ENCRYPT(‚4688-8299-4664-4385‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (10,ENCRYPT(‚4339-4136-0219-4499‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (11,ENCRYPT(‚4016-7845-2950-8098‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (12,ENCRYPT(‚4961-6591-9364-9979‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (13,ENCRYPT(‚4787-0768-3223-3820‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (14,ENCRYPT(‚4582-2114-8843-1397‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (15,ENCRYPT(‚4549-3001-8407-0658‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (16,ENCRYPT(‚4464-3676-8558-3399‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (17,ENCRYPT(‚4804-1261-5366-8044‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (18,ENCRYPT(‚4284-1059-8455-5702‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (19,ENCRYPT(‚4127-1856-7120-0292‘))
DB20000I The SQL command completed successfully.
INSERT INTO acc_test VALUES (20,ENCRYPT(‚4633-7926-0863-2443‘))
DB20000I The SQL command completed successfully.
commit
DB20000I The SQL command completed successfully.
terminate
DB20000I The TERMINATE command completed successfully.
1) Quick check for storing in operating system
More details about investigation proper file in my previous article about DB2.
db2inst1@debian:~/db2inst1/NODE0000/SAMPLE/T0000003$ time strings -a C0000000.LRG | grep "[0-9]\{4\}\-[0-9]\{4\}\-[0-9]\{4\}\-[0-9]\{4\}"
real 0m0.878s
user 0m0.256s
sys 0m0.044s
Ok, looks like sensitive data are really encrypted.
2) Selecting values from table
After common select, we have some nice looking results:
Classical select statement with decrypt function is failing now:
db2 => select no, decrypt_char(accno) as accno from acc_test
NO ACCNO
—— ——————————————
SQL20143N The encryption or decryption function failed, because the
ENCRYPTION PASSWORD value is not set. SQLSTATE=51039
db2 =>
In this case, any user who wants to see accno value needs to know password and input it prior to selecting data:
db2 => SET ENCRYPTION PASSWORD ='@w4!_ui'
DB20000I The SQL command completed successfully.
db2 => select no, decrypt_char(accno) as accno from acc_test
NO ACCNO
------ ------------------------------------------
1 4255-8613-9716-5076
2 4531-2930-4967-2232
3 4548-7929-1826-4899
4 4691-3746-9407-0405
5 4649-1103-3295-2551
6 4997-5257-8004-9774
7 4692-4078-2653-8573
8 4044-8832-9347-1412
9 4688-8299-4664-4385
10 4339-4136-0219-4499
11 4016-7845-2950-8098
12 4961-6591-9364-9979
13 4787-0768-3223-3820
14 4582-2114-8843-1397
15 4549-3001-8407-0658
16 4464-3676-8558-3399
17 4804-1261-5366-8044
18 4284-1059-8455-5702
19 4127-1856-7120-0292
20 4633-7926-0863-2443
20 record(s) selected.
db2 =>
SET ENCRYPTION is a special registry value. It is not connected with authority and is used only for encrypting. For more information please check DB2 documentation.
-a-