Re: Encrypt and Decrypt the data

From: Jared Still <jkstill_at_gmail.com>
Date: Mon, 12 Jun 2006 10:03:10 -0700
Message-ID: <bf46380606121003l26a51031he632ab491fd836c4@mail.gmail.com>

On 6/11/06, Sachin Sethi <s.sethi_at_telserra.com> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 12 2006 - 12:03:10 CDT