Pete Finnigan's Oracle Security Weblog

I talked last time on Oracle 11g Security about the new password hash algorithm and that there may be a salt involved in the algorithm because the password changes each time its reset to the same value or if the same account is dropped and recreated with the same password. A post on my Oracle Security forum raised an issue as to whether the salt could be the create time of the user or the password time. In the post titled "Oracle 11g Authentication" Gary suggested testing with the fixed_date parameter to change the password and see if the SHA-1 hash remained the same for the same username/password. So I thought I would give it a try. First connect to 11gR1 and set the fixed_date parameter:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> alter session set nls_date_format='YYYY-MM-DD:hh24:mi:ss'; Session altered. SQL> alter system set fixed_date='2007-03-04:16:00:00'; System altered. SQL> select sysdate from dual; SYSDATE ------------------- 2007-03-04:16:00:00

Then create a simple user/password and print out the details:

SQL> create user c identified by c; User created. SQL> set serveroutput on size 1000000 SQL> exec print_table('select * from sys.user$ where name=''C'''); USER# : 95 NAME : C TYPE# : 1 PASSWORD : AEE1788DB3B2F3C3 DATATS# : 4 TEMPTS# : 3 CTIME : 04-mar-2007 16:00:00 PTIME : 17-sep-2007 19:45:57 EXPTIME : LTIME : RESOURCE$ : 0 AUDIT$ : DEFROLE : 1 DEFGRP# : DEFGRP_SEQ# : ASTATUS : 0 LCOUNT : 0 DEFSCHCLASS : DEFAULT_CONSUMER_GROUP EXT_USERNAME : SPARE1 : 0 SPARE2 : SPARE3 : SPARE4 : S:8C9FD807B8BED0246C2C86C4748CB3700EF68A8642E66C8C0BAC746A9EB7 SPARE5 : SPARE6 : ----------------- PL/SQL procedure successfully completed. SQL>

Alter the users password to the same value and then drop and recreate the user again with the same password:

SQL> alter user c identified by c; User altered. SQL> exec print_table('select * from sys.user$ where name=''C'''); USER# : 95 NAME : C TYPE# : 1 PASSWORD : AEE1788DB3B2F3C3 DATATS# : 4 TEMPTS# : 3 CTIME : 04-mar-2007 16:00:00 PTIME : 17-sep-2007 19:47:44 EXPTIME : LTIME : RESOURCE$ : 0 AUDIT$ : DEFROLE : 1 DEFGRP# : DEFGRP_SEQ# : ASTATUS : 0 LCOUNT : 0 DEFSCHCLASS : DEFAULT_CONSUMER_GROUP EXT_USERNAME : SPARE1 : 0 SPARE2 : SPARE3 : SPARE4 : S:C10D037D6CE8BBC546B761491DFE708407C8032E1DD2367D83C4146CE3D0 SPARE5 : SPARE6 : ----------------- PL/SQL procedure successfully completed. SQL> drop user c cascade; User dropped. SQL> create user c identified by c; User created. SQL> exec print_table('select * from sys.user$ where name=''C'''); USER# : 96 NAME : C TYPE# : 1 PASSWORD : AEE1788DB3B2F3C3 DATATS# : 4 TEMPTS# : 3 CTIME : 04-mar-2007 16:00:00 PTIME : 17-sep-2007 19:48:49 EXPTIME : LTIME : RESOURCE$ : 0 AUDIT$ : DEFROLE : 1 DEFGRP# : DEFGRP_SEQ# : ASTATUS : 0 LCOUNT : 0 DEFSCHCLASS : DEFAULT_CONSUMER_GROUP EXT_USERNAME : SPARE1 : 0 SPARE2 : SPARE3 : SPARE4 : S:6F62FF27DA1319AECC190F045DA02AA7A2594864AC3E8534681A5950FE4E SPARE5 : SPARE6 : ----------------- PL/SQL procedure successfully completed. SQL>

Note that in each case the ctime is set to the same date (from the fixed_date parameter) but the SHA-1 verifier is different in each case. Therefore we can show that at least one assumtpion is wrong, the salt isn't the CTIME, well not exclusively the CTIME. As Gary points out the other possibility is the PTIME as its the only other variable parameter in the table. That assumes that the salt is indeed in the table..:-)

The other obvious point to note is that the SHA-1 verifier should be 160 bits but the value stored is actually 240 bits long. The Salt - indicated by S: could be included with the SHA-1 verifier and may be simply random but that would mean that subsequent connections would need to extract the salt from the hash as stored and use it, that would be neater but would afford no better security than for instance reading the PTIME column. Anyway, its interesting to speculate!