Oracle required permissions

This topic lists all Oracle permissions required to use Oracle with Data Integration. Permissions are organized by use case: standard rivers, CDC, flashback query, and synonym support.

Standard river permissions

For standard (incremental or full load) rivers, the Oracle user requires basic access to read data and metadata. In multi-tenant environments, apply these grants to the local PDB user.

Permission	Purpose
`CREATE SESSION`	Connect to the database.
`CONNECT, RESOURCE`	Standard session and resource access.
`SELECT ON ALL_TABLES`	Access table metadata.
`SELECT ON ALL_VIEWS`	Access view metadata.
`SELECT ON ALL_TAB_COLUMNS`	Read column definitions.

GRANT CREATE SESSION TO <username>;
GRANT CONNECT, RESOURCE TO <username>;
GRANT SELECT ON ALL_TABLES TO <username>;
GRANT SELECT ON ALL_VIEWS TO <username>;
GRANT SELECT ON ALL_TAB_COLUMNS TO <username>;

CDC permissions: single-tenant

For CDC rivers on single-tenant (non-CDB) Oracle instances, the following privileges must be granted directly to the user. Roles alone are not sufficient.

Permission	Purpose
`SELECT ANY TRANSACTION`	Query the `V$LOGMNR_CONTENTS` view.
`SELECT_CATALOG_ROLE`	Query Oracle data dictionary views.
`EXECUTE_CATALOG_ROLE`	Execute SYS packages including `DBMS_LOGMNR`.

GRANT SELECT ANY TRANSACTION TO <username>;
GRANT SELECT_CATALOG_ROLE TO <username>;
GRANT EXECUTE_CATALOG_ROLE TO <username>;

CDC permissions: multi-tenant (CDB user)

For CDC rivers on multi-tenant (CDB/PDB) Oracle instances, a Common User (c##user) at the CDB level is required. This user reads redo and archive logs across all PDBs.

Permission	Scope	Purpose
`CREATE SESSION, ALTER SESSION, SET CONTAINER`	`CONTAINER=ALL`	Connect and switch between PDBs.
`SELECT ON V$CONTAINERS`	—	Access container metadata.
`SELECT ON SYS.V_$DATABASE`	—	Read database identity and incarnation info.
`SELECT ON SYS.V_$PARAMETER`	—	Read database parameters.
`SELECT ON SYS.V_$ARCHIVED_LOG`	—	Access archived log metadata.
`SELECT ON SYS.V_$ARCHIVE_DEST`	—	Access archive destination info.
`SELECT ON SYS.V_$LOGMNR_CONTENTS`	—	Read LogMiner redo log events.
`SELECT ON SYS.V_$LOG`	`CONTAINER=ALL`	Read log group info across PDBs.
`SELECT ON SYS.V_$LOG_HISTORY`	`CONTAINER=ALL`	Access log history across PDBs.
`SELECT ON SYS.V_$LOGMNR_LOGS`	`CONTAINER=ALL`	Access LogMiner log info across PDBs.
`SELECT ON SYS.V_$LOGMNR_PARAMETERS`	`CONTAINER=ALL`	Read LogMiner parameters across PDBs.
`SELECT ON SYS.V_$LOGFILE`	`CONTAINER=ALL`	Access log file info across PDBs.
`SELECT ON SYS.V_$ARCHIVE_DEST_STATUS`	`CONTAINER=ALL`	Read archive destination status across PDBs.
`SELECT ON SYS.V_$TRANSACTION`	`CONTAINER=ALL`	Access transaction metadata across PDBs.
`SELECT ON V_$MYSTAT`	`CONTAINER=ALL`	Session statistics.
`SELECT ON V_$STATNAME`	`CONTAINER=ALL`	Statistic name lookup.
`EXECUTE ON DBMS_LOGMNR`	—	Run LogMiner procedures.
`EXECUTE ON DBMS_LOGMNR_D`	—	Access LogMiner dictionary procedures.
`SELECT ANY TRANSACTION`	—	Query `V$LOGMNR_CONTENTS`.
`EXECUTE_CATALOG_ROLE`	—	Execute SYS packages.
`LOGMINING`	—	Use Oracle LogMiner.

ALTER SESSION SET CONTAINER=CDB$ROOT;
CREATE USER c##user IDENTIFIED BY <PASSWORD> CONTAINER=ALL;
GRANT CREATE SESSION, ALTER SESSION, SET CONTAINER TO c##user CONTAINER=ALL;
ALTER USER c##user SET CONTAINER_DATA=ALL CONTAINER=CURRENT;

GRANT SELECT ON V$CONTAINERS TO c##user;
GRANT SELECT ON SYS.V_$DATABASE TO c##user;
GRANT SELECT ON SYS.V_$PARAMETER TO c##user;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO c##user;
GRANT SELECT ON SYS.V_$ARCHIVE_DEST TO c##user;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO c##user;
GRANT SELECT ON SYS.V_$LOG TO c##user CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOG_HISTORY TO c##user CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOGMNR_LOGS TO c##user CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOGMNR_PARAMETERS TO c##user CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOGFILE TO c##user CONTAINER=ALL;
GRANT SELECT ON SYS.V_$ARCHIVE_DEST_STATUS TO c##user CONTAINER=ALL;
GRANT SELECT ON SYS.V_$TRANSACTION TO c##user CONTAINER=ALL;
GRANT SELECT ON V_$MYSTAT TO c##user CONTAINER=ALL;
GRANT SELECT ON V_$STATNAME TO c##user CONTAINER=ALL;
GRANT EXECUTE ON DBMS_LOGMNR TO c##user;
GRANT EXECUTE ON DBMS_LOGMNR_D TO c##user;
GRANT SELECT ANY TRANSACTION TO c##user;
GRANT EXECUTE_CATALOG_ROLE TO c##user;
GRANT LOGMINING TO c##user;

CDC permissions: flashback query

To support DDL synchronization and schema drift detection, the connector user requires one of the following options. For full details, refer to Oracle CDC DDL and flashback query.

Option A: system-level (recommended):

GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO <connector_user>;

Option B: object-level (if system-level access is restricted):

GRANT SELECT, FLASHBACK ON <schema>.<table_name> TO <connector_user>;

CDC permissions: synonym support

When using Oracle synonyms or editioning views as CDC sources, additional SELECT permissions are required on Oracle dictionary views. These are split into two groups: views used at CDC runtime, and views used when configuring a river in the Data Integration UI. For full details, refer to Oracle CDC with synonym support.

CDC runtime

Oracle dictionary view	Purpose	Single-tenant	Multi-tenant (CDB)
`ALL_OBJECTS_AE`	Edition-aware object type classification for chain resolution.	Required	Required
`ALL_SYNONYMS`	Synonym chain resolution.	Required	Required
`ALL_EDITIONING_VIEWS_AE`	Editioning view to base table (all editions).	Required	Required
`ALL_TAB_COLUMNS`	Physical table column metadata.	Required	Required
`V$DATABASE`	Current SCN, supplemental logging, and archive log mode checks.	Required	Covered by CDB grants above
`V$LOGMNR_LOGS`	Log files in active LogMiner session.	Required	Covered by CDB grants above
`V$ARCHIVE_DEST`	Filter archived logs by valid destination.	Required	Covered by CDB grants above
`V$VERSION`	Oracle version detection at startup.	Required	Required
`V$ARCHIVED_LOG`	Access archived logs for historical CDC.	Required	Covered by CDB grants above
`V$LOGFILE`	Identify active redo log files.	Required	Covered by CDB grants above
`V$LOG`	Current log group status.	Required	Covered by CDB grants above
`V$INSTANCE`	Database instance information.	Required	Required

GRANT SELECT ON ALL_OBJECTS_AE TO <username>;
GRANT SELECT ON ALL_SYNONYMS TO <username>;
GRANT SELECT ON ALL_EDITIONING_VIEWS_AE TO <username>;
GRANT SELECT ON ALL_TAB_COLUMNS TO <username>;
GRANT SELECT ON V$DATABASE TO <username>;
GRANT SELECT ON V$LOGMNR_LOGS TO <username>;
GRANT SELECT ON V$ARCHIVE_DEST TO <username>;
GRANT SELECT ON V$VERSION TO <username>;
GRANT SELECT ON V$ARCHIVED_LOG TO <username>;
GRANT SELECT ON V$LOGFILE TO <username>;
GRANT SELECT ON V$LOG TO <username>;
GRANT SELECT ON V$INSTANCE TO <username>;

note

Multi-tenant (CDB) users already receive several of these grants in the CDC permissions: multi-tenant (CDB user) section above. Apply only the additional grants marked Required for single-tenant environments.

River configuration UI

Grant the following permissions to enable table and synonym selection during river configuration in Data Integration.

Oracle dictionary view	Purpose	Single-tenant	Multi-tenant (CDB)
`ALL_OBJECTS`	Table and synonym listing in the river configuration UI.	Required	Required
`ALL_VIEWS`	Editioning view detection via `EDITIONING_VIEW` column.	Required	Required
`ALL_EDITIONING_VIEWS`	Editioning view to base table (current edition).	Required	Required
`ALL_TAB_COLUMNS`	Column metadata for field mapping.	Required	Required
`DBA_SEGMENTS`	Table size estimation.	Required	Required
`DBA_TABLES`	Table listing joined with `DBA_SEGMENTS` for size data.	Required	Required
`ALL_TAB_PARTITIONS`	Partitioned table chunking during data load.	Required	Required

GRANT SELECT ON ALL_OBJECTS TO <username>;
GRANT SELECT ON ALL_VIEWS TO <username>;
GRANT SELECT ON ALL_EDITIONING_VIEWS TO <username>;
GRANT SELECT ON ALL_TAB_COLUMNS TO <username>;
GRANT SELECT ON DBA_SEGMENTS TO <username>;
GRANT SELECT ON DBA_TABLES TO <username>;
GRANT SELECT ON ALL_TAB_PARTITIONS TO <username>;