Eagle

It was in 1997 when I designed and implemented a tool to monitor Oracle databases. I called this tool Eagle. I had also written an article describing Eagle in IOUG Select magazine in 1997.

After 20 years in 2017, I just came across an article, Enhancing Database Security: Concepts and Tools for the DBA by Peter J. Magee of SQRIBE Technologies that utilizes Eagle and extends it further.

Quite exciting to see an application of Eagle! More information on Eagle can be found in my blog as well. https://jmehtablog.blogspot.com/2009/08/do-it-yourself-implement-performance.html

Implement SQL*Net Encryption and Checksum

My article on SQL*Net Encryption and Checksum was published in the 2017 Winter edition of UKOUG Scene. 

-----

Implement SQL*Net Encryption and Checksum

Oracle SQL*Net encryption is included in the database license. At one point, it required an Oracle Advanced Security option license at an additional cost, but not anymore. I would strongly recommend that you take advantage of this feature to bolster the security of your database, if you haven't done so yet.

Without encryption, database communication will be in clear-text, potentially compromising the confidentiality of your data. Malicious users and/or hackers would be able to eavesdrop on the data communications using network sniffer tools such as Wireshark. Not only data communications could be at risk, but DBA activities such as password changes would also be visible to the sniffer tools.

I believe the primary reasons for not implementing SQL*Net encryption are: (a) Many DBAs are still not aware that SQL*Net encryption feature is now available at no additional cost; (b) Many DBAs and organizations do not see a need to implement SQL*Net encryption due to the fact that databases are within the internal network and behind the network firewall; (c) SQL*Net encryption is not a mandate or organizational requirement or (d) What if this breaks my applications?

Even though databases are behind the network firewall, it is important to implement SQL*Net encryption/check-sum for two main reasons: (a) hackers may find a vulnerable system elsewhere on the network and establish a post to sniff the network traffic, including Oracle database, and (b) one cannot discount insider threats from malicious/disgruntled users who can sniff the network traffic.

Although SQL*Net encryption implementation details are readily available on the web, this article attempts to demonstrate an approach that should help you implement this feature without any service disruptions.

Start with REQUESTED

My recommendation is to set sqlnet.ora encryption and checksum parameters to REQUESTED on the database server, as shown below:

SQLNET.CRYPTO_CHECKSUM_SERVER=requested

SQLNET.ENCRYPTION_SERVER=requested

SQLNET.CRYPTO_CHECKSUM_CLIENT=requested

SQLNET.ENCRYPTION_CLIENT=requested

Configuring and managing these parameters at the database server level, instead of at all client locations, provides a centralized approach to implement encryption and check-sum. It requests that all bi-directional database traffic be encrypted and check-summed.

Please note that Oracle does not implement encryption and check-sum out-of-the-box. The default value for these parameters is ACCEPTED which triggers encryption and checksum only if specifically required or requested by the client. Setting these parameters to REQUESTED on the database server notifies the database clients that the database has requested encryption and checksum services. However, the clients may reject database server's request through sqlnet.ora settings or may not be able honor it due to incompatible client Oracle software. In this situation, the database server will proceed without encryption/checksum services and establish clear-text communications with the clients. The point is that REQUESETD setting won't break your connections to the database.

The two _CLIENT parameters mentioned above are for database connections originating from the database server, including database link connections to other databases. Just an FYI that SQLNET.CRYPTO_SEED parameter is obsolete since Oracle9i.

I would suggest not specifying any encryption and checksum protocols at this point. Let Oracle database server negotiate with the client and choose the strongest protocols that are acceptable to both parties.

Run the following query (a) to identify clients who have (or have not) implemented the encryption and checksum services and (b) protocols that have been negotiated by the server and the clients:

select * from v$session_connect_info order by sid;

If this query returns two rows with protocol names as shown below, then you are using encryption and checksum services. Please note that protocols may vary for some clients.

AES256 Encryption service adapter for 64-bit Windows: Version 12.1.0.2.0 - Production

SHA1 Crypto-checksumming service adapter for 64-bit Windows: Version 12.1.0.2.0 - Production

If you don’t see these two rows with protocol names, then use SID and SERIAL# returned by this query to identify the clients who are not using these services and work with them to resolve issues. Older versions of SQL*Net client software may be the most likely issue that you will need to resolve.

In addition, the above mentioned query also returns the following rows for each client which is for informational purposes only.

Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 12.1.0.2.0 - Production

Encryption service for 64-bit Windows: Version 12.1.0.2.0 - Production

Crypto-checksumming service for 64-bit Windows: Version 12.1.0.2.0 - Production

As you may have noticed, REQUESTED setting, in essence, implements a “monitoring” mode. Database server requests the clients to support the encryption and checksum services, but doesn't require/force them to do so. If database clients are unable to fulfill this request, then the database server proceeds with establishing a database connection in clear-text. Database connections are not rejected or denied and hence nothing breaks!

Configure Encryption and Checksum Protocols

The next step is to configure encryption and check-sum protocols. Your organization may have a mandate for strong encryption standards such as FIPS 140-2. Or your objective may be to remove weaker protocols such as MD5 or RC4. Whatever may be the reason, review the results of the query against v$session_connect_info view and compile a list of protocols that are being used for encryption and checksum. If the protocols that are being used are weaker as per your organization policy, then you may need to identify the root cause which very well may be an older version of the SQL*Net client.  You need to work with the users to upgrade the software before stronger protocols can be deployed.

You could turn on SQL*Net tracing, as shown below, to troubleshoot the issue:

TRACE_LEVEL_CLIENT=16

TRACE_DIRECTORY_CLIENT = c:\Oracle\temp

TRACE_FILE_CLIENT= SQLNet.trc

TRACE_TIMESTAMP_CLIENT = ON

TRACE_UNIQUE_CLIENT = ON

Once you have ensured that all the clients are supporting the protocols that you would like them to, adjust the values of the following parameters in sqlnet.ora on the database server. More than one protocols can be specified as shown below.

SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)

SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)

SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA512, SHA384, SHA256, SHA1)

SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA512, SHA384, SHA256, SHA1)

If you have a combination of Oracle10g, Oracle11g and Oracle 12cR1 database servers and clients, then the above protocols should work for you. Of course, you need to test the configuration in your environments (development, test and/or UAT and finally the production).

Oracle10g, Oracle11g and Oracle 12cR1 supports the following encryption protocols:

·        Advanced Encryption Standard (AES): AES256, AES192 and AES128 

·        Triple DES: 3DES168, 3DES112 

·        DES: DES and DES40

·        RC: RC4_256, RC4_128, RC4_56, RC4_40 

Oracle10g, Oracle11g and Oracle12c R1 (12.2.0.1) supports the following checksum protocols:

·        MD5

·        SHA1

Oracle12c R1 introduces the following three additional protocols for checksum

·        SHA512

·        SHA384

·        SHA256 

Switch to REQUIRED

Once you are satisfied that all clients are supporting the encryption and checksum services with the approved protocols, then you should switch the values of these parameters to REQUIRED as follows:

SQLNET.CRYPTO_CHECKSUM_SERVER=required

SQLNET.ENCRYPTION_SERVER=required

SQLNET.CRYPTO_CHECKSUM_CLIENT=required

SQLNET.ENCRYPTION_CLIENT=required

Typical Errors

 You will get “ORA-12649: Unknown encryption or data integrity algorithm” when encryption or checksum protocols that you specified in SQLNET.ORA are unknown or not supported. For example, you have specified SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA512) but SHA512 is not supported by Oracle11g client.

Oracle error “ORA-12650: No common encryption or data integrity algorithm” will be reported when there are no common encryption protocols between client and server. For example, SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(MD5) on the client side and SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SH1) on the server.

Oracle error “ORA-12660: Encryption or crypto-checksumming parameters incompatible” will be reported when the server is requesting encryption or check-sum, but the client is not supporting it or vice versa.

You should monitor Oracle alert logs and SQL*Net logs on the database server to see if there are any issues. You may see the above mentioned ORA- errors. In addition, alert log also shows error “opiodr aborting process unknown ospid (11992) as a result of ORA-609”