oraclegeek.net - dbms_obfuscation

dbms_obfuscation_toolkit

Written by Amit Poddar

Wednesday, 18 January 2006

Article Index
dbms_obfuscation_toolkit
Page 2
Page 3
Page 4

Page 1 of 4

1.0 Introduction

I looked at the basics of encryption here. Now lets try to encrypt database columns (salary and ssn in table EMPLOYEE) for real using dbms_obfuscation_toolkit. From the basics article, we need following for encryption:

a) Encryption Algorithm: dbms_obfuscation_toolkit only supports DES algorithm. We will use Triple DES in our example

b) Encryption Key: I will use Oracle's function dbms_obfuscation_toolkit.des3GetKey to generate Triple DES keys. I will use a separate key to encrypt each row. I will store each key in a separate table EMPLOYEE_KEY, and create a view over table EMPLOYEE that will join tables EMPLOYEE and EMPLOYEE_KEY and expose the decrypted values of the encrypted columns.

c) Padding Scheme: Since dbms_obfuscation_toolkit does not support PKCS #5 for padding I will pad the data with chr(10)s (nulls).

d) Block Chaining Scheme: dbms_obfuscation_tookit does not provide us with a option in this case. It uses CBC for block chaining.

2.0 Schema Design For This Example

Let me describe the schema design

a) Create schema owner poddar and grant him the necessary privileges to execute this example.

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)

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.

d) 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> 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
    1         (
    2            employee_id number(10) primary key,
    3            employee_name varchar2(60),
    4            employee_dob date,
    5            ssn raw(255),
    6            salary raw(255)
    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(255)
    5        )
    6 /
    
    Table created.

Prev - Next >>

Last Updated ( Sunday, 29 January 2006 )

[ Back ]