ApsaraDB for OceanBase:Minimum privileges required when an Oracle database serves as the source

Data source creation

GRANT ALTER SESSION to oms_user;
GRANT CREATE SESSION to oms_user;
GRANT CONNECT to oms_user;

GRANT SELECT on DUAL to oms_user;
GRANT SELECT on NLS_DATABASE_PARAMETERS to oms_user;
GRANT SELECT on PRODUCT_COMPONENT_VERSION to oms_user;
GRANT SELECT on V_$DATABASE to oms_user;
GRANT SELECT ANY DICTIONARY to oms_user;

Schema migration

GRANT ALTER SESSION to oms_user;
GRANT CREATE SESSION to oms_user;
GRANT CONNECT to oms_user;

-- The SELECT privilege on all tables to be migrated.
GRANT SELECT on any-replicated-table to oms_user;

Full migration

GRANT ALTER SESSION to oms_user;
GRANT CREATE SESSION to oms_user;
GRANT CONNECT to oms_user;

GRANT SELECT on DBA_CONSTRAINTS to oms_user;
GRANT SELECT on DBA_CONS_COLUMNS to oms_user;
GRANT SELECT on DBA_EXTENTS to oms_user;
GRANT SELECT on DBA_INDEXES to oms_user;
GRANT SELECT on DBA_IND_COLUMNS to oms_user;
GRANT SELECT on DBA_MVIEWS to oms_user;
GRANT SELECT on DBA_MVIEW_LOGS to oms_user;
GRANT SELECT on DBA_OBJECTS to oms_user;
GRANT SELECT on DBA_PART_KEY_COLUMNS to oms_user;
GRANT SELECT on DBA_SUBPART_KEY_COLUMNS to oms_user;
GRANT SELECT on DBA_TABLES to oms_user;
GRANT SELECT on DBA_TAB_COLS to oms_user;
GRANT SELECT on DBA_TAB_COLUMNS to oms_user;
GRANT SELECT on DBA_TAB_PARTITIONS to oms_user;
GRANT SELECT on DBA_TAB_SUBPARTITIONS to oms_user;
GRANT SELECT on DBA_USERS to oms_user;
GRANT SELECT on NLS_DATABASE_PARAMETERS to oms_user;
GRANT SELECT on SYS.SMON_SCN_TIME to oms_user;
GRANT SELECT on V_$INSTANCE to oms_user;
GRANT SELECT on V_$NLS_PARAMETERS to oms_user;

-- The SELECT privilege on all tables to be migrated.
GRANT SELECT on any-replicated-table to oms_user;

Incremental synchronization

GRANT ALTER SESSION to oms_user;
GRANT CREATE SESSION to oms_user;
GRANT CONNECT to oms_user;

GRANT SELECT on ALL_CONSTRAINTS to oms_user;
GRANT SELECT on ALL_INDEXES to oms_user;
GRANT SELECT on ALL_IND_COLUMNS to oms_user;
-- The privileges to query object attributes.
GRANT SELECT on ALL_OBJECTS to oms_user; 
GRANT SELECT on ALL_TAB_COLS to oms_user;
-- The privileges to query users on the allowlist and blocklist.
GRANT SELECT on ALL_USERS to oms_user; 
GRANT SELECT on DBA_CONSTRAINTS to oms_user;
GRANT SELECT on DBA_CONS_COLUMNS to oms_user;
GRANT SELECT on DBA_MVIEWS to oms_user;
-- The privilege to exclude the materialized views when you query all objects.
GRANT SELECT on DBA_MVIEW_LOGS to oms_user; 
-- The privilege to query the current time and time zone of the database.
GRANT SELECT on DUAL to oms_user; 
-- The privilege to query the number of nodes in RAC.
GRANT SELECT on GV_$INSTANCE to oms_user; 
-- The privileges to query the character set used by the database.
GRANT SELECT on NLS_DATABASE_PARAMETERS to oms_user; 
GRANT SELECT on SYS.CDEF$ to oms_user;
GRANT SELECT on SYS.COL$ to oms_user;
GRANT SELECT on SYS.CON$ to oms_user;
GRANT SELECT on SYS.ICOL$ to oms_user;
GRANT SELECT on SYS.OBJ$ to oms_user;
-- The privilege to query the names of users in the blocklist under the SYS tenant to filter out tables of these users.
GRANT SELECT on SYS.USER$ to oms_user; 
-- The privileges to query archived files.
GRANT SELECT on V_$ARCHIVED_LOG to oms_user; 
GRANT SELECT on V_$ARCHIVE_DEST to oms_user;
-- The privileges to query whether supplemental logging is enabled and the access mode of Oracle.
GRANT SELECT on V_$DATABASE to oms_user; 
GRANT SELECT on V_$LOG to oms_user;
-- The privilege to query online logs.
GRANT SELECT on V_$LOGFILE to oms_user; 
-- The privileges to query the result set returned by LogMiner.
GRANT SELECT on V_$LOGMNR_CONTENTS to oms_user;
GRANT SELECT on V_$PARAMETER to oms_user;

GRANT EXECUTE on SYS.DBMS_LOGMNR to oms_user;

-- The SELECT privilege on all tables to be migrated. If a new table must be synchronized during incremental migration, the migration user must have the SELECT privilege on this table.
GRANT SELECT on any-replicated-table to oms_user;

-- The flashback and SELECT ANY TRANSACTION privileges on all tables to be migrated. (By default, flashback queries are not used and in this case the flashback privilege is not required.)
GRANT FLASHBACK on any-replicated-table to oms_user;
GRANT SELECT ANY TRANSACTION to oms_user;

-- The following privileges must be granted in Oracle Database 12c and later versions:
GRANT LOGMINING TO oms_user;  
GRANT SELECT on DBA_PDBS to oms_user;

You must grant additional privileges to the migration user in the following cases:

• For Oracle Database 12c and later versions, grant the following privileges during incremental synchronization:

  GRANT LOGMINING TO oms_user;  
  GRANT SELECT on DBA_PDBS to oms_user;

• If flashback query must be enabled (disabled by default) during incremental synchronization, grant the following privileges:

  GRANT FLASHBACK on any-replicated-table to oms_user;
  GRANT SELECT ANY TRANSACTION to oms_user;

• If the source database is a pluggable database (PDB) of Oracle Database 12c, 18c, or 19c, a common user is required to pull data from the PDB. In addition, CONTAINER=ALL should be added to statements for granting corresponding privileges.

  alter session set container=CDB$ROOT;
  create user C##XXX identified by yyy;

  Add CONTAINER=ALL to the preceding statements except for those that grant privileges on business tables to be synchronized. For example, you can rewrite GRANT ALTER SESSION to oms_user; to GRANT ALTER SESSION to C##XXX CONTAINER=ALL;.

• If the incremental data of a new table must be synchronized during incremental synchronization, grant the SELECT privilege on this new table to the migration user.

  If you cannot identify new tables before you create a data migration project, grant the SELECT ANY TABLE privilege to the migration user.

Forward switchover

GRANT ALTER SESSION to oms_user;
GRANT CREATE SESSION to oms_user;
GRANT CONNECT to oms_user;

GRANT SELECT on ALL_CONSTRAINTS to oms_user;
GRANT SELECT on ALL_CONS_COLUMNS to oms_user;
GRANT SELECT on ALL_IND_EXPRESSIONS to oms_user;
GRANT SELECT on ALL_LOBS to oms_user;
GRANT SELECT on ALL_MVIEWS to oms_user;
GRANT SELECT on ALL_MVIEW_LOGS to oms_user;
GRANT SELECT on ALL_TABLES to oms_user;
GRANT SELECT on ALL_TAB_COLUMNS to oms_user;
GRANT SELECT on ALL_TAB_PRIVS to oms_user;
GRANT SELECT on ALL_TYPES to oms_user;
GRANT SELECT on ALL_USERS to oms_user;
GRANT SELECT on ALL_VIEWS to oms_user;
GRANT SELECT on DUAL to oms_user;
GRANT SELECT on NLS_DATABASE_PARAMETERS to oms_user;
GRANT SELECT on PRODUCT_COMPONENT_VERSION to oms_user;
GRANT SELECT on USER_ROLE_PRIVS to oms_user;
GRANT SELECT on V_$DATABASE to oms_user;
GRANT SELECT on V_$PWFILE_USERS to oms_user;

Reverse incremental migration

GRANT ALTER SESSION to oms_user;
GRANT CREATE SESSION to oms_user;
GRANT CONNECT to oms_user;

-- The privilege to create internal transaction tables to ensure the data quality of tables without a primary key.
GRANT CREATE ANY TABLE to oms_user; 
-- The privileges to drop internal transaction tables, which can be faster if you drop the tables by partition.
GRANT DROP ANY TABLE to oms_user; 
GRANT INSERT ANY TABLE to oms_user;
GRANT DELETE ANY TABLE to oms_user;
GRANT UPDATE ANY TABLE to oms_user;
GRANT UNLIMITED TABLESPACE to oms_user;

If you need reverse synchronization for the DDL statements, grant DDL permissions to the migration user. For example: Here is an example:

GRANT ALTER ANY TABLE to oms_user;