Suppose your organization wants to enforce a security policy on database password strength. The DBA’s have implemented a password strength verification function in PLSQL such as the oracle supplied ora12c_strong_verify_function in the DEFAULT profile of the database. There seems no way to get around it at first:
Database account u4 is created:
create user U4 identified by Tig#3rT1g$er User created
U4 logs in and tries to keep it simple, i.e. keep the password simple:
alter user r4 identified by tesimpel replace Tig#3rT1g$er; * ERROR at line 1: ORA-28003: password verification for the specified password failed ORA-20001: Password length less than 9 <pre>
That password verification function got in the way. U4 searches for solutions to get around this block and stumbles upon the blog from Steve Karam titled Password Verification Security Loophole where Steve demonstrated that it is possible to enter a weak password when creating a user or altering a password, even when a database password verify plsql function is enforced. The way to accomplish this is to use the special IDENTIFIED BY VALUES clause when running the ALTER USER command:
</pre> collabn1:racdba\1\sys> ALTER USER U4 IDENTIFIED BY tesimpel; ALTER USER U4 IDENTIFIED BY tesimpel * ERROR at line 1: ORA-28003: password verification for the specified password failed ORA-20001: Password length less than 9 collabn1:racdba\1\sys> alter USER U4 IDENTIFIED BY VALUES 'S:01D65DF12484184411F1F9BE6E3BA86D4283F1A6078791996DB83AE1B8D4;H:2B6837E21E96A0F1CD9F2B8E796944B0;T:DC0B0B4585E003284FA3952CE73CE71BD876461CE649E1E6829B431F4C28105D12A5FA06A8DB4BDB96B4AFC2BA313964579FE4234E49377AD4B5570AB39E756F62F1CC99E69BB17B9B58D308D648B6CD;B49FE0460E2FCD0A'; User altered. <span style="font-family: Consolas, Monaco, monospace; line-height: 1.5;">
the string between single quotes after IDENTIFIED BY VALUES is the pre-cooked-at-home 12c password hash value for the plaintext password “tesimpel”.
As you can see user U4 can now login with password “tesimpel”:
</pre> c:\tools\work>sqlplus u4/tesimpel@192.168.78.251/pdb_1 SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 6 11:18:37 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options collabn1:racdba\1\u4> <span style="font-family: Consolas, Monaco, monospace; line-height: 1.5;">
despite the ORA_STIG_PROFILE profile that enforces the oracle supplied ora12c_strong_verify_function function, The reason that the simple password still works despite the password verify function is that Oracle database has no way to reverse engineer the plain text password from the hash except by guessing and that, theoretically, could literaaly be a neverending story..
The password strength rules only apply to the plaintext password value. The only way to crack a hash would be to feed the hash algorithm with candidate cleartext passwords and see if the hash value matches the encoded password string that is known in the data dictionary.
Until Oracle decides (to make it possible) to disable this feature that allows the pre-cooked-at-home encoded hash value to be used, there seems to be no way to stop users from using the IDENTIFIED BY VALUES clause when they have the privilege to use the ALTER USER command.
In fact there is a way to do anti-featuring. It’s possible in one of my favorite EE options called Database Vault (a separately licenced product for Oracle Database Enterprise Edition) because it allows us to create our own rules on commands such as ALTER USER on top of required system privileges we would normally need to use the command. If we have the database vault rules enabled we would see following when someone tries to use the IDENTIFIED BY VALUES clause:
</pre> collabn2:dbv\1\u4> @alteruseridentifiedbyvalues.sql alter USER "U4" IDENTIFIED BY VALUES 'S:01D65DF12484184411F1F9BE6E3BA86D4283F1A6078791996DB83AE1B8D4;H:2B6837E21E96A0F1CD9F2B8E796944B0;T:DC0B0B4585E003284FA3952CE73CE71BD876461CE649E1E6829B431F4C28105D12A5FA06A8DB4BDB96B4AFC2BA313964579FE4234E49377AD4B5570AB39E756F62F1CC99E69BB17B9B58D308D648B6CD' * ERROR at line 1: ORA-47306: 20600: IDENTIFIED BY VALUES clause not allowed <pre>
as you can see, IDENTIFIED BY VALUES clause can no longer can be used.
The setup script in Datababase Vault I used is given below and should be run by a database account with at least DV_ADMIN role enabled. Note that individual DV rules are first combined into a DV rule set and then this rule set is used as the command rule for ALTER/CREATE USER & CHANGE PASSWORD. Rules in a rule set will be evaluated either using ALL TRUE or ANY TRUE logic. In my case I needed a mix, therefore I created one DV rule with two checks that were combined using ANY TRUE and a second DV rule to check the sql string. These two DV rules were then put in the DV rule set using ALL TRUE evaluation logic. The ‘Is user allowed or modifying own password’ rule is in fact a copy of an Oracle supplied rule. It checks whether the user has the DV_ACCTMGR role OR whether the user is trying to change his/her own password.
-- create DV RULES
BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE (
rule_name => 'Contains no identified by values clause',
rule_expr => 'UPPER(DVSYS.DV_SQL_TEXT) not like ''%IDENTIFIED BY VALUES%''');
DVSYS.DBMS_MACADM.CREATE_RULE (
rule_name => 'Is user allowed or modifying own password',
rule_expr => 'DVSYS.DBMS_MACADM.IS_ALTER_USER_ALLOW_VARCHAR(''"''||dvsys.dv_login_user||''"'') = ''Y'' OR DVSYS.dv_login_user = dvsys.dv_dict_obj_name');
END;
/
-- CREATE DV RULESET
BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE_SET (
rule_set_name =>'(Is user allowed or modifying own password) AND (command does not contain IDENTIFIED BY VALUES clause)',
description => 'rule set for (Is user allowed or modifying own password) AND (command does not contain IDENTIFIED BY VALUES clause)',
enabled => 'Y',
eval_options => '1',
audit_options => '3',
fail_options => '1',
fail_message => 'IDENTIFIED BY VALUES clause not allowed',
fail_code => '-20600',
handler_options => '0',
handler => NULL);
END;
/
-- ADD RULES TO RULESET
BEGIN
DVSYS.DBMS_MACADM.ADD_RULE_TO_RULE_SET (
rule_set_name => '(Is user allowed or modifying own password) AND (command does not contain IDENTIFIED BY VALUES clause)',
rule_name => 'Contains no identified by values clause',
rule_order => '1',
enabled => 'Y');
DVSYS.DBMS_MACADM.ADD_RULE_TO_RULE_SET (
rule_set_name => '(Is user allowed or modifying own password) AND (command does not contain IDENTIFIED BY VALUES clause)',
rule_name => 'Is user allowed or modifying own password',
rule_order => '1',
enabled => 'Y');
END;
/
-- UPDATE COMMAND RULE
BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE (
command => 'CREATE USER',
rule_set_name => '(Is user allowed or modifying own password) AND (command does not contain IDENTIFIED BY VALUES clause)',
object_owner => DBMS_ASSERT.ENQUOTE_NAME ('%', FALSE),
object_name => '%',
enabled => 'Y');
END;
/
BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE (
command => 'ALTER USER',
rule_set_name => '(Is user allowed or modifying own password) AND (command does not contain IDENTIFIED BY VALUES clause)',
object_owner => DBMS_ASSERT.ENQUOTE_NAME ('%', FALSE),
object_name => '%',
enabled => 'Y');
END;
/
BEGIN DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE (
command => 'CHANGE PASSWORD',
rule_set_name => '(Is user allowed or modifying own password) AND (command does not contain IDENTIFIED BY VALUES clause)',
object_owner => DBMS_ASSERT.ENQUOTE_NAME ('%', FALSE),
object_name => '%',
enabled => 'Y');
END;
/
NOTES:
- the password command in sqlplus also seems to be using the IDENTIFIED BY VALUES clause, so using this DV setup would disable that command too
collabn2:dbv\1\u4> password Changing password for U4 Old password: New password: Retype new password: ERROR: ORA-47306: 20600: IDENTIFIED BY VALUES clause not allowed Password unchanged
- to find out the hash encoded string to be used in IDENTIFIED BY VALUES clause one can easily create a user in a homegrown database (preferably using same version as victim database) and afterwards retrieve the spare4 column value from SYS.USER$ table for that user. Note that the username itself is used in the Oracle algorithm to calculate the hash value so the hash value only works for a user with the same name.
