Protect
you Code and Data
Protect your Code
It's very easy in Oracle to protect your code, you just need to Wrap it
before installing. Here is a quick example:
CREATE OR REPLACE Procedure
diego_test
IS
v_date date;
BEGIN
select sysdate into
v_date from dual;
END;
/
Once you save your that code in a file (Example.sql) type the following
line to create another file:
wrap iname=c:\temp\Example
oname=c:\temp\Example
This will generate a new file (Example.plb) that you will use it to
create the procedure or function.
Protect your Data
Encryption has been available in Oracle for quite a while and its use is increasing significantly. There are basically two ways to obtain an encryption of a data item:
by
using DBMS_UTILITY.GET_HASH_VALUE, available in Oracle 7 and later.
This tool uses a proprietary hashing algorithm in Oracle7, and then
changes to using the standard Oracle hash algorithm generator
kgghash(). That is why DBMS_UTILITY.GET_HASH_VALUE returns different
results in 7.x and 8.x and later, for the same input, but it will
return the same values for 8, 8i, 9i
by using DBMS_OBFUSCATION_TOOLKIT, available in Oracle 8i and later. This package uses ASO, and because there were some changes in ASO from 8 to 8i, you may not be able to back port (by running dbmsobtk.sql and prvtobtk.sql) and use this package in 8.x
There are a few main differences between the two methods:
using
DBMS_UTILITY.GET_HASH_VALUE, you always obtain the same encrypted
string for the same input parameters and data string, with the
exception of between Oracle7 and Oracle8 (and later versions), as shown
above
using
DBMS_OBFUSCATION_TOOLKIT, you always obtain the same encrypted string
for the same input parameters and data string
there
is no way to decrypt back the string created with
DBMS_UTILITY.GET_HASH_VALUE, while you can decrypt strings created with
DBMS_OBFUSCATION_TOOLKIT
regarding
the power of the encryption, it appears that
DBMS_OBFUSCATION_TOOLKIT.MD5 is capable of returning a hash of value
2^128, while DBMS_UTILITY.GET_HASH_VALUE can return a hash of (2^31)-1
GET_HASH_VALUE is probably sufficient for most practical purposes and a lot easier to use, while MD5, though more powerful, requires more coding to implement in your application
These differences will also guide your choice of algorithm, depending on the requirements of your application.
Let’s say you want to generate passwords that you do not want to be decryptable; you just want to match the stored encrypted string of the password with the encrypted string of the password that the user enters at login time. In this case, you are going to use DBMS_UTILITY.GET_HASH_VALUE. This is the way most password-based login systems work. If you migrate from Oracle7 to Oracle8 or later, then you have to regenerate the encrypted strings.
If you
want to encrypt a string and decrypt it later (e.g., when you have your
string traveling via an unsecured medium such as the Internet), you can
generate the encrypted string on the user interface, ship it to the
server, and have the server decrypt it. In this case, you can use
DBMS_OBFUSCATION_TOOLKIT. We will focus aour discussion on DBMS_OBFUSCATION_TOOLKIT
package.
Oracle allows data to be encrypted and decrypted using the built in
package DBMS_OBFUSCATION_TOOLKIT.
The package contains 4 procedures :
- 2 procedures that Encrypt VARCHAR2 and RAW data
- 2 procedures that Decrypt VARCHAR2 and RAW data.
To install the package :
1) connect as SYS and run dbmsobtk.sql and prvtobtk.plb
2) grant execute on dbms_obfuscation_toolkit to public
The functions accept 2 parameters : the data to encrypt or decrypt and
the key used for the encryption or decryption algorithm. A working
example is shown below.
If the input data or key given to the PL/SQL DESDecrypt function is
empty, then Oracle raises ORA error 28231 "Invalid input to Obfuscation
toolkit".
If the input data given to the DESDecrypt function is not a multiple of
8 bytes, Oracle raises ORA error 28232 "Invalid input size for
Obfuscation toolkit".
--Create Table to save Data
create table cards
(
cust_id number primary key,
card_id number(16),
encrypted_card_id varchar2(64)
);
-- Procedure INSERT_CARD is used
to store the card info
create or replace procedure
insert_card( cust_id IN number,
plain_card_no IN varchar2) as
password
VARCHAR2(8) := 'scottsco';
encrypted_string VARCHAR2(2048);
decrypted_string VARCHAR2(2048);
begin
dbms_output.put_line('> input
string
: '
|| plain_card_no);
-- encrypt the plain card
id
dbms_obfuscation_toolkit.DESEncrypt(
input_string => plain_card_no,
key_string => password,
encrypted_string => encrypted_string);
dbms_output.put_line('> encrypted string
value : ' ||
encrypted_string);
insert into cards values
(cust_id, to_number(plain_card_no), encrypted_string);
commit;
end;
/
-- Show how to Save the Card Number, given the correct
password
set serveroutput on
declare
begin
-- You must supply
a string that is a multiple of 8 bytes
insert_card(1,'1234567890123456');
end;
/
--- Procedure GET_CARD is used to Get Card Info
create or replace procedure
get_card(cust_id IN number,
plain_card_data OUT varchar2) as
v_encrypted_card varchar2(64);
v_password
VARCHAR2(8) := 'scottsco';
begin
select encrypted_card_id
into v_encrypted_card
from cards
where cust_id = cust_id;
dbms_obfuscation_toolkit.DESDecrypt(
input_string => v_encrypted_card,
key_string => v_password,
decrypted_string => plain_card_data);
end;
/
show err
-- Show how to retrieve the plain card number, given the correct
password
declare
plain_card_id
varchar2(16);
begin
get_card(1,
plain_card_id);
dbms_output.put_line('> encrypted string
value : ' || plain_card_id);
end;
/
More Examples from Oracle
-- Begin testing string data encryption and decryption
DECLARE
input_string
VARCHAR2(16) := 'tigertigertigert';
key_string
VARCHAR2(8) := 'scottsco';
wrong_input_string VARCHAR2(25) :=
'not_a_multiple_of_8_bytes';
wrong_key_string VARCHAR2(8) :=
'scottsco';
encrypted_string
VARCHAR2(2048);
decrypted_string
VARCHAR2(2048);
error_in_input_buffer_length EXCEPTION;
PRAGMA EXCEPTION_INIT(error_in_input_buffer_length,
-28232);
INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100)
:= '*** DES INPUT BUFFER NOT
A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***';
double_encrypt_not_permitted EXCEPTION;
PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted,
-28233);
DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
'*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION
***';
xprice number;
BEGIN
select itemcost into xprice
from BILLING_DIEGO
where BILLINGRATEID = 1;
dbms_output.put_line('> input
string
: '
|| input_string);
dbms_obfuscation_toolkit.DESEncrypt(
input_string => xprice,
key_string => key_string,
encrypted_string => encrypted_string );
dbms_output.put_line('> encrypted hex
value
: ' ||
rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_string)));
dbms_obfuscation_toolkit.DESDecrypt(
input_string => encrypted_string,
key_string => key_string,
decrypted_string => decrypted_string );
dbms_output.put_line('> decrypted
string output : '
||
decrypted_string);
if input_string = decrypted_string THEN
dbms_output.put_line('> String DES Encyption and Decryption
Successful');
END if;
EXCEPTION
WHEN error_in_input_buffer_length THEN
dbms_output.put_line(' ' || INPUT_BUFFER_LENGTH_ERR_MSG);
END;
DECLARE
input_string
VARCHAR2(16) := 'tigertigertigert';
key_string
VARCHAR2(8) := 'scottsco';
wrong_input_string VARCHAR2(25) :=
'not_a_multiple_of_8_bytes';
wrong_key_string VARCHAR2(8) :=
'scottsco';
encrypted_string
VARCHAR2(2048);
decrypted_string
VARCHAR2(2048);
error_in_input_buffer_length EXCEPTION;
PRAGMA EXCEPTION_INIT(error_in_input_buffer_length,
-28232);
INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100)
:= '*** DES INPUT BUFFER NOT
A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***';
double_encrypt_not_permitted EXCEPTION;
PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted,
-28233);
DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
'*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION
***';
xprice number;
BEGIN
select itemcost into xprice
from BILLING_DIEGO
where BILLINGRATEID = 1;
dbms_output.put_line('> input
string
: '
|| input_string);
dbms_obfuscation_toolkit.DESEncrypt(
input_string => input_string,
key_string => key_string,
encrypted_string => encrypted_string );
dbms_output.put_line('> encrypted hex
value
: ' ||
rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_string)));
dbms_obfuscation_toolkit.DESDecrypt(
input_string => encrypted_string,
key_string => key_string,
decrypted_string => decrypted_string );
dbms_output.put_line('> decrypted
string output : '
||
decrypted_string);
if input_string = decrypted_string THEN
dbms_output.put_line('> String DES Encyption and Decryption
Successful');
END if;
EXCEPTION
WHEN error_in_input_buffer_length THEN
dbms_output.put_line(' ' || INPUT_BUFFER_LENGTH_ERR_MSG);
END;
-- Sample procedures to store sensitive data encrypted
-- (C) 2002 Oracle Corporation
-- create a sample application owner for the secure creditcard
information
create user secure identified by secure default tablespace users
temporary tablespace temp;
grant connect,resource to secure;
connect secure/secure
-- create a sample table that is supposed to store encrypted creditcards
-- the link to the customer details is via the PK cust_id
-- drop table cards;
create table cards (cust_id number primary key, encrypted_card_id
raw(64));
-- Procedure INSERT_CARD is used to store the card info
create or replace procedure insert_card( cust_id IN number,
plain_card_id IN varchar2,
password in raw) as
random_seed raw(80);
random_IV raw(8);
pseudo_string varchar2(100);
plain_card_raw raw(256);
encrypted_card_raw raw(256);
begin
-- generate a random IV, it does not need to be secret, only
random
pseudo_string := to_char(sysdate,'yyyymmddssmi');
pseudo_string := rpad(pseudo_string,80,pseudo_string);
random_seed := utl_raw.cast_to_raw(pseudo_string);
dbms_obfuscation_toolkit.desgetkey(seed => random_seed,
key => random_IV);
-- prefix the plain_card_id with the random IV
plain_card_raw := random_IV||utl_raw.cast_to_raw(plain_card_id);
-- encrypt the plain card id
dbms_obfuscation_toolkit.DES3Encrypt(
input => plain_card_raw,
key => password,
encrypted_data => encrypted_card_raw,
which => 1);
insert into cards values (cust_id,encrypted_card_raw);
commit;
end;
/
show err
create or replace procedure get_card(cust_id IN number,
password IN raw,
plain_card_id OUT varchar2) as
plain_card_raw raw(256);
encrypted_card_raw raw(256);
decrypted_card_id varchar2(24);
begin
select encrypted_card_id into encrypted_card_raw
from cards where cust_id = cust_id;
dbms_obfuscation_toolkit.DES3Decrypt(
input => encrypted_card_raw,
key => password,
decrypted_data => plain_card_raw,
which => 1);
decrypted_card_id := utl_raw.cast_to_varchar2(plain_card_raw);
-- discard the random IV
plain_card_id := substr(decrypted_card_id,9);
end;
/
show err
set serveroutput on
declare
password raw(256);
begin
-- this is a sample password, do not use this password in
real applications
password :=
hextoraw('0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF');
-- you must supply a string that is a multiple of 8 bytes
insert_card(1,'1234567890123456',password);
end;
/
-- show sample encrypted card info
select * from cards;
-- show how to retrieve the plain card number, given the correct
password
declare
password raw(256);
plain_card_id varchar2(16);
begin
password :=
hextoraw('0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF');
get_card(1,password,plain_card_id);
dbms_output.put_line(plain_card_id);
end;
/
Sample Output
CUST_ID ENCRYPTED_CARD_ID
---------- ------------------------------------------------
1
6C9BE56E97E09042BE7CB5046EF3A457A4026B6519D7A5E9
1234567890123456