Re: encryption of strings

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/13
Message-ID: <8i5g0o$5d$1@nnrp1.deja.com>#1/1

In article <8i4qp7$lf2$1_at_nntp.hamburg.pop.de>,   "Clinique" <clinique_at_gmx.net> wrote:
> Hi everybody,
> I am searching for a possibility to encrypt string data types which
 serve as
> passwords in a database table for user management.
> Does Oracle offer some kind of procedure for encryption?
> Susanne
>
>

I'll get to some possible solutions for 8.1.5 and before in a moment but in Oracle8i, release 8.1.6 there is:

<quote>
The DBMS_OBFUSCATION_TOOLKIT Package

For applications dealing with this highly sensitive data, Oracle provides the
DBMS_OBFUSCATION_TOOLKIT PL/SQL package to encrypt and decrypt data, including string inputs and raw inputs. The function is limited to selected algorithms, such as the Data Encryption Standard (DES). Developers may not plug in their own encryption algorithms, and the key length is also fixed. The function prohibits making multiple passes of encryption; that is, you cannot nest encryption calls, thereby encrypting an encrypted value. These restrictions are required by U.S. laws governing the export of cryptographic products.
</quote>

You can read more about it at

http://technet.oracle.com/doc/oracle8i_816/server.816/a76936/dbms_obf.ht m#6518

Now, in 8.1.5 and before -- what could you do? Well, most passwords are not stored encrypted, but rather they are stord HASHED or DIGESTED. For example, in Oracle, the password is not encrypted (that would imply there is a decrypt but there is not). Instead, to validate a username/password we take them, plus some magic "salt" and hash it. This results in a fixed length string of some bytes of data. We compare that to the stored hash and if they match -- you are in. If not -- you are not.

So, if I was to write my own password check function, I would simply glue the USERNAME together with the supplied PASSWORD. I would call dbms_utility.get_hash_value to generate some hashes. See

http://technet.oracle.com/doc/oracle8i_816/server.816/a76936/dbms_ut2.ht m#1002139
for info in the hashing package. For example:

ops$tkyte_at_8i> declare
  2 function digest( p_username in varchar2, p_password in varchar2 ) return varchar2

  3      is
  4      begin
  5          return ltrim( to_char( dbms_utility.get_hash_value(

upper(p_username)||'/'||upper(p_password),   6
1000000000, power(2,30) ),

  7                          rpad( 'X',29,'X')||'X' ) );
  8      end digest;
  9  begin
 10      for x in ( select username from all_users where rownum
< 20 )
 11      loop
 12          dbms_output.put_line( 'User: ' || rpad( x.username
, 30 ) ||
 13                                ' digest: ' || digest(
x.username, 'TIGER' ) );
 14      end loop;

 15 end;
 16 /

User: SYS                            digest: 6869FA1A
User: SYSTEM                         digest: 79F08AFC
User: OUTLN                          digest: 5ABFB255
User: DBSNMP                         digest: 43415F6B
User: TRACESVR                       digest: 49CF26F6
User: CTXSYS                         digest: 4910C297
User: OEM                            digest: 69463BC2
User: ORDSYS                         digest: 6F048B2B
User: ORDPLUGINS                     digest: 6547459C
User: MDSYS                          digest: 43C0B367
User: AURORA$ORB$UNAUTHENTICATED     digest: 5073BBFC
User: WEB$CDEJESUS                   digest: 6FB5CDB6
User: SCOTT                          digest: 4307767C
User: WEB$SMAYFIEL                   digest: 71ED5065
User: UTILS                          digest: 5B7912B7
User: OAS_PUBLIC                     digest: 502BAE3A
User: WEBDB                          digest: 5A7AC149
User: WEB$RDRISCOL                   digest: 3E72D3F6
User: WEB$KWARREN                    digest: 7123F5A1

PL/SQL procedure successfully completed.

So, I have a function digest that takes a username and password, hashes it into 1 of 1073741824 different numeric values, adds 1000000000 to it (to make it big) and turns it into HEX. This is what I would store in the database -- not the password (which I really don't ever need to know).

Now when the user presents me a username/password, I digest it and compare -- if they match, you get in. If not you do not.

Note that before 8.1.5, the to_char( .., 'XXXXXX' ) format does not work (not implemented). See
http://osi.oracle.com/~tkyte/hexdec/index.html for a solution in 8.0 and
7.x (I have a to_hex routine you can use).

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue Jun 13 2000 - 00:00:00 CDT