Wallets for encrypting database connections? No, not any more…!
When you want to encrypt your client connections to the database, one used to create Oracle Wallets. With an Oracle wallet you run ‘SQL*Net over an SSL connection’. Your tcp connection will be transformed to tcps.
This is not necessary if you easily want to encrypt all your connections to the database. You do not use tcps, you still use tcp, but you encrypt SQL*Net traffic, which is a different approach.
If you use “Native Oracle Net Services encryption and integrity”, you can encrypt all SQL*Net traffic from a client, for all connections to a database and it’s even also configurable per WebLogic Connection Pool.
Setting the encryption/checksum(integrity) for a whole client or whole server can be done in sqlnet.ora by setting the following:
The encryption/checksum(integrity) type is a list, specifying more can be done like this: (AES256,AES192) . I’m not sure how Oracle will try handshaking the types, from left to right or from high to low encryption, I hope the last one.
So this will be for all the databases and clients using the sqlnet.ora in that home. You can set the encryption only at the database server side and all your client must obey the settings. The default is ACCEPTED for both client and server.
- Setting it on the server to REQUESTED and if your clients are able to use it, all our connections to the database will be encrypted and/or checksummed.
- Setting it on the server to REQUIRED, clients which can not use the encryption or checksum algorithms/protocols will not be able to connect.
=> I would advice to set encryption to REQUIRED for all cloud based databases!
Check for all possible combinations and connectivity outcomes the following matrix: Encryption and Data Integrity Negotiations
A thing with JDBC is that it does not pick-up sqlnet.ora, but you can set these encryption and integrity settings as properties:
prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_LEVEL, "REQUIRED"); prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_TYPES, "(AES256)"); prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_CHECKSUM_LEVEL, "REQUIRED"); prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_CHECKSUM_TYPES, "(MD5)");
(MD5 is ok for checksumming)
WebLogic (Java JNDI)
props.put("oracle.net.encryption_client", "REQUIRED"); props.put("oracle.net.encryption_types_client", "(AES256)"); props.put("oracle.net.crypto_checksum_client", "REQUIRED"); props.put("oracle.net.crypto_checksum_types_client", "(MD5)");
WebLogic connection pool
Use the following key/value pair in the Connection Pool properties:
oracle.net.encryption_client=REQUIRED oracle.net.encryption_types_client=(AES256) oracle.net.crypto_checksum_client=REQUIRED oracle.net.crypto_checksum_types_client=(MD5)
Also see: Connection Encryption Parameters and WebLogic Configuration Constants in the Fusion Middleware Administering JDBC Data Sources for Oracle WebLogic Server 12.1.3 documentation.
Verifying if its working
Of course you can use Wireshark to try to read the gibberish, but one can also rely on what Oracle says, Check v$session_connect_info after joining with v$session for the network_service_banner with values like ‘AES256 Encryption service adapter for Linux: Version 220.127.116.11.0 – Production’:
SQL> select network_service_banner from v$session_connect_info where sid = sys_context('USERENV','SID'); TCP/IP NT Protocol Adapter for Linux: Version 18.104.22.168.0 - Production Encryption service for Linux: Version 22.214.171.124.0 - Production AES256 Encryption service adapter for Linux: Version 126.96.36.199.0 - Production Crypto-checksumming service for Linux: Version 188.8.131.52.0 - Production MD5 Crypto-checksumming service adapter for Linux: Version 184.108.40.206.0 - Production
If it’s not encrypted, you don’t see the highlighted line, but you will see the three other ones. If you would have used the wallet approach, my guess is the TCP/IP line would be something like TCPS/IP. (I did not try this).
For system user to check encrypted connections
select ses.sid, ses.username, ses.machine, ses.program, ses.logon_time, nvl(sci.network_service_banner,'Not encrypted') encryption from v$session ses left join v$session_connect_info sci on (sci.sid = ses.sid and upper(sci.network_service_banner) like '%ENCRYPTION%ADAPTER%') where type = 'USER' and (program not like '%(J___)%' or program is null) order by encryption, ses.username;
(Internal) background processes and database Jobs (Jnnn) are not encrypted. These live inside the database already and do not create an SQL*NET connection.