User privileges

Before you migrate or synchronize data between databases by using OceanBase Migration Service (OMS) Community Edition, 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

To synchronize data from a MySQL database to a Kafka, RocketMQ, or DataHub instance:

• If the data to be synchronized is in a database user, the user must have the SELECT privilege on the database user.

• If the data to be synchronized is in a database table, the user must have the SELECT privilege on the database table.

• The database user must have the read privilege on the database to be migrated. If the database version is MySQL 8.0, the user must also have the SHOW VIEW privilege.

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

• When you perform incremental synchronization from a 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 you do not have the privilege to read from all tables at the source during incremental synchronization, the task may be interrupted.

  • The WITH GRANT OPTION clause is optional.

User privileges required when a MySQL database serves as the destination

To synchronize data from a Kafka, RocketMQ, or DataHub instance to a MySQL database, the user must have the following privileges:

• The user must have the full data modification privileges on the topic or DataHub instance from which data is synchronized. If data is synchronized from multiple topics or DataHub instances, the user must have the full data modification privileges on all these topics or DataHub instances.
• The user must have the full data modification privileges on the database to which the synchronized data is written.

When a MySQL database serves as the destination, the migration user must have the CREATE, CREATE VIEW, INSERT, UPDATE, and DELETE privileges on the destination database.

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

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

To synchronize data from an OceanBase Community Edition database to a Kafka, RocketMQ, or DataHub instance:

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

To synchronize data to an OceanBase Community Edition database, the user must have the following privileges:

• The CREATE, CREATE VIEW, SELECT, INSERT, UPDATE, ALTER, INDEX, and DELETE privileges.

  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

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

During schema migration from a PostgreSQL database to the MySQL compatible mode of OceanBase Database, 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 migration user must have the following privileges:

• If the specified allowlist of tables to migrate contains wildcard characters, the migration user must be granted the superuser privilege. Otherwise, an error is returned when a publication is created, indicating that the user does not have the required privilege. If the migration user does not need to be granted the superuser privilege, no action is required.

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

User privileges required when an ElasticSearch database serves as the source

To synchronize data from an ElasticSearch database, the database user must have the view_index_metadata privilege on the index to be synchronized.

POST /_security/role/{roleName}
{
  "cluster": ["all"],
  "indices": [
    {
      "names": [ "index_name" ],
      "privileges": ["view_index_metadata"]
    }
  ],
  "run_as": [ "*" ],
  "metadata": {},
  "transient_metadata": {
    "enabled": true
  }
}

User privileges required when a Kafka database serves as the destination

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

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

• 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 the following privileges:

• Create and view topics.

• View topic message queue information.

• Write records.

• Read records.