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 the target.

User privileges required when a MySQL database serves as the source

• The database user must have the read privilege on the database to be migrated. If the database version is MySQL V8.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.

• When you migrate data from a MySQL database to the MySQL compatible mode of OceanBase Database, if reverse increment is involved, the migration user must also have the CREATE, SELECT, INSERT, UPDATE, and DELETE privileges on the source test database.

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

User privileges required when a MySQL database serves as the target

When a MySQL database serves as the target, the migration 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];

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

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

• If the target is a Kafka, RocketMQ, or other message queue, the user must have the SELECT privilege on the source database from which data is to be synchronized.

  If the target is a MySQL database or OceanBase Community Edition database, the user 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 OceanBase Community Edition database V4.0.0 and later.

• In incremental synchronization scenarios, 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 <database_name>.* TO <drc_user>;
  

• When you migrate data between MySQL-compatible tenants of OceanBase Database, if reverse increment is involved, the migration user must also have the CREATE, SELECT, INSERT, UPDATE, and DELETE privileges on the source test database.

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

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

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 and the PROCESS privilege on the database from which data is migrated.

  GRANT SELECT ON <database_name>.* TO '<user_name>';
  GRANT PROCESS ON *.* 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 an ElasticSearch database serves as the target

When an ElasticSearch database serves as the target, the user must have the create_index, write, and view_index_metadata privileges.

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

User privileges required when a MongoDB database serves as the source

When a MongoDB database serves as the source, the database user must have the listDatabases, listCollections, find, collStats, and splitVector privileges.

db.createRole({
  role: "username",
  privileges: [
     { resource: { db: "database", collection: "" }, actions: ["listDatabases","listCollections","find","splitVector","collStats"] }
  ],
 roles:[]
});

User privileges required when a Kafka database serves as the target

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 target

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.