What privileges do I need to grant to a user during data migration to or from an Oracle database?|V4.2.4|OceanBase Migration Service| docs|Distributed Database

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

Last Updated：2025-05-12 01:56:25 Updated

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:

Grant the CONNECT privilege.
```
GRANT CONNECT TO <user_name>;
```

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>;

Grant the LOGMINER privilege to the migration user.

GRANT EXECUTE ON SYS.DBMS_LOGMNR TO <user_name>;

Grant the CREATE TABLE and UNLIMITED TABLESPACE privileges to the migration user.
```
GRANT CREATE TABLE, UNLIMITED TABLESPACE TO <user_name>;
```

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

If they are consistent, execute the following statement:
```
GRANT CREATE SEQUENCE,CREATE VIEW TO <user_name>;
```

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>;

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

Grant the DBA role to the migration user:
```
GRANT DBA TO <user_name>;
```
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-compatible tenant of OceanBase Database, a common user account is required for pulling data from the PDB.

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;
```
Grant the DBA role to the migration user:
```
GRANT DBA TO <C##XXX> CONTAINER=ALL;
```
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

Grant the CONNECT privilege to the migration user:
```
GRANT CONNECT TO <user_name>;
```
Grant the read privilege on the SYS.USER$ view to the migration user:
```
GRANT SELECT ON SYS.USER$ TO <user_name>;
```

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>;

Grant the LOGMINER privilege to the migration user.

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

Grant the CREATE TABLE and UNLIMITED TABLESPACE privileges to the migration user.
```
GRANT CREATE TABLE, UNLIMITED TABLESPACE TO <user_name>;
```

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

If they are consistent, execute the following statement:
```
GRANT CREATE SEQUENCE,CREATE VIEW TO <user_name>;
```

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-compatible tenant of OceanBase Database, a common user account is required for pulling data from the PDB.

Grant the CONNECT privilege to the migration user:
```
GRANT CONNECT TO <C##XXX> CONTAINER=ALL;
```
Grant the read privilege on the SYS.USER$ view to the migration user:
```
GRANT SELECT ON SYS.USER$ TO <C##XXX> CONTAINER=ALL;
```

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;

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;

Grant the CREATE TABLE and UNLIMITED TABLESPACE privileges to the migration user:
```
GRANT CREATE TABLE, UNLIMITED TABLESPACE TO <C##XXX> CONTAINER=ALL;
```

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

If they are consistent, execute the following statement:

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

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;

Enterprise Edition

Community Edition

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

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

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

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

For a non-PDB database

For a PDB

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

For a non-PDB database

For a PDB