How to store a password


A frequently ask question is:

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:

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. 
You can read more about it at http://technet.oracle.com/doc/oracle8i_816/server.816/a76936/dbms_obf.htm#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.htm#1002139 for info in the hashing package. For example:

ops$tkyte@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 in place of the to_char( number, 'XXXXX' ) format).

All information and materials provided here are provided "as-is"; Oracle disclaims all express and implied warranties, including, the implied warranties of merchantability or fitness for a particular use. Oracle shall not be liable for any damages, including, direct, indirect, incidental, special or consequential damages for loss of profits, revenue, data or data use, incurred by you or any third party in connection with the use of this information or these materials.