Re: Encrypt and Decrypt the data

On 6/11/06, Sachin Sethi <s.sethi@xxxxxxxxxxxx> wrote:


HELLO

How to Encrypt and Decrypt the data in oracle .


In lieu of asking you to do some research, I will instead provide you with a sample solution:

drop table encrypted;

create table encrypted (
       first_name varchar2(50) not null,
       last_name varchar2(50) not null,
       account_id varchar2(50) not null
)
/

create or replace package encrypt_key
as
       function key return varchar2;
end;
/

create or replace package body encrypt_key
as
       function key return varchar2
       is
       begin
               return 'short_encryption_key';
       end;
end;
/

show error package encrypt_key

create or replace trigger encrypted_insert
before insert on encrypted
for each row
declare
begin
       :new.first_name := utl_raw.bit_xor(utl_raw.cast_to_raw(:
new.first_name),utl_raw.cast_to_raw(encrypt_key.key));
       :new.last_name := utl_raw.bit_xor(utl_raw.cast_to_raw(:new.last_name
),utl_raw.cast_to_raw(encrypt_key.key));
       :new.account_id := utl_raw.bit_xor(utl_raw.cast_to_raw(:
new.account_id),utl_raw.cast_to_raw(encrypt_key.key));
end;
/

show error trigger encrypted_insert

insert into encrypted values('Barney','Rubble','BR0928817A1');
insert into encrypted values('Betty','Rubble','BR0928817A2');
insert into encrypted values('Flintstone','Fred','BR2778913B2');
insert into encrypted values('Flintstone','Wilma','BR2778913B1');

commit;

create or replace view decrypted_data
as
select

utl_raw.cast_to_varchar2(utl_raw.bit_xor(hextoraw(first_name),utl_raw.cast_to_raw(encrypt_key.key)))
first_name,

utl_raw.cast_to_varchar2(utl_raw.bit_xor(hextoraw(last_name),utl_raw.cast_to_raw(encrypt_key.key)))
last_name,

utl_raw.cast_to_varchar2(utl_raw.bit_xor(hextoraw(account_id),utl_raw.cast_to_raw(encrypt_key.key)))
account_id
from encrypted
/

col first_name format a40
col last_name format a40
col account_id format a40
set line 122

select * from encrypted;

select * from decrypted_data;


HTH

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Other related posts: