Re: storing encrypted data in oracle

From: sysdba <sysdba_at_inter.net.il>
Date: Thu, 18 Jan 2001 11:49:09 +0200
Message-ID: <946e5p$svu$1@news3.inter.net.il>

there's a package called DBMS_OBFUSCATION_TOOLKIT, but its available in > 8.1.6.
I know its not what u asked, but U can use a function manualy configured to give some
encryption yourself. here is a sample from Oracle knowledge base:

CREATE OR REPLACE FUNCTION encrypt( convstr IN VARCHAR2 ) RETURN VARCHAR2 IS   retstr VARCHAR2( 32000 ):= NULL;
  tempno NUMBER;
BEGIN
  FOR i IN 1 .. length( convstr ) LOOP
    tempno:= ascii( substr( convstr, i, 1 ));     retstr:= retstr || ltrim( to_char( tempno + 60, '000' ));   END LOOP;   RETURN retstr;
END;
/

CREATE OR REPLACE FUNCTION decrypt( convstr IN VARCHAR2 ) RETURN VARCHAR2 IS   retstr VARCHAR2( 32000 ):= NULL;
  tempch VARCHAR2( 2 );
  loopno NUMBER;
  stepno NUMBER;
BEGIN
  loopno:= length( convstr ) / 3;
  FOR i IN 1 .. loopno LOOP

    stepno:= i * 3 - 2;
    tempch:= chr( to_number( substr( convstr, stepno, 3 )) - 60 );
    retstr:= retstr || tempch;

END LOOP; RETURN retstr;
END;
/

- - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -

Sample Output

SQL> SELECT encrypt('mystr') FROM dual;

ENCRYPT('MYSTR')

169181175176174

SQL> SELECT decrypt('169181175176174') FROM dual;

DECRYPT('169181175176174')

mystr

Hope this will help,

Asaf Shoval
Oracle Israel Support Center

<catatony_at_my-deja.com> wrote in message news:9456st$on7$1_at_nnrp1.deja.com...

> I would like to store initial settings for unix usernames and associated
> passwords (for newly created accounts) in an oracle table for reference
> purposes.  However, I want to encrypt the password so that the data is
> more secure.  How could I use oracle to perform the
> encryption/decryption?  Would it be smarter/easier to encrypt the data
> before insertion using some non-oracle method?  If so, how?
>
> (Oracle 8.1.5; Solaris 2.6)
>
> Thanks,
> Tony Catania
>
>
> Sent via Deja.com
> http://www.deja.com/

Received on Thu Jan 18 2001 - 03:49:09 CST