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

Short description

AWS DMS requires permissions to read data from the source database to migrate the data to the target. Depending on your AWS DMS task settings and the type of migration that you perform, the required permissions can vary.

Resolution

Oracle source databases that AWS manages

For Oracle databases that Amazon manages, see Working with an AWS managed Oracle database as a source for AWS DMS. This includes Oracle databases such as Amazon Relational Database Service (Amazon RDS).

Self-managed Oracle source databases

For Oracle databases that are self-managed, these 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 show views, these permissions are required:

GRANT SELECT on ALL\_VIEWS to <dms\_user>;

Note: To expose views, you must also add these extra connection attributes to the source endpoint:

exposeViews=true

You can use a pattern that matches the table names in your replication task. You can also allow the source database user to read tables from a different schema. To do either task, add these permissions:

GRANT SELECT ANY TABLE to <dms\_user>;

To specify a table list in your replication task, these 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 turn on AWS DMS validation for large objects (LOBs), these permissions are required:

GRANT execute on sys.dbms_crypto to <dms_user>;

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

GRANT ALTER ANY TABLE to <dms\_user>

Oracle source databases with ongoing replication tasks

Oracle offers two methods to read the redo logs during ongoing replication: Oracle LogMiner and Oracle Binary Reader. Depending on the method that you use, you must grant the source user additional permissions after you grant the required permissions mentioned earlier.

To use LogMiner, these permissions are required:

GRANT EXECUTE ON dbms\_logmnr TO <dms\_user>;

For Oracle 12c and later, these permissions are required:

GRANT LOGMINING TO <dms\_user>;

Binary Reader uses Oracle directories to read redo and archive logs. You must grant AWS DMS access to create the Oracle directories on the source database:

GRANT CREATE ANY DIRECTORY to <dms\_user>;

To use AWS DMS to create the Oracle directories, you must grant the CREATE ANY DIRECTORY privilege. AWS DMS creates the directory names with the DMS_ prefix. If you don't grant the CREATE ANY DIRECTORY privilege, then you must manually create the directories. When you manually create the Oracle directories, the Oracle user that you specify on the endpoint isn't always the user that created the directories. In these cases, you must grant the READ on DIRECTORY privilege.

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

To confirm that the directories are created, query the ALL_DIRECTORIES table. To use Binary Reader to access the redo logs in Automatic Storage Management (ASM), grant these permissions to the Oracle user on the endpoint:

GRANT SELECT ON v_$transportable_platform  to <dms_user>; - Grant this privilege if the redo logs are stored in Oracle Automatic Storage Management (ASM) and AWS DMS accesses them from ASM.
GRANT SYSASM to <ASM_USER>; - To access the ASM account with Oracle 11g Release 2 (version 11.2.0.2) and higher, grant the Oracle endpoint user the SYSASM privilege. For older supported Oracle versions, it's typically sufficient to grant the Oracle endpoint user the SYSDBA privilege.

Where the Oracle source uses ASM, you can work with high-performance options in Binary Reader for transaction processing at scale. These options include extra connection attributes to specify the number of parallel threads (parallelASMReadThreads) and the number of read-ahead buffers (readAheadBlocks). Setting these attributes together can significantly improve the performance of the CDC task. The following settings provide good results for most ASM configurations.

Note: AWS DMS supports Oracle Active Data Guard Standby as a source. In Active Data Guard mode, you can open standby databases in read-only mode. After you grant the permissions mentioned previously on the primary instance, perform several log switches to replicate the permissions to the standby database. For more information, see Forcing log switches on the Oracle website.

Related information

Using an Oracle database as a source for AWS DMS

Using a self-managed Oracle Standby as a source with Binary Reader for CDC in AWS DMS