Oracle database user requirements

Important

This feature is in Beta.

This page lists the privileges that the Oracle replication user requires for ingesting into Azure 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.
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
RDSADMIN.RDS_CONFIGURATION	SELECT (required for RDS)
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

Dependency on SELECT ANY DICTIONARY

The setup tool grants SELECT ANY DICTIONARY, which gives the replication user read access to all data dictionary and dynamic performance (V$/GV$) views. At runtime, the connector reads several views that the tool does not grant SELECT on individually, and which are covered only by SELECT ANY DICTIONARY, including:

• GV$DATABASE_INCARNATION
• V$ENCRYPTION_WALLET
• V$RMAN_CONFIGURATION
• V$VERSION
• V$LOGMNR_LOGS
• CDB_* views for tables, columns, their constraints.

If your security policy prevents granting SELECT ANY DICTIONARY, you must grant SELECT on these views (and the views in the object privileges table) explicitly. Otherwise, keep SELECT ANY DICTIONARY granted so the connector can read every view it depends on.

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:

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

Next steps

Create an Oracle connection.