Required permissions

This section describes the permissions that are required for working with an Oracle source.

When replicating from Oracle for Amazon RDS, see Required Amazon RDS for Oracle permissions instead.

General permissions

To use an Oracle source in a Replicate task, the user specified in the Replicate Oracle endpoint connection settings must be granted the following privileges in the Oracle database:

If any of the required privileges cannot be granted to a V$xxx, then grant them to the V_$xxx.

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
If views are exposed: SELECT on ALL_VIEWS

Grant the following additional privilege (for each source table) when you are using a specific table list:

SELECT on <any-source-table>;

Grant the following additional privilege when using a pattern for the table list:

SELECT ANY TABLE;

Grant the following additional privilege (for each source table) when Replicate adds supplemental logging automatically (the default behavior) and you are using a specific table list. For information on how to turn off supplemental logging, see Setting advanced connection properties using Oracle LogMiner.

ALTER on <any-source-table>;

Grant the following additional privilege when Replicate adds supplemental logging automatically (the default behavior). For information on how to turn off supplemental logging, see Setting advanced connection properties using Oracle LogMiner.

ALTER ANY TABLE;
When accessing an Oracle standby database, the following privilege must be granted:

SELECT on V$STANDBY_LOG

When accessing nested tables, the following privileges must be granted:

SELECT on all_nested_tables

SELECT on all_nested_table_cols

Access privileges when using Oracle LogMiner to access the redo logs

If you are using Oracle LogMiner to access the Redo logs, grant the following privileges.

CREATE SESSION
EXECUTE on DBMS_LOGMNR
SELECT on V_$LOGMNR_LOGS
SELECT on V_$LOGMNR_CONTENTS
LOGMINING

Information note
This privilege is only required for Oracle 12c or later.

Access privileges when using Replicate Log Reader to access the redo logs

When using Replicate Log Reader to access the Redo logs, the following privileges should be granted to the user specified in the Oracle endpoint settings:

CREATE SESSION
SELECT on v_$transportable_platform

Grant the SELECT on v_$transportable_platform privilege if the Redo logs are stored in ASM and accessed by Replicate from ASM.
SELECT ON V_$DATABASE_INCARNATION
CREATE ANY DIRECTORY

Qlik Replicate uses following Oracle file access features:
BFILE read - Used when Replicate does not have file-level access to the Redo logs, and the Redo logs are not accessed from ASM.
DBMS_FILE_TRANSFER package - Used to copy the Redo log files to a temporary folder (in which case the EXECUTE ON DBMS_FILE_TRANSFER privilege needs to be granted as well)
DBMS_FILE_GROUP package - Used to delete the Redo log files from a temporary/alternate folder (in which case the EXECUTE ON DBMS_FILE_GROUP privilege needs to be granted as well).

Oracle file features work together with Oracle directories. Each Oracle directory object includes the name of the folder containing the files which need to be processed.

If you want Replicate to create and manage the Oracle directories, you need to grant the CREATE ANY DIRECTORY privilege specified above. Note that the directory names will be prefixed with attrep_. If you do not grant this privilege, you need to create the corresponding directories manually. The names of directories that are created manually must not be prefixed with ATTUREP_, ATTUTMP_, or ATTUGRP_. If you create the directories manually and the Oracle user specified in the Oracle Source endpoint is not the user that created the Oracle Directories, grant the READ on DIRECTORY privilege as well.

If the Oracle source endpoint is configured to copy the Redo log files to a temporary folder, and the Oracle user specified in the Oracle source endpoint is not the user that created the Oracle directories, the following additional privileges are required:

READ on the Oracle directory object specified as the source directory
WRITE on the directory object specified as the destination directory in the copy process

Required ASM privileges

The following section describes the additional permissions that are required when the redo logs are stored in ASM.

Grant the following read privilege:

SELECT ON v_$transportable_platform

From Oracle 11g Release 2 (11.2.0.2), Replicate must be granted the SYSASM privilege in order to access the ASM account. For older supported versions, granting Replicate the SYSDBA privilege should be sufficient.

When connecting to ASM, Replicate will first try to log in as SYSDBA and, if unsuccessful, will try to log in as SYSASM.

You can validate ASM account access by opening a command prompt and issuing the following statements:

sqlplus asmuser/asmpassword@+asmserver as sysdba

-OR-

sqlplus asmuser/asmpassword@+asmserver as sysasm

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here