Replication Agent Permissions

Replication Agent for Oracle uses the pds_username to connect to Oracle and must have some Oracle permissions.

These permissions are required:

GRANT ALTER ANY PROCEDURE – required to manage procedures for replication.
GRANT ALTER DATABASE – required for Replication Agent to read from a Data Guard standby database transaction log.
GRANT ALTER ON TABLE_NAME – required to replicate user-defined datatypes if table-level supplemental logging has not been enabled for the specified TABLE_NAME.
GRANT ALTER SYSTEM – required to perform redo log archive operations.
GRANT CREATE ANY PROCEDURE – required to mark procedures for replication.
GRANT CREATE PROCEDURE – required to create rs_marker and rs_dump proc procedures.
GRANT CREATE PUBLIC SYNONYM – required to create synonyms for created tables in the primary database.
GRANT CREATE SEQUENCE – required to support replication.
GRANT CREATE SESSION – required to connect to Oracle.
GRANT CREATE TABLE – required to create tables in the primary database.
GRANT DROP PUBLIC SYNONYM – required to drop created synonyms.
GRANT EXECUTE_CATALOG_ROLE – required to use Oracle LogMiner.
GRANT EXECUTE ON DBMS_FLASHBACK – required to execute DBMS_FLASHBACK.get_system_change_number.
GRANT EXECUTE ON SYS.DBMS_LOCK – required to generate commit log records at the primary database.
GRANT SELECT ANY TRANSACTION – required to use Oracle LogMiner.
GRANT SELECT_CATALOG_ROLE – required to select from the DBA_* views.
GRANT SELECT ON SYS.OPQTYPE$ – required for DDL replication and XMLTYPE data replication.
GRANT SELECT ON SYS.RECYCLEBIN$ – required to use Oracle Flashback with Replication Agent.
GRANT SELECT ON SYS.ARGUMENT$ – required to process procedure DDL commands.
GRANT SELECT ON SYS.CCOL$ – required to support table replication (column constraint information).
GRANT SELECT ON SYS.CDEF$ – required to support table replication (constraint information).
GRANT SELECT ON SYS.COL$ – required to support table replication (column information).
GRANT SELECT ON SYS.COLLECTION$ – required to support table replication.
GRANT SELECT ON SYS.COLTYPE$ – required to support table replication.
GRANT SELECT ON SYS.CON$ – required to support table replication (constraint information).
GRANT SELECT ON SYS.DEFERRED_STG$ – required to suppress replication of compressed tables on Oracle 11g Release 2, on which LogMiner does not support compressed tables.
GRANT SELECT ON SYS.IND$ – required to identify indexes.
GRANT SELECT ON SYS.INDCOMPART$ – required to identify indexes.
GRANT SELECT ON SYS.INDPART$ – required to identify indexes.
GRANT SELECT ON SYS.INDSUBPART$ – required to identify indexes.
GRANT SELECT ON SYS.LOB$ – required for LOB replication support.
GRANT SELECT ON SYS.LOBCOMPPART$ – required to support partitioned LOB replication.
GRANT SELECT ON SYS.LOBFRAG$ – required to support partitioned LOB replication.
GRANT SELECT ON SYS.MLOG$ – required to filter materialized view log tables.
GRANT SELECT ON SYS.NTAB$ – required to support table replication.
GRANT SELECT ON SYS.OBJ$ – required for processing procedure DDL commands in the repository.
GRANT SELECT ON SYS.PROCEDUREINFO$ – required for procedure replication support.
GRANT SELECT ON SYS.SEG$ – required to suppress replication of compressed tables on versions of Oracle where LogMiner does not support compressed tables.
GRANT SELECT ON SYS.SEQ$ – required to support sequence replication.
GRANT SELECT ON SYS.SNAP$ – required to filter out materialized view tables.
GRANT SELECT ON SYS.TAB$ – required to support table replication.
GRANT SELECT ON SYS.TABCOMPART$ – required to support partitioned table replication.
GRANT SELECT ON SYS.TABPART$ – required to support partitioned table replication.
GRANT SELECT ON SYS.TABSUBPART$ – required to support partitioned table replication.
GRANT SELECT ON SYS.TS$ – required to identify tablespace encryption in Oracle 11g.
GRANT SELECT ON SYS.TYPE$ – required to process Oracle predefined and user-defined types.
GRANT SELECT ON SYS.USER$ – required for Oracle user identification.
GRANT SELECT ON SYS.ATTRIBUTE$ – required to process Oracle types.
GRANT SELECT ON V_$LOGMNR_CONTENTS – required to use Oracle LogMiner.
GRANT SELECT ON V_$LOGMNR_LOGS – required to use Oracle LogMiner.

If the Replication Agent is configured to remove old archive files, the user must have UPDATE authority to the directory and the archive log files.

Replication Agent for Oracle requires the ALTER SYSTEM privilege to issue the ALTER SYSTEM ARCHIVE LOG command. If Replication Agent is configured to access only online Oracle redo logs, Replication Agent issues the ALTER SYSTEM ARCHIVE LOG SEQUENCE command when the online redo log is no longer needed for replication (as when all data from the log has been replicated). Regardless of online or archive log processing, Replication Agent uses the ALTER SYSTEM privilege to issue the ALTER SYSTEM ARCHIVE LOG CURRENT command when Replication Agent is instructed to move processing to the end of the Oracle log. By issuing the ALTER SYSTEM ARCHIVE LOG CURRENT command, Replication Agent ensures that the current redo log file does not contain old data. Replication Agent moves processing to the end of the Oracle redo log when requested by the move_truncpt option of the ra_locator command. Replication Agent may also move processing to the end of the Oracle redo log during migration from one version of Replication Agent to another.

Oracle 10g and 11g Privileges for DDL Replication

Note: Issuing GRANT ALL PRIVILEGES TO DDLUSER turns the DDL user into a superuser, like the SYS or SYSTEM user.

Different versions of Oracle have different permission requirements. For Oracle 10g and 11g, grant the DDL user permission to execute these commands:

GRANT ALTER ANY INDEX
GRANT ALTER ANY INDEXTYPE
GRANT ALTER ANY PROCEDURE
GRANT ALTER ANY TABLE
GRANT ALTER ANY TRIGGER
GRANT ALTER ANY TYPE
GRANT ALTER SESSION
GRANT BECOME USER
GRANT CREATE ANY INDEX
GRANT CREATE ANY INDEXTYPE
GRANT CREATE ANY PROCEDURE
GRANT CREATE ANY SYNONYM
GRANT CREATE ANY TABLE
GRANT CREATE ANY TRIGGER
GRANT CREATE ANY TYPE
GRANT CREATE ANY VIEW
GRANT CREATE INDEXTYPE
GRANT CREATE MATERIALIZED VIEW
GRANT CREATE PROCEDURE
GRANT CREATE PUBLIC SYNONYM
GRANT CREATE SYNONYM
GRANT CREATE TABLE
GRANT CREATE TRIGGER
GRANT CREATE TYPE
GRANT CREATE VIEW
GRANT DELETE ANY TABLE
GRANT DROP ANY INDEX
GRANT DROP ANY INDEXTYPE
GRANT DROP ANY MATERIALIZED VIEW
GRANT DROP ANY PROCEDURE
GRANT DROP ANY SYNONYM
GRANT DROP ANY TABLE
GRANT DROP ANY TRIGGER
GRANT DROP ANY TYPE
GRANT DROP ANY VIEW
GRANT DROP PUBLIC SYNONYM
GRANT INSERT ANY TABLE
GRANT SELECT ANY TABLE
GRANT UPDATE ANY TABLE

Revoke user permission from the DDL user to execute:

GRANT ALTER DATABASE
GRANT ALTER ROLLBACK SEGMENT
GRANT ALTER SYSTEM
GRANT ALTER TABLESPACE
GRANT ANALYZE ANY
GRANT AUDIT ANY
GRANT AUDIT SYSTEM
GRANT CREATE DATABASE LINK
GRANT CREATE ROLLBACK SEGMENT
GRANT CREATE TABLESPACE
GRANT DROP PUBLIC DATABASE LINK
GRANT DROP ROLLBACK SEGMENT
GRANT DROP TABLESPACE
GRANT LOCK ANY TABLE