Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "Decompilation - reality or myth"] [Next entry: "Oracle 11g Security - part 5 {Playing for time}"]

Oracle 11g Security - part 4 {Times and dates and lengths}



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!

There has been 3 Comments posted on this article


September 17th, 2007 at 11:47 pm

Pete Finnigan says:

Thanks for investigating.
Also interesting is that PTIME was variable despite FIXED_DATE. I guess that even a database with a FIXED_DATE (eg year end reporting) will still enforce password expiry times.
It makes it harder to see if PTIME is the salt. I suppose you could hack the PTIME value and see if the password still works...



September 18th, 2007 at 12:59 pm

Pete Finnigan says:

it may be worth writing a background batch job to keep resetting ptime at intervals < 1 second [ i.e. system time so that a ptime always reports 20:00:00 or some such [ doesn't work if ptime clock resolution < millisecond intervals ]

A nasty man might create a replacement dummy for dbms_crypto.hash and see if that was being used....



September 18th, 2007 at 06:25 pm

Pete Finnigan says:

Hi Gary,

Thanks for your comment. I tried to hack PTIME, the password still works. I will create another blog and show.

Hi Chris,

see above, PTIME doesnt affect authenticating as a user but it could still be involved in creating the salt. What makes you think DBMS_CRYPTO is used?

cheers

Pete