Thursday, October 20, 2005

Encryption And dbms_obfuscation_toolkit

1. Introduction

Enterprise Security consists of layers of security. Following lists the layer of security.

  • Firewall around the whole information infrastructure of the enterprise. This prevents any use to access the data inside the organization. But no firewall is full proof. Moreover there is always a need to poke holes into the firewall to allow legitimate network traffic.
  • Authentication: If a user somehow gets inside the enterprise system, he would need to provide some kind of credentials (password, security certificates etc) to access the server or the database.
  • Authorization. After passing the previous layer of authentication users must be allowed to access only that information that he/she is authorized to access. If the user does not have access to salary information then he should not be allowed to view that information. If a hacker gets into the system but has no access of any kind, then the data is still protected.
  • In this day and age it is possible that the intruder breaks all the above security layers and gets access to the system. In this case we would have to plan for the final layer of security i.e. encryption. We must alter(encrypt) the data in such a way that it does not make sense to the intruder. Encryption is a process which alters the data so that, it is garbage to everyone except to those who knows how to decrypt the data.

2. Encryption

Encryption consists of two different components.

  • Encryption algorithm
  • Encryption Key

Encryption alogrithm applies the Encryption key to the data and returns the altered data. For example if we need to encrypt the salary column, we can store salary*5 instead of the salary itself. Here the encryption key is 5 and the algorithm is to multiply the data with the encryption key.

To read the data back we would have to apply the process called decryption. Decryption of data would involve reversing the encryption algorithm and use the same key which was used to encrypt the data. In the above example we would have to divide the encrypted data by 5 to get the databack.

Encryption algorithms used nowdays belong to public domain. i.e. everyone knows the encryption alogrithms used. So the only security is to choose long enough encryption key, so that guessing the encryption key is almost impossible.

For example. consider the above example:Since our key consist of only 1 digit it would take about 9 gusses to guess the key. If we change the key to two digits it would take 99 guesses and so on. So the security lies in, how difficult it is to guess the encryption key.

3. dbms_obfuscation_toolkit

Oracle 8i/9i provides a package dbms_obfuscation_toolkit. Function and Procedures in this package can be used to implement the above mentioned encryption and decryption of sensitive data.

Since the security lies in, how difficult it is to guess the key, it is very important that we use a random key to encrypt the data. It is also very important to store the keys securely. Since hackers generally don't try to do a exhaustive search of all the keys in hope of finding the right one, they generally look for weakness in key storage.

In my example below I will use des3 encryption (dbms_obfuscation_tookit only supports DES encryption) for two columns (ssn and salary) in a table owned by user "poddar". I will encrypt each row with a different key (obtained from dbms_obfuscation_toolkit.des3getkey) and store the key along with the row. (We could also encrypt all the rows with one key, keep the key hard coded in the plsql code and then wrap the plsql code. OR We could use one key to crypt all the rows of a single table, encrypt that key with a master key, store the encrypted key in a seperate keys table and store the master key in some secure place). I will then create a view over the table so that it does not expose the key column. I would then grant needed privileges on this view instead of the table to the read only account. To take care of DMLS I would then create a "instead of trigger" on this view.

4. On To The Example

First lets create user, tables and Indexes for our example. We will create a table Employee with columns employee_id (indexed) also the primary key, employee_name(indexed), employee_dob, ssn (we want to encrypt), salary (we want to encrypt), key (this will be our encryption key). If you notice our encrypted columns i.e. salary and ssn are defined as raw. I will be using raw datatype in this whole example to avoid characterset conversion issues.


SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 20 12:52:41 2005

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP
and Data Mining options

SQL> create user poddar identified by poddar default tablespace users temporary tablespace temp;

User created.

SQL> grant connect, resource to poddar;

Grant succeeded.

SQL> grant create view to poddar;

Grant succeeded.

SQL> grant create public synonym to poddar;

Grant succeeded.

SQL> grant drop public synonym to poddar;

Grant succeeded.

SQL> create user poddar_read_only identified by poddar_read_only default tablespace users temporary tablespace temp;

User created.

SQL> grant create session to poddar_read_only;

Grant succeeded.

SQL> connect poddar
Enter password:
Connected.
SQL> create table employee
2 ( employee_id number(10),
3 employee_name varchar2(60),
4 employee_dob date,
5 ssn raw(255),
6 salary raw(255)
7 key raw(255)
8 );

Table created.

SQL> create index employee_id_ind on employee(employee_id);

Index created.

SQL> create index employee_name_ind on employee(employee_name);

Index created.

SQL> alter table employee add constraint employee_pk primary key (employee_id);

Table altered.

Since dbms_obfuscation_tookit is not that intuitive to call, we will create a user friendly wrapper to make
our life easier. We would call our wrapper functions to encrypt and decrypt data instead of calling
dbms_obfuscation_toolkit directly. Comments inside the code document the use of each function
.


SQL> @cr.sql
SQL> create or replace package crypt_util
2 as
3 function crypt (p_str in varchar2, p_key in raw) return raw;
4 function decryptVarchar (p_data in raw, p_key in raw) return varchar2;
5 function get_key return raw;
6 end crypt_util;
7 /

Package created.

SQL>
SQL> create or replace package body crypt_util
2 as
3 /*
4 We will be using raw datatype through out. Key and encrypted data will be of raw
5 datatype. This would help us to avoid the problem with characterset conversion.
6 */
7
8 /******************************************************************************
9 This function is used to crypt the data. The parameters passed are the key
10 and the data to be encrypted. It returns the encrypted data.
11 ******************************************************************************/
12
13 function crypt (p_str in varchar2, p_key in raw)
14 return raw
15 as
16 l_data varchar2(255);
17 l_datar raw(255);
18 l_retval raw(255);
19 begin
20 /****************************************************************************
21 we have to pad the data so that it meets the 8 byte boundary
22 since DES algorithgms need it like that. Both key and data to
23 be encrypted needs to be match the 8 byte boundary.This is important to remember
24 since after decrypting we will have to remove the padding.
25 ****************************************************************************/
26 l_data := rpad( p_str, (trunc(length(p_str)/8)+1)*8, chr(0) );
27
28 /***************************************************************************
29 Since we are doing all this in raw we would need to call the
30 des3encrypt procedure which returns raw. It also expects its input
31 in raw. Therefore we have to convert our input into raw
32 ***************************************************************************/
33 l_datar := utl_raw.cast_to_raw(l_data);
34
35 dbms_obfuscation_toolkit.des3encrypt( input => l_datar, --input data to be encrypted
36 key => p_key, --key to be used for encryption
37 which => dbms_obfuscation_toolkit.ThreeKeyMode, --use des3 encryption
38 encrypted_data => l_retval --output encrypted data
39 );
40 return l_retval;
41 end;
42
43 /*******************************************************************************************
44 Returns a unique key every time. This is important since key management is the
45 most important part of the whole encryption business. Protecting the key is very
46 important for the encryption security. We will store the key along with the data
47 ********************************************************************************************/
48
49 function get_key
50 return raw
51 as
52 l_keyr raw(255);
53 l_seed varchar2(255);
54 l_seedr raw(255);
55 begin
56 /****************************************************************************
57 des3GetKey returns a random key based on the seed value
58 This is the seed value we will use.
59 ****************************************************************************/
60 l_seed := 'UpKYrZHeiooBqkvpJHuImXrLOmVzYhgBhJcNLQLwkKYAhKgoZKnXPDBjcgYPGnfPyQOBAGmtRTJUhXAo';
61
62 /* Convert the seed to raw, since we are doing all our work in raw */
63
64 l_seedr := utl_raw.cast_to_raw(l_seed);
65
66 /* the which argument decides what kind of key is created
67 i.e. DES - 8 bit key (only 7 bit used for encryption)
68 DES2 - 16 bit key
69 DES3 - 24 bit key */
70 dbms_obfuscation_toolkit.des3GetKey( which => dbms_obfuscation_toolkit.ThreeKeyMode,
71 seed => l_seedr,
72 key => l_keyr );
73 return l_keyr;
74 end;
75
76 /*******************************************************************************************
77 This function is used to decrypt encrypted varchar2 columns.
78 It expects encrypted data and the encryption key as the input and
79 returns decrypted data as varchar2
80 ********************************************************************************************/
81
82 function decryptVarchar (p_data in raw, p_key in raw)
83 return varchar2
84 as
85 l_data varchar2(255);
86 l_datar raw(255);
87 begin
88 l_datar := dbms_obfuscation_toolkit.des3decrypt(input => p_data,
89 key => p_key,
90 which => dbms_obfuscation_toolkit.ThreeKeyMode);
91
92 /******************************************************************************************
93 Since DES needs data to be in multples of 8 bytes we had padded the data, if the
94 data did not meet the 8 bytes boundry requirment. So now after decrypting we
95 would need to remove the padding if it exists
96 *****************************************************************************************/
97
98 return (substr(utl_raw.cast_to_varchar2(l_datar),1,instr(utl_raw.cast_to_varchar2(l_datar),chr(0),1)-1));
99 end;
100 end crypt_util;
101 /

Package body created.

Now lets move on to creating a view employee_vw over the employee table. This view will expose all the
columns except the key column. This view will expose the decrypted values of salary and ssn columns
using crypt_util.decryptVarchar. Since crypt_util.decryptVarchar returns the value as varchar data type,
the column will be shown as varchar2(2000) in the view description. To avoid this I will cast these
columns to the right datatype. It is this view that I will grant the rights to instead of the original table.
I would then create a public synonym for this view.

SQL> @vw.sql
SQL> create or replace view employee_vw
2 as
3 select employee_id,
4 employee_name,
5 employee_dob,
6 cast (crypt_util.decryptVarchar(ssn,key) as varchar2(20)) ssn,
7 cast (crypt_util.decryptVarchar(salary,key) as number(7,2)) salary
8 from employee
9 /

View created.

SQL> grant select,insert,update,delete on employee_vw to poddar_read_only
2 /

Grant succeeded.

SQL> drop public synonym employee_table
2 /

Synonym dropped.

SQL> create public synonym employee_table for employee_vw
2 /

Synonym created.

Since employee table is a synonym pointing to view employee_vw, poddar_read_only can easily select
data from the table. But what if we
need to give poddar_read_only DML access to this table. By using an
INSTEAD OF trigger. This trigger manipulates the data in the base table whenever a user inserts or updates
the view. The INSTEAD OF trigger
fires when the data is either inserted or updated in the view, which,
in turn, does an INSERT or UPDATE on the base tables. While updating, it makes sure the encrypted values
are also updated. Following shows
the code for the instead of trigger.


SQL> @trg.sql
SQL> create or replace trigger employee_vw_trg
2 instead of insert or update on
3 employee_vw for each row
4 declare
5 l_key raw(255);
6 begin
7 if (inserting)
8 then
9 l_key := crypt_util.get_key;
10
11 insert into employee
12 (employee_id,
13 employee_name,
14 employee_dob,
15 ssn,
16 salary,
17 key
18 )
19 values
20 (
21 :new.employee_id,
22 :new.employee_name,
23 :new.employee_dob,
24 crypt_util.crypt(to_char(:new.ssn),l_key),
25 crypt_util.crypt(to_char(:new.salary),l_key),
26 l_key
27 );
28 else
29 select key
30 into l_key
31 from employee
32 where employee_id = :new.employee_id;
33
34 update employee
35 set employee_name = :new.employee_name,
36 employee_dob = :new.employee_dob
37 where employee_id = :new.employee_id;
38
39 update employee
40 set ssn = crypt_util.crypt(to_char(:new.ssn), l_key),
41 salary = crypt_util.crypt(to_char(:new.salary), l_key)
42 where employee_id = :new.employee_id;
43 end if;
44 end;
45 /

Trigger created.

5. Testing The Code

Now Lets see if all this really does what I expect it to do

SQL> @test.sql
SQL> prompt I will insert some rows into employee_table as poddar_read_only
I will insert some rows into employee_table as poddar_read_only
SQL> connect poddar_read_only/poddar_read_only
Connected.
SQL> insert into employee_table values (1,'Amit','30-DEC-2005','111-111-1111',1000.23);

1 row created.

SQL> insert into employee_table values (2,'Tom','30-NOV-2005','222-222-2222',4000.23);

1 row created.

SQL> insert into employee_table values (3,'Dick','30-SEP-2005','333-333-3333',6000.23);

1 row created.

SQL> insert into employee_table values (4,'Harry','30-AUG-2005','333-333-3333',8000.23);

1 row created.

SQL> prompt Now I will connect as poddar and see whether ssn and salary columns are really encrypted
Now I will connect as poddar and see whether ssn and salary columns are really encrypted
SQL> connect poddar/poddar
Connected.
SQL> column ssn format a40;
SQL> column salary format a40;
SQL> set linesize 130;
SQL> select ssn,salary from employee;

SSN SALARY
---------------------------------------- ----------------------------------------
4CA3252C799636A0E5DE7660B2849D89 B03C6AD2B303E6D7
886AD4F3BF1C6573A7626D1BCD70D685 B2439B1931A3DDD8
8B1F5D70F8527F44F658ADECBC7675E9 4C013ED7DF76C64D
66DC42ED2E0A60D5F04953C9AC7626E4 075AE4284F182225

SQL> prompt I will now connect as poddar_read_only and update some records
I will now connect as poddar_read_only and update some records
SQL> connect poddar_read_only/poddar_read_only
Connected.
SQL> column ssn format a20;
SQL> column salary format 99999999.99;
SQL> column employee_name format a30;
SQL> set linesize 130;
SQL> update employee_table set employee_name='Poddar',
2 employee_dob='30-DEC-1923',
3 ssn='444-444-4444',
4 salary=3222.32
5 where employee_name='Amit';

1 row updated.

SQL> select * from employee_table where employee_name='Poddar';

EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_ SSN SALARY
----------- ------------------------------ --------- -------------------- ------------
1 Poddar 30-DEC-23 444-444-4444 3222.32

SQL> prompt I will now finally delete one row from the table
I will now finally delete one row from the table
SQL> prompt I will then delete all the rows from the table
I will then delete all the rows from the table
SQL> delete from employee_table where employee_name='Poddar';

1 row deleted.

SQL> delete from employee_table;

3 rows deleted.


6. Conclusion

Here we looked at a method of encrypting the data. This method can be combined with VPD so that
different users see parts of data based on
their access level. We can also refine our key storage.
But this gives
you a starting point for encrypting your sensitive information. My next post will look at
the performance of this design.