Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re:RE: Oracle column level encryption?

Re:RE: Oracle column level encryption?

From: <>
Date: Wed, 19 Jul 2000 14:11:56 -0400
Message-Id: <>


    How badly do you want to encrypt the data? Oracle posted a set of two functions on Metalink sometime ago to do very basic encryption. I took that write up & created a package for our purposes. It works, but I'm sure a determined hacker could crack it.

Here is the package:

create or replace package encrypt as

   function code(inp_data varchar2, key varchar2 default 'fillonein') return varchar2;

   pragma restrict_references(code, RNDS, WNDS, WNPS); end;

create or replace package body encrypt is

   function convbin(c1 varchar2) return varchar2 is

     loop1 number;
     value number;
     divis number;
     r1 varchar2(30);
     r1 := '';
     divis := 128;
     value := ascii(c1);
     for loop1 in 0..7 loop
       if(trunc(value/divis) = 1) then
         r1 := r1||'1';
         r1 := r1||'0';
       end if;
       value := mod(value, divis);
       divis := divis/2;
     end loop;
     return r1;


   function code(inp_data varchar2, key varchar2 default 'vicorwestcor') return varchar2 is

     loop1 number;
     loop11 number;
     r1 varchar2(8);
     r2 varchar2(8);
     key1 varchar2(4000);
     r3 number;
     result varchar2(40);
     divis number;
     key1 := key;
     while (length(inp_data) > length(key1)) loop
       key1 := key1||key1;
     end loop;
     result := '';
     for loop1 in 1..length(inp_data) loop
       r1 := convbin(substr(inp_data,loop1,1));
       r2 := convbin(substr(key1,loop1,1));
       divis := 128;
       r3 := 0;
       for loop11 in 1..8 loop
         if(to_number(substr(r1,loop11,1))+to_number(substr(r2,loop11,1)) = 1)
             r3 := r3+divis;
         end if;
         divis := divis/2;
       end loop;
       result := result||chr(r3);
     end loop;
     return result;


grant execute on encrypt to public;
create public synonym encrypt for system.encrypt;

Dick Goulet

____________________Reply Separator____________________
Subject: RE: Oracle column level encryption? Author: "Kirsh; Gary" <> Date: 7/18/00 8:59 AM

I believe that Protegrity is once such product. Gary

-----Original Message-----
Sent: Tuesday, July 18, 2000 10:12 AM
To: 'Fatcity's Oracle ListServ'; 'LazyDBA's Oracle ListServ'

        I have been asked to investigate the possibility of using column level encryption to safeguard confidential data, but on a standard issue of the DBMS, in this case 8.1.6, not "Secure Oracle".

        Does anyone know of any commercially available products that can encrypt the data in a particular column on any given table so that only authorised users can decode the data and no one else can, including the DBA's? This has the added fun of having to be application transparent!!

        Thanks in adv

If you're bored, then visit the list's website: (updated daily)
to unsubscribe, send a blank email to to subscribe send a blank email to
Author: Kirsh, Gary

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
Received on Wed Jul 19 2000 - 13:11:56 CDT

Original text of this message