User privileges

Before you migrate data by using OceanBase Migration Service (OMS), make sure that a database user is created for each data source as the migration or synchronization user. The user must have the privileges on the source and target.

User privileges required when a MySQL database serves as the source

• The database user must have the read privilege on the database from which data is to be migrated. If the MySQL database version 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 during incremental synchronization, the task may be interrupted.

  • The WITH GRANT OPTION parameter is optional.

  If you select Enable OMS to write heartbeat data to the instance during incremental synchronization to resolve the high latency issue when no business data is written to the source when you add the MySQL data source, OMS creates and updates the drc.heartbeat table in the corresponding MySQL database. At this point, the MySQL database user must have the privileges to create and write to tables. For more information, see Create and update a heartbeat table.

• When you migrate data from a MySQL database to a MySQL compatible mode of OceanBase Database, the migration user must also have the CREATE, SELECT, INSERT, UPDATE, and DELETE privileges on the test database at the source. Run the following statement to grant the privileges to the migration user:

  GRANT CREATE,SELECT,INSERT,UPDATE,DELETE ON test.* TO '<user_name>';
  

User privileges required when MySQL is the target database

• The database user must have the CREATE, CREATE VIEW, INSERT, UPDATE, and DELETE privileges on the target database.

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

  Parameter	Description
  privilege_type	The privileges to grant. You can grant CREATE, INSERT, UPDATE, and other operation privileges to the account. To grant all privileges to the account, set this parameter to ALL.
  database_name	The name of the database. To grant operation privileges on all databases to the account, set this parameter to an asterisk (*).
  table_name	The name of the table. To grant operation privileges on all tables to the account, set this parameter to an asterisk (*).
  user_name	The account to which privileges are granted.
  host_name	The host from which the account is allowed to log in to the database. To allow the account to log in to the database from any host, set this parameter to a percent sign (%).
  WITH GRANT OPTION	Grants the account the privilege to use the GRANT command. This parameter is optional.

• When you perform schema migration, you need the following privileges to migrate views and indexes.

  • To migrate views, you need the SELECT privilege on the database.

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

  • To migrate indexes, you need the INDEX privilege on the database.

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

• In a bidirectional synchronization task from OceanBase Database in MySQL compatible mode to a MySQL database, the target database user must have the SELECT, CREATE, INSERT, and UPDATE privileges on the oms database.

  GRANT SELECT, CREATE, INSERT, UPDATE ON oms.* TO '<user_name>';
  

• When you create an incremental validation task, the database user must have the REPLICATION CLIENT and REPLICATION SLAVE privileges.

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

User privileges required when the MySQL compatible mode of OceanBase Database serves as the source

If the MySQL compatible mode of OceanBase Database serves as the source, the user privileges required for migration or synchronization are as follows:

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

  If the target is a MySQL database or the MySQL compatible mode of OceanBase Database, you must have the SELECT privilege on the source database from which data is to be migrated and the oceanbase database.

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

  Notice

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

  • In the MySQL compatible mode of OceanBase Database earlier than V4.0.0, you need to grant the SELECT privilege on the oceanbase.gv$table view if you do not grant the oceanbase.* privilege to the user.

• In an incremental data synchronization scenario, you need to read incremental log data and database object schemas. Therefore, you need to create a user under the sys tenant of the source database and grant the SELECT ON *.* privilege to the user.

  GRANT SELECT ON *.* TO <drc_user>;
  

• When you migrate data between MySQL compatible modes of OceanBase Database and there are reverse incremental data, the migration user must have the CREATE, SELECT, INSERT, UPDATE, and DELETE privileges on the test database of the source. Run the following statement to grant the privileges to the user: '<user_name>'.

  GRANT CREATE,SELECT,INSERT,UPDATE,DELETE ON test.* TO '<user_name>';
  

User privileges required when MySQL compatible mode of OceanBase Database serves as the target

If the MySQL compatible mode of OceanBase Database serves as the target, the migration user must have the following privileges:

• The user must have the CREATE, CREATE VIEW, DROP VIEW, SELECT, INSERT, UPDATE, ALTER, INDEX, and DELETE privileges on the target database.

  GRANT CREATE,CREATE VIEW,DROP 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>';
  

• For OceanBase Database V4.2.4 to V4.3.0 (excluding V4.3.0) or V4.3.3 and later in MySQL compatible mode, when it serves as the target database:

  • The migration user must have the TRIGGER privilege to check whether a trigger exists in the target database. If a trigger exists, it may cause data inconsistency.

  • If you select schema migration when you create a data migration task, the migration user must also have the REFERENCES privilege on the target database.

  You can grant the TRIGGER and REFERENCES privileges on the corresponding database, or grant the global TRIGGER and REFERENCES privileges.

  • Grant the TRIGGER and REFERENCES privileges on the corresponding database:

    GRANT TRIGGER,REFERENCES ON <database_name>.* TO '<user_name>';
    

  • Grant the global TRIGGER and REFERENCES privileges:

    GRANT TRIGGER,REFERENCES ON *.* TO '<user_name>';
    

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

The user privileges required for forward migration when an Oracle database serves as the source and for reverse migration when it serves as the target are the same. This section describes the user privileges required for different versions of Oracle databases and user roles after a user is created.

DBA privileges to be granted to the migration user in an Oracle database of a version earlier than 12c

If the migration user can be granted the database administrator (DBA) role and the version of the Oracle database is earlier than 12c, run the following statement to grant the DBA role to the migration user:

GRANT DBA TO <user_name>;

General user privileges to be granted to the migration user in an Oracle database of a version earlier than 12c

If the migration user is granted a minimal set of privileges, and the version of the Oracle database 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.

   Note

   If incremental data is obtained from the Oracle data source by using Kafka Incremental Data Synchronization, you do not need to grant the LOGMINER privilege.

   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 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 Database 12c and later

If your environment allows you to assign the DBA role to the migration user and the version of the Oracle database is 12c or later, you need to determine whether to use the pluggable database (PDB) of 12c/18c/19c.

• Non-PDB

  1. Execute the following statement to grant the DBA privilege 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 the source Oracle database to be migrated to the Oracle database of OceanBase Database is a pluggable database (PDB) of 12c/18c/19c, the migration user must be a common user.

  1. Execute the following statement to switch to the CDB$ROOT container:

     ALTER SESSION SET CONTAINER=CDB$ROOT;
     

     Each common user can connect to the root container (named CDB$ROOT) and any PDB to which they have connection privileges and perform operations.

  2. Execute the following statement to grant the DBA privilege 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 Database 12c and later

If the environment is cautious about granting privileges to the migration user and the Oracle database version 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 privileges to the migration user.

     Note

     If Incremental data is obtained by using Kafka is enabled for the Oracle data source, you do not need to grant the LOGMINER privileges.

     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 the source Oracle database to which data is to be migrated is a Pluggable Database (PDB) of Oracle Database 12c, 18c, or 19c, the account for pulling data from the PDB must be a common user.

  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 privileges to the migration user.

     Note

     If Incremental data is obtained by using Kafka is enabled for the Oracle data source, you do not need to grant the LOGMINER privileges.

     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 the Oracle compatible mode of OceanBase Database serves as the source

If the Oracle compatible mode of OceanBase Database serves as the source, the migration or synchronization user must have the following privileges:

• For the Oracle compatible mode of OceanBase Database of a version earlier than V2.2.70, the user privileges required are GRANT SELECT ON *.* TO '<user_name>';.

• For the Oracle compatible mode of OceanBase Database V2.2.70 and later, the user privileges required are GRANT DBA TO '<user_name>';.

  • For the Oracle compatible mode of OceanBase Database V4.0.0 and later, the user must also have the SELECT privilege on DBA_OB_ARCHIVELOG.

    GRANT SELECT ON DBA_OB_ARCHIVELOG TO '<user_name>';
    

  • For the Oracle compatible mode of OceanBase Database V4.2.0 and later, the user must also have the SELECT privilege on DBA_OB_TABLE_LOCATIONS.

    GRANT SELECT ON DBA_OB_TABLE_LOCATIONS TO '<user_name>';
    

User privileges required when the Oracle compatible mode of OceanBase Database serves as the target

The user privileges required vary depending on the version of the Oracle compatible mode of OceanBase Database.

User privileges required when the version of the Oracle compatible mode of OceanBase Database is V2.2.5 or V2.2.3

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

• Method 1

  • Execute the following statement to grant all privileges to the migration user. This method is simple but grants a large number of privileges. Note that the migration user may have excessive privileges.

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

• Method 2

  1. Grant the SELECT privilege on system views in the SYS database to the migration user.

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

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

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

User privileges required when the version of the Oracle compatible mode of OceanBase Database is V2.2.7 or later

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

• Method 1

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

  GRANT DBA TO '<user_name>';
  

• Method 2

  Grant all types 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 target

If a DB2 LUW database serves as the source or target, the migration user must have the DBADM privilege.

1. Connect to the target database.

   db2 CONNECT TO <database_name>
   

2. Grant the DBADM privilege to the migration user.

   db2 GRANT DBADM ON DATABASE TO USER <user_name>;
   

User privileges required when a PostgreSQL database serves as the source

During schema migration from a PostgreSQL database to the MySQL compatible mode 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 the MySQL compatible mode of OceanBase Database, the required privileges 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 the replication_group role.
  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>; 
  

• If you select Allow OMS to write heartbeat data to this instance during incremental synchronization to resolve high latency in the source instance when no business data is written when you add the PostgreSQL data source, OMS will create and update the oms_postgres_heartbeat table in the PostgreSQL database. In this case, the PostgreSQL database user must have the privileges to create and write to tables. For more information, see Create and update a heartbeat table.

User privileges required when a TiDB database serves as the source

A user that migrates data from a TiDB database must have the full SELECT privilege.

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

User privileges required when DataHub is the target

DataHub uses endpoint/ak/sk for authentication. For more information, see DataHub privileges.

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

User privileges required when Kafka is the target

If Kafka authentication is enabled, see Create a Kafka data source.

To synchronize data to a Kafka data source, you must have the following privileges:

• Create and view topics

• View topic partition information

• Write records

• Read records

User privileges required when RocketMQ is the target

When RocketMQ is the target, the user must have the following privileges:

• Create and view topics

• View topic message queues

• Write records

• Read records