I recently wrote a blog post about Oracle SQLNet TLS encryption and how easy it is to configure. See also Oracle SQLNet TLS configuration simplified. This was followed by a short discussion about whether a simple ALTER USER … IDENTIFIED BY is encrypted in a regular connection or not. So let’s validate this by examining the network traffic with tcpdump and Wireshark. For my test I use a local test environment on my MacBook Pro M1 with an ARM based Docker Container for Oracle Database 19c, SQL Developer and sqlcl.
Unencrypted SQL Net connection
For this test, both Native SQLNet encryption and TLS encryption are turned off. By querying V$SESSION_CONNECT_INFO we can see if we have Native SQLNet encryption. The script net.sql does show the network service banner for the current session or you can directly query the view.
SELECT
sid,network_service_banner
FROM
v$session_connect_info
WHERE sid=(SELECT sid FROM v$mystat WHERE ROWNUM = 1)
AND NETWORK_SERVICE_BANNER like '%Encryption%';
SID NETWORK_SERVICE_BANNER ___ _____________________________________________________________ 296 Encryption service for Linux: Version 19.0.1.0.0 - Production
You can see that only one row is returned, which means that the network encryption is available but not active. Furthermore, by querying the NETWORK_PROTOCOL in the context USERENV, we can see if we have a TCP or TCPS connection.
SELECT sys_context('userenv','network_protocol') network_protocol
FROM dual;
NETWORK_PROTOCOL ___________________ tcp
Now that we know that SQLNet is not encrypted with either native encryption or TLS, we can start a tcpdump session to record the network traffic. To do this, we start tcpdump as root and provide a specific network interface to collect all TCP packets. In my Docker container, this is the interface eth0.
tcpdump -i eth0 -s 65535 -w /u00/app/oracle/admin/CDBUA190/adhoc/noenc_v1.0.pcap
In the following you can see the connection as pdbadmin via sqlcli and the execution of ALTER USER.
soe@MacBook:~/ > sql pdbadmin@localhost:1521/PDB2 SQLcl: Release 23.2 Production on Tue Sept 19 17:48:37 2023 Copyright (c) 1982, 2023, Oracle. All rights reserved. Password? (**********?) ************ Last Successful login time: Tue Sep 19 2023 17:48:39 -07:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.19.0.0.0 SQL> SHOW user USER is "PDBADMIN" SQL> ALTER USER scott IDENTIFIED BY tiger; User SCOTT altered. SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.19.0.0.0
Finally lets analyse the pcap file in Wireshark. Just load the file and select follow TCP Stream for protocol TNS, as you can see in the imabe below.
In the following picture, you do see the TCP stream for the TNS protocol between the client and the server. In my example, red is the transfer of data from the client to the server and blue is the reverse. The data can be searched using find. Thus one finds relatively fast the unencrypted password as it is sent with the execution of ALTER USER from the client to the server.
When parsing the SQL statement, Oracle then determines that this is a password. Accordingly, the SQL statement is masked in the audit trail.
SELECT sql_text FROM unified_audit_trail WHERE dbusername='PDBADMIN' AND action_name='ALTER USER';
SQL_TEXT ------------------------------------------------- ALTER USER scott IDENTIFIED BY *
Encrypted SQL Net connection
Now let’s try it out with native SQLNet encryption. For this we set the parameter SQLNET.ENCRYPTION_SERVER in sqlnet.ora on the database server to REQUIRED.
# Oracle Net encryption # avaiable encryption algorithms in favored priority (default all ) SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256,RC4_256,AES192,AES128) SQLNET.ENCRYPTION_TYPES_SERVER=(AES256,RC4_256,AES192,AES128) # enable encryption by following parameters # ENCRYPTION_CLIENT and ENCRYPTION_SERVER can be set to #SQLNET.ENCRYPTION_CLIENT = REQUIRED SQLNET.ENCRYPTION_SERVER = REQUIRED
Again, we start tracing by calling tcpdump as root for interface eth0.
tcpdump -i eth0 -s 65535 -w /u00/app/oracle/admin/CDBUA190/adhoc/enc_v1.0.pcap
The query of V$SESSION_CONNECT_INFO now shows two entries, i.e. the SQLNet encryption is available and is also used with AES256.
SELECT
sid,network_service_banner
FROM
v$session_connect_info
WHERE sid=(SELECT sid FROM v$mystat WHERE ROWNUM = 1)
AND NETWORK_SERVICE_BANNER like '%Encryption%';
SID NETWORK_SERVICE_BANNER
______ ___________________________________________________________________________
22 Encryption service for Linux: Version 19.0.1.0.0 - Production
22 AES256 Encryption service adapter for Linux: Version 19.0.1.0.0 - Production
And now we run the same statements to alter the user password as before without encryption.
soe@MacBook:~/ > sql pdbadmin@localhost:1521/PDB2 SQLcl: Release 23.2 Production on Wed Sept 20 16:50:42 2023 Copyright (c) 1982, 2023, Oracle. All rights reserved. Password? (**********?) ************ Last Successful login time: Wed Sep 20 2023 16:50:48 -07:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.19.0.0.0 SQL> SHOW user USER is "PDBADMIN" SQL> ALTER USER scott IDENTIFIED BY tiger; User SCOTT altered. SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.19.0.0.0
As before, we load the pcap file and select a TNS package and choose Follow TCP Stream. The following figure shows as before with two colors (red and blue) the data, which were sent between client and server. In contrast to before, you can only read the beginning of the connection in plain text. The rest of the data is encrypted and thus no longer readable. Thus, the ALTER USER statement with the password is also encrypted accordingly.
But what about the Password command?
In SQLPlus, SQL Developer, sqlcl but also in other tools like TOAD, there is a password command. Either as command line command or in a menu e.g. Edit User in SQL Developer. The big question is, what happens in the background when you set a new password with this function?
Analogous to the procedure from before, we can record and view the network traffic for the different variants. For example for sqlcli.
soe@MacBook:~/ > sql pdbadmin@localhost:1521/PDB2 SQLcl: Release 23.2 Production on Wed Sept 20 17:01:30 2023 Copyright (c) 1982, 2023, Oracle. All rights reserved. Password? (**********?) ************ Last Successful login time: Wed Sep 20 2023 17:01:32 -07:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.19.0.0.0 SQL> password scott New password: ***** Retype new password: ***** Password changed SQL> exit
The corresponding TCP stream then looks like this.
Oops, you can see the password because the command is converted to an ALTER USER statement. Let’s check the same with SQLPlus.
soe@MacBook:~/ > sqlplus pdbadmin@localhost:1521/PDB2 SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 20 17:16:58 2023 Version 19.8.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Enter password: Last Successful login time: Wed Sep 20 2023 17:01:32 -07:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.19.0.0.0 SQL> password scott Changing password for scott New password: Retype new password: Password changed SQL> exit
This unencrypted TCP stream looks better after all. You can see at the bottom that an AUTH_NEWPASSWORD is sent from the client to the server. This is followed by a longer hash value which represents the encrypted password. The encryption is done with the help of the session key.
Conclusion
This simple test demonstrates that with Wireshark, it is relatively easy to monitor SQLNet traffic between a client and a server. You can accomplish this either directly or by using tcpdump, as I did. In either case, you can observe a lot, including SQL statements. In the case of an ALTER USER operation directly involving a password change, this functions as expected because the SQL statements are ultimately parsed on the database server. Consequently, only the server knows that the statement contains a password. In such cases, the password is masked appropriately, as we saw in the Unified Audit Trail, but just not on the network.
Only in the example involving SQLPlus, where we use the PASSWORD command, is no password transferred in plain text. The reason for this lies in the implementation and the client itself. SQLPlus utilizes the OCI library with suitable functions for altering passwords. On the other hand, SQL Developer, sqlcl, and other tools do not necessarily use the OCI library but implicitly execute an ALTER USER command, which is always visible when transmitted over an unencrypted connection.
Nowadays, encryption is employed for other network protocols and connections. For instance, HTTPS is used instead of HTTP, and LDAPS is preferred over LDAP. However, when it comes to network access to Oracle databases, encryption is often neglected. Yet there are compelling reasons why all access to an Oracle database should be encrypted. You can opt for the easy-to-configure Native Encryption or the TLS-based encryption.
If your main goal is to prevent unauthorized access to data on the network, the variant you choose is less important. It is more important that the network traffic is encrypted in the first place. In contrast to native encryption, certificate-based encryption through TLS over TCPS also provides a certain level of protection against man-in-the-middle attacks. However, it does have the drawback of necessitating the management of corresponding keystores and certificates. It appears that there will soon be a new feature to configure TCPS without the need for dedicated Oracle wallets.