User privileges

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

Privileges required when a MySQL database is the source

• The database user must have read privileges on the database to be migrated. For MySQL V8.0, you must also grant the SHOW VIEW privilege.

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

• When you synchronize incremental data 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 the database user does not have read privileges on all tables in the source database, the task may fail.

  • WITH GRANT OPTION is an optional parameter.

  If you select Allow OMS to automatically write heartbeat data to this instance during incremental synchronization to address high latency in scenarios where there is no business write to 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 this case, 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, if there is reverse incremental data, the migration user must have the CREATE, SELECT, INSERT, UPDATE, and DELETE privileges on the test database in the source database.

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

Permissions required for a MySQL database as the destination

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

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

  Parameter	Description
  privilege_type	The operation permissions to grant to the account, such as CREATE, INSERT, and UPDATE. If you want to grant all permissions to the account, set this parameter to ALL.
  database_name	The name of the database. If you want to grant all database operation permissions to the account, set this parameter to a star (*).
  table_name	The name of the table. If you want to grant all table operation permissions to the account, set this parameter to a star (*).
  user_name	The account to be authorized.
  host_name	The host that allows the account to log in. If the account is allowed to log in from any host, set this parameter to a percent sign (%).
  WITH GRANT OPTION	The permission to use the GRANT command. This parameter is optional.

• The database user must have the TRIGGER permission.

  This permission is used to check whether a trigger exists in the destination database. If a trigger exists, it may cause data inconsistency.

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

  or

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

User privileges required when OceanBase Database is used as the source in MySQL compatible mode

When OceanBase Database is used as the source in MySQL compatible mode, the migration/synchronization user must have the following privileges:

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

  When the destination is a database such as MySQL or OceanBase Database in MySQL compatible mode, the user must have the SELECT privilege on the source database to be migrated and the oceanbase database.

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

  Notice

  You must grant the SELECT privilege on the oceanbase database to the user only in OceanBase Database V4.0.0 and later.

• In incremental data synchronization, you must read incremental log data and database object structure information. Therefore, you must create a user in 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 OceanBase Database instances in MySQL compatible mode, if there is a reverse incremental, the migration user must have the CREATE, SELECT, INSERT, UPDATE, and DELETE privileges on the test database of the source database.

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

User privileges required when OceanBase Database is used in MySQL compatible mode as the destination

When OceanBase Database is used in MySQL compatible mode as the destination, 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>';
  

• OceanBase Database V4.2.4 to V4.3.0 or V4.3.3 and later in MySQL compatible mode as the destination:

  • The migration user must have the trigger privilege to check whether triggers exist on the destination. If triggers exist, data inconsistency may occur.

  • If you select schema migration when you create a data migration task and the database tables contain foreign key constraints, the migration user must also have the REFERENCES privilege on the database where the tables referenced by the foreign key constraints are located.

  You can grant the trigger and REFERENCES privileges on the corresponding database or 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>';
    

Privileges required when an Oracle database serves as the source or target

The 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. After you create a user, the privileges required for different versions of an Oracle database and the user roles are described as follows.

Granting DBA privileges to a user in an Oracle database of a version earlier than 12c

If your environment allows you to grant the DBA role to a migration user and the Oracle database version is earlier than 12c, you can execute the following statement to grant the DBA privileges to the migration user:

GRANT DBA TO <user_name>;

Granting non-DBA privileges to a user in an Oracle database of a version earlier than 12c

If your environment is cautious about granting privileges to a migration user and the Oracle database version is earlier than 12c, perform the following steps:

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.

   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 schema name to be migrated is the same as the user_name, execute the following statement:

   GRANT CREATE SEQUENCE, CREATE VIEW TO <user_name>;
   

   If the schema name to be migrated is different from the 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>;
   # Grant the DELETE, INSERT, and UPDATE privileges on the tables to be migrated to the migration user.
   GRANT DELETE, INSERT, UPDATE ON <database name>.<table name> TO <user_name>;
   

Granting DBA privileges to a user in Oracle Database 12c and later

If your environment allows you to grant the Database Administrator (DBA) role to a migration user and your Oracle database is of version 12c or later, you need to determine whether to use a pluggable database (PDB) of 12c, 18c, or 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 migration user the SELECT privilege on the SYS.USER$ table.

     GRANT SELECT ON SYS.USER$ TO <user_name>;
     

• PDB

  If you migrate an Oracle database of version 12c, 18c, or 19c to OceanBase Database and the source database is a PDB, the account used to pull the PDB must be a common user.

  1. Execute the following statement to switch to the root container.

     ALTER SESSION SET CONTAINER=CDB$ROOT;
     

     A common user can connect to the root container (named CDB$ROOT) and any PDB to which it has access 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 migration user the SELECT privilege on the SYS.USER$ table.

     GRANT SELECT ON SYS.USER$ TO C##XXX CONTAINER=ALL;
     

Granting privileges to non-DBA users in Oracle Database 12c and later

If your environment grants privileges to migration users cautiously and your Oracle database is of version 12c or later, perform the following steps:

• Non-PDB

  1. Grant the CONNECT privilege.

     GRANT CONNECT TO <user_name>;
     

  2. Execute the following statement to grant the migration user the SELECT privilege on the SYS.USER$ table.

     GRANT SELECT ON SYS.USER$ TO <user_name>;
     

  3. Grant the migration user the CREATE SESSION, ALTER SESSION, SELECT ANY TRANSACTION, SELECT ANY TABLE, and SELECT ANY DICTIONARY privileges.

     GRANT CREATE SESSION, ALTER SESSION, SELECT ANY TRANSACTION, SELECT ANY TABLE, SELECT ANY DICTIONARY TO <user_name>;
     

  4. Grant the migration user the LOGMINER privilege.

     GRANT LOGMINING TO <user_name>;
     GRANT EXECUTE ON SYS.DBMS_LOGMNR TO <user_name>;
     

  5. Grant the migration user the CREATE TABLE and UNLIMITED TABLESPACE privileges.

     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 you migrate an Oracle database of version 12c, 18c, or 19c to an Oracle tenant of OceanBase Database, the account to be pulled from the source database 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 migration user the SELECT privilege on the SYS.USER$ table.

     GRANT SELECT ON SYS.USER$ TO <C##XXX> CONTAINER=ALL;
     

  3. Grant the migration user the CREATE SESSION, ALTER SESSION, SELECT ANY TRANSACTION, SELECT ANY TABLE, and SELECT ANY DICTIONARY privileges.

     GRANT CREATE SESSION, ALTER SESSION,
     SELECT ANY TRANSACTION, SELECT ANY TABLE, SELECT ANY DICTIONARY TO <C##XXX> CONTAINER=ALL;
     

  4. Grant the migration user the LOGMINER privilege.

     GRANT LOGMINING TO <C##XXX> CONTAINER=ALL;
     GRANT EXECUTE ON SYS.DBMS_LOGMNR TO <C##XXX> CONTAINER=ALL;
     

  5. Grant the migration user the CREATE TABLE and UNLIMITED TABLESPACE privileges.

     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;
     

Privileges required for OceanBase Database in Oracle compatible mode as the source

When OceanBase Database in Oracle compatible mode is used as the source, the migration/synchronization user needs the following privileges:

• For OceanBase Database in Oracle compatible mode V2.2.70 and earlier, the source migration user needs the GRANT SELECT ON *.* TO '<user_name>'; privilege.

• For OceanBase Database in Oracle compatible mode V2.2.70 and later, the source migration user needs the GRANT DBA TO '<user_name>'; privilege.

  • For OceanBase Database in Oracle compatible mode V4.0.0 and later, the source migration user also needs the SELECT privilege on DBA_OB_ARCHIVELOG.

    GRANT SELECT ON DBA_OB_ARCHIVELOG TO '<user_name>';
    

  • For OceanBase Database in Oracle compatible mode V4.2.0 and later, the source migration user also needs the SELECT privilege on DBA_OB_TABLE_LOCATIONS.

    GRANT SELECT ON DBA_OB_TABLE_LOCATIONS TO '<user_name>';
    

Privileges for OceanBase Database in Oracle compatible mode as the destination

The privileges vary depending on the version of OceanBase Database in Oracle compatible mode as the destination.

Privileges for OceanBase Database in Oracle compatible mode V2.2.5 or V2.2.3

You can grant privileges to the migration user in the following two ways:

• Method 1

  • Execute the following statement to grant all privileges to the migration user. This method is simple but grants extensive privileges.

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

• Method 2

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

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

  2. Grant various privileges on business database tables to the migration user. If there are multiple business databases, grant privileges to each database separately.

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

Privileges for OceanBase Database in Oracle compatible mode V2.2.7 and later

You can grant privileges to the migration user in the following two ways:

• Method 1

  Execute the following statement. This method is simple but grants extensive privileges.

  GRANT DBA TO '<user_name>';
  

• Method 2

  Grant various privileges on business database tables to the migration user. If there are multiple business databases, grant privileges to each database 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>';
  

Privileges required for DB2 LUW databases as source and target databases

When you use DB2 LUW databases as source and target databases, the migration user must have the administrator (DBADM) privilege.

1. Connect to the target database.

   db2 CONNECT TO <database_name>
   

2. Grant the database administrator privilege to the migration user.

   db2 GRANT DBADM ON DATABASE TO USER <user_name>;
   

Privileges required when a PostgreSQL database serves as the source

When you migrate the schema of a PostgreSQL database to a MySQL compatible mode of OceanBase Database, you must grant the SELECT privilege on tables and views to the migration user.

When you incrementally synchronize data from a PostgreSQL database to a MySQL compatible mode of OceanBase Database, you must grant the following privileges to the migration user:

• If the whitelist of the tables to be migrated contains wildcards, you must use a superuser. Otherwise, an error will be returned when you create a publication. If the whitelist does not contain wildcards, you can use a non-superuser.

• You must have 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>;

• You must be the owner of the tables to be migrated.

  // Create a replication group.
  CREATE ROLE <replication_group>;  
  // Add the original owner of the tables to be migrated to the replication group.
  GRANT <replication_group> TO <original_owner>; 
  // Add the migration user to the replication group.
  GRANT <replication_group> TO <replication_user>; 
  // Change the owner of the tables to be migrated to the new replication group.
  ALTER TABLE <table_name> OWNER TO <replication_group>; 
  

• If you select Allow OMS to automatically write heartbeat data to this instance to resolve high latency in scenarios where no business data is written to the source when you add a PostgreSQL data source, OMS will create and update the oms_postgres_heartbeat table in the corresponding 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 for TiDB as the source database

The migration user must have the full SELECT privilege on the TiDB database.

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

DataHub as the destination

DataHub authenticates based on the endpoint, access key, and secret key. For more information, see DataHub permission control.

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

Permissions when Kafka is the target

If Kafka has authentication, see Create a Kafka data source.

When Kafka is the target, the synchronization user must have the following permissions:

• Create and view Topics.

• View Topic Partition information.

• Write Records.

• Read Records.

Permissions required when RocketMQ is the destination

When RocketMQ is the destination, the user must have the following permissions:

• Create and view Topics

• View Topic MessageQueue information

• Write Records

• Read Records