Minimal rights for Oracle Source replication

[Shai_E]

This can serve as a more curated list of minimal permissions required for oracle source, to work with Replicate.

SELECT ANY TRANSACTION
SELECT on V_$ARCHIVED_LOG
SELECT on V_$LOG
SELECT on V_$LOGFILE
SELECT on V_$DATABASE
SELECT on V_$THREAD
SELECT on V_$PARAMETER
SELECT on V_$NLS_PARAMETERS
SELECT on V_$TIMEZONE_NAMES
SELECT on GV_$TRANSACTION
SELECT on V_$CONTAINERS
SELECT on ALL_INDEXES
SELECT on ALL_OBJECTS
SELECT on DBA_OBJECTS - Required if the Oracle version is earlier than 11.2.0.3.
SELECT on ALL_TABLES
SELECT on ALL_USERS
SELECT on ALL_CATALOG
SELECT on ALL_CONSTRAINTS
SELECT on ALL_CONS_COLUMNS
SELECT on ALL_TAB_COLS
SELECT on ALL_IND_COLUMNS
SELECT on ALL_LOG_GROUPS
SELECT on SYS.DBA_REGISTRY
SELECT on SYS.OBJ$
SELECT on SYS.ENC$
SELECT on DBA_TABLESPACES
SELECT on ALL_TAB_PARTITIONS
SELECT on ALL_ENCRYPTED_COLUMNS
SELECT on ALL_VIEWS - If views are exposed

if source database is not a multitenant database v$containers grant is not required as this grant is meant for a multitenant database

The remaining permissions depend on your settings.

for example:

SELECT ANY TABLE; ---> if you are not willing to grant that permission, you can choose to grant select on <replicate table> alone

When accessing an Oracle standby database, the following privilege must be granted:
SELECT on V$STANDBY_LOG

we recommend to use log reader method and there few additional grants needed for this depends on where your redo logs exist ASM vs non -the ASM decides which permissions are required

If redo logs are in regular filesystem we need CREATE ANY DIRECTORY privilege.
if you are not willing to grant create any directory then you will have to pre-create the directories as per the instructions on the user guide.

[Sreekanth_333]

Hello, 

One of my customer has concerns about granting the CREATE ANY DIRECTORY privilege. Since we are using LogReader to read the logs and have already provided READ access to ONLINELOG_DIR and ARCHIVELOG_DIR, will this be sufficient, or is the CREATE ANY DIRECTORY privilege specifically required?

[Dana_Baldwin]

Hi @Sreekanth_333 

Please refer to this knowledge article, it should clarify the work around to this privilege. The only potential caveat is if you are storing the redo/archived logs FRA and the directories are being recreated regularly:

Why does Replicate perform 'create directory' and ... - Qlik Community - 1881066

Thanks,

Dana