Oracle database user requirements

Beta

This feature is in Beta. Workspace admins can control access to this feature from the Previews page. See Manage Databricks previews.

This page lists the privileges that the Oracle replication user requires for ingesting into Databricks.

Databricks recommends that you create a database user that is solely used for Databricks ingestion. The setup tool (dbx_oracle_setup_util) grants the system and object privileges for you when you call GRANT_PERMISSIONS, and grants table SELECT when you call GRANT_SELECT_PERMISSIONS or GRANT_SELECT_ON_TABLE. This list is provided for reference and audit purposes.

For multi-tenant (CDB) databases, the setup tool grants each privilege with CONTAINER=ALL and sets CONTAINER_DATA=ALL on the user so that it can read change data across all containers. For Amazon RDS, the tool grants object privileges through the rdsadmin.rdsadmin_util.grant_sys_object procedure.

System privileges

Privilege	Why it's required
CREATE SESSION	Connect to the database.
SELECT ANY TRANSACTION	Read pending transaction state (gv$transaction) to bound LogMiner reads.
EXECUTE_CATALOG_ROLE	Execute the DBMS_LOGMNR packages and access the LogMiner data dictionary.
SELECT ANY DICTIONARY	Read data dictionary views used for schema and metadata discovery.
LOGMINING	Run LogMiner (DBMS_LOGMNR.START_LOGMNR) to read redo and archive logs.

Privilege	Why it's required
CREATE SESSION	Connect to the database.
SELECT ANY TRANSACTION	Read pending transaction state (gv$transaction) to bound LogMiner reads.
EXECUTE_CATALOG_ROLE	Execute the DBMS_LOGMNR packages and access the LogMiner data dictionary.
SELECT ANY DICTIONARY	Read data dictionary views used for schema and metadata discovery.
LOGMINING	Run LogMiner (DBMS_LOGMNR.START_LOGMNR) to read redo and archive logs.

Data access privileges

The replication user needs SELECT on every table you replicate. Grant it with the setup tool's GRANT_SELECT_PERMISSIONS procedure (all tables in a schema) or GRANT_SELECT_ON_TABLE procedure (specific tables). See Grant SELECT privileges on tables.

Object privileges

The setup tool grants EXECUTE or SELECT on the following objects:

Object	Privilege
DBMS_LOGMNR, DBMS_LOGMNR_D	EXECUTE
DBA_SEGMENTS	SELECT
The following V_$ views and their GV_$ equivalents: V_$TRANSACTION V_$DATABASE V_$CONTAINERS V_$PDBS V_$SESSION V_$INSTANCE V_$LOGFILE V_$LOG V_$ARCHIVED_LOG V_$LOGMNR_CONTENTS V_$PARAMETER	SELECT

Object	Privilege
DBMS_LOGMNR, DBMS_LOGMNR_D	EXECUTE
DBA_SEGMENTS	SELECT
The following V_$ views and their GV_$ equivalents: V_$TRANSACTION V_$DATABASE V_$CONTAINERS V_$PDBS V_$SESSION V_$INSTANCE V_$LOGFILE V_$LOG V_$ARCHIVED_LOG V_$LOGMNR_CONTENTS V_$PARAMETER	SELECT

Container access for CDB environments

For multi-tenant databases, the tool runs the following statement so that the user can read data in all containers:

SQL

ALTER USER <username> SET CONTAINER_DATA=ALL CONTAINER=CURRENT;

Next steps

Create an Oracle connection.