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
