 |
|
Oracle
Key Generation
Security
Oracle Security Tips by
Burleson Consulting |
This is an excerpt from the
bestselling book "Oracle
Privacy Security Auditing", a complete Oracle security reference
with working Oracle security scripts.
Key Generation
The other component we will have to worry about
is the actual key that is used to encrypt and decrypt. The key must
be such that it is not easily guessed, and since no one is expected
to remember it, it may not be a string of human comprehensible
characters. It can be just any arbitrary string of characters. The
best option to generate a key is using the new code segment
DES3GetKey in the package dbms_obfuscation_toolkit. As in all other
codes in this package, the DES3GetKey code is also implemented as
both a procedure and a function and overloaded with both VARCHAR2
and RAW datatypes. Here is the explanation of the parameters to the
procedure version in the RAW format.
In the procedure version of the VARCHAR2
format, the parameters are the same, except seed is renamed to
seed_string. Of course, the datatypes change from RAW to VARCHAR2.
Here is an example of how the procedure is
used:
declare
v_key_raw raw (2000);
v_key varchar2 (2000);
v_seed varchar2 (2000) := 'Seed';
v_seed_raw raw (2000);
begin
v_seed := rpad(v_seed, 80);
v_seed_raw := utl_raw.cast_to_raw (v_seed);
dbms_obfuscation_toolkit.DES3GetKey(
which=> 1,
seed => v_seed_raw,
key => v_key_raw
);
v_key := rawtohex(v_key_raw);
dbms_output.put_line('The key is '||v_key);
end;
/
Let's analyze the code. The seed to be passed
has to be in raw. So, we added the utl_raw.cast_to_raw to the seed
value passed as a string. The seed must be at least 80 characters,
so we add blank characters to the variable V_SEED to make it at
least 80 characters.
The output comes back as:
The key is
4B547B479B25967365D3717112A52C954883F02416296F7A
This is a key we can use to encrypt all the
data. Subsequent calls to the above code will produce different
results, so the key value can be different each time.
If your database is 8i, then you don’t have the
luxury of using this procedure/function. However, you can easily
create your own as in the code example given below. This uses the
dbms_random.random function to randomize a number from which the
character string is constructed. From the random number obtained, we
will take the 3 numbers from the third position and check if they
exist within 33 and 125, the allowable numbers for ASCII printable
characters. If they fall within that range, we will get the ASCII
character for that number and append to the key string. The idea
behind selecting from the middle of the number is simply to make
sure that we will get a number beginning with zero, or in effect, a
two digit number. Selecting the first three numbers from the random
number will never yield a two-digit number.
* get_key_8i.sql
--**********************************************
--
-- Copyright © 2003 by Rampant TechPress Inc.
--
-- Free for non-commercial use.
-- For commercial licensing, e-mail
info@rampant.cc
--
--
*********************************************
create or replace function get_key_8i
return varchar2
is
filled boolean;
int_num number(14);
v_key varchar2(24);
counter number := 0;
begin
loop
exit when filled;
int_num := to_number(substr(to_char
(abs(dbms_random.random)),3,3));
if int_num between 33 and 125 then
counter := counter + 1;
if counter = 24 then
filled := true;
end if;
v_key := v_key ||chr(int_num);
end if;
end loop;
return v_key;
end;
/
So, we now have a procedure to generate the key
and encrypt the data, the two very important steps in building an
encryption system. The next step is to build one.
Building an Encrypted System
In the example system, we will take the case of
the table claim_line into account. Some of the information in the
table is identified as PHI, and therefore may be a good candidate
for encryption. One such field is procedure codes (procedure_code).
This field holds the diagnosis of diseases or other diagnostic
information done on the user, and is therefore extremely sensitive
as per HIPAA regulations. Other fields in other tables may be Credit
Card Numbers, Social Security Numbers, etc.
The usual approach is to generate a specific
key for a specific value, and store that key in a separate place. To
make it simple, we can use a single key for a single record of
claim_line table, regardless of how many columns are to be
encrypted.
Our key holding table will then have to hold
the row identifier of the claim line table, and the key used to
encrypt data in that row. The row identifier could be the primary
key, or some other set of columns to uniquely identify a row. To
make it more generic, we can have a single table to hold all the
keys or all the tables, partitioned on the table_name column. For
the sake of this example, assume there is one table to hold keys per
the source table to be encrypted. That table, named claim_line_keys
could be defined as
CLAIM_ID NUMBER
LINE_ID NUMBER
KEY_VALUE VARCHAR2(200)
There are two ways to handle the encryption in
the table claim_line.
View Method - The table could be renamed to
claim_line_clear and a view named claim_line be created. The
PROCEDURE_CODE column of the view could display the data in
encrypted manner. No one should have access to the original table.
All the applications, the users, etc. should refer to the view.
When the applications need to insert data into
the table, there could be an INSTEAD OF trigger defined on the view
that decrypts the data and stores the original value in the CLEAR
table, which contains the data in the unencrypted manner.
Direct Encryption - When the data is entered
into the claim_line table, or the field PROCEDURE_CODE is updated,
the changed data could be encrypted before putting it into the
table, so that the field PROCEDURE_CODE contains encrypted values.
In this manner the query to the table will always return an
encrypted value. The user must decrypt this to get the actual value.
The inserts and updates can be handled in a
slightly simpler manner, using triggers to update the value before
the database value is written.
Either method is easy to accomplish and may
prove better or worse depending on your specific situation. We will
use the direct update of the table approach as an example.
Our redesigned table CLAIM_LINE looks like this:
CLAIM_ID NOT NULL NUMBER
LINE_ID NOT NULL NUMBER
PROCEDURE_CODE
VARCHAR2(200)
CLAIM_AMOUNT NUMBER(5)
PAID_AMOUNT NUMBER(5)
Notice that procedure_code is now 200
characters long, enough to hold the encrypted value.
To hold the keys, we will design another table
claim_line_keys as follows.
CLAIM_ID NOT NULL NUMBER
LINE_ID NOT NULL NUMBER
KEY_STRING NOT NULL VARCHAR2(80)
The primary key is (CLAIM_ID, LINE_ID) and the
key to encrypt the corresponding procedure code is stored in the
column KEY_STRING.
The decryption will work as per Figure 6.1. We
will get the encrypted value and the corresponding key, and feed
them to the decryption process to get the original value.
Figure 6.1 The claim_line Decryption Process
Key Generation
The first task is to generate the key for the
encryption. Either of the two methods described earlier can be used.
We will create a function to return the key. For simplicity, the
DES3GetKEy procedure of the package is used here; but the other
method can be used as well.
* get_key.sql
--**********************************************
--
-- Copyright © 2003 by Rampant TechPress Inc.
--
-- Free for non-commercial use.
-- For commercial licensing, e-mail
info@rampant.cc
--
--
*********************************************
create or replace function get_key
(
p_seed in varchar2 := 'Seed'
)
return raw
is
v_key_raw raw (2000);
v_seed_raw raw (2000);
v_seed varchar2(2000) := p_seed;
begin
v_seed := rpad(v_seed, 80);
v_seed_raw := utl_raw.cast_to_raw (v_seed);
dbms_obfuscation_toolkit.DES3GetKey(
which=> 1,
seed => v_seed_raw,
key => v_key_raw
);
return v_key_raw;
end;
/
This function will return a different and
randomized key every time it is called.
The Encryption Process
The next step is to create a function that
returns the encrypted value from an input value.
* get_encrypted_value.sql
--**********************************************
--
-- Copyright © 2003 by Rampant TechPress Inc.
--
-- Free for non-commercial use.
-- For commercial licensing, e-mail
info@rampant.cc
--
--
*********************************************
create or replace function get_encrypted_value
(
p_input_value in varchar2,
p_key in raw
)
return raw
is
v_iv raw (2000) := null;
v_inlen number;
v_padlen number;
v_extra number;
v_input_value varchar2(2000);
v_ret raw(2000);
v_input_raw raw(2000);
begin
--
-- Next,we will pad the value to make the
-- length a multiple of 8.
v_inlen := length (p_input_value);
if (v_inlen < 8)
then
v_padlen := 8;
else
v_extra := mod(v_inlen, 8);
if (v_extra = 0)
then
v_padlen := v_inlen;
else
v_padlen := v_inlen - v_extra + 8;
end if;
end if;
v_input_value := rpad(p_input_value,
v_padlen);
--
-- The input value has to be converted to
RAW
v_input_raw := utl_raw.cast_to_raw (v_input_value);
--
-- Now we have to encrypt the value
dbms_obfuscation_toolkit.DES3Encrypt (
input => v_input_raw,
key => p_key,
which => 1,
iv => v_iv,
encrypted_data => v_ret
);
return v_ret;
end;
/
This function takes an input value and a key,
and throws out an encrypted value.
 |
This is an excerpt
from the book "Oracle
Privacy Security Auditing". You can buy it direct from the
publisher for 30%-off and get instant access to the code depot
of Oracle security and auditing scripts. |
