UCP: No Error Reported When A Connection Attempt Is Made To Multi-Tenant Database With An Incorrect Role Password (Doc ID 2934596.1)

Last updated on MARCH 14, 2023

Applies to:

Symptoms

It is observed that when a connection attempt is made to a multi-tenant database with incorrect role password, no SQL Exception (ORA-01979) is thrown.
Instead, a successful connection is returned to the client. When the client uses this connection to run a query against the database, it reports error "ORA: - Table or view does not exist".
Here, although the database connection is invalid, there is no status indicator from UCP to the client for identifying and validating the connection status.

Steps to Reproduce Problem

1. Create a UCP connection pool with an incorrect role password to a multi-tenant database.
2. Get a connection from the pool.
3. You will see a successful connection being returned instead of getting an SQL exception "ORA-01979: missing or invalid password for role 'xxxxxx' ".

Additional information
-------------------------
After enabling UCP debugging, it can be seen from the logs that UCP is actually throwing an exception on invalid role password.  But the exception is not propagated to the client.
Also, when checked, the status of the returned connection is found to be "UniversalPooledConnectionStatus.STATUS_NORMAL_DESC" instead of "UniversalPooledConnectionStatus.STATUS_BAD_DESC".
Hence, there is no way for the client to know whether the Connection is Valid or Invalid.


UCP Debug logs
------------------
Jan 11, 2023 4:02:49 PM oracle.ucp.logging.ClioSupport _log
FINEST: :::oracle.ucp.common.CoreConnectionImpl@6e521c1e created
Jan 11, 2023 4:02:49 PM oracle.ucp.logging.ClioSupport _log
FINEST: :::made operable: broker=recentAttemptTimestamp=0, retryInterval=0
Jan 11, 2023 4:02:49 PM oracle.ucp.logging.ClioSupport _log
FINEST: :::created: cri=user=xxxxxxxxxxxxx, password=********, labels={}, Service Name =xxxxxxxxxxxxx, PDB Roles ={xxxxxxxxxxxxx="xxxxxxxxxxxxx"},
createModes=[USE_BEST_INSTANCE], timeToRetry=3000, createdUpc=oracle.ucp.common.CoreConnectionImpl@6e521c1e

Jan 11, 2023 4:02:49 PM oracle.ucp.logging.ClioSupport _log
FINEST: :::throwing: ORA-01979: missing or invalid password for role 'xxxxxxxxxxxxx'
:[oracle.ucp.common.CoreConnectionImpl.onBorrow(CoreConnectionImpl.java:675),
oracle.ucp.common.Core.growBorrowed(Core.java:1298),
oracle.ucp.common.UniversalConnectionPoolImpl.helpGrowBorrowed(UniversalConnectionPoolImpl.java:406),
oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionWithoutCountingRequests(UniversalConnectionPoolImpl.java:349),
oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionAndValidateHelper(UniversalConnectionPoolImpl.java:205),
oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionAndValidate(UniversalConnectionPoolImpl.java:165),
oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnection(UniversalConnectionPoolImpl.java:138),
oracle.ucp.jdbc.JDBCConnectionPool.borrowConnection(JDBCConnectionPool.java:184),
oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.borrowConnection(OracleJDBCConnectionPool.java:610),
oracle.ucp.jdbc.oracle.OracleConnectionConnectionPool.borrowConnection(OracleConnectionConnectionPool.java:118),
oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1879),
oracle.ucp.jdbc.PoolDataSourceImpl.access$300(PoolDataSourceImpl.java:224),
oracle.ucp.jdbc.PoolDataSourceImpl$3.build(PoolDataSourceImpl.java:4050),
com.testing.MyPoolTest.getConnection(MyPoolTest.java:84),
com.testing.MyPoolTest.main(MyPoolTest.java:27)]

Jan 11, 2023 4:02:49 PM oracle.ucp.logging.ClioSupport _log
FINEST: :::connection=oracle.ucp.common.CoreConnectionImpl@6e521c1e
Jan 11, 2023 4:02:49 PM oracle.ucp.logging.ClioSupport _log

Changes

 Upgraded UCP drivers from 12.2.0.1.0 to 21.7.0.0.0.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.