User privileges

Before you migrate data between databases by using OceanBase Migration Service (OMS) Community Edition, make sure that you have created a database user for each data source as the migration or synchronization user. These users 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 SHOW VIEW privilege is also required.

  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

  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 OceanBase Database Community Edition serves as the source data source

If OceanBase Database Community Edition serves as the source, the migration or synchronization user must have the following privileges:

• If the destination is an message queue instance, such as a Kafka or RocketMQ instance, the user must have the SELECT privilege on the source database to be synchronized.

  If the destination is a MySQL database or OceanBase Database Community Edition, the user must have the SELECT privilege on the source database to be migrated and the oceanbase database.

  Notice

  The SELECT privilege on the oceanbase database is required only in OceanBase Database Community Edition V4.0.0 and later.

• To synchronize incremental data, you must create a user in the sys tenant of OceanBase Database Community Edition and grant it the SELECT ON *.* privilege.

User privileges required when OceanBase Database Community Edition serves as the destination data source

If OceanBase Database Community Edition serves as the destination database, 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 a TiDB database serves as the source data source

• The database user must have the read privilege on the database from which data is migrated.

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

• During incremental synchronization from a TiDB 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

  The WITH GRANT OPTION parameter is optional.

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 privileges required for the migration user are as follows:

• If the specified whitelist 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 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.