1.0 Introduction

I looked at the basics of encryption here. I also looked at how to encrypt data using dbms_obfuscation_toolkit here, which was the only way possible in Oracle8i/9i. Starting from Oracle 10g, Oracle introduced a new package for this purpose, dbms_crypto. Starting from 10g using dbms_crypto over dbms_obfuscation toolkit should be preferred because of following reasons:

a) dbms_obfuscation_toolkit provides only DES algorithms and not AES which is the defacto standard nowadays.

b) dbms_obfuscation_toolkit does not provide automatic padding using PKCS #5. so we have pad the input data ourselves.

c) dbms_obfuscation_toolkit does not provide us with different options of block chaining. It uses CBC.

d) dbms_obfuscation_toolkit does not support clob and blob column encryption.

e) Oracle most probably will deprecate dbms_obfuscation_toolkit in favour of dbms_crypto.

So now lets go through an example of encrypting data with dbms_crypto.

Let me describe the schema design:

a) Create schema owner poddar and grant him the necessary privileges to execute this example. In this case we will need to grant execute on dbms_crypto. This was not done in dbms_obfuscation_toolkit, since by default public is given grant to execute dbms_obfuscation_toolkie. (Which is not secure. You should revoke this grant from public and grant it explicitly to the user who needs it) .
b) Create user poddar_read_only who needs to see the decrypted data.
c) Create table employee with columns:
- Employee Id (Primary Key)
- Employee Name
- Employee DOB
- SSN (Column to be encrypted)
- Salary (Column to be encrypted)
d) All the columns to be encrypted are defined as raw. We will use the datatype raw through out to avoid problems with characterset conversion. The users will see the correct datatypes through the view. Each row in Employee will be encrypted by a different key stored in employee_key table. Create table employee_key. This table holds key which is used to encrypt rows in table Employee. It has following columns:
- employee_id (primary key an references employee's primary key)
- key (is the key used to encrypt rows in table Employee)

SQL> @sch.sql 
   SQL> drop user poddar cascade 
   2 / 
   
   User dropped. 
   
   SQL> create user poddar identified by gadha007 default tablespace users temporary tablespace temp 
   2 / 
   
   User created. 
   
   SQL> grant connect, resource to poddar 
   2 / 
   
   Grant succeeded. 
   
   SQL> grant create view to poddar 
   2 / 
   
   Grant succeeded. 
   
   SQL> grant create public synonym to poddar 
   2 / 
   
   Grant succeeded. 
   
   SQL> grant drop public synonym to poddar 
   2 / 
   
   Grant succeeded. 
   
   SQL> grant execute on dbms_crypto to poddar 
   2 / 
   
   Grant succeeded. 
   
   SQL> drop user poddar_read_only 
   2 / 
   
   User dropped. 
   
   SQL> create user poddar_read_only identified by poddar_read_only default tablespace users temporary tablespace temp 
   2 / 
   
   User created. 
   
   SQL> grant create session to poddar_read_only 
   2 / 
   
   Grant succeeded. 
   
   SQL> connect poddar 
   Enter password: 
   Connected. 
   SQL> drop table employee 
   2 / 
   drop table employee 
   * 
   ERROR at line 1: 
   ORA-00942: table or view does not exist 
   
   
   SQL> create table employee 
   2          ( employee_id number(10) primary key, 
   3            employee_name varchar2(60), 
   4            employee_dob date, 
   5           ssn raw(2000), 
   6           salary raw(2000) 
   7         ) 
   8 / 
   
   Table created. 
   
   SQL> drop table employee_key 
   2 / 
   drop table employee_key 
   * 
   ERROR at line 1: 
   ORA-00942: table or view does not exist 
   
   
   SQL> create table employee_key 
   2         ( 
   3            employee_id primary key references employee(employee_id), 
   4            key raw(2000) 
   5         ) 
   6 / 
   
   Table created.