A high percentage of databases include an APEX installation so we also want to test APEX passwords. The passwords are stored in the WWV_FLOW_FND_USER table in the WEB_PASSWORD2 column.
It is interesting to note that if someone updated a password directly with SQL then it would be in the WEB_PASSWORD column and be clear text. As part of an audit we want to make sure that there are no clear text passwords.
First log into the database:
C:\>sqlplus scanner/scanner@//192.168.56.18:1521/freepdb1
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 22 11:30:42 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL>
Print out the WWV_FLOW_FND_USER table:
SQL> set lines 220
SQL> set serveroutput on
SQL> @sc_print 'select * from apex_220200.wwv_flow_fnd_user'
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from apex_220200.wwv_flow_fnd_user','''','''''');
Executing Query [select * from apex_220200.wwv_flow_fnd_user]
USER_ID : 3800883939144175
SECURITY_GROUP_ID : 10
USER_NAME : ADMIN
FIRST_NAME :
LAST_NAME :
CREATION_DATE : 03-APR-23
CREATED_BY : SYS
LAST_UPDATE_DATE : 15-DEC-23
LAST_UPDATED_BY : ADMIN
START_DATE : 03-APR-23
END_DATE : 29-MAR-43
PERSON_TYPE :
EMAIL_ADDRESS :
WEB_PASSWORD2 : 44A592AEEFFE5DCF40239F21D77838EDABC26EF81F6D558018FA4CD13B1AF45D94911E5BBBB4BD5734CE2B966FDFBA5802DD819EF12AD3B71411FF98772FF4F9
WEB_PASSWORD_VERSION : 5;5;10000
LAST_LOGIN : 15-DEC-23
BUILDER_LOGIN_COUNT : 4
LAST_AGENT : Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36 Edg/120.0.0.0
LAST_IP : 192.168.56.1
ACCOUNT_LOCKED : N
ACCOUNT_EXPIRY : 03-APR-23
FAILED_ACCESS_ATTEMPTS : 0
LAST_FAILED_LOGIN :
FIRST_PASSWORD_USE_OCCURRED : Y
CHANGE_PASSWORD_ON_FIRST_USE : N
ALLOW_APP_BUILDING_YN : N
ALLOW_SQL_WORKSHOP_YN : N
ALLOW_TEAM_DEVELOPMENT_YN : N
DEFAULT_SCHEMA :
ALLOW_ACCESS_TO_SCHEMAS :
DESCRIPTION :
WEB_PASSWORD :
WEB_PASSWORD_RAW :
PASSWORD_DATE :
PASSWORD_ACCESSES_LEFT :
PASSWORD_LIFESPAN_ACCESSES :
PASSWORD_LIFESPAN_DAYS :
DEFAULT_DATE_FORMAT :
KNOWN_AS :
EMPLOYEE_ID :
PERSON_ID :
PROFILE_IMAGE :
PROFILE_IMAGE_NAME :
PROFILE_MIMETYPE :
PROFILE_FILENAME :
PROFILE_LAST_UPDATE :
PROFILE_CHARSET :
ATTRIBUTE_01 :
ATTRIBUTE_02 :
ATTRIBUTE_03 :
ATTRIBUTE_04 :
ATTRIBUTE_05 :
ATTRIBUTE_06 :
ATTRIBUTE_07 :
ATTRIBUTE_08 :
ATTRIBUTE_09 :
ATTRIBUTE_10 :
-------------------------------------------
USER_ID : 7407615931466835
SECURITY_GROUP_ID : 100001
USER_NAME : CUSTADMIN
FIRST_NAME : Pete
LAST_NAME : Finnigan
CREATION_DATE : 15-DEC-23
CREATED_BY : ADMIN
LAST_UPDATE_DATE : 15-DEC-23
LAST_UPDATED_BY : CUSTADMIN
START_DATE : 15-DEC-23
END_DATE : 10-DEC-43
PERSON_TYPE :
EMAIL_ADDRESS : pete@petefinnigan.com
WEB_PASSWORD2 : 1894A011E2E1F02902A55551191C7058015C85A63F00D683D236367CDFF50F326EE6981B2B8E463068DABCEBC8D1CD67E3B115F1A3DC6626ECEEC7C0E3979E72
WEB_PASSWORD_VERSION : 5;5;10000
LAST_LOGIN : 15-DEC-23
BUILDER_LOGIN_COUNT : 3
LAST_AGENT : Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36 Edg/120.0.0.0
LAST_IP : 192.168.56.1
ACCOUNT_LOCKED :
ACCOUNT_EXPIRY : 15-DEC-23
FAILED_ACCESS_ATTEMPTS : 0
LAST_FAILED_LOGIN :
FIRST_PASSWORD_USE_OCCURRED : Y
CHANGE_PASSWORD_ON_FIRST_USE : Y
ALLOW_APP_BUILDING_YN :
ALLOW_SQL_WORKSHOP_YN :
ALLOW_TEAM_DEVELOPMENT_YN :
DEFAULT_SCHEMA : CUSTOMER
ALLOW_ACCESS_TO_SCHEMAS :
DESCRIPTION :
WEB_PASSWORD :
WEB_PASSWORD_RAW :
PASSWORD_DATE :
PASSWORD_ACCESSES_LEFT :
PASSWORD_LIFESPAN_ACCESSES :
PASSWORD_LIFESPAN_DAYS :
DEFAULT_DATE_FORMAT :
KNOWN_AS : Pete
EMPLOYEE_ID :
PERSON_ID :
PROFILE_IMAGE :
PROFILE_IMAGE_NAME :
PROFILE_MIMETYPE :
PROFILE_FILENAME :
PROFILE_LAST_UPDATE :
PROFILE_CHARSET :
ATTRIBUTE_01 :
ATTRIBUTE_02 :
ATTRIBUTE_03 :
ATTRIBUTE_04 :
ATTRIBUTE_05 :
ATTRIBUTE_06 :
ATTRIBUTE_07 :
ATTRIBUTE_08 :
ATTRIBUTE_09 :
ATTRIBUTE_10 :
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
As you can see we have two users and each has a password hash in the WEB_PASSWORD2 column. In the database there has been a number of algorithms used over the years from a custom DES used as a hash from Oracle 6, The SHA1 algorithm from 11.1 and the PDKDF2 and SHA2 from 12.1.0.2.
Apex in current versions also uses PBKDF2 for default authentication and uses a SALT of the security group, username, userID and also specifies the number of iterations of the algorithm. In Apex its possible to change the algorithm and hash function and also number of iterations via instance parameters and of course its possible to create custom authentication schemes. So to check passwords usefully in APEX we can only check the standard hashed ones. This is fine. We can use the WEB_PASSWORD_VERSION to check which passwords are standard or not.
The salt values are stored also in the table so are easy to use. To crack or check the passwords we can use a built in Apex API WWV_FLOW_CRYPTO.HASH_PASSWORD() to test this. So we can try and crack the ADMIN and CUSTADMIN users passwords but first lets get the values we need from the WWV_FLOW_FND_USER table:
SQL> select trim(to_char(user_id,'99999999999999999999')),security_group_id,user_name,web_password_version,web_password2 from apex_220200.wwv_flow_fnd_user;
TRIM(TO_CHAR(USER_ID, SECURITY_GROUP_ID USER_NAME WEB_PASSWORD_VERSION
--------------------- ----------------- ---------------------------------------------------------------------------------------------------- --------------------
WEB_PASSWORD2
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3800883939144175 10 ADMIN 5;5;10000
44A592AEEFFE5DCF40239F21D77838EDABC26EF81F6D558018FA4CD13B1AF45D94911E5BBBB4BD5734CE2B966FDFBA5802DD819EF12AD3B71411FF98772FF4F9
7407615931466835 100001 CUSTADMIN 5;5;10000
1894A011E2E1F02902A55551191C7058015C85A63F00D683D236367CDFF50F326EE6981B2B8E463068DABCEBC8D1CD67E3B115F1A3DC6626ECEEC7C0E3979E72
SQL>
Now we can test the password and see if its correct or not for the ADMIN user:
C:\>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 22 12:31:37 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL> set serveroutput on
SQL> get sc_apx_p.sql
1 begin
2 dbms_output.put_line(apex_220200.wwv_flow_crypto.hash_password('oracle','5;5;10000',10,'ADMIN',3800883939144175));
3* end;
SQL> /
44A592AEEFFE5DCF40239F21D77838EDABC26EF81F6D558018FA4CD13B1AF45D94911E5BBBB4BD57
34CE2B966FDFBA5802DD819EF12AD3B71411FF98772FF4F9
PL/SQL procedure successfully completed.
SQL>
We need to run this as SYS or SYSTEM or a user with the APEX_ADIMISTRATOR_ROLE so I simply logged in as SYS as my user SCANNER doesn't have the APEX_ADMINISTRATOR_ROLE. The password was cracked BUT I knew the password was oracle in advance of course so I could simply test it. I could create a loop and try lots of passwords starting with "A" then "B" and so on or use common passwords from a dictionary.
There are a few comments:
- The algorithm is very slow as that was the idea behind PBKDF2 to make it hard to crack passwords by needing a lot of work to be done; thats why it does 10,000 iterations. We cannot realistically test a lot of potential passwords with PL/SQL.
- As an auditor I do not want the user used for the audit to be granted APEX_ADMINISTRATOR_ROLE. I prefer to have just CREATE SESSION, SELECT ANY DICTIONARY, SELECT ANY TABLE and access to SYS.USER$ and execute on DBMS_CRYPTO
- As an auditor I do not want to execute any API such as WWV_FLOW_CRYPTO.HASH_PASSWORD as I cannot be certain without a lot of checking and testing that it does not change anything and then I would need to convince the customer
- A C based solution would be better but in PL/SQL we need an anonymous block that does not call any APIs except DBMS_CRYPTO
I have created a simple PL/SQL framework as an anonymous block with its own PBKDF2 function. It gets the salt details and runs the algorithm and checks if the resultant hash matches. A sample run is here:
SQL> @sc_apx_pass.sql
CHK[3.1]: Apex Password Cracker
DBG: Schema=[APEX_220200]
DBG: lv_user_id =[3800883939144175]
DBG: lv_security_group=[10]
DBG: lv_user_name =[ADMIN]
DBG: lv_web_password2
=[44A592AEEFFE5DCF40239F21D77838EDABC26EF81F6D558018FA4CD13B1AF45D94911E5BBBB4BD
5734CE2B966FDFBA5802DD819EF12AD3B71411FF98772FF4F9]
ISSUE|CHK[3.1]: password for user [ADMIN] cracked - [oracle]
DBG: lv_user_id =[7407615931466835]
DBG: lv_security_group=[100001]
DBG: lv_user_name =[CUSTADMIN]
DBG: lv_web_password2
=[1894A011E2E1F02902A55551191C7058015C85A63F00D683D236367CDFF50F326EE6981B2B8E46
3068DABCEBC8D1CD67E3B115F1A3DC6626ECEEC7C0E3979E72]
PL/SQL procedure successfully completed.
SQL>
The admin password was cracked but the CUSTADMIN was not. We use a small dictionary as a simple initial check and its slow.
Make sure that you set the APEX password profile settings as this will prevent a simple cracker like this finding a password.
#oracleace #sym_42 #oracle #apex #security #password #cracking
