Creating Oracle User and Grant Permissions

Create an Oracle database user for use by Replication Agent when connected to the primary database.

1. Use SQLPLUS to connect to the primary database as a system administrator and run these commands to create an Oracle user named “RA_USER,” specified by pds_username, with the password “sybase,” and grant permissions to the user:

   CREATE USER RA_USER PROFILE "DEFAULT" IDENTIFIED
   BY "sybase" DEFAULT TABLESPACE "USERS" ACCOUNT
   UNLOCK;
   GRANT ALTER ANY PROCEDURE TO RA_USER; 
   GRANT ALTER ANY TABLE TO RA_USER;
   
   GRANT ALTER SESSION TO RA_USER;
   GRANT ALTER SYSTEM TO RA_USER;
   GRANT CONNECT TO RA_USER;
   GRANT CREATE PROCEDURE TO RA_USER;
   GRANT CREATE PUBLIC SYNONYM TO RA_USER;
   GRANT CREATE SESSION TO RA_USER;
   GRANT CREATE TABLE TO RA_USER;
   GRANT DROP ANY PROCEDURE TO RA_USER;
   GRANT DROP ANY TABLE TO RA_USER;
   GRANT DROP PUBLIC SYNONYM TO RA_USER;
   GRANT EXECUTE_CATALOG_ROLE TO RA_USER;
   GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO RA_USER;
   GRANT EXECUTE ON SYS.DBMS_LOCK TO RA_USER;
   GRANT RESOURCE TO RA_USER;
   GRANT SELECT ANY TRANSACTION TO RA_USER;
   GRANT SELECT_CATALOG_ROLE TO RA_USER;
   GRANT SELECT ON SYS.ARGUMENT$ TO RA_USER;
   GRANT SELECT ON SYS.ATTRIBUTE$ TO RA_USER;
   GRANT SELECT ON SYS.CCOL$ TO RA_USER;
   GRANT SELECT ON SYS.CDEF$ TO RA_USER;
   GRANT SELECT ON SYS.COL$ TO RA_USER;
   GRANT SELECT ON SYS.COLLECTION$ TO RA_USER;
   GRANT SELECT ON SYS.COLTYPE$ TO RA_USER;
   GRANT SELECT ON SYS.CON$ TO RA_USER;
   GRANT SELECT ON SYS.DEFERRED_STG$ TO RA_USER;
   GRANT SELECT on SYS.ICOL$ TO RA_USER;
   GRANT SELECT ON SYS.IND$ TO RA_USER;
   GRANT SELECT ON SYS.INDCOMPART$ TO RA_USER;
   GRANT SELECT ON SYS.INDPART$ TO RA_USER;
   GRANT SELECT ON SYS.INDSUBPART$ TO RA_USER;
   GRANT SELECT ON SYS.LOB$ TO RA_USER;
   GRANT SELECT ON SYS.LOBCOMPPART$ TO RA_USER;
   GRANT SELECT ON SYS.LOBFRAG$ TO RA_USER;
   GRANT SELECT ON SYS.MLOG$ TO RA_USER;
   GRANT SELECT ON SYS.NTAB$ TO RA_USER;
   GRANT SELECT ON SYS.OBJ$ TO RA_USER;
   GRANT SELECT ON SYS.OPQTYPE$ TO RA_USER;
   GRANT SELECT ON SYS.PARTOBJ$ TO RA_USER;
   GRANT SELECT ON SYS.PROCEDUREINFO$ TO RA_USER;
   GRANT SELECT ON SYS.RECYCLEBIN$ TO RA_USER;
   GRANT SELECT ON SYS.SEG$ TO RA_USER;
   GRANT SELECT ON SYS.SEQ$ TO RA_USER;
   GRANT SELECT ON SYS.SNAP$ TO RA_USER;
   GRANT SELECT ON SYS.TAB$ TO RA_USER;
   GRANT SELECT ON SYS.TABCOMPART$ TO RA_USER;
   GRANT SELECT ON SYS.TABPART$ TO RA_USER;
   GRANT SELECT ON SYS.TABSUBPART$ TO RA_USER;
   GRANT SELECT ON SYS.TS$ TO RA_USER;
   GRANT SELECT ON SYS.TYPE$ TO RA_USER;
   GRANT SELECT ON SYS.USER$ TO RA_USER;
   GRANT SELECT ON V_$LOGMNR_CONTENTS TO RA_USER;
   GRANT SELECT ON V_$LOGMNR_LOGS TO RA_USER;

   For Oracle 12c:

   GRANT LOGMINING TO RA_USER

   The Replication Agent for Oracle Log Reader that uses XStream APIs to read from Oracle 11g and 12c transaction logs requires DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE.

   To grant the XStream administrator privilege to the pds_username, issue:

   BEGIN 
   DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE( grantee => 'pds_username'); 
   END;
   /
   

   See the Replication Agent Primary Database Guide.
2. If you configure the ra_admin_owner user, make sure that these permissions are also granted:

   GRANT CREATE SESSION TO RA_USER;
   GRANT CREATE TABLE TO RA_USER;
   GRANT CREATE SEQUENCE TO RA_USER;
   GRANT CREATE ANY PROCEDURE TO RA_USER;
   GRANT SELECT_CATALOG_ROLE TO RA_USER;