GRANTS in source Oracle DB user for AWS RDS DB migration using DMS

 

Using an Oracle database as a source for AWS DMS

 

All the below SQL-statements (Create, Grant etc.) need to execute in Staging database.

User account privileges required on a self-managed Oracle source for AWS DMS

#Create user

create user DMS_ORACLE_STG identified by oracle default tablespace users quota unlimited on users;

#All required grants

GRANT CREATE SESSION TO DMS_ORACLE_STG;

GRANT SELECT ANY TRANSACTION TO DMS_ORACLE_STG;

GRANT SELECT ON V_$ARCHIVED_LOG TO DMS_ORACLE_STG;

GRANT SELECT ON V_$LOG TO DMS_ORACLE_STG;

GRANT SELECT ON V_$LOGFILE TO DMS_ORACLE_STG;

GRANT SELECT ON V_$LOGMNR_LOGS TO DMS_ORACLE_STG;

GRANT SELECT ON V_$LOGMNR_CONTENTS TO DMS_ORACLE_STG;

GRANT SELECT ON V_$DATABASE TO DMS_ORACLE_STG;

GRANT SELECT ON V_$THREAD TO DMS_ORACLE_STG;

GRANT SELECT ON V_$PARAMETER TO DMS_ORACLE_STG;

GRANT SELECT ON V_$NLS_PARAMETERS TO DMS_ORACLE_STG;

GRANT SELECT ON V_$TIMEZONE_NAMES TO DMS_ORACLE_STG;

GRANT SELECT ON V_$TRANSACTION TO DMS_ORACLE_STG;

GRANT SELECT ON V_$CONTAINERS TO DMS_ORACLE_STG;                  

GRANT SELECT ON ALL_INDEXES TO DMS_ORACLE_STG;

GRANT SELECT ON ALL_OBJECTS TO DMS_ORACLE_STG;

GRANT SELECT ON ALL_TABLES TO DMS_ORACLE_STG;

GRANT SELECT ON ALL_USERS TO DMS_ORACLE_STG;

GRANT SELECT ON ALL_CATALOG TO DMS_ORACLE_STG;

GRANT SELECT ON ALL_CONSTRAINTS TO DMS_ORACLE_STG;

GRANT SELECT ON ALL_CONS_COLUMNS TO DMS_ORACLE_STG;

GRANT SELECT ON ALL_TAB_COLS TO DMS_ORACLE_STG;

GRANT SELECT ON ALL_IND_COLUMNS TO DMS_ORACLE_STG;

GRANT SELECT ON ALL_ENCRYPTED_COLUMNS TO DMS_ORACLE_STG;

GRANT SELECT ON ALL_LOG_GROUPS TO DMS_ORACLE_STG;

GRANT SELECT ON ALL_TAB_PARTITIONS TO DMS_ORACLE_STG;

GRANT SELECT ON SYS.DBA_REGISTRY TO DMS_ORACLE_STG;

GRANT SELECT ON SYS.OBJ$ TO DMS_ORACLE_STG;

GRANT SELECT ON DBA_TABLESPACES TO DMS_ORACLE_STG;

GRANT SELECT ON DBA_OBJECTS TO DMS_ORACLE_STG;

GRANT SELECT ON SYS.ENC$ TO DMS_ORACLE_STG;

GRANT SELECT ON GV_$TRANSACTION TO DMS_ORACLE_STG;

GRANT SELECT ON V_$DATAGUARD_STATS TO DMS_ORACLE_STG;

GRANT SELECT ON DBA_TYPES TO DMS_ORACLE_STG;

GRANT EXECUTE ON SYS.DBMS_CRYPTO TO DMS_ORACLE_STG;                              

GRANT SELECT ON SYS.DBA_DIRECTORIES TO DMS_ORACLE_STG;               

GRANT SELECT on ALL_VIEWS to DMS_ORACLE_STG;

 

#Enable SUPPLEMENTAL log

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 

#Account privileges required when using AWS DMS Log Miner to access the redo logs,

GRANT EXECUTE on DBMS_LOGMNR to DMS_ORACLE_STG;

GRANT SELECT on V_$LOGMNR_LOGS to DMS_ORACLE_STG;

GRANT SELECT on V_$LOGMNR_CONTENTS to DMS_ORACLE_STG;

GRANT LOGMINING to DMS_ORACLE_STG;

#Account privileges required when using AWS DMS Binary Reader to access the redo logs

GRANT SELECT on v_$transportable_platform to DMS_ORACLE_STG; 

GRANT CREATE ANY DIRECTORY to DMS_ORACLE_STG;                

GRANT EXECUTE on DBMS_FILE_TRANSFER to DMS_ORACLE_STG;      

GRANT EXECUTE on DBMS_FILE_GROUP to DMS_ORACLE_STG;

#Additional account privileges required when using Binary Reader with Oracle ASM

Grant SELECT ON v_$transportable_platform to DMS_ORACLE_STG;

Grant SYSDBA to DMS_ORACLE_STG;

 

On ASM instce

SQL> create user DMS_ORACLE_ STG identified by oracle default tablespace users quota unlimited on users;

 

User created.

 

SQL>  Grant SYSASM to DMS_ORACLE_ STG;

 

 

Additional privs for all Tables in Schema :-

 

#To grant select table for DMS replication.

GRANT SELECT ON  schema.tabe_name  TO DMS_ORACLE_STG;

 

#To grant alter table for DMS replication.

GRANT ALTER ON  schema.tabe_name  TO DMS_ORACLE_STG;

 

 

#To grant ADD SUPPLEMENTAL table for DMS replication.

 

ALTER TABLE schema.tabe_name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;