Replication Agent Permissions

Replication Agent for Oracle uses the pds_username to connect to Oracle and must have certain 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