Flink CDC Oracle 用户权限不足 ORA-01031: insufficient privileges-Toy模板网

这篇具有很好参考价值的文章主要介绍了Flink CDC Oracle 用户权限不足 ORA-01031: insufficient privileges。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

Flink CDC Oracle用户权限不足
版本：flink1.14.5 、flinkcdc 2.2.1、oracle11g、
场景：flink cdc 实时抽取oracle的数据表。DBA为了数据库安全考虑，对访问用户权限进行控制。将oracle的flinkuser用户XE下的orders表授权只读权限给readuser用户。授权情况如下：

create user readuser identified by '123456';
grant create session,execute_catalog_role,select_catalog_role to readuser;
grant select on flinkuser.orders to readuser;

此时执行flink oracle cdc 任务:

CREATE TABLE `default_catalog`.`default_database`.`orders` (
  `ORDER_ID` INT NOT NULL,
  `ORDER_DATE` TIMESTAMP(0),
  `CUSTOMER_NAME` VARCHAR(2147483647),
  `PRICE` DECIMAL(10, 5),
  `PRODUCT_ID` INT,
  `ORDER_STATUS` INT,
  CONSTRAINT `PK_2016962539` PRIMARY KEY (`ORDER_ID`) NOT ENFORCED
) WITH (
  'debezium.log.mining.continuous.mine' = 'true',
  'hostname' = '192.168.1.188',
  'password' = '123456',
  'debezium.log.mining.strategy' = 'online_catalog',
  'connector' = 'oracle-cdc',
  'port' = '1521',
  'database-name' = 'XE',
  'schema-name' = 'flinkuser',
  'table-name' = 'orders',
  'username' = 'readuser'
);

select * from orders;

taskmanger执行报错提示：

com.ververica.cdc.connectors.shaded.org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped.
	at io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:42)
	at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:127)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: io.debezium.DebeziumException: com.ververica.cdc.connectors.shaded.org.apache.kafka.connect.errors.ConnectException: Snapshotting of table XE.FLINKUSER.ORDERS failed
	at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:82)
	at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:110)
	... 5 more
Caused by: com.ververica.cdc.connectors.shaded.org.apache.kafka.connect.errors.ConnectException: Snapshotting of table XE.FLINKUSER.ORDERS failed
	at io.debezium.relational.RelationalSnapshotChangeEventSource.createDataEventsForTable(RelationalSnapshotChangeEventSource.java:389)
	at io.debezium.relational.RelationalSnapshotChangeEventSource.createDataEvents(RelationalSnapshotChangeEventSource.java:305)
	at io.debezium.relational.RelationalSnapshotChangeEventSource.doExecute(RelationalSnapshotChangeEventSource.java:138)
	at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:71)
	... 6 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41)
	at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:765)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
	at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1362)
	at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:369)
	at io.debezium.relational.RelationalSnapshotChangeEventSource.createDataEventsForTable(RelationalSnapshotChangeEventSource.java:340)
	... 9 more
Caused by: Error : 1031, Position : 26, Sql = SELECT * FROM "FLINKUSER"."ORDERS" AS OF SCN 8483955, OriginalSql = SELECT * FROM "FLINKUSER"."ORDERS" AS OF SCN 8483955, Error Msg = ORA-01031: insufficient privileges

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
	... 22 more

初看，readuser用户没有读取表权限，但是已经授权表权限且通过dbeaver可以查询数据表。上向定位到debezium，百度发现debezium 采集数据是需要归档及logminer查询权限。尝试赋权：

grant flashback on flinkuser.orders to readuser;
grant select any transaction,select any dictionary to readuser;
grant select on SYSTEM.LOGMNR_COL$ to readuser;
grant select on SYSTEM.LOGMNR_OBJ$ to readuser;
grant select on SYSTEM.LOGMNR_USER$ to readuser;
grant select on SYSTEM.LOGMNR_UID$ to readuser;
grant select on V_$DATABASE to readuser;

重新运行任务，taskmanager执行依然报错权限不足，不过换了报错提示：
Caused by: Error : 1031, Position : 0, Sql = CREATE TABLE LOG_MINING_FLUSH(LAST_SCN NUMBER(19,0)), OriginalSql = CREATE TABLE LOG_MINING_FLUSH(LAST_SCN NUMBER(19,0)), Error Msg = ORA-01031: insufficient privileges at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513) ... 24 more
可见此处readuser没有建表权限，授权建表权限。