What are the minimum privileges required when an Oracle database serves as the source - ApsaraDB for OceanBase (Deprecated)

Cette rubrique décrit les privilèges minimum requis lors des différentes phases de migration des données lorsque une base de données Oracle est utilisée comme source.

Création de la source de données

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;

Migration du schéma

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;

Migration complète

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;

Synchronisation incrémentielle

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;

Vous devez accorder des privilèges supplémentaires à l'utilisateur de migration dans les cas suivants :

Pour Oracle Database 12c et versions ultérieures, accordez les privilèges suivants lors de la synchronisation incrémentielle :
```
GRANT LOGMINING TO oms_user;  
GRANT SELECT on DBA_PDBS to oms_user;
```
Si les requêtes Flashback doivent être activées (désactivées par défaut) pendant la synchronisation incrémentielle, accordez les privilèges suivants :
```
GRANT FLASHBACK on any-replicated-table to oms_user;
GRANT SELECT ANY TRANSACTION to oms_user;
```
Si la base de données source est une base de données enfichable (PDB) d'Oracle Database 12c, 18c ou 19c, un utilisateur commun est nécessaire pour extraire les données de la PDB. De plus, CONTAINER=ALL doit être ajouté aux instructions d'attribution des privilèges correspondants.
```
alter session set container=CDB$ROOT;
create user C##XXX identified by yyy;
```
Ajoutez CONTAINER=ALL aux instructions précédentes, à l'exception de celles qui accordent des privilèges sur les tables métier à synchroniser. Par exemple, vous pouvez réécrire GRANT ALTER SESSION to oms_user; en GRANT ALTER SESSION to C##XXX CONTAINER=ALL;.
Si les données incrémentielles d'une nouvelle table doivent être synchronisées lors de la synchronisation incrémentielle, accordez le privilège SELECT sur cette nouvelle table à l'utilisateur de migration.

Si vous ne pouvez pas identifier les nouvelles tables avant de créer un projet de migration de données, accordez le privilège SELECT ANY TABLE à l'utilisateur de migration.

Basculement avant

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;

Migration incrémentielle inverse

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;

Si vous avez besoin d'une synchronisation inverse pour les instructions DDL, accordez les permissions DDL à l'utilisateur de migration. Voici un exemple :

GRANT ALTER ANY TABLE to oms_user;