Notes on Oracle Password Security

This morning I had the great opportunity to participate in the virtual event AUSOUG Connect 2021 with my lecture Security Best Practice: Oracle passwords, but secure!. For me it was a premiere and a pleasure to be part of an Oracle event in Australia.

Oracle Password Security is a small but central topic in database security. Database security and especially passwords have been on my mind for a while. Over the time, one or the other example on that subject accumulates. Therefore it is not always easy to concentrate on the essential points. And as so often in today’s lecture, there was not enough time to go into all possible examples and demos. For this reason, I try to briefly pick up on one or the other point in this blog post.

Demo Environment

A simple Docker container with Oracle Database 21.4.0.0 is used for the Lab environment. The following sketch shows the schematic structure of the environment. The scripts for this environment and the following demos are available in the GitHub repository oehrlis/orapwd. The Docker container is based on oehrlis/docker. However, the scripts can also be used in another Oracle database container or Oracle database.

Logon Process

The login process into the Oracle database can be performed most easily with a network analysis e.g. with Wireshark. This way you can see exactly which TCP packets are sent from or to the DB server. The network traffic is collected either directly with Wireshark or with the help of tcpdump on the DB server. Here is an example of how to use tcpdump. This requires that tcpdump is installed in advanced. Command has to be run as root.

bash-4.2# mkdir -p /u01/config/tcpdump
bash-4.2# tcpdump -i eth0 -s 65535 -w /u01/config/tcpdump/tcpdump_$(date "+%Y%m%d_%H%M").dmp
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes

Then you can connect via SQL*Plus and run test queries. After finishing the tests you can stop tcpdump and analyse the created dump file with Wireshark. After loading the tcpdump file, select a TNS packet for analysis and select Fallow TCP Stream. In the new window you can see the information that is exchanged between the database server and client, as you can see in the following picture.

Example output of a TNS network analysis with Wireshark

With unencrypted SQL*Net connections you can see very nicely the connection establishment, session key exchange and subsequently also the SQL statements which are sent from the client to the server. With an ALTER USER scott IDENTIFIED BY tiger; the statement is only parsed on the server and is therefore not encrypted.

Different Password Verifier

As explained in the lecture, you can use ALLOWED_LOGON_VERSION_SERVER or ALLOWED_LOGON_VERSION_CLIENT in sqlnet.ora to specify which version of the authentication protocol and thus which password verifier should be used. See also Oracle® Database Database Net Services Reference 21c or Oracle® Database Security Guide 21c.

Lets set ALLOWED_LOGON_VERSION_SERVER to 11 in sqlnet.ora either using vi or just append it to the end of sqlnet.ora with echo.

echo "SQLNET.ALLOWED_LOGON_VERSION_SERVER=11" >> $TNS_ADMIN/sqlnet.ora

Now create a configure a couple of users using sqlplus. Preferably one user per password verifier. Whereby we set the passwords explicitly with IDENTIFIED BY VALUES.

ALTER SESSION SET CONTAINER=pdb1;
CREATE USER test_10g IDENTIFIED BY VALUES 'AF310E4D20D06950';
CREATE USER test_11g IDENTIFIED BY VALUES
'S:6702B83E88D277BFC378AD6B22DD1AE01895A254470F8124A9D3C5347056';
CREATE USER test_12c IDENTIFIED BY VALUES
T:45738A7B75C9E31ED0C533BCF4931084658A143FD7CF826B980A88EA6C4F0BE66C28DA7085BCAE386723029BA967DC4F45E9C146F6FA7C22E44BA2C1BD2F56F8C22291D417E26D4B810003F3F055EDFF';
CREATE USER test_all IDENTIFIED BY Welcome1;

Don’t forget to grant some privileges..

GRANT CREATE SESSION TO test_10g;
GRANT CREATE SESSION TO test_11g;
GRANT CREATE SESSION TO test_12c;
GRANT CREATE SESSION TO test_all;

GRANT SELECT_CATALOG_ROLE TO test_10g;
GRANT SELECT_CATALOG_ROLE TO test_11g;
GRANT SELECT_CATALOG_ROLE TO test_12c;
GRANT SELECT_CATALOG_ROLE TO test_all;

Now lets see what we have in SYS.USER$

SET LINESIZE 160 PAGESIZE 200
COL name FOR a10
COL password FOR a16
COL spare4 FOR a20
SELECT name,password,spare4 FROM user$ WHERE name LIKE 'TEST_%' ORDER BY 1;

NAME	   PASSWORD	    SPARE4
---------- ---------------- ----------------------------------------
TEST_10G   AF310E4D20D06950
TEST_11G		    S:6702B83E88D277BFC378AD6B22DD1AE01895A2
			    54470F8124A9D3C5347056

TEST_12C		    T:45738A7B75C9E31ED0C533BCF4931084658A14
			    3FD7CF826B980A88EA6C4F0BE66C28DA7085BCAE
			    386723029BA967DC4F45E9C146F6FA7C22E44BA2
			    C1BD2F56F8C22291D417E26D4B810003F3F055ED
			    FF

TEST_ALL   4932A1B4C59EC3D0 S:FA89B6A242F2E80B1F45E2A7861D9CF49F51ED
			    34B4D2FABAA319561AEEE4;T:E66E6EEDA917E09
			    28E40C05E5B5E8D34D20AA1FDB1F108E4EE8DCE2
			    31EE59BD17BBEB58F83DD01713911A96E817BE8D
			    F28584350991611EF366CC2AEE9CBBAB668D69C8
			    03C92639BC3853F527A1B8DB3

In DBA_USERS we do see the password verifier version

SET LINESIZE 160 PAGESIZE 200
COL username FOR a10
COL password_versions FOR a20
SELECT username, password_versions FROM dba_users WHERE username LIKE 'TEST_%';
USERNAME   PASSWORD_VERSIONS
---------- --------------------
TEST_10G   10G
TEST_11G   11G
TEST_ALL   10G 11G 12C
TEST_12C   12C

As you can see Oracle did create all 3 password hashes for the user TEST_ALL as ALLOWED_LOGON_VERSION_SERVER is set to 11. We now change it to 12 and see, that the user TEST_10G can no longer connect.

host sed -i 's/SQLNET\.ALLOWED_LOGON_VERSION_SERVER.*/SQLNET\.ALLOWED_LOGON_VERSION_SERVER=12/' $cdn/admin/sqlnet.ora
SQL> connect test_10g/Welcome1@pdb1
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE

Change ALLOWED_LOGON_VERSION_SERVER to 12a will then cause, that only the user TEST_12C and TEST_ALL can log in.

host sed -i 's/SQLNET\.ALLOWED_LOGON_VERSION_SERVER.*/SQLNET\.ALLOWED_LOGON_VERSION_SERVER=12a/' $cdn/admin/sqlnet.ora
SQL> connect test_11g/Welcome1@pdb1
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect test_12c/Welcome1@pdb1
Connected.
SQL> connect test_all/Welcome1@pdb1
Connected.

When now changing the password for user TEST_ALL, Oracle will only create the password hash for 12c.

SET LINESIZE 160 PAGESIZE 200
COL username FOR a10
COL password_versions FOR a20
SELECT username, password_versions FROM dba_users WHERE username LIKE 'TEST_%';

USERNAME   PASSWORD_VERSIONS
---------- --------------------
TEST_10G   10G
TEST_11G   11G
TEST_ALL   12C
TEST_12C   12C

Check Passwords

To check the default Oracle password we can query dba_users_with_defpwd. Here we see that in PDB1 the user SCOTT still has a default password.

ALTER SESSION SET CONTAINER=pdb1;

Session altered.

SELECT username FROM dba_users_with_defpwd;

USERNAME
-----------
SCOTT

For Oracle 10g respectively passwords stored in SYS.USER$.PASSWORD column we can also verify the password hashes using the script verify_alluser_passwords.sql. This script just calculates the a few passwords based on an embedded dictionary and compares the hashes. You see that the user TEST_10G has a week password.

SQL> @verify_alluser_passwords.sql
User			      Status	Password
----------------------------- -----------------------------
SYS			      0 	OK
AUDSYS			      8 	OK
SYSTEM			      0 	OK
OUTLN			      8 	OK
...
SCOTT			      16	OK
TVD_HR			      0 	OK
TVD_HR_SEC		      0 	OK
TEST_10G		      0 	WELCOME1
TEST_11G		      0 	OK
TEST_12C		      0 	OK
TEST_ALL		      0 	OK

Let’s create manually a Oracle 10g password hash using create_password_hash.sql. The script does use DBMS_CRYPTO to manually create a hash value. 

SQL> @create_password_hash.sql system ieShae0
Username : system
Password : ieShae0
Hash	 : 0AD56CF5F1CB8D2A
SQL	 : alter user system identified by values '0AD56CF5F1CB8D2A';

When now can create a hash file for the hashcat tool to run a brute force attack on the hash.

echo "0AD56CF5F1CB8D2A:SYSTEM" > demo.hash

hashcat as a couple of options and parameters. Please see https://hashcat.net/hashcat/ for more detailed information. For now we just use the following parameters:

  • --increment will start to brute force with shorter length e.g 4 characters
  • --custom-charset1 to define numbers and characters
  • --hash-type Oracle 7+ respectively password verifier 10g
  • --show show the password

Let’s start a hashcat session

hashcat --attack-mode 3 --increment --increment-min 4 --custom-charset1 ?l?d --hash-type 3100 ./demo.hash ?1?1?1?1?1?1?1

This session is not using any dictionary or rule base attack. Is is testing all password combination according the formate defined above. This can take a moment. To speed this up you’ll would have to use a dictionary, rule based or combined attack. 

hashcat (v6.1.1) starting...

/usr/local/Cellar/hashcat/6.1.1/share/hashcat/OpenCL/m03100_a3-optimized.cl: Pure kernel not found, falling back to optimized kernel
OpenCL API (OpenCL 1.2 (Sep  5 2021 22:39:07)) - Platform #1 [Apple]
====================================================================
* Device #1: Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz, skipped
* Device #2: Intel(R) UHD Graphics 630, 1472/1536 MB (384 MB allocatable), 24MCU
* Device #3: AMD Radeon Pro 5500M Compute Engine, 8112/8176 MB (2044 MB allocatable), 24MCU

/usr/local/Cellar/hashcat/6.1.1/share/hashcat/OpenCL/m03100_a3-optimized.cl: Pure kernel not found, falling back to optimized kernel
Minimum password length supported by kernel: 0
Maximum password length supported by kernel: 30

Hashes: 1 digests; 1 unique digests, 1 unique salts
Bitmaps: 16 bits, 65536 entries, 0x0000ffff mask, 262144 bytes, 5/13 rotates

Applicable optimizers applied:
* Optimized-Kernel
* Zero-Byte
* Not-Iterated
* Single-Hash
* Single-Salt
* Brute-Force

Watchdog: Hardware monitoring interface not found on your system.
Watchdog: Temperature abort trigger disabled.

Host memory required for this attack: 602 MB
Session..........: hashcat
Status...........: Cracked
Hash.Name........: Oracle H: Type (Oracle 7+)
Hash.Target......: 0AD56CF5F1CB8D2A:SYSTEM
Time.Started.....: Thu Nov 11 13:52:50 2021 (1 min, 2 secs)
Time.Estimated...: Thu Nov 11 13:53:52 2021 (0 secs)
Guess.Mask.......: ?1?1?1?1?1?1?1 [7]
Guess.Charset....: -1 ?l?d, -2 Undefined, -3 Undefined, -4 Undefined 
Guess.Queue......: 4/4 (100.00%)
Speed.#2.........:  4586.9 kH/s (9.83ms) @ Accel:8 Loops:32 Thr:8 Vec:1
Speed.#3.........:   365.5 MH/s (7.82ms) @ Accel:64 Loops:32 Thr:64 Vec:1
Speed.#*.........:   370.1 MH/s
Recovered........: 1/1 (100.00%) Digests
Progress.........: 23809572864/78364164096 (30.38%)
Rejected.........: 0/23809572864 (0.00%)
Restore.Point....: 399360/1679616 (23.78%)
Restore.Sub.#2...: Salt:0 Amplifier:3552-3584 Iteration:0-32
Restore.Sub.#3...: Salt:0 Amplifier:5920-5952 Iteration:0-32
Candidates.#2....: NPRH5I1 -> TFCIN8M
Candidates.#3....: LDD45HR -> SBRYHG0

Started: Thu Nov 11 13:52:44 2021
Stopped: Thu Nov 11 13:53:53 2021

The result can also be grabbed by using the --show command. By the way, the brute force attack on this hash just took about 1min on my MacBookPro.

hashcat --hash-type 3100 ./demo.hash --show

Verify the performance of your environment by running a benchmark on Oracle 7+ hash

hashcat --benchmark --hash-type 3100
hashcat (v6.1.1) starting in benchmark mode...

Benchmarking uses hand-optimized kernel code by default.
You can use it in your cracking session by setting the -O option.
Note: Using optimized kernel code limits the maximum supported password length.
To disable the optimized kernel code in benchmark mode, use the -w option.

OpenCL API (OpenCL 1.2 (Sep  5 2021 22:39:07)) - Platform #1 [Apple]
====================================================================
* Device #1: Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz, skipped
* Device #2: Intel(R) UHD Graphics 630, 1472/1536 MB (384 MB allocatable), 24MCU
* Device #3: AMD Radeon Pro 5500M Compute Engine, 8112/8176 MB (2044 MB allocatable), 24MCU

Benchmark relevant options:
===========================
* --optimized-kernel-enable

Hashmode: 3100 - Oracle H: Type (Oracle 7+)

Speed.#2.........:  4946.0 kH/s (78.31ms) @ Accel:128 Loops:16 Thr:8 Vec:1
Speed.#3.........:   349.2 MH/s (71.25ms) @ Accel:32 Loops:512 Thr:64 Vec:1
Speed.#*.........:   354.2 MH/s

Started: Thu Nov 11 13:57:56 2021
Stopped: Thu Nov 11 13:58:02 2021

As you see hashcat, john the ripper etc are powerful but also dangerous tools when it comes to password engineering 😊

Recommendations

  • Keep your Oracle Clients and Server up to date
  • Consider using strong Authentication
    • Kerberos and SSL based Authentication
  • Don’t use legacy password verifier
    • Use Oracle password file version 12.2
    • Explicitly configure ALLOWED_LOGON_VERSION_SERVER to 12a and exclusively use 12c hash values
  • Revise your password policies
  • User awareness training
  • Reduce the attack vector
    • Limit access to password hash values
    • Know where you have password hash values
    • Start using NOAUTHENTICATION for schema owners where no login is required.
  • Implement general database hardening

Slides to my Lecture

AUSOUG virtual Connect 2021 – Oracle passwords, but secure! by Stefan Oehrli

Disclaimer

The use of methods and tools to verify password hashes are not allowed everywhere. In particular, their use on productive environments is explicitly not recommended. Please check if the tools are allowed in the respective environment, company, country, etc. before using them. The author disclaims any liability.