What are the permissions required for AWS DMS when using Oracle as the source endpoint?

Short description

AWS DMS requires permissions to read data from the source database so that it can migrate the data to the target. Depending on your AWS DMS task settings and the type of migration that you're performing, the required permissions vary. See the following minimum required permissions for:

• Oracle source databases that are AWS managed; that is, Amazon Relational Database Service (Amazon RDS).
• Oracle source databases that are self-managed
• Oracle source databases that have ongoing replication tasks

Resolution

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

For Oracle databases that are managed by Amazon, for example an Amazon RDS DB instance that runs Oracle, see Working with an Amazon managed Oracle database as a source for AWS DMS.

Oracle source databases that are self-managed

For Oracle databases that are self-managed, the following permissions are required:

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

To expose views, the following permissions are required:

GRANT SELECT on ALL_VIEWS to <dms_user>;

Note: To expose views, you must also add the following extra connection attribute to the source endpoint: exposeViews=true.

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

GRANT SELECT ANY TABLE to <dms_user>;

To specify a table list in your replication task, the following permissions are required:

GRANT SELECT on <schema>.<table > ;

Note: Add these permissions for all tables in the table list. To migrate from Oracle RAC, you must grant SELECT permissions on materialized views that have the prefixes gv_$ and v_$.

To enable AWS DMS validation for large objects (LOBs), the following permissions are required:

GRANT execute on sys.dbms_crypto to <dms_user>;

If you use the addSupplementalLogging extra connection attribute, the following permissions are required:

GRANT ALTER ANY TABLE to <dms_user>

Oracle source databases that have ongoing replication tasks

Oracle offers two methods for reading the redo logs during ongoing replication, Oracle LogMiner and Oracle Binary Reader. Depending on whether you use LogMiner or Binary Reader, you must grant the source user additional permissions after you grant the required permissions mentioned previously.

To use LogMiner, the following permissions are required:

GRANT EXECUTE ON dbms_logmnr TO <dms_user>;

For Oracle 12c only, the following permissions are required:

GRANT LOGMINING TO <dms_user>;

To use Binary Reader, the following permissions are required:

GRANT SELECT ON v_$transportable_platform  to <dms_user>;

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

GRANT CREATE ANY DIRECTORY to <dms_user>;

Or, if you don't want to allow AWS DMS to create Oracle directories on your source Oracle database, you can precreate the Oracle directories on the redo and archive log paths. Then, 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, confirm that the directories are created by querying the ALL_DIRECTORIES table.

If the source database uses Oracle ASM, then the following permissions are required:

GRANT SYSASM  to <ASM_user>;

Note: These permissions are required because reading the redo and archive logs from Oracle ASM uses a function from the Oracle DBMS_DISKGROUP package that requires the SYSASM or SYSDBA privileges.

If you use the copyToTempFolder extra connection attribute, then you must create a directory and grant the following permissions:

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

For AWS DMS versions 3.1 or later, it's a best practice to add the parallelASMReadThreads extra connection attribute. For more information, see Using Oracle LogMiner or Oracle Binary Reader for Change Data Capture (CDC).

Note: AWS DMS supports Oracle Active Data Guard Standby as a source. In Active Data Guard mode, standby databases can be open in read-only mode, so you must grant the previously mentioned permissions on the primary instance. After the permissions are granted on the primary instance, perform a few log switches so that the permissions are replicated to the standby database. For more information, see the Oracle documentation for Forcing log switches.

Related information

Using an Oracle database as a source for AWS DMS