Let us discuss the Permissions Required for AWS DMS with Oracle.

AWS DMS requires permissions to read data from the source database to migrate the data to the target.

Here, at Bobcares, we assist our customers with several AWS queries as part of our AWS Support Services.

Today, let us see the minimum permissions AWS DMS requires.

Permissions Required for AWS DMS with Oracle

Depending on the AWS DMS task settings and the type of migration, the permissions vary.

Moving ahead, let us see few permissions our Support Techs find important.

Oracle source databases that are AWS managed; that is, Amazon RDS

We need to grant the following privileges to the Oracle user account in the Oracle source endpoint definition:

GRANT CREATE SESSION to db_user;
GRANT SELECT ANY TRANSACTION to db_user;
GRANT SELECT on DBA_TABLESPACES to db_user;
GRANT LOGMINING to db_user; (for Oracle 12c only)
GRANT SELECT ON any-replicated-table to db_user;

Oracle source databases that are self-managed

Similarly, if we have self-managed Oracle databases, it requires the following permissions:

GRANT SELECT on V_$DATABASE to <dms_user>;
GRANT SELECT on V_$THREAD to <dms_user>;
GRANT SELECT on V_$PARAMETER to <dms_user>;
GRANT SELECT on V_$NLS_PARAMETERS to <dms_user>;
GRANT SELECT on V_$TIMEZONE_NAMES to <dms_user>;
GRANT SELECT on ALL_INDEXES to <dms_user>;
GRANT SELECT on ALL_OBJECTS to <dms_user>;
GRANT SELECT on ALL_TABLES to <dms_user>;
GRANT SELECT on ALL_USERS to <dms_user>;
GRANT SELECT on ALL_CATALOG to <dms_user>;
GRANT SELECT on ALL_CONSTRAINTS to <dms_user>;
GRANT SELECT on ALL_CONS_COLUMNS to <dms_user>;
GRANT SELECT on ALL_TAB_COLS to <dms_user>;
GRANT SELECT on ALL_IND_COLUMNS to <dms_user>;
GRANT SELECT on ALL_LOG_GROUPS to <dms_user>;
GRANT SELECT on SYS.DBA_REGISTRY to <dms_user>;
GRANT SELECT on SYS.OBJ$ to <dms_user>;
GRANT SELECT on DBA_TABLESPACES to <dms_user>;
GRANT SELECT on ALL_TAB_PARTITIONS to <dms_user>;
GRANT SELECT on ALL_ENCRYPTED_COLUMNS to <dms_user>;
GRANT SELECT ANY TRANSACTION to <dms_user>;
GRANT SELECT on V_$LOGMNR_LOGS to <dms_user>;
GRANT SELECT on V_$LOGMNR_CONTENTS to <dms_user>;
GRANT SELECT on V_$LOG to <dms_user>;
GRANT SELECT on V_$ARCHIVED_LOG to <dms_user>;
GRANT SELECT on V_$LOGFILE to <dms_user>;
GRANT SELECT on V_$TRANSACTION to <dms_user>;

Then to expose views, it requires:

GRANT SELECT on ALL_VIEWS to <dms_user>;

To use a pattern that matches the table names in the replication task or to allow the source database user to read tables from a different schema:

GRANT SELECT ANY TABLE to <dms_user>;

After that, to specify a table list in the replication task:

GRANT SELECT on <schema>.<table > ;

To enable AWS DMS validation for large objects:

GRANT execute on sys.dbms_crypto to <dms_user>;

We need permission if we use the addSupplementalLogging extra connection attribute:

GRANT ALTER ANY TABLE to <dms_user>

Oracle source databases that have ongoing replication tasks

Depending on whether we use LogMiner or Binary Reader, we must grant the source user additional permissions.

To use LogMiner:

GRANT EXECUTE ON dbms_logmnr TO <dms_user>;

For Oracle 12c only:

GRANT LOGMINING TO <dms_user>;

To use Binary Reader:

GRANT SELECT ON v_$transportable_platform to <dms_user>;

Since Binary Reader reads redo and archive logs by using Oracle directories, we must either grant AWS DMS access to create the Oracle directories on the source database:

GRANT CREATE ANY DIRECTORY to <dms_user>;

Or, if we don’t want to allow access, we can precreate the Oracle directories on the redo and archive log paths.

Then, we grant READ access to the AWS DMS user on those directories:

GRANT READ ON DIRECTORY <RedoLog_directory_name> to <dms_user>;
GRANT READ ON DIRECTORY <ArchiveLog_directory_name> to <dms_user>;

Finally, we confirm that the directories were created by querying the ALL_DIRECTORIES table.

If the source database uses Oracle ASM:

GRANT SYSASM to <ASM_user>;

We must create a directory and grant the following permissions if we use the copyToTempFolder extra connection attribute:

Create directory <bfile_dir> as <copyToTempFolder_PATH>;
GRANT READ, WRITE ON DIRECTORY <bfile_dir> TO <dms_user>;

[Confused with the permissions? Feel free to contact us]

Conclusion

In short, we saw how our Support Techs go about permission for AWS DMS with Oracle.