Tag: security

How Often Do You Patch Your Oracle Database Client?

The most important reason why you must patch your Oracle Database is security. The threats any company faces today is very different than 10 or 20 years ago. Especially with the introduction of ransomware, everyone is a target.

When I talk to database specialists, there is consensus about the importance of applying patches. Luckily, it’s rare nowadays that you have to argue with people over it. Further, I see more and more companies putting this higher on the agenda. I know patching is extra work, but you must do it. You must avoid the embarrassment and potentially devasting effect on your company, as explained by Connor McDonald.

How do you patch the Oracle Database? Release Updates are the vehicle for delivering security fixes to your Oracle Database; so far, so good. But what about the client? How often do you patch your Oracle Database clients? Do you need to patch the client?

As always, the answer is: It depends… on which clients you are using.

The Critical Patch Updates

The place to look for information about security issues is the critical patch updates (CPU). Be sure to check it every quarter when the new one comes out.

If a specific client is affected, it is listed. For example, check the CPU from January 2023. It lists a vulnerability in Oracle Data Provider for .NET.

If you look back a few more quarters, vulnerabilities in the client appears to be rare. But they do occur.

Client-Only

If you use client-only installations, you can check the Oracle Database Server Risk Matrix in the CPU. In the text, Oracle states whether the vulnerabilities affect client-only installations.

Here is an overview of the last three years showing whether the vulnerabilities affected the client-only installation.

Release Update Client-only affected
October 2023 No
July 2023 Yes
April 2023 No
January 2023 Yes
October 2022 No
July 2022 Yes
April 2022 No
January 2022 No
October 2021 No
July 2021 Yes
April 2021 Yes
January 2021 No

To patch a client-only installation, you download the latest Release Update and apply it to the Oracle home, just like if it was the Oracle Database itself (the server).

You can use ORAdiff to find a list of included fixes.

Instant Client

For instant client, you download a new package and overwrite the existing instant client.

JDBC

To update JDBC, you download new jar files or use Maven Central Repository. On the JDBC download page, you can find a list of bugs fixed in the various releases. Here is the list for 19c.

ODP.NET

For Oracle Data Provider for .NET (ODP.NET), you can find the latest drivers on NuGet Gallery. The readme section has a list of bugs fixed since the previous version.

OCI

For OCI (Oracle Call Interface), you get the latest instant client and extract the relevant files from there.

ODAC

The Oracle Data Access Components (ODAC) package also contains other clients. You download the latest version and follow the instructions to unpack it.

Recommendation

For the database server, I strongly recommend:

  • Applying patches every quarter.
  • Using the latest Release Updates, although I do also understand why some people prefer to use the second latest Release Update (an N-1 approach).

For the database clients, I recommend:

  • Having a structured process to evaluate the risk when the Critical Patch Update Advisories come out every quarter.
  • Asses not only the security issues but also functional issues. Some drivers have a bugs fixed list. Use it to determine whether you use functionality that could benefit from the bug fixes.
  • Applying patches to your clients periodically. This ensures you have a structured and well-tested process. When it becomes urgent to patch your client, it’s easy because you’ve already done it so many times.

In general, I strongly recommend:

Troubleshooting Rabbit Hole: From Data Guard to Data Integrity Checks

I always fear the worst when I get a TNS error. It’s not my expertise. A TNS error was exactly what I got while I configured a Data Guard environment. Redo Transport didn’t work; the redo logs never made it to the standby database.

The Error

I took a look in the alert log on the primary database and found this error:

2022-05-10T08:25:28.739917+00:00
"alert_SALES2.log" 5136L, 255034C
        TCP/IP NT Protocol Adapter for Linux: Version 12.2.0.1.0 - Production
  Time: 10-MAY-2022 18:09:02
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12650

TNS-12650: No common encryption or data integrity algorithm
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0

A little further in the alert log, I found proof that the primary database could not connect to the standby database:

2022-05-10T18:09:02.991061+00:00
Error 12650 received logging on to the standby
TT04: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (12650)
TT04: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
2022-05-10T18:09:02.991482+00:00
Errors in file /u01/app/oracle/diag/rdbms/sales2_fra3cx/SALES2/trace/SALES2_tt04_75629.trc:
ORA-12650: No common encryption or data integrity algorithm
Error 12650 for archive log file 1 to '...'

The Investigation

As always, Google it! Although I have used DuckDuckGo for privacy reasons instead of Google for many years, I still say google it, which is fairly annoying.

The search revealed this MOS note: ORA-12650: No Common Encryption Or Data Integrity Algorithm When Using SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=sha256 (Doc ID 2396891.1) Although it is fairly old, it led me to look for issues with data integrity checks defined in sqlnet.ora.

The primary database had the following defined in sqlnet.ora:

SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA1)
SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA1)

The above means that any connection made to or from this database must use data integrity checks. CRYPTO_CHECKSUM_SERVER and CRYPTO_CHECKSUM_CLIENT defines that. Also, the database will only accept connections using the SHA1 algorithm.

Then I looked in sqlnet.ora on the standby database:

SQLNET.CRYPTO_CHECKSUM_CLIENT=ACCEPTED
SQLNET.CRYPTO_CHECKSUM_SERVER=ACCEPTED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256,SHA384,SHA512,SHA1)
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA256,SHA384,SHA512)

This database does not require data integrity checks. But if the other party requests or requires it, then the server is fine with it. That’s the meaning of ACCEPTED. But look at the allowed algorithms. When acting as server (i.e. receiving connections from someone else), it does not allow SHA1 algorithm, the only one allowed by the counterpart.

The Solution

I decided to remove all instances of SHA1 because:

  • It is an old algorithm
  • Any 12c database or client supports newer algorithms
  • In this environment, I don’t have any old 11g servers or clients

I added all the SHA-2 algorithms as supported algorithms. Now, sqlnet.ora in both databases look like this:

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

This solved the problem and now redo transport worked fine.

If I wanted to go maximum security, I should allow only the SHA512 algorithm in both sqlnet.ora files:

SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA512)
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA512)

And force both databases to always use data integrity checks:

SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED

Security

Some questions I asked myself while reading the Security Guide 19c.

Why do you want data integrity checks in our connections?

To protect against two types of attack:

  1. Data modification attack An unauthorized party intercepting data in transit, altering it, and retransmitting it is a data modification attack. For example, intercepting a $100 bank deposit, changing the amount to $10,000, and retransmitting the higher amount is a data modification attack.
  2. Replay attack Repetitively retransmitting an entire set of valid data is a replay attack, such as intercepting a $100 bank withdrawal and retransmitting it ten times, thereby receiving $1,000.

Can I do more to strengthen security in sqlnet.ora?

Yes. You should definitely also take a look at network encryption to protect data-in-transit. Take a look at Configuring Oracle Database Native Network Encryption and Data Integrity in the Security Guide 19c. These four parameters are of interest:

Also, reading Securing the Oracle Database – A technical primer can inspire you.

What’s wrong with SHA-1?

It’s old and has been made insecure by computer evolution. From Wikipedia:

In cryptography, SHA-1 (Secure Hash Algorithm 1) is a cryptographically broken but still widely used hash function which takes an input and produces a 160-bit (20-byte) hash value known as a message digest – typically rendered as a hexadecimal number, 40 digits long. It was designed by the United States National Security Agency, and is a U.S. Federal Information Processing Standard.

Since 2005, SHA-1 has not been considered secure against well-funded opponents; as of 2010 many organizations have recommended its replacement. NIST formally deprecated use of SHA-1 in 2011 and disallowed its use for digital signatures in 2013. As of 2020, chosen-prefix attacks against SHA-1 are practical. As such, it is recommended to remove SHA-1 from products as soon as possible and instead use SHA-2 or SHA-3. Replacing SHA-1 is urgent where it is used for digital signatures.