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 database

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

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

• The database user must have the REPLICATION CLIENT and REPLICATION SLAVE privileges to perform incremental synchronization on a MySQL database.

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

  If you select Allow OMS to automatically write heartbeat data into this instance during incremental synchronization to resolve the problem of high latency when no business data is written into the source database when you add a MySQL data source, OMS will create and update the drc.heartbeat table in the corresponding MySQL database. In that case, the MySQL database user must have the privileges to create and write the table. For more information about how to create a data source, see Create a MySQL data source.

  • Grant a database user the privilege to create the drc.heartbeat table:

    GRANT CREATE ON drc.heartbeat TO '<user_name>';
    

  • Grant a database user the privilege to write the drc.heartbeat table:

    GRANT INSERT, UPDATE, DELETE ON drc.heartbeat TO '<user_name>';
    

• The database user must have the SELECT *.* privilege to synchronously pull incremental logs.

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

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

Run the following command to grant privileges to the user in the MySQL database:

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

User privileges required when OceanBase Community Edition serves as the source database

To synchronize data from OceanBase Community Edition to a Kafka or RocketMQ database, the user must have the following privileges on the source:

• SELECT privilege on the source business database.

• SELECT privilege on the OceanBase and MySQL databases of the source tenant.

• To synchronize incremental data, you need to create a user under the sys tenant of OceanBase Community Edition and grant the SELECT ON *.* privilege to the user.

  The username and password must be the same as those in the config.yaml file.

User privileges required when OceanBase Community Edition serves as the destination database

To migrate data from a MySQL database to OceanBase Community Edition, the user must have the following privileges on the destination:

• SELECT, INSERT, UPDATE, and DELETE privileges on the business database.

• The SELECT privilege on the OceanBase databases and MySQL databases.

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

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

If you select Allow OMS to automatically write heartbeat data into this instance during incremental synchronization. This resolves the problem of high latency when no business data is written in the source database when you add a PostgreSQL data source, OMS Community Edition will create and update the oms_postgres_heartbeat table in the corresponding PostgreSQL database. In that case, the PostgreSQL database user must have the privileges to create and write the table. For more information about how to create a data source, see Create a PostgreSQL data source.

• Grant a database user the privilege to create the drc.heartbeat table:

  GRANT CREATE ON SCHEMA public TO '<user_name>';
  

• Grant a database user the privilege to write the drc.heartbeat table:

  GRANT INSERT, UPDATE, DELETE ON oms_postgres_heartbeat TO '<user_name>';
  

User privileges required when a Kafka database serves as the destination

If the Kafka database 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

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

• Create and view topics.

• View the information of the topic message queue.

• Write records.

• Read records.