User privileges

Before you migrate or synchronize data between databases by using OceanBase Migration Service (OMS), make sure that you have created a database user dedicated for migration or synchronization for each data source. This user must have the required privileges on the source and destination data sources.

User privileges required when a MySQL database serves as the source data source

• The database user must have the read privilege on the database from which data is migrated. If the version of the MySQL database is 8.0, the user must also have the SHOW VIEW privilege.

  GRANT SELECT ON <database_name>.* TO '<user_name>';
  

• During incremental synchronization from the MySQL database, the database user must have the REPLICATION CLIENT, REPLICATION SLAVE, and SELECT *.* privileges.

  GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO '<user_name>' [WITH GRANT OPTION];
  GRANT SELECT ON *.* TO '<user_name>';
  

  Note

  • If the user does not have the read privilege on all tables at the source, the project may be interrupted during incremental synchronization.

  • The WITH GRANT OPTION parameter is optional.

User privileges required when a MySQL database serves as the destination data source

During incremental synchronization to the MySQL database, the database user must have the CREATE, CREATE VIEW, INSERT, UPDATE, and DELETE privileges.

GRANT <privilege_type> ON <database_name>.<table_name> TO '<user_name>'@'<host_name>' [WITH GRANT OPTION];

User privileges required when a MySQL tenant of OceanBase Database serves as the source data source

If a MySQL tenant of OceanBase Database serves as the source data source, the migration or synchronization user must have the following privileges:

• If the destination is a Kafka, DataHub, or RocketMQ instance, the user must have the SELECT privilege on the source database from which data is to be synchronized.

  If the destination is a MySQL database or a MySQL tenant of OceanBase Database, the user must have the SELECT privilege on the source database from which data is to be migrated and the oceanbase database.

  Notice

  You need to grant the SELECT privilege on the oceanbase database to the user only in OceanBase Database V4.0.0 and later.

• For incremental data synchronization scenarios, you need to create a user under the sys tenant of OceanBase Database and grant the SELECT ON *.* privilege to the user.

User privileges required when a MySQL tenant of OceanBase Database serves as the destination data source

If a MySQL tenant of OceanBase Database serves as the destination data source, the migration user must have the following privileges:

• The CREATE, CREATE VIEW, SELECT, INSERT, UPDATE, ALTER, INDEX, and DELETE privileges on the destination database.

  GRANT CREATE,CREATE VIEW,SELECT,INSERT,UPDATE,ALTER,INDEX,DELETE ON <database_name>.* TO '<user_name>';
  

• The SELECT privilege on the entire tenant.

  GRANT SELECT ON *.* TO '<user_name>';
  

User privileges required when an Oracle database serves as the source or destination data source

The user privileges required for forward migration when an Oracle database serves as the source data source are the same as those required for reverse migration when an Oracle database serves as the destination data source. This section describes the user privileges required for different roles in different versions of Oracle databases.

User privileges required for DBA users in Oracle databases of a version 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>;

User privileges required for non-DBA users in Oracle databases of a version 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. If the name of the schema to be migrated is the same as user_name, execute the following statement:

   GRANT CREATE SEQUENCE,CREATE VIEW TO <user_name>;
   

   If the name of the schema to be migrated is different from user_name, 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>;
   

   You can also 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>;
   # Specify the table in the Oracle database to which data is to be migrated. 
   GRANT DELETE, INSERT, UPDATE ON <database name>.<table name> TO <user_name>;
   

User privileges required for DBA users in Oracle databases 12c and later

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, you must determine whether to use a pluggable database (PDB) of 12c, 18c, or 19c.

• Non-PDB

  1. Execute the following statement to grant DBA privileges to the migration user:

     GRANT DBA TO <user_name>;
     

  2. Execute the following statement to grant the read privilege on the SYS.USER$ table to the migration user:

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

• 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. Execute the following statement to switch to the CDB$ROOT container:

     ALTER SESSION SET CONTAINER=CDB$ROOT;
     

     All common users can connect to the root container named CDB$ROOT and any accessible PDB and then perform the related operations.

  2. Execute the following statement to grant DBA privileges to the migration user:

     GRANT DBA TO C##XXX CONTAINER=ALL;
     

  3. Execute the following statement to grant the read privilege on the SYS.USER$ table to the migration user:

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

User privileges required for non-DBA users in Oracle databases 12c and later

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:

• Non-PDB

  1. Grant the CONNECT privilege.

     GRANT CONNECT TO <user_name>;
     

  2. Execute the following statement to grant the read privilege on the SYS.USER$ table 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. If the name of the schema to be migrated is the same as user_name, execute the following statement:

     GRANT CREATE SEQUENCE,CREATE VIEW TO <user_name>;
     

     If the name of the schema to be migrated is different from user_name, 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>;
     

• 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.

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

  2. Execute the following statement to grant the read privilege on the SYS.USER$ table 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. If the name of the schema to be migrated is the same as C##XXX, execute the following statement:

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

     If the name of the schema to be migrated is different from C##XXX, 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 <C##XXX> CONTAINER=ALL;
     

User privileges required when an Oracle tenant of OceanBase Database serves as the source data source

To synchronize data from an Oracle tenant of OceanBase Database to a Kafka, RocketMQ, or DataHub instance:

• For OceanBase Database earlier than V2.2.70, you must execute the GRANT SELECT ON *.* TO <user_name>; statement to grant the full SELECT privilege to the source migration user.

• For OceanBase Database V2.2.70 and later, you must execute the GRANT DBA TO <user_name>; statement to grant DBA privileges to the source migration user.

User privileges required when an Oracle tenant of OceanBase Database serves as the destination data source

When an Oracle tenant of OceanBase Database serves as the destination data source, the user privileges required vary with the version of OceanBase Database.

User privileges required for an Oracle tenant of OceanBase Database V2.2.5 or V2.2.3

You can grant privileges to the migration user by using one of the following two methods:

• Method 1

  • Execute the following statement to grant all privileges to the migration user. This method is simple but high-level privileges are granted.

    GRANT ALL PRIVILEGES ON *.* TO <user_name>;
    

• Method 2

  1. Grant the SELECT privilege on system views in the sys tenant to the migration user.

     GRANT SELECT ON SYS.* TO <user_name>;
     

  2. Grant all kinds of privileges on business tables to the migration user. If multiple business databases exist, grant the privileges separately.

     GRANT SELECT, UPDATE, DELETE ON <db_name>.* TO <user_name>;
     GRANT CREATE, INDEX, ALTER ON <db_name>.* TO <user_name>;
     

User privileges required for an Oracle tenant of OceanBase Database V2.2.7 or later

You can grant privileges to the migration user by using one of the following two methods:

• Method 1

  Execute the following statement to grant DBA privileges to the migration user. This method is simple but high-level privileges are granted.

  GRANT DBA TO <user_name>;
  

• Method 2

  Grant all kinds of privileges on business tables to the migration user. If multiple business databases exist, grant the privileges separately.

  GRANT CONNECT TO <user_name>;
  GRANT CREATE SESSION, ALTER SESSION, SELECT ANY TABLE, SELECT ANY DICTIONARY TO <user_name>;
  GRANT CREATE ANY TABLE, CREATE ANY INDEX, CREATE ANY VIEW, INSERT ANY TABLE, UPDATE ANY TABLE, ALTER ANY TABLE, DELETE ANY TABLE TO <user_name>;
  

User privileges required when a DB2 LUW database serves as the source or destination data source

The migration user must have the sysadm privilege when a DB2 LUW database serves as the source or destination data source.

User privileges required when a PostgreSQL database serves as the source data source

During schema migration from a PostgreSQL database to a MySQL tenant of OceanBase Database, you must grant the SELECT privilege on tables and views to the migration user.

During incremental synchronization from a PostgreSQL database to a MySQL tenant of OceanBase Database, the user privileges required for the migration user are as follows:

• If the specified allowlist of tables to migrate contains wildcard characters, the migration user must be granted the superuser privilege. Otherwise, publication creation will fail and an error indicating no privilege will be returned. If no wildcard character is contained, the superuser privilege is not required.

• The migration user must be granted the REPLICATION and LOGIN roles and the CREATE PUBLICATION privilege.

  • CREATE USER <user_name> REPLICATION LOGIN ENCRYPTED PASSWORD '<password>';

  • GRANT CREATE ON DATABASE <database_name> TO <user_name>;

• The migration user must be the owner of the tables to migrate.

  // Create a role named replication_group. 
  CREATE ROLE <replication_group>;  
  // Add the original owner of the tables to migrate to the replication_group role. 
  GRANT <replication_group> TO <original_owner>; 
  // Add the migration account to replication_group. 
  GRANT <replication_group> TO <replication_user>; 
  // Change the owner of the tables to migrate to the replication_group role. 
  ALTER TABLE <table_name> OWNER TO <replication_group>; 
  

User privileges required when a TiDB database serves as the source data source

The migration user of a TiDB database must have the full SELECT privilege.

GRANT SELECT ON *.* TO '<user_name>';

User privileges required when a DataHub instance serves as the destination data source

DataHub performs authentication based on the endpoint, access key, or secret key. For more information, see "Privilege control" in DataHub documentation.

A DataHub user must have the following privileges: GetProject, CreateTopic, ListTopic, GetTopic, ListShard, PutRecords, GetRecords, and GetCursor.

User privileges required when a Kafka instance serves as the destination data source

If the Kafka instance requires authentication, see Create a Kafka data source.

To synchronize data to a Kafka database, the user must have privileges to perform the following operations:

• Create and view topics.

• View topic partition information.

• Write records.

• Read records.

User privileges required when a RocketMQ database serves as the destination data source

To synchronize data to a RocketMQ instance, the user must have privileges to perform the following operations:

• Create and view topics.

• View the information about the topic message queue.

• Write records.

• Read records.