What privileges do I need to grant to a user during data migration to or from an Oracle database?

This topic describes how to grant privileges to migration users when you migrate data to or from an Oracle database.

Consider the following factors when you grant privileges to a user:

• Whether the Oracle database version is earlier than 12C

• Whether to grant the DBA role to the user

• Whether the Oracle database is a pluggable database (PDB)

Privileges required for DBA users in Oracle databases earlier than 12c

If the environment allows you to assign the database administrator (DBA) role to the migration user and the Oracle database version is earlier than 12c, execute the following statement to grant the DBA privileges to the migration user:

GRANT DBA TO <user_name>;

Privileges required for non-DBA users in Oracle databases earlier than 12c

If the environment allows you to grant only the required privileges to the migration user and the Oracle database version is earlier than 12c, perform the following operations:

1. Grant the CONNECT privilege.

   GRANT CONNECT TO <user_name>;
   

2. Grant the CREATE SESSION, ALTER SESSION, SELECT ANY TRANSACTION, SELECT ANY TABLE, and SELECT ANY DICTIONARY privileges to the migration user.

   GRANT
       CREATE SESSION,
       ALTER SESSION,
       SELECT ANY TRANSACTION,
       SELECT ANY TABLE,
       SELECT ANY DICTIONARY
   TO <user_name>;
   

3. Grant the LOGMINER privilege to the migration user.

   GRANT EXECUTE ON SYS.DBMS_LOGMNR TO <user_name>;
   

4. Grant the CREATE TABLE and UNLIMITED TABLESPACE privileges to the migration user.

   GRANT CREATE TABLE, UNLIMITED TABLESPACE TO <user_name>;
   

5. Check whether the name of the schema to be migrated is consistent with the value of user_name.

   1. If they are consistent, execute the following statement:

      GRANT CREATE SEQUENCE,CREATE VIEW TO <user_name>;
      

   2. If they are inconsistent, you can grant the user all privileges on all schemas:

      GRANT
          CREATE ANY TABLE,
          CREATE ANY INDEX,
          DROP ANY TABLE,
          ALTER ANY TABLE,
          COMMENT ANY TABLE,
          DROP ANY INDEX,
          ALTER ANY INDEX,
          CREATE ANY SEQUENCE,
          ALTER ANY SEQUENCE,
          DROP ANY SEQUENCE,
          CREATE ANY VIEW,
          DROP ANY VIEW,
          INSERT ANY TABLE,
          DELETE ANY TABLE,
          UPDATE ANY TABLE
      TO <user_name>;
      

   3. If they are inconsistent and you want to grant the user only the INSERT, UPDATE, and DELETE privileges on the databases and tables to be migrated, execute the following statement:

      GRANT
          CREATE ANY TABLE,
          CREATE ANY INDEX,
          DROP ANY TABLE,
          ALTER ANY TABLE,
          COMMENT ANY TABLE,
          DROP ANY INDEX,
          ALTER ANY INDEX,
          CREATE ANY SEQUENCE,
          ALTER ANY SEQUENCE,
          DROP ANY SEQUENCE,
          CREATE ANY VIEW,
          DROP ANY VIEW
      TO <user_name>;
      # Execute the following statement for each database and table involved in the migration:
      GRANT INSERT, UPDATE, DELETE ON <database name>.<table name> TO <user_name>;
      

Privileges required for DBA users in Oracle Database 12c and later versions

If the environment allows you to assign the DBA role to the migration user and the version of the Oracle database is 12c or later, determine whether a PDB is used.

For a non-PDB database

1. Grant the DBA role to the migration user:

   GRANT DBA TO <user_name>;
   

2. Grant the read privilege on the SYS.USER$ view to the migration user:

   GRANT SELECT ON SYS.USER$ TO <user_name>;
   

For a PDB

If you are to migrate data from a PDB of Oracle Database 12c, 18c, or 19c to an Oracle tenant of OceanBase Database, a common user account is required for pulling data from the PDB.

1. Execute the following statement to switch to the CDB$ROOT container: All common users can connect to the root container named CDB$ROOT and any accessible PDB and then perform related operations.

   ALTER SESSION SET CONTAINER=CDB$ROOT;
   

2. Grant the DBA role to the migration user:

   GRANT DBA TO <C##XXX> CONTAINER=ALL;
   

3. Grant the read privilege on the SYS.USER$ view to the migration user:

   GRANT SELECT ON SYS.USER$ TO <C##XXX> CONTAINER=ALL;
   

Privileges required for non-DBA users in Oracle Database 12c and later versions

If the environment allows you to grant only the required privileges to the migration user and the version of the Oracle database is 12c or later, perform the following operations:

For a non-PDB database

1. Grant the CONNECT privilege to the migration user:

   GRANT CONNECT TO <user_name>;
   

2. Grant the read privilege on the SYS.USER$ view to the migration user:

   GRANT SELECT ON SYS.USER$ TO <user_name>;
   

3. Grant the CREATE SESSION, ALTER SESSION, SELECT ANY TRANSACTION, SELECT ANY TABLE, and SELECT ANY DICTIONARY privileges to the migration user:

   GRANT
       CREATE SESSION,
       ALTER SESSION,
       SELECT ANY TRANSACTION,
       SELECT ANY TABLE,
       SELECT ANY DICTIONARY
   TO <user_name>;
   

4. Grant the LOGMINER privilege to the migration user.

   GRANT LOGMINING TO <user_name>;
   GRANT EXECUTE ON SYS.DBMS_LOGMNR TO <user_name>;
   

5. Grant the CREATE TABLE and UNLIMITED TABLESPACE privileges to the migration user.

   GRANT CREATE TABLE, UNLIMITED TABLESPACE TO <user_name>;
   

6. Check whether the name of the schema to be migrated is consistent with the value of user_name.

   1. If they are consistent, execute the following statement:

      GRANT CREATE SEQUENCE,CREATE VIEW TO <user_name>;
      

   2. If they are inconsistent, execute the following statement:

      GRANT
          CREATE ANY TABLE,
          CREATE ANY INDEX,
          DROP ANY TABLE,
          ALTER ANY TABLE,
          COMMENT ANY TABLE,
          DROP ANY INDEX,
          ALTER ANY INDEX,
          CREATE ANY SEQUENCE,
          ALTER ANY SEQUENCE,
          DROP ANY SEQUENCE,
          CREATE ANY VIEW,
          DROP ANY VIEW,
          INSERT ANY TABLE,
          DELETE ANY TABLE,
          UPDATE ANY TABLE
      TO <user_name>;
      

For a PDB

If you migrate data from a PDB of Oracle Database 12c, 18c, or 19c to an Oracle tenant of OceanBase Database, a common user account is required for pulling data from the PDB.

1. Grant the CONNECT privilege to the migration user:

   GRANT CONNECT TO <C##XXX> CONTAINER=ALL;
   

2. Grant the read privilege on the SYS.USER$ view to the migration user:

   GRANT SELECT ON SYS.USER$ TO <C##XXX> CONTAINER=ALL;
   

3. Grant the CREATE SESSION, ALTER SESSION, SELECT ANY TRANSACTION, SELECT ANY TABLE, and SELECT ANY DICTIONARY privileges to the migration user:

   GRANT
       CREATE SESSION,
       ALTER SESSION,
       SELECT ANY TRANSACTION,
       SELECT ANY TABLE,
       SELECT ANY DICTIONARY
   TO <C##XXX> CONTAINER=ALL;
   

4. Grant the LOGMINER privilege to the migration user:

   GRANT LOGMINING TO <C##XXX> CONTAINER=ALL;
   GRANT EXECUTE ON SYS.DBMS_LOGMNR TO <C##XXX> CONTAINER=ALL;
   

5. Grant the CREATE TABLE and UNLIMITED TABLESPACE privileges to the migration user:

   GRANT CREATE TABLE, UNLIMITED TABLESPACE TO <C##XXX> CONTAINER=ALL;
   

6. Check whether the name of the schema to be migrated is consistent with the value of C##XXX.

   1. If they are consistent, execute the following statement:

      GRANT CREATE SEQUENCE, CREATE VIEW TO <C##XXX> CONTAINER=ALL;
      

   2. If they are inconsistent, execute the following statement:

      GRANT
          CREATE ANY TABLE,
          CREATE ANY INDEX,
          DROP ANY TABLE,
          ALTER ANY TABLE,
          COMMENT ANY TABLE,
          DROP ANY INDEX,
          ALTER ANY INDEX,
          CREATE ANY SEQUENCE,
          ALTER ANY SEQUENCE,
          DROP ANY SEQUENCE,
          CREATE ANY VIEW,
          DROP ANY VIEW,
          INSERT ANY TABLE,
          DELETE ANY TABLE,
          PDATE ANY TABLE
      TO <C##XXX> CONTAINER=ALL;