{"id":376,"date":"2014-02-11T13:16:22","date_gmt":"2014-02-11T13:16:22","guid":{"rendered":"http:\/\/it.tuxie.eu\/?p=376"},"modified":"2014-02-16T08:55:35","modified_gmt":"2014-02-16T08:55:35","slug":"db2-column-encryption","status":"publish","type":"post","link":"http:\/\/it.tuxie.eu\/?p=376","title":{"rendered":"DB2 – column encryption"},"content":{"rendered":"

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.<\/p>\n

<\/p>\n

I\u00b4m continuing with my DB2 install described before. Let\u00b4s try to recreate acc_test table with encrypted credit cards numbers. First of all, we need to drop old table:<\/p>\n

db2 => drop table acc_test<\/strong><\/span><\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

Let\u00b4s have a file \/home\/db2inst\/enc_cc.sql<\/strong> containing:<\/p>\n

connect to sample;<\/span>
\n SET ENCRYPTION PASSWORD =’@w4!_ui‘;<\/span>
\n create table acc_test (no smallint,accno varchar(50)\u00a0for bit data);<\/span>
\n INSERT INTO acc_test VALUES (01,ENCRYPT(‚4255-8613-9716-5076‘));<\/span>
\n INSERT INTO acc_test VALUES (02,ENCRYPT(‚4531-2930-4967-2232‘));<\/span>
\n INSERT INTO acc_test VALUES (03,ENCRYPT(‚4548-7929-1826-4899‘));<\/span>
\n INSERT INTO acc_test VALUES (04,ENCRYPT(‚4691-3746-9407-0405‘));<\/span>
\n INSERT INTO acc_test VALUES (05,ENCRYPT(‚4649-1103-3295-2551‘));<\/span>
\n INSERT INTO acc_test VALUES (06,ENCRYPT(‚4997-5257-8004-9774‘));<\/span>
\n INSERT INTO acc_test VALUES (07,ENCRYPT(‚4692-4078-2653-8573‘));<\/span>
\n INSERT INTO acc_test VALUES (08,ENCRYPT(‚4044-8832-9347-1412‘));<\/span>
\n INSERT INTO acc_test VALUES (09,ENCRYPT(‚4688-8299-4664-4385‘));<\/span>
\n INSERT INTO acc_test VALUES (10,ENCRYPT(‚4339-4136-0219-4499‘));<\/span>
\n INSERT INTO acc_test VALUES (11,ENCRYPT(‚4016-7845-2950-8098‘));<\/span>
\n INSERT INTO acc_test VALUES (12,ENCRYPT(‚4961-6591-9364-9979‘));<\/span>
\n INSERT INTO acc_test VALUES (13,ENCRYPT(‚4787-0768-3223-3820‘));<\/span>
\n INSERT INTO acc_test VALUES (14,ENCRYPT(‚4582-2114-8843-1397‘));<\/span>
\n INSERT INTO acc_test VALUES (15,ENCRYPT(‚4549-3001-8407-0658‘));<\/span>
\n INSERT INTO acc_test VALUES (16,ENCRYPT(‚4464-3676-8558-3399‘));<\/span>
\n INSERT INTO acc_test VALUES (17,ENCRYPT(‚4804-1261-5366-8044‘));<\/span>
\n INSERT INTO acc_test VALUES (18,ENCRYPT(‚4284-1059-8455-5702‘));<\/span>
\n INSERT INTO acc_test VALUES (19,ENCRYPT(‚4127-1856-7120-0292‘));<\/span>
\n INSERT INTO acc_test VALUES (20,ENCRYPT(‚4633-7926-0863-2443‘));<\/span>
\n commit;<\/span>
\n terminate;<\/span>
\n terminate;<\/span><\/p>\n

And we can run it, massive output is expected:<\/p>\n

db2inst1@debian:~$ db2 -tvsf \/home\/db2inst1\/enc_cc.sql<\/strong><\/span><\/span>
\nconnect to sample<\/span><\/p>\n

\u00a0\u00a0 Database Connection Information<\/span><\/p>\n

\u00a0Database server\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = DB2\/LINUXX8664 10.5.0<\/span>
\n\u00a0SQL authorization ID\u00a0\u00a0 = DB2INST1<\/span>
\n\u00a0Local database alias\u00a0\u00a0 = SAMPLE<\/span><\/p>\n

SET ENCRYPTION PASSWORD =’@w4!_ui‘<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

create table acc_test (no smallint,accno varchar(50))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (01,ENCRYPT(‚4255-8613-9716-5076‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (02,ENCRYPT(‚4531-2930-4967-2232‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (03,ENCRYPT(‚4548-7929-1826-4899‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (04,ENCRYPT(‚4691-3746-9407-0405‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (05,ENCRYPT(‚4649-1103-3295-2551‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (06,ENCRYPT(‚4997-5257-8004-9774‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (07,ENCRYPT(‚4692-4078-2653-8573‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (08,ENCRYPT(‚4044-8832-9347-1412‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (09,ENCRYPT(‚4688-8299-4664-4385‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (10,ENCRYPT(‚4339-4136-0219-4499‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (11,ENCRYPT(‚4016-7845-2950-8098‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (12,ENCRYPT(‚4961-6591-9364-9979‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (13,ENCRYPT(‚4787-0768-3223-3820‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (14,ENCRYPT(‚4582-2114-8843-1397‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (15,ENCRYPT(‚4549-3001-8407-0658‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (16,ENCRYPT(‚4464-3676-8558-3399‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (17,ENCRYPT(‚4804-1261-5366-8044‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (18,ENCRYPT(‚4284-1059-8455-5702‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (19,ENCRYPT(‚4127-1856-7120-0292‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

INSERT INTO acc_test VALUES (20,ENCRYPT(‚4633-7926-0863-2443‘))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

commit<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

terminate<\/span>
\nDB20000I\u00a0 The TERMINATE command completed successfully.<\/span><\/p>\n

1) Quick check for storing in operating system<\/strong><\/p>\n

More details about investigation proper file in my previous article about DB2<\/a>.<\/p>\n

db2inst1@debian:~\/db2inst1\/NODE0000\/SAMPLE\/T0000003$ time strings -a C0000000.LRG | grep \"[0-9]\\{4\\}\\-[0-9]\\{4\\}\\-[0-9]\\{4\\}\\-[0-9]\\{4\\}<\/strong><\/span><\/span>\"<\/span><\/code><\/p>\n

real\u00a0\u00a0 \u00a00m0.878s<\/span>
\nuser\u00a0\u00a0 \u00a00m0.256s<\/span>
\nsys\u00a0\u00a0 \u00a00m0.044s<\/span><\/p>\n

Ok, looks like sensitive data are really encrypted.<\/p>\n

2) Selecting values from table<\/strong><\/p>\n

After common select, we have some nice looking results:<\/p>\n

\"\"<\/p>\n

Classical select statement with decrypt function is failing now:<\/p>\n

db2 => select no, decrypt_char(accno) as accno from acc_test<\/strong><\/span><\/span><\/p>\n

NO\u00a0\u00a0\u00a0\u00a0 ACCNO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n—— ——————————————<\/span>
\nSQL20143N\u00a0 The encryption or decryption function failed, because the <\/span>
\nENCRYPTION PASSWORD value is not set.\u00a0 SQLSTATE=51039<\/span>
\ndb2 => <\/span><\/p>\n

In this case, any user who wants to see accno value needs to know password and input it prior to selecting data:<\/p>\n

db2 => SET ENCRYPTION PASSWORD ='@w4!_ui' <\/code><\/strong><\/span><\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span>
\ndb2 => select no, decrypt_char(accno) as accno from acc_test<\/strong><\/span><\/span><\/p>\n

NO\u00a0\u00a0\u00a0\u00a0 ACCNO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n------ ------------------------------------------<\/code><\/span>
\n\u00a0\u00a0\u00a0\u00a0 1 4255-8613-9716-5076\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 2 4531-2930-4967-2232\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 3 4548-7929-1826-4899\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 4 4691-3746-9407-0405\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 5 4649-1103-3295-2551\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 6 4997-5257-8004-9774\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 7 4692-4078-2653-8573\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 8 4044-8832-9347-1412\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 9 4688-8299-4664-4385\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 10 4339-4136-0219-4499\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 11 4016-7845-2950-8098\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 12 4961-6591-9364-9979\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 13 4787-0768-3223-3820\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 14 4582-2114-8843-1397\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 15 4549-3001-8407-0658\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 16 4464-3676-8558-3399\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 17 4804-1261-5366-8044\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 18 4284-1059-8455-5702\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 19 4127-1856-7120-0292\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 20 4633-7926-0863-2443\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span><\/p>\n

\u00a0 20 record(s) selected.<\/span><\/p>\n

db2 => <\/span><\/p>\n

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>.<\/p>\n

-a-<\/p>\n","protected":false},"excerpt":{"rendered":"

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.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7,11],"tags":[],"_links":{"self":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/376"}],"collection":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=376"}],"version-history":[{"count":0,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/376\/revisions"}],"wp:attachment":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=376"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=376"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=376"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}