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

Resolution

The required permissions vary for different types of DMS task settings and different types of migration.

Oracle source databases that AWS manages

For information about permissions for Oracle databases that AWS manages, see User account privileges required on a self-managed Oracle source for AWS DMS.

Self-managed Oracle source databases

For self-managed Oracle databases, you must grant the following permissions to the DMS user:

GRANT CREATE SESSION TO dms_user;
GRANT SELECT ANY TRANSACTION TO dms_user;
GRANT SELECT ON V_$ARCHIVED_LOG TO dms_user;
GRANT SELECT ON V_$LOG TO dms_user;
GRANT SELECT ON V_$LOGFILE 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_$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 V_$TRANSACTION TO dms_user;
GRANT SELECT ON V_$CONTAINERS 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_ENCRYPTED_COLUMNS TO dms_user;
GRANT SELECT ON ALL_LOG_GROUPS TO dms_user;
GRANT SELECT ON ALL_TAB_PARTITIONS 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 DBA_OBJECTS TO dms_user;
GRANT SELECT ON SYS.ENC$ TO dms_user;
GRANT SELECT ON GV_$TRANSACTION TO dms_user;
GRANT SELECT ON V_$DATAGUARD_STATS TO dms_user;
GRANT SELECT ON V_$DATABASE_INCARNATION TO dms_user;

Note:

• Grant SELECT ON DBA_OBJECTS only when the Oracle version is earlier than 11.2.0.3.
• If you turned on transparent data encryption (TDE), then grant SELECT ON SYS.ENC$.
• Grant SELECT ON GV_$TRANSACTION only when the source database is Oracle RAC in AWS DMS version 3.4.6 or higher./li>
• Grant SELECT ON V_$DTATGUARD_STATS only when you use an Oracle Active Data Guard standby database as the source database in the latest release of DMS version 3.4.6, 3.4.7, or higher.

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

To show views, grant the following permission:

GRANT SELECT on ALL_VIEWS to dms_user;

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

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. For either task, add the following permission:

GRANT SELECT ANY TABLE to dms_user;

To specify a table list in your replication task, grant the following permission:

GRANT SELECT on schema.table;

To migrate from Oracle Real Application Clusters (RACs), you must grant SELECT permission on materialized views with the gv_$ and v_$ prefixes.

To turn on DMS validation for large binary objects (LOBs), grant the following permission:

GRANT execute on sys.dbms_crypto to dms_user;

If you use the addSupplementalLogging connection attribute, then grant the following permission:

GRANT ALTER ANY TABLE to dms_user

Oracle source databases with ongoing replication tasks

To read the redo logs during replication, Oracle offers Oracle LogMiner and Oracle Binary Reader. 

To use LogMiner, grant the following permission:

GRANT EXECUTE ON dbms_logmnr TO dms_user;

For Oracle Database 12c and later, grant the following permission:

GRANT LOGMINING TO dms_user;

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

GRANT CREATE ANY DIRECTORY to dms_user;

To use DMS to create the Oracle directories, you must grant the CREATE ANY DIRECTORY permission. DMS creates the directory names with the DMS_ prefix. If you don't grant the CREATE ANY DIRECTORY permission, 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 this case, you must grant the following READ on DIRECTORY permissions:

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

To confirm that you created the directories, query the ALL_DIRECTORIES table.

To use Binary Reader to access the redo logs in Automatic Storage Management (ASM), grant one of the following permissions to the Oracle endpoint user.

If you store the redo logs in ASM and AWS DMS accesses logs from ASM, then grant the following permission:

GRANT SELECT ON v_$transportable_platform  to dms_user;

To access the ASM account with Oracle 11g Release 2 version 11.2.0.2 or higher, grant the SYSASM permission:

GRANT SYSASM to ASM_USER;

Note: For supported earlier versions of Oracle, you can grant the SYSDBA permission.

When the Oracle source uses ASM, you can use high-performance options in Binary Reader to process transactions at scale. The options include extra connection attributes to specify the number of parallel threads (parallelASMReadThreads) and the number of read-ahead buffers (readAheadBlocks). Set the attributes together to improve the performance of the CDC task.

To assume the ASM user, modify your Oracle source endpoint to include the DMS user and ASM user, and separate them with commas.

Example source endpoint format:

Username: oracle_user,asm_user
Password: oracle_user_password,asm_user_password

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