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 DATABASE 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;

   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;

3. If you intend to replicate Oracle partitioned tables or partitioned large objects (LOBs), grant select privileges to the user ID specified by pds_username on certain system tables.

   See the Replication Agent Primary Database Guide.