Debezium oracle connector embedded mode:ORA-01031: insufficient privileges

[Satheesh Thodupunoori]

Hi Team,

I have been working on a POC to integrate debezium into our application.I have been able to complete the setup using mysql connector. However i am seeing an issue with oracle.  

I am using oracle 12c (But database to cpature events is in root. We are not using PDB),

debezium-embedded', version  '1.9.0.Alpha1',

debezium-connector-oracle version: '1.9.0.Alpha1'

Below is the log i get when i start the server

2022-02-19 20:24:09.729  INFO 20148 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Snapshotting contents of 1 tables while still in transaction
2022-02-19 20:24:09.729  INFO 20148 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Exporting data from table 'DEV.TEST_DEB_1.STUDENT' (1 of 1 tables)
2022-02-19 20:24:09.730  INFO 20148 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource :          For table 'DEV.TEST_DEB_1.STUDENT' using select statement: 'SELECT "ID", "NAME", "LAST_NAME", "CTD_DATE" FROM "TEST_DEB_1"."STUDENT" AS OF SCN 11415579'
2022-02-19 20:24:09.773  INFO 20148 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource :          Finished exporting 0 records for table 'DEV.TEST_DEB_1.STUDENT'; total duration '00:00:00.044'
2022-02-19 20:24:09.807  INFO 20148 --- [rce-coordinator] .d.p.s.AbstractSnapshotChangeEventSource : Snapshot - Final stage
2022-02-19 20:24:09.808  INFO 20148 --- [rce-coordinator] i.d.p.ChangeEventSourceCoordinator       : Snapshot ended with SnapshotResult [status=COMPLETED, offset=OracleOffsetContext [scn=11415579]]
2022-02-19 20:24:09.813  INFO 20148 --- [rce-coordinator] DefaultStreamingChangeEventSourceMetrics : Connected metrics set to 'true'
2022-02-19 20:24:09.814  INFO 20148 --- [rce-coordinator] i.d.p.ChangeEventSourceCoordinator       : Starting streaming
2022-02-19 20:24:10.018  INFO 20148 --- [pool-1-thread-1] i.d.connector.common.BaseSourceTask      : 1 records sent during previous 00:00:32.895, last recorded offset: {snapshot=true, scn=11415579, snapshot_completed=false}
EmbeddedEngineChangeEvent [key={"schema":{"type":"struct","fields":[{"type":"string","optional":false,"field":"databaseName"}],"optional":false,"name":"io.debezium.connector.oracle.SchemaChangeKey"},"payload":{"databaseName":"DEV"}}, value={"schema":{"type":"struct","fields":[{"type":"struct","fields":[{"type":"string","optional":false,"field":"version"},{"type":"string","optional":false,"field":"connector"},{"type":"string","optional":false,"field":"name"},{"type":"int64","optional":false,"field":"ts_ms"},{"type":"string","optional":true,"name":"io.debezium.data.Enum","version":1,"parameters":{"allowed":"true,last,false,incremental"},"default":"false","field":"snapshot"},{"type":"string","optional":false,"field":"db"},{"type":"string","optional":true,"field":"sequence"},{"type":"string","optional":false,"field":"schema"},{"type":"string","optional":false,"field":"table"},{"type":"string","optional":true,"field":"txId"},{"type":"string","optional":true,"field":"scn"},{"type":"string","optional":true,"field":"commit_scn"},{"type":"string","optional":true,"field":"lcr_position"}],"optional":false,"name":"io.debezium.connector.oracle.Source","field":"source"},{"type":"string","optional":true,"field":"databaseName"},{"type":"string","optional":true,"field":"schemaName"},{"type":"string","optional":true,"field":"ddl"},{"type":"array","items":{"type":"struct","fields":[{"type":"string","optional":false,"field":"type"},{"type":"string","optional":false,"field":"id"},{"type":"struct","fields":[{"type":"string","optional":true,"field":"defaultCharsetName"},{"type":"array","items":{"type":"string","optional":false},"optional":true,"field":"primaryKeyColumnNames"},{"type":"array","items":{"type":"struct","fields":[{"type":"string","optional":false,"field":"name"},{"type":"int32","optional":false,"field":"jdbcType"},{"type":"int32","optional":true,"field":"nativeType"},{"type":"string","optional":false,"field":"typeName"},{"type":"string","optional":true,"field":"typeExpression"},{"type":"string","optional":true,"field":"charsetName"},{"type":"int32","optional":true,"field":"length"},{"type":"int32","optional":true,"field":"scale"},{"type":"int32","optional":false,"field":"position"},{"type":"boolean","optional":true,"field":"optional"},{"type":"boolean","optional":true,"field":"autoIncremented"},{"type":"boolean","optional":true,"field":"generated"},{"type":"string","optional":true,"field":"comment"}],"optional":false,"name":"io.debezium.connector.schema.Column"},"optional":false,"field":"columns"},{"type":"string","optional":true,"field":"comment"}],"optional":false,"name":"io.debezium.connector.schema.Table","field":"table"}],"optional":false,"name":"io.debezium.connector.schema.Change"},"optional":false,"field":"tableChanges"}],"optional":false,"name":"io.debezium.connector.oracle.SchemaChangeValue"},"payload":{"source":{"version":"1.9.0.Alpha1","connector":"oracle","name":"my-app-connector2","ts_ms":1645282449295,"snapshot":"true","db":"DEV","sequence":null,"schema":"TEST_DEB_1","table":"STUDENT","txId":null,"scn":"11415579","commit_scn":null,"lcr_position":null},"databaseName":"DEV","schemaName":"TEST_DEB_1","ddl":"\n  CREATE TABLE \"TEST_DEB_1\".\"STUDENT\" \n   (\t\"ID\" NUMBER, \n\t\"NAME\" VARCHAR2(20), \n\t\"LAST_NAME\" VARCHAR2(20), \n\t\"CTD_DATE\" DATE, \n\t SUPPLEMENTAL LOG DATA (ALL) COLUMNS\n   ) ;","tableChanges":[{"type":"CREATE","id":"\"DEV\".\"TEST_DEB_1\".\"STUDENT\"","table":{"defaultCharsetName":null,"primaryKeyColumnNames":[],"columns":[{"name":"ID","jdbcType":2,"nativeType":null,"typeName":"NUMBER","typeExpression":"NUMBER","charsetName":null,"length":0,"scale":null,"position":1,"optional":true,"autoIncremented":false,"generated":false,"comment":null},{"name":"NAME","jdbcType":12,"nativeType":null,"typeName":"VARCHAR2","typeExpression":"VARCHAR2","charsetName":null,"length":20,"scale":null,"position":2,"optional":true,"autoIncremented":false,"generated":false,"comment":null},{"name":"LAST_NAME","jdbcType":12,"nativeType":null,"typeName":"VARCHAR2","typeExpression":"VARCHAR2","charsetName":null,"length":20,"scale":null,"position":3,"optional":true,"autoIncremented":false,"generated":false,"comment":null},{"name":"CTD_DATE","jdbcType":93,"nativeType":null,"typeName":"DATE","typeExpression":"DATE","charsetName":null,"length":null,"scale":null,"position":4,"optional":true,"autoIncremented":false,"generated":false,"comment":null}],"comment":null}}]}}, sourceRecord=SourceRecord{sourcePartition={server=my-app-connector2}, sourceOffset={snapshot=true, scn=11415579, snapshot_completed=false}} ConnectRecord{topic='my-app-connector2', kafkaPartition=0, key=Struct{databaseName=DEV}, keySchema=Schema{io.debezium.connector.oracle.SchemaChangeKey:STRUCT}, value=Struct{source=Struct{version=1.9.0.Alpha1,connector=oracle,name=my-app-connector2,ts_ms=1645282449295,snapshot=true,db=DEV,schema=TEST_DEB_1,table=STUDENT,scn=11415579},databaseName=DEV,schemaName=TEST_DEB_1,ddl=
  CREATE TABLE "TEST_DEB_1"."STUDENT"
   (        "ID" NUMBER,
        "NAME" VARCHAR2(20),
        "LAST_NAME" VARCHAR2(20),
        "CTD_DATE" DATE,
         SUPPLEMENTAL LOG DATA (ALL) COLUMNS
   ) ;,tableChanges=[Struct{type=CREATE,id="DEV"."TEST_DEB_1"."STUDENT",table=Struct{primaryKeyColumnNames=[],columns=[Struct{name=ID,jdbcType=2,typeName=NUMBER,typeExpression=NUMBER,length=0,position=1,optional=true,autoIncremented=false,generated=false}, Struct{name=NAME,jdbcType=12,typeName=VARCHAR2,typeExpression=VARCHAR2,length=20,position=2,optional=true,autoIncremented=false,generated=false}, Struct{name=LAST_NAME,jdbcType=12,typeName=VARCHAR2,typeExpression=VARCHAR2,length=20,position=3,optional=true,autoIncremented=false,generated=false}, Struct{name=CTD_DATE,jdbcType=93,typeName=DATE,typeExpression=DATE,position=4,optional=true,autoIncremented=false,generated=false}]}}]}, valueSchema=Schema{io.debezium.connector.oracle.SchemaChangeValue:STRUCT}, timestamp=null, headers=ConnectHeaders(headers=)}]
2022-02-19 20:24:14.503 ERROR 20148 --- [rce-coordinator] i.d.c.oracle.logminer.LogMinerHelper     : Mining session stopped due to the {}

java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 7384
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 7398
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 7554
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1

I have ensured the oracle user has all permissions mentioned here. https://debezium.io/documentation/reference/stable/connectors/oracle.html#setting-up-oracle.

When i restart the server i found that all the changes done while debezium connector was down are getting logged and then the issue with  privileges  occurs.

Please suggest me if i am missing anything here.

Thanks in advance.

Satheesh T

[Chris Cranford]

Hi Satheesh,

See inline...

On 2/19/22 11:10, Satheesh Thodupunoori wrote:

Hi Team,

I have been working on a POC to integrate debezium into our application.I have been able to complete the setup using mysql connector. However i am seeing an issue with oracle.  

I am using oracle 12c (But database to cpature events is in root. We are not using PDB),

debezium-embedded', version  '1.9.0.Alpha1',

debezium-connector-oracle version: '1.9.0.Alpha1'

....

java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 7384
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 7398
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 7554
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1

This looks like both "DBMS_LOGMNR" and "DBMS_LOGMNR_D" packages do not have EXECUTE permissions.  Can you please double check those grants.

When i restart the server i found that all the changes done while debezium connector was down are getting logged and then the issue with  privileges  occurs.

It is expected behavior for any Debezium connector to stream changes that were performed while the connector is down.  This is because Debezium connectors start from where they left off to avoid any data loss.

HTH,
CC

[Gunnar Morling]

Chris,

> This looks like both "DBMS_LOGMNR" and "DBMS_LOGMNR_D" packages do not have EXECUTE permissions.  Can you please double check those grants.

Can we check whether the connector has all the required privileges upon connector config validation, and if that's not the case, raise a meaningful error describing the missing privileges?

If doable, could you capture this in Jira?

Thanks,

--Gunnar

[Chris Cranford]

Hi Gunnar -

We most definitely can validate some of the grants, some are definitely a bit easier to check than others, i.e. we do have to consider PDB vs non-PDB, situations where "ANY TABLE" grants are replaced by specific table grants in production environments, or the few that are version-specific such as LOGMINING role that doesn't exist on older Oracle releases.  I've logged https://issues.redhat.com/browse/DBZ-4775 to continue this discussion there.

CC

--
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/8fe7202a-56c9-49f7-b87d-52067043e264n%40googlegroups.com.

[Satheesh Thodupunoori]

Hi Chris,

Thanks for your reply.

We have verified the grants but couldn't get to the root cause of the issue. Below are the list of grants provided to the oracle user. Could you please suggest us if we are missing anything.

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

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

  GRANT EXECUTE ON DBMS_LOGMNR TO DEB_TEST1;
  GRANT EXECUTE ON DBMS_LOGMNR_D TO DEB_TEST1;

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

On another note, We tried with system user(Only to see if that works) and we are able to capture the events. But observed delay in getting the events .i.e approximately 7mins in our case.

How can we reduce this delay..?

Thanks

Satheesh T

[Chris Cranford]

Hi Satheesh -

So I just compared your GRANTs with my Oracle 12 non-CDB installation script and this is what I have that you do not:

    GRANT SELECT ANY DICTIONARY TO DEB_TEST1;
   
I'm fairly certain this is the grant you explicitly need because in this other thread, https://groups.google.com/g/debezium/c/-akfG_llRDg, you'll notice that SYS.DBMS_LOGMNR_INTERNAL line 7384 was referenced but in this case an ORA-01327 was thrown that the data dictionary could not be exclusively locked.  So my guess is you don't have read access to the data dictionary of the database and that's why the data dictionary build step to redo logs fails.

As to the SYSTEM user, yes I would expect that to work; that is an administrator account; however, SYS and SYSTEM user changes are not captured by the connector so I would not recommend using these user accounts other than for maintenance of Oracle as they're intended. 

Finally, the delay is likely the writing & reading of the data dictionary from the redo logs.  Did you have the DBA resize your redo logs before you deployed the connector?  You can get your current redo log sizes by using the following SQL, see the SIZE_MB column:

    SELECT F.MEMBER, L.GROUP#, L.THREAD#, L.SEQUENCE#, L.BYTES/1024/1024 AS SIZE_MB, L.ARCHIVED, L.STATUS, L.FIRST_CHANGE#, L.NEXT_CHANGE#
      FROM V$LOG L, V$LOGFILE F
    WHERE F.GROUP# = L.GROUP#
    ORDER BY L.GROUP#;

Could you please share the output of the above SQL.  Thanks

Thanks,
CC

--

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/d6fcb515-60f1-4460-9d0a-ee6c6e06485en%40googlegroups.com.

[Satheesh Thodupunoori]

Hi Chris,

We have provided read access to the database dictionary but could not avoid the error(Created a new user this time).  Please suggest us if we have to verify any other grants/configuration.

And the output of given query is below

Thanks

Satheesh T

[Satheesh Thodupunoori]

Hi Chris,

We observed the privilege issue is from below alter statement in dbms_logmnr_internal package.

alter session set container=<PDB_NAME>

We are using 12c version of oracle but not using any pluggable databases. All out database setup is in root. can this log mining be done excluding the PDBs.

Please suggest.

Thanks

Satheesh T

[Chris Cranford]

Hi Satheesh -

What is your connector configuration?  It sounds like you therefore have some type of connector misconfiguration.

Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/8fec259a-743d-471b-9b88-b70c52d7d5e6n%40googlegroups.com.

[Satheesh Thodupunoori]

Hi Chris,

Here is the configuration.

 props.setProperty("name", "oracle_debezium_engine1");
                 props.setProperty("offset.storage", "org.apache.kafka.connect.storage.FileOffsetBackingStore");
                 props.setProperty("connector.class", "io.debezium.connector.oracle.OracleConnector");
                 props.setProperty("offset.storage.file.filename", "/tmp/offsets_oracle11.dat");
             
                 props.setProperty("database.hostname", "<<HOST_IP>>");
                 props.setProperty("database.port", "1521");
                 props.setProperty("database.user", "DEB_TEST2");
                 props.setProperty("database.password", "DEB_TEST2");
                 props.setProperty("database.dbname", "dev");
               
                 props.setProperty("database.server.id", "857441212");
                 props.setProperty("database.server.name", "my-app-connector2");
                
                 props.setProperty( "schema.include.list","DEB_TEST2");
                props.setProperty("table.include.list", "DEB_TEST2.TEST_CDC");
                 props.setProperty("database.history",
                       "io.debezium.relational.history.FileDatabaseHistory");
                 
                 props.setProperty("database.history.file.filename",
                       "/tmp/dbhistory_oracle11.dat");

Thanks

Satheesh T

[Chris Cranford]

Hi Satheesh -

If you look in V$DATABASE, is there a CDB column and if so is its value YES or NO?

If the value is YES, then you are running Oracle against a container-database (CDB) installation of Oracle, in which cases the grants should have been as described in the documentation to include the container-clause.  Furthermore, the connector configuration should also include the "database.pdb.name" option to tell the connector which PDB you are going to stream changes from while "database.dbname" refers to the CDB database.

If the value is NO, then your connector configuration looks acceptable and under no circumstance should dbms_logmnr_internal performed an alter session setting the container.  If it is; that's a bug in Oracle.

Hope that helps,
Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/668ab15c-0b65-443f-b90b-99bede765d1en%40googlegroups.com.