Call: +44 (0)7759 277220 Call

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.

Home » Archives » September 2007 » Oracle 11g Security - part 5 {Playing for time}

[Previous entry: "Oracle 11g Security - part 4 {Times and dates and lengths}"] [Next entry: "Oracle 11g Password algorithm revealed"]

Oracle 11g Security - part 5 {Playing for time}

September 19th, 2007 by Pete

I did a couple more tests on the new password algorithm, its worth having a look at them for interest. They show; along with the previous blog how to start to investigate issues without using deep techniques.

Why are we looking at the password algorithm? - two reasons , we would like to be able to test for weak passwords and also we would like to see if the new algorithm is weaker than the old.

Yesterday we fixed CTIME by using the fixed_date parameter but PTIME is not fixed. One way to check if PTIME is used in the authentication (we cannot easily prove if its used in the salt) is to update SYS.USER$ and see if we can still log in:

SQL> connect system@ora11g
Enter password: ******

SQL> set serveroutput on size 1000000
SQL> exec print_table('select * from sys.user$ where name=''Z''');
USER# : 110
TYPE# : 1
PASSWORD : 319B5E833E3291F7
CTIME : 18-sep-2007 17:05:12
PTIME : 18-sep-2007 17:05:12
SPARE1 : 0
Sbig grin5FE25BF206BEFF6BB589C830E111C363A906C191704E35978F32A1F6164

PL/SQL procedure successfully completed.

SQL> update sys.user$ set ptime=to_date('18-sep-2007 18:00:00','DD-MON-YYYY HH24:MI:SS')
2 where name='Z';
update sys.user$ set ptime=to_date('18-sep-2007 18:00:00','DD-MON-YYYY HH24:MI:SS')
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> connect sys@ora11g as sysdba
Enter password: ******
SQL> /

1 row updated.

SQL> commit;

Commit complete.

SQL> grant create session to z;

Grant succeeded.

SQL> connect z/z@ora11g

Ok, so changing the PTIME for user proves that the salt is not read from data in SYS.USER$ as part of the authentication process, it doesn't prove whether PTIME is part of the salt. A second test that used to be useful for pre 11gR1 was to show that a password for a user XX/X and a user X/XX were the same. For 11g we already know that this is not the case but if the salt were based on ptime then the hashes would be the same if user/pwd for X/XX and XX/X produced the same hash. This is a newton raphson issue as we have two or more unknowns, we dont know if PTIME affects the salt/hash and whether XX/X and X/XX should produce the same hash. lets test anyway. How can we get the same PTIME and CTIME for two users?

Run this script:

-- create a number users fast to see if we can get the same PTIME for each
drop user yyyy;
drop user yyy;
drop user yy;
drop user y;

create user yyyy identified by y;
create user yyy identified by yy;
create user yy identified by yyy;
create user y identified by yyyy;

set serveroutput on size 1000000

exec print_table('select name,password,ctime,ptime,spare4 from sys.user$ where name like ''Y%''');

Running gives:

SQL> connect system@ora11g
Enter password: ******
SQL> @fast_cr

User dropped.

User dropped.

User dropped.

User dropped.

User created.

User created.

User created.

User created.

PASSWORD : 25712A8BFC65A418
CTIME : 18-sep-2007 18:50:24
PTIME : 18-sep-2007 18:50:24
PASSWORD : 25712A8BFC65A418
CTIME : 18-sep-2007 18:50:24
PTIME : 18-sep-2007 18:50:24
PASSWORD : 25712A8BFC65A418
CTIME : 18-sep-2007 18:50:24
PTIME : 18-sep-2007 18:50:24
PASSWORD : 25712A8BFC65A418
CTIME : 18-sep-2007 18:50:24
PTIME : 18-sep-2007 18:50:24

PL/SQL procedure successfully completed.


So all the old passwords are the same, also the CTIME and PTIME are the same for each user but the new hashes are not. Does this mean neither date are involved in the hash or that XX/X does not produce the same password as X/XX. I am sure the latter is true, that XX/X doesn't produce the same password as XX/X as this was a weakness in earlier versions, hashes could in rare circumstances be the same, I am sure Oracle fixed this in 11gR1. Is PTIME involved in the salt? - dont know.

OK, now I will start to look and blog about something different in 11g Oracle Security next.. satisfied

There has been 2 Comments posted on this article

September 20th, 2007 at 11:18 am

Niels Teusink says:

Hi Pete,

The oracle password hash seems to be calculated like this:
sha1(password . 10 character salt)
the salt is then appended to the sha1 hash in hexadecimal format.

The following PHP code verifies Oracle 11g hashes:

echo verifyOraclePass('AC003C8C1DB8A7A097C4DE69059ACD3E0FB2F7654C893239029D5359B9BC', 'yyyy') ? "YES\n" : "NO\n";

function verifyOraclePass($saltyhash, $plain)(
  if(strlen($saltyhash) != 60)(
    trigger_error("Invalid password hash length", E_USER_NOTICE);
    return false;
  $saltyhash = strtolower($saltyhash);
  $salt = substr($saltyhash, 40);
  $hash = substr($saltyhash,0,40);
  return sha1($plain . pack("H*", $salt)) . $salt == $saltyhash;

September 21st, 2007 at 09:14 am

Pete says:

Hi Niels,

Yes I was already aware of the algorithm as are most security researchers interested in Oracle security that i know. I think its good that Oracle made the algorithm simple to guess, its a pity they didnt go the whole way and publish it. This means researchers can easily scrutinse the choice and also means simple tools to check the effectiveness and strength of passwords chosen for 11g can be implemented by anyone.

The new algorithm is easily guessable and in fact is quite simple. Once you realise that the spare4 column is 80 bits too long its obvious that the extra bits are the salt, then its a case of trying usernamepwdsalt or saltusernamepwd or pwdsalt or saltpwd to see which one works.

The fact that x/xx and xx/x gave the same hash in 10g and lower was an issue and also the Oracle documentation says the password is hashed with SHA-1 leads you to further guess that the username is not included so the guess of the algorithm is simple.

Oracle are right to use an industry standard hash algorithm although SHA-1 was shown to be broken some time back in some specific circumstances and they are also right to fix the issues such as two usernames generating the same hash. The use of the salt also ensures that the password hash of say SYSTEM is not the same in two different databases and even different in the same database even if the same password is chosen. This is an improvement over 10g and lower. The main weakness that i can see is that even though Oracle have implemented case sensitivity for 11g database passwords, the 10g and lower password hash is still there by default so if access to the hashes is possible then the position falls back to the weakest, i.e. no case sensitivity, x/xx = xx/x and so on.



  • PFCLScan

    Simply connect PFCLScan to your Oracle database and it will automatically discover the security issues that could make your Oracle database vulnerable to attack and to the potential loss of your data.

  • PFCL Obfuscate PFCLObfuscate

    PFCLObfuscate is the only tool available that can automatically add license controls to your PL/SQL code. PFCLObfuscate protects your Intellectual Property invested in your PL/SQL database code.

  • PFCL Training PFCLTraining

    PFCLTraining is a set of expert training classes for you, aimed at teaching how to audit your own Oracle database, design audit trails, secure code in PL/SQL and secure and lock down your Oracle database.

  • PFCL Services PFCLServices

    Choose PFCLServices to add Ltd to your team for your Oracle Security needs. We are experts in performing detailed security audits, data security design work and policy creation