How to setup minimum privileges for Oracle 12c on-prem or EC2 for DMS full load and CDC migration

Jephe Wu - http://linuxtechres.blogspot.com

1.  create user and tablespace first

create tablespace jephe

create user jephe identified by jephe default tablespace jephe;

grant connect,resource,create view to jephe;

revoke unlimited tablespace from jephe;

alter user jephe quota unlimited on jephe;

2. grant required privileges for user jephe

grant create session to jephe;
grant create view to jephe;
grant select any table to jephe;
grant select on all_views to jephe;
grant SELECT ANY TRANSACTION to jephe;
grant SELECT on V_$ARCHIVED_LOG to jephe;
grant SELECT on V_$LOG to jephe;
grant SELECT on V_$LOGFILE to jephe;
grant SELECT on V_$DATABASE to jephe;
grant SELECT on V_$THREAD to jephe;
grant SELECT on V_$PARAMETER to jephe;
grant SELECT on V_$NLS_PARAMETERS to jephe;
grant SELECT on V_$TIMEZONE_NAMES to jephe;
grant SELECT on V_$TRANSACTION to jephe;
grant SELECT on ALL_INDEXES to jephe;
grant SELECT on ALL_OBJECTS to jephe;
grant SELECT on ALL_TABLES to jephe;
grant SELECT on ALL_USERS to jephe;
grant SELECT on ALL_CATALOG to jephe;
grant SELECT on ALL_CONSTRAINTS to jephe;
grant SELECT on ALL_CONS_COLUMNS to jephe;
grant SELECT on ALL_TAB_COLS to jephe;
grant SELECT on ALL_IND_COLUMNS to jephe;
grant SELECT on ALL_LOG_GROUPS to jephe;
grant SELECT on SYS.DBA_REGISTRY to jephe;
grant SELECT on SYS.OBJ$ to jephe;
grant SELECT on DBA_TABLESPACES to jephe;
grant SELECT on ALL_TAB_PARTITIONS to jephe;
grant SELECT on ALL_ENCRYPTED_COLUMNS to jephe;

# the following is required for successful connection test
grant SELECT on V_$LOGMNR_LOGS to jephe;
grant SELECT on V_$LOGMNR_CONTENTS to jephe;

# the following is requried during CDC phase to add redo logs for logminer
grant execute on dbms_logmnr to jephe;
grant logmining to jephe;

# how to check the existing privileges

select * from dba_role_privs where grantee = 'JEPHE'; 'minimum is empty'
select * from dba_sys_privs where grantee = 'JEPHE';
select * from dba_tab_privs where grantee = 'JEPHE';