Re: [debezium] With Oracle connector getting ORA-01031: insufficient privileges

[Chris Cranford]

Hi -

So without your connector configuration, I can only make a few assumptions.

Given that your GRANTs are for the `cdbzuser`, this tells me that you have given it to a local user. You would only do this if you do not have CDB enabled in your database (no multi-tenancy support).  You can confirm multitenancy by query the V$DATBASE table using the following query:

    SELECT CDB FROM V$DATABASE

If the `CDB` column returns `NO`, then what it would appear is you've provided the `database.pdb.name` in your connector configuration, which toggles multitenancy ON from the connector's perspective, which is at odds with your Oracle installation.  In this case, please remove the `database.pdb.name` configuration as its unnecessary.

If the `CDB` column returns `YES`, then you have multi-tenancy enabled, in which case you've configured the user for the connector incorrectly. For multitenancy installations, the user should be a common user, meaning that it must have a name prefixed with `c##`,  which is special for Oracle to designate a user that is created and managed across not only the container root database but all pluggable databases (tenants) in Oracle.  In this case, please create the user correctly using a common user and change the `database.user` reference in the configuration to use the common user account rather than a local user account.

If you have any further questions, please provide your connector configuration and we can dig deeper.

Thanks,
Chris

On 4/3/24 08:33, Arvind Deshmukh wrote:

Hi Chris,

I am using spring boot dependency io.debezium:debezium-connector-oracle:2.4.0.Final.

I have configured below permission to my user.

GRANT CREATE SESSION TO cdbzuser;
GRANT SET CONTAINER TO cdbzuser;
GRANT SELECT ON V_$DATABASE TO cdbzuser;
GRANT FLASHBACK ANY TABLE TO cdbzuser;
GRANT SELECT ANY TABLE TO cdbzuser;
GRANT SELECT_CATALOG_ROLE TO cdbzuser;
GRANT EXECUTE_CATALOG_ROLE TO cdbzuser;
GRANT SELECT ANY TRANSACTION TO cdbzuser;
GRANT SELECT ANY DICTIONARY TO cdbzuser;
GRANT LOGMINING TO cdbzuser;

GRANT CREATE TABLE TO cdbzuser;
GRANT LOCK ANY TABLE TO cdbzuser;
GRANT CREATE SEQUENCE TO cdbzuser;

GRANT EXECUTE ON DBMS_LOGMNR TO cdbzuser;
GRANT EXECUTE ON DBMS_LOGMNR_D TO cdbzuser;

GRANT SELECT ON V_$LOG TO cdbzuser;
GRANT SELECT ON V_$LOG_HISTORY TO cdbzuser;
GRANT SELECT ON V_$LOGMNR_LOGS TO cdbzuser;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO cdbzuser;
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO cdbzuser;
GRANT SELECT ON V_$LOGFILE TO cdbzuser;
GRANT SELECT ON V_$ARCHIVED_LOG TO cdbzuser;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO cdbzuser;
GRANT SELECT ON V_$TRANSACTION TO cdbzuser;

 After this, I am getting below error while performing snapshot.

2024-04-03 16:22:32 [pool-12-thread-1] INFO  io.debezium.jdbc.JdbcConnection - Connection gracefully closed
2024-04-03 16:22:32 [debezium-oracleconnector-tceng-change-event-source-coordinator] ERROR i.d.r.RelationalSnapshotChangeEventSource - Error during snapshot
java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
at io.debezium.connector.oracle.OracleConnection.resetSessionToCdb(OracleConnection.java:131) ~[debezium-connector-oracle-2.4.0.Final.jar:2.4.0.Final]
at io.debezium.connector.oracle.logminer.LogMinerAdapter.determineSnapshotOffset(LogMinerAdapter.java:138) ~[debezium-connector-oracle-2.4.0.Final.jar:2.4.0.Final]
at io.debezium.connector.oracle.OracleSnapshotChangeEventSource.determineSnapshotOffset(OracleSnapshotChangeEventSource.java:168) ~[debezium-connector-oracle-2.4.0.Final.jar:2.4.0.Final]
at io.debezium.connector.oracle.OracleSnapshotChangeEventSource.determineSnapshotOffset(OracleSnapshotChangeEventSource.java:45) ~[debezium-connector-oracle-2.4.0.Final.jar:2.4.0.Final]
at io.debezium.relational.RelationalSnapshotChangeEventSource.doExecute(RelationalSnapshotChangeEventSource.java:146) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:92) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:253) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:237) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
at io.debezium.pipeline.ChangeEventSourceCoordinator.executeChangeEventSources(ChangeEventSourceCoordinator.java:189) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:137) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) ~[na:na]
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[na:na]
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) ~[na:na]
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) ~[na:na]
at java.base/java.lang.Thread.run(Thread.java:833) ~[na:na]
Caused by: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1230) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:511) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:122) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1199) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1819) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1471) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2504) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2459) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:327) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
at io.debezium.connector.oracle.OracleConnection.resetSessionToCdb(OracleConnection.java:128) ~[debezium-connector-oracle-2.4.0.Final.jar:2.4.0.Final]
... 14 common frames omitted
Caused by: oracle.jdbc.OracleDatabaseException: ORA-01031: insufficient privileges

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:636) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
... 27 common frames omitted

what could be issue here? am I missing something?

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/d79ad946-b418-4c75-9a4a-196966d78182n%40googlegroups.com.