The normal wisdom in a 10g database would have been that Oracle generates a single password hash for each user when a user is created or it would update that password hash when a users password was changed. This was the password hash stored in SYS.USER$.PASSWORD column and this uses the DES based HASH. The username is the SALT and is concatenated with the PASSWORD and then encrypted with DES CBC using a standard key and once the first round is complete the process starts again using the last input vector as the new key. This means that whilst an encryption algorithm is used (DES CBC) it is actually a one way HASH as there is no way to decrypt the password. The standard method to crack this is to use a cracker and either use a dictionary based approach - i.e. test each password generated from a dictionary of words and compare the generated hash with the one stored in the database. Another possibility is to use brute force; i.e. try a password of A, then B and then C and then AA, AB, AC... and AAA, AAB, AAC and so on until all possible passwords have been tried for the keyspace. A simple PL/SQL password cracker is available here. This is an older version of my PL/SQL cracker; version 1.5; if you have been on two day my training class - How to perform a security audit of an Oracle database - then you would have the latest version which is faster and has more default passwords and more features - A benefit of attending my classes is to get all the latest scripts and tools. I will be teaching this class in my home city of York at the beginning of July; come along! I may release the later version of this cracker on here soon; so watch out for a blog post on that.
So, 10g database, thats one password hash. When Oracle database 11.1 was released Oracle added case sensitive passwords and the new (At the time) SHA1 password hash. This is stored in SYS.USER$.SPARE4 and is identified as the "S:" string. This is just SHA1 on the actual password and with the addition of a SALT to make the password that is stored different to other hashes of the same password. So if i set the SYS password to TEST and then the SYSTEM password to TEST even though the hash is only of the password the hash is different as Oracle generates a random SALT each time a password is hashed. This SALT is also stored in SYS.USER$.SPARE4 with the hashed password. I created a simple SHA1 hash test back in 2007 when 11.1 was released. This script is sha1.sql and is here.
In 11.2 this situation continued; so that DES and the SHA1 hashes are available in SYS.USER$. When 12.1.0.1 was released an additional password hash was added in SYS.USER$.SPARE4 - this one identified by the "H:" - This is an HTTP Digest for use in web based applications authentication. In 12.1.0.1 this could not be turned off; so you got a HTTP digest whether you liked it or not. In 12.1.0.2 Oracle changed the HTTP digest slightly if a multi-tennent database were created. In 12.1.0.1 the hash for common users (default users as well as ones created by you) was visible in the PDB; in 12.1.0.2 this is now only visible in the CBD for common users; in a PDB the HTTP digest is still available for LOCAL users. In 12.2.0.2 the HTTP digest is not created by default anymore BUT can be created with a new additional syntax - I will come back to this in a later post just about this hash. So as of Oracle 12.1.0.1 there were 3 core password hashes.
In 12.1.0.2 added the new SHA2 password algorithm. This is a combination of the PBKDF2 multiple rounds and then a final SHA2 hash. As with the SHA1 in 11.1 this password hash is stored in SYS.USER$.SPARE4 and is the "T:" password hash. Also as with SHA1 in 11g the SALT used is also stored in the same column with the hashed password. More in a later post about this algorithm. So as of 12.2.0.1 whilst the HTTP Digest is normally turned off and the default is that the DES hash is not generated it is possible to get all 4; either through an upgrade or because you use the syntax to create the HTTP digest or because you have set the network authentication in the sqlnet.ora to default to an earlier setting to get the DES hash also.
OK, but thats just 4 password hashes what about the other two. Well the 5th is generated by Oracle internally and not stored in a public place where you can view it. If an account is a default account then every time a password changes (CREATE or ALTER) then Oracle creates an un-salted SHA1 hash and uses it to compare with an internal list to see if the account is a default and whether the password is a default password. Oracle does this to detect default passwords. The hash is not stored as far as I can see BUT the accounts status is updated to indicate that the password is a default. I will come back to this in a more detailed review in another post soon. So this is 5 password hashes generated in 12.1.0.2 and above.
The one i want to focus on for the rest of this blog is the 6th password hash created for a user. This hash is only created if you run a DDL trigger on CREATE and / or ALTER or generally an un-constrained DDL trigger. If you collect the ORA_DES_ENCRYPTED_PASSWORD trigger attribute in the DDL trigger for a password change then this trigger attribute is populated. As the name implies it should be a DES hash or DES encrypted password. If it is indeed encrypted then that also implies decryption. Lets have a look and test the ORA_DES_ENCRYPTED_PASSWORD trigger attribute.
First I installed PFCLATK our Oracle audit trail toolkit into my Oracle 12.2.0.1 database:
PFCLATK: Release 1.7.2.0 - Production on Wed Jun 13 18:07:26 2018
Copyright (c) 2009 - 2018 PeteFinnigan.com Limited. All rights reserved.
SECTION-[1] - Remove existing schemas and users
[1-1] Drop The ATK Admin user
[1-2] Drop the ATK Reports User
[1-3] Run schema drop script
SECTION-[2] - Create the Schema owner ATK (Functional owner)
[2-1] Create ATK Schema
[2-2] Perform ATK Grants
SECTION-[3] - Create schema owner ATKD (Data owner)
[3-1] Create ATKD schema
[3-2] Perform ATKD Grants
...
SECTION-[17] - Install the user policies
SECTION-[18] - Install the user factors
SECTION-[19] - Install the PUL jobs/policies
SECTION-[20] - Clean up...
[20-1] Purge the PFCLAudit Audit table
[20-2] Print out existing audit, except OLS and DV
SQL>
This gives me a very detailed policy driven audit trail in my database that allows certain actions to be raised as exceptions and alerts fired. We are not going to explore the audit trail in this blog post but i will come back and discuss this toolkit in a future blog post. We use it to help customers through consulting engagements develop and deploy a rich policy driven audit trail with alerts and escalations quickly and easily. Currently it's a PL/SQL based command line tool but a GUI is coming soon to allow customers to manage, choose and design their own policies or to amend or simply use ours. There will be an admin GUI and a dashboard to allow you to monitor multiple databases. The toolkit can be deployed to a target database to audit it or it can be be deployed to a central database so that all target databases can send their audit trails to it for centralised storage and reporting. More soon here in the blog on the toolkit; but if you are interested now to have us help you design and deploy very useful audit trails email info@petefinnigan.com for availability and costs.
We installed PFCLATK because it already knows how to collect the ORA_DES_ENCRYPTED_PASSWORD field when a user is created (password first set) or when the password is changed. So lets create a user called TEST and set his password to A:
SQL> create user TEST identified by A;
User created.
SQL> col err_sql for a40
SQL> col ora_dict_obj for a40
SQL> col ora_des for a32
SQL> set lines 220
SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;
ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
SQL>
This doesn't tell us much so far. Lets change the password to B, then C, then D and see what happens:
SQL> alter user TEST identified by B;
User altered.
SQL> alter user TEST identified by C;
User altered.
SQL> alter user TEST identified by D;
User altered.
SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;
ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee
SQL>
Now we are getting somewhere. As you can see the first 16 characters of the DES encrypted password change as each new password is set BUT the last 16 characters do not. This implies that a short password is only encrypted into the first 16 characters (8 bytes) and a longer one into the next. But before we explore that rollover what if we set the same 4 passwords for the user ORABLOG:
SQL> alter user ORABLOG identified by A;
User altered.
SQL> alter user ORABLOG identified by B;
User altered.
SQL> alter user ORABLOG identified by C;
User altered.
SQL> alter user ORABLOG identified by D;
User altered.
SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;
ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a0fe3035825f3637aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a6b47b9609ea127baad3b435b51404ee
alter user ORABLOG identified by * ORABLOG ce0beda1542358c2aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG 5a7c34dd04a4c3adaad3b435b51404ee
8 rows selected.
SQL>
The same 4 passwords were set for ORABLOG as i set for the user TEST. The same 4 DES encrypted passwords were created. This means that the encryption is just for the password and the username is not involved and also it means that there is no SALT. What if we now set the passwords using lower case a, b, c and d:
SQL> alter user TEST identified by a;
User altered.
SQL> alter user TEST identified by b;
User altered.
SQL> alter user TEST identified by c;
User altered.
SQL> alter user TEST identified by d;
User altered.
SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;
ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a0fe3035825f3637aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a6b47b9609ea127baad3b435b51404ee
alter user ORABLOG identified by * ORABLOG ce0beda1542358c2aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG 5a7c34dd04a4c3adaad3b435b51404ee
alter user TEST identified by * TEST a6b09e2de7820bedaad3b435b51404ee
alter user TEST identified by * TEST 94326650a42f2bd8aad3b435b51404ee
alter user TEST identified by * TEST e6ea6b41dedd8560aad3b435b51404ee
ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST cd961c7f88ea81c7aad3b435b51404ee
12 rows selected.
SQL>
This is interesting as the encrypted passwords are not the same as the upper case ones. This means that the encryption used must be CASE SENSITIVE unlike the DES algorithm used in the database password hash from Oracle 6 upwards; actually the algorithm supports upper and lower case and specials etc but the double quote syntax was needed to access that. You will have noticed that the last 16 characters (8 bytes) of the DES encrypted password is always the same; before we play with that lets test if this also works for SYS.
This is interesting as the reason I first looked into this area was to see if there was any way I could simulate password management in any other way than the password management built into Oracle; this built in password management does not work for SYS; so i was looking to see if i could simulate it in any other way. More on this in another blog post and i will show you what I worked out for SYS. So lets try this for SYS:
SQL> alter user sys identified by a;
User altered.
SQL> alter user sys identified by b;
User altered.
SQL> alter user sys identified by c;
User altered.
SQL> alter user sys identified by d;
User altered.
SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;
ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a0fe3035825f3637aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a6b47b9609ea127baad3b435b51404ee
alter user ORABLOG identified by * ORABLOG ce0beda1542358c2aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG 5a7c34dd04a4c3adaad3b435b51404ee
alter user TEST identified by * TEST a6b09e2de7820bedaad3b435b51404ee
alter user TEST identified by * TEST 94326650a42f2bd8aad3b435b51404ee
alter user TEST identified by * TEST e6ea6b41dedd8560aad3b435b51404ee
ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST cd961c7f88ea81c7aad3b435b51404ee
alter user sys identified by * SYS a6b09e2de7820bedaad3b435b51404ee
alter user sys identified by * SYS 94326650a42f2bd8aad3b435b51404ee
alter user sys identified by * SYS e6ea6b41dedd8560aad3b435b51404ee
alter user sys identified by * SYS cd961c7f88ea81c7aad3b435b51404ee
16 rows selected.
SQL>
So, yes it works for SYS and the results are the same as the passwords for TEST. So next, lets truncate the audit trail and lets try extending the password from A to 16 A's and see what happens:
SQL> truncate table atkd.pfclatk_audit;
Table truncated.
SQL> alter user TEST identified by A;
User altered.
SQL> alter user TEST identified by AA;
User altered.
SQL> alter user TEST identified by AAA;
User altered.
SQL> alter user TEST identified by AAAA;
User altered.
SQL> alter user TEST identified by AAAAA;
User altered.
SQL> alter user TEST identified by AAAAAA;
User altered.
SQL> alter user TEST identified by AAAAAAA;
User altered.
SQL> alter user TEST identified by AAAAAAAA;
User altered.
SQL> alter user TEST identified by AAAAAAAAA;
User altered.
SQL> alter user TEST identified by AAAAAAAAAA;
User altered.
SQL> alter user TEST identified by AAAAAAAAAAA;
User altered.
SQL> alter user TEST identified by AAAAAAAAAAAA;
User altered.
SQL> alter user TEST identified by AAAAAAAAAAAAA;
User altered.
SQL> alter user TEST identified by AAAAAAAAAAAAAA;
User altered.
SQL> alter user TEST identified by AAAAAAAAAAAAAAA;
User altered.
SQL> alter user TEST identified by AAAAAAAAAAAAAAAA;
User altered.
SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;
ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST e59f3bfac2082d77aad3b435b51404ee
alter user TEST identified by * TEST f984b70b43584870aad3b435b51404ee
alter user TEST identified by * TEST 00629bf7623f9c45aad3b435b51404ee
alter user TEST identified by * TEST 5a52d459aeb64441aad3b435b51404ee
alter user TEST identified by * TEST 8ee7deffee86298baad3b435b51404ee
alter user TEST identified by * TEST 1e3528942decf14daad3b435b51404ee
alter user TEST identified by * TEST 1e3528942decf14da0fe3035825f3637
alter user TEST identified by * TEST 1e3528942decf14de59f3bfac2082d77
alter user TEST identified by * TEST 1e3528942decf14df984b70b43584870
alter user TEST identified by * TEST 1e3528942decf14d00629bf7623f9c45
ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST 1e3528942decf14d5a52d459aeb64441
alter user TEST identified by * TEST 1e3528942decf14d8ee7deffee86298b
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d
16 rows selected.
SQL>
This shows some interesting information and facts:
- First the known string in the second 16 characters (8 bytes) aad3b435b51404ee remains the same until the password is 8 characters long. This means that all passwords 7 characters and less are encrypted into the first 16 characters (8 bytes) and the second 16 characters (8 bytes) remain the standard blank value. This is odd, why 7 characters and not 8 which is a standard input to DES? possibly the password is a standard C string with a NULL terminator and the terminator is also encrypted?
- After and including the 8 character password the second 16 characters change. This is interesting as if we take the first 16 characters of the password A and the second 16 characters of the password AAAAAAAA (8 A's) the they are the same a0fe3035825f3637. So the second half of the algorithm used is the same as the first half. So it does not encrypt AAAAAAAA (8 A's) as one string; it takes the first 7 A's and encrypts to 1e3528942decf14 and then encrypts the 8th A on its own as a0fe3035825f3637 so is the same password DES string as a single A. So the password is definitely split before encryption and the same encryption is used on each half
- Passwords 7 (7 A's) and onwards all have the same first half no matter what as they are all the same password half (AAAAAAA).
- When we get to a 14 character password the second 16 characters of the encrypted password are the same as the first i.e. we have AAAAAAA + AAAAAAA = 1e3528942decf14d + 1e3528942decf14d
- A password that is 15 characters or higher; its clear that the characters above 14 are ignored!!
If i repeat the output with the passwords also its a little clearer:
alter user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee 1 - (A)
alter user TEST identified by * TEST e59f3bfac2082d77aad3b435b51404ee 2 - (AA)
alter user TEST identified by * TEST f984b70b43584870aad3b435b51404ee 3 - (AAA)
alter user TEST identified by * TEST 00629bf7623f9c45aad3b435b51404ee 4 - (AAAA)
alter user TEST identified by * TEST 5a52d459aeb64441aad3b435b51404ee 5 - (AAAAA)
alter user TEST identified by * TEST 8ee7deffee86298baad3b435b51404ee 6 - (AAAAAA)
alter user TEST identified by * TEST 1e3528942decf14daad3b435b51404ee 7 - (AAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14da0fe3035825f3637 8 - (AAAAAAAA) (2nd 16 chars change)
alter user TEST identified by * TEST 1e3528942decf14de59f3bfac2082d77 9 - (AAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14df984b70b43584870 10 - (AAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d00629bf7623f9c45 11 - (AAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d5a52d459aeb64441 12 - (AAAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d8ee7deffee86298b 13 - (AAAAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d 14 - (AAAAAAAAAAAAAA) (2nd 16 = 1st 16 chars)
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d (15 - (AAAAAAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d (16 - (AAAAAAAAAAAAAAAA)
16 rows selected.
So the passwords seem to be split into 7 character strings and the separately DES encrypted or hashed? and then concatenated again as a HEX string. The testing that i have done above is in 12.2.0.1. Lets try the same 16 passwords in 11.2.0.4:
SQL> create user test identified by A;
User created.
SQL> col err_sql for a40
SQL> col ora_dict_obj for a30
SQL> col ora_des for a32
SQL> set lines 220
SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;
ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ------------------------------ --------------------------------
create user test identified by * TEST a0fe3035825f3637aad3b435b51404ee
SQL>
In 11.2.0.4 the hash is exactly the same as in 12.2.0.1 for a password of A. We can therefore rely on the values to analyse the passwords of users as they are changed. As we can do this in a much better way with a password verify function and the password management settings this is sort of redundant but as i said the reason i started to look into this was to do password management of sorts for SYS; more of that in a later post.
In summary we could easily tell if a password is not at least 8 characters long as the last 16 characters of the ORA_DES_ENCRYPTED_PASSWORD are known if its length is seven or less. We can see that the password is split into 7 character blocks so in rare cases we might be able to detect repeat patterns or passwords all of the same character. There is no SALT but there is case sensitivity.
If we could test the actual algorithm and detect its implementation then we could perhaps decrypt a password up to 14 characters. But if it were decrypted at 14 characters but the password were 16 then it would be simple to test that the password is not cracked so therefore must be longer. Therefore we could brute force the extra characters easily - so a 16 character password would be like cracking a 2 character password if we knew the first 14 only.
The testing implies the same key is always used as the first 16 characters were the same as the second so it implies its not a random key used but a fixed key; knowing Oracle thats probably 0123456789ABCDEF. A bit of testing and trial and error could possibly reveal the algorithm used so that the password could be decrypted but i have not done this.
So, 6 password hashes for each user in some circumstances in 12.2.0.1. There are other cases as well of encrypted passwords such as LINKS.
