Pete Finnigan's Oracle Security Weblog

Its been a few days, I had planned to write more often now that I am my own boss again but I have been too busy working and dealing with running a business. Anyway to 11g Security; I wanted to have a first look at the new password algorithm used in 11g. The documentation says that in 11g there is a new password algorithm and that passwords are hashed with the Secure Hash Algorithm (SHA) cryptographic hash function SHA-1. It goes on to say that the Oracle database uses the SHA-1 verifier to authenticate the user password and establish a session, in addition case sensitivity is enforced and passwords are restricted to 160 bits.

Let's explore a bit.

SQL> create user x identified by x; User created. SQL>

This is the simple user we created in the last blog. Look at the new SHA-1 password shown in bold (Thanks to Tom's print_table script!):

SQL> set serveroutput on size 100000 SQL> exec print_table('select * from sys.user$ where name=''X'' '); USER# : 88 NAME : X TYPE# : 1 PASSWORD : 8E9A9A5413F0B5EE DATATS# : 4 TEMPTS# : 3 CTIME : 26-aug-2007 21:46:09 PTIME : 26-aug-2007 21:46:09 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:FF5FB0BFD44E35386C0ADDC28AD63E30DC24BABAA57E60D83185AEDE366C SPARE5 : SPARE6 : ----------------- PL/SQL procedure successfully completed. SQL>

In 10gR2 and lower if we create the same user any number of times with the same password the stored hash is always the same. lets try in 11gR1:

SQL> drop user x cascade; User dropped. SQL> grant create session to x identified by x; Grant succeeded. SQL> exec print_table('select * from sys.user$ where name=''X'' '); USER# : 90 NAME : X TYPE# : 1 PASSWORD : 8E9A9A5413F0B5EE DATATS# : 4 TEMPTS# : 3 CTIME : 28-aug-2007 21:52:52 PTIME : 28-aug-2007 21:52:52 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:1765D190198C6F55E192F49047C77D6FB851B5222C8FBD0B8359FEBA6227 SPARE5 : SPARE6 : ----------------- PL/SQL procedure successfully completed. SQL>

Very interesting indeed, the SHA-1 hash has changed, why?. Interestingly also we can see that the old password hash is still the same as expected. SHA-1 is a repeatable function, i.e. if you run it for the same input many times you should get the same output. Let's test this:

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 31 22:02:22 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> SQL> connect sys/change_on_install@ora10gr2 as sysdba Connected. SQL> set serveroutput on size 1000000 SQL> l 1 DECLARE 2 lv_pwd VARCHAR2(19) := 'XX'; 3 lv_pwd_raw RAW(128) := utl_raw.cast_to_raw(lv_pwd); 4 lv_enc_raw RAW(2048); 5 BEGIN 6 dbms_output.put_line('PWD: ' || lv_pwd_raw); 7 lv_enc_raw := sys.dbms_crypto.hash(lv_pwd_raw, 3); 8 dbms_output.put_line('S: ' || lv_enc_raw); 9* END; SQL> / PWD: 5858 S: 20026DC165C030FE3A5D9609A6E61AB26210CBC1 PL/SQL procedure successfully completed. SQL> / PWD: 5858 S: 20026DC165C030FE3A5D9609A6E61AB26210CBC1 PL/SQL procedure successfully completed. SQL>

OK, so running SHA-1 should give the same hash again and again for the same input. Let's check the old verifier that we used to do in 10gR2 and lower, create a user 'A' identified by 'AA' and then a user 'AA' identified by 'A' - in 10gR2 and lower we would get the same password hash. Let's try:

SQL> create user a identified by aa; User created. SQL> create user aa identified by a; User created. SQL> exec print_table('select name,password,spare4 from sys.user$ where name in (''A'',''AA'')'); NAME : A PASSWORD : 637CFFBB696F8AF9 SPARE4 : S:8CAE3110AE48B8AC3B10365BD7F1BBD2ECB37A0DAFD01CC11939154B7DF7 ----------------- NAME : AA PASSWORD : 637CFFBB696F8AF9 SPARE4 : S:437572D2C884BB4BCB3C635EE8BEDF92D495C93F3E58DB300553BA18FD59 ----------------- PL/SQL procedure successfully completed. SQL>

OK, what is going on here? - The old 10gR2 passwords are the same, the new 11gR1 SHA-1 passwords are different, that implies a salt is used. Also just to make sure we do the same for users B/BB and BB/B:

SQL> exec print_table('select name,password,spare4 from sys.user$ where name in (''B'',''BB'')'); NAME : B PASSWORD : 6B3E43737BA3DB1A SPARE4 : S:05B7AB47678CBB66FC866C445D1B5FBDD9D966190EE29615814A0DD996FE ----------------- NAME : BB PASSWORD : 6B3E43737BA3DB1A SPARE4 : S:89BF77CB05B09A4E07D377FDC1FC4DAF892C00FE20FA5647CD54486ADFB4 ----------------- PL/SQL procedure successfully completed. SQL>

In the case of verifying a users entered password the hash algorithm chosen should return the same hash otherwise how could you verify the password? - Clearly, if the password changes each time the same user is created in the first example and also hinted at by the second example there could be a salt used. This is better than 10gR2 and lower, hashes are not predictable in terms of the same hash for the same username/password combo. This means that a default password checker could not use a table of hashes to check against, at least not with the 11gR1 new algorithm. The new built in default password check uses the old hashes. In one sense this is worse as for sad people like me who recognise default hashes simply by inspection this will not be the case in 11gR1 from the new hashes, i.e. a default word could be used but its not checkable simply by comparing hashes, well not from the new one anyway. Looks like its not simply just SHA-1 and also looks like it could be better than the old DES based hash as the created hashes are not the same each time. Is this better or not? - I think so, its a better algorithm and its clearly been thought out by Oracle to avoid some of the issues from 10gR2 and lower. Oracle do seem to have paid attention to the security details this time in 11gR1, good!