User privileges

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

User privileges required when a MySQL database serves as the 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 user must also have the SHOW VIEW privilege. Execute the following statement to grant the privileges to the user:

  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. Execute the following statement to grant the privileges to the user:

  GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO '<user_name>' [WITH GRANT OPTION];
  GRANT SELECT ON *.* TO '<user_name>';
  

  Note

  • If the user does not have the read privilege on all tables at the source, the task may be interrupted during incremental synchronization.

  • The WITH GRANT OPTION parameter is optional.

  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 the MySQL data source, OMS creates and updates the drc.heartbeat table in the MySQL database. In this case, the MySQL database user must have the privilege to create tables and write to tables. For more information, see Create and update a heartbeat table.

• 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

• The database 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];
  

  Parameter	Description
  privilege_type	The privileges to grant. You can grant CREATE, INSERT, UPDATE, and other operation privileges to the account. To grant all privileges to the account, set this parameter to ALL.
  database_name	The name of the database. To grant operation privileges on all databases to the account, set this parameter to an asterisk (*).
  table_name	The name of the table. To grant operation privileges on all tables to the account, set this parameter to an asterisk (*).
  user_name	The account to which privileges are granted.
  host_name	The host from which the account is allowed to log in to the database. To allow the account to log in to the database from any host, set this parameter to a percent sign (%).
  WITH GRANT OPTION	Grants the account the privilege to use the GRANT command. This parameter is optional.

• The database user must have the trigger privilege.

  This privilege is used to check whether triggers exist in the target. If triggers exist, data inconsistency may occur.

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

  or

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

User privileges required when the MySQL compatible mode of OceanBase Database serves as the source

If the MySQL compatible mode of OceanBase Database serves as the source, the migration or synchronization user must have the following privileges:

• If the target is a Kafka, DataHub, or RocketMQ instance, 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 the MySQL compatible mode of OceanBase 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 Database V4.0.0 and later.

• In an incremental data synchronization scenario, 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 the MySQL compatible mode of OceanBase Database serves as the target

To synchronize data from the MySQL compatible mode of OceanBase Database to a Kafka, RocketMQ, or DataHub instance, the migration user must have the following privileges:

• The user must have the CREATE, CREATE VIEW, DROP VIEW, SELECT, INSERT, UPDATE, ALTER, INDEX, and DELETE privileges on the target database.

  GRANT CREATE,CREATE VIEW,DROP VIEW,SELECT,INSERT,UPDATE,ALTER,INDEX,DELETE ON <database_name>.* TO '<user_name>';
  

• The user must have the SELECT privilege on the entire tenant.

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

• If the target is OceanBase Database V4.2.4 or later but earlier than V4.3.0, or OceanBase Database V4.3.3 or later:

  • The migration user must have the trigger privilege to check whether a trigger exists in the target. If a trigger exists, data may be inconsistent.

  • If you select schema migration when you create a data migration task and the database schema contains 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>';
    

User privileges required when an Oracle database serves as the source or the target

The user privileges required for forward migration when an Oracle database serves as the source are the same as those required for reverse migration when an Oracle database serves as the target. This section describes the user privileges required for different roles in different versions of Oracle databases.

User privileges required for triggers of the target Oracle databases

When an Oracle database is used as the target, in addition to assigning corresponding privileges based on different versions, the CREATE ANY TRIGGER privilege is also required.

GRANT CREATE ANY TRIGGER TO <user_name>;

User privileges required for DBA users in Oracle databases of a version earlier than 12c

If the user's environment allows the migration user to be assigned the database administrator (DBA) role and the Oracle database version is earlier than 12c, execute the following statement to grant the DBA privileges to the migration user.

GRANT DBA TO <user_name>;

User privileges required for non-DBA users in Oracle 12c and earlier

If the user environment is cautious about granting privileges to the migration user and the Oracle database version is before 12c, perform the following operations:

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.

   Note

   If the Oracle data source has configured Obtain Incremental Data through Kafka, you do not need to grant the permissions related to LOGMINER.

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

   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>;
   # Add the table to which data needs to be migrated in the Oracle database.
   GRANT DELETE, INSERT, UPDATE ON <database name>.<table name> TO <user_name>;
   

User privileges required for DBA users in Oracle Database 12c and later

If the user's environment allows them to be assigned the database administrator (DBA) role and the Oracle database version is 12c or later, they need to determine whether to use a pluggable database (PDB) of 12c, 18c, or 19c.

• Non-PDB

  1. Execute the following authorization statement to grant DBA privileges to the migration user.

     GRANT DBA TO <user_name>;
     

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

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

• PDB

  If you migrate an Oracle database from an Oracle database of a version earlier than Oracle Database 12c, 18c, or 19c, you must use a common user account to pull data from the pluggable database (PDB).

  1. Execute the following statement to switch to the CDB$ROOT:

     ALTER SESSION SET CONTAINER=CDB$ROOT;
     

     Each common user can connect to the root container named CDB$ROOT and any PDB to which they have connection privileges and perform related operations.

  2. Execute the following statement to grant DBA privileges to the migration user.

     GRANT DBA TO C##XXX CONTAINER=ALL;
     

  3. Execute the following statement to grant the read privilege on the SYS.USER$ table to the migration user.

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

User privileges required for non-DBA users in Oracle Database 12c and later

If the user environment is cautious about granting privileges to the migration user and the Oracle database version is 12c or later, perform the following operations:

• Non-PDB

  1. Grant the CONNECT privilege.

     GRANT CONNECT TO <user_name>;
     

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

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

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

  4. Grant the LOGMINER privilege to the migration user.

     Note

     If the Oracle data source has configured Obtain Incremental Data through Kafka, you do not need to grant the permissions related to LOGMINER.

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

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

     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 migrated schema 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 the source Oracle database is a pluggable database (PDB) of Oracle Database 12c, 18c, or 19c, the account used to pull data from the PDB 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 read privilege on the SYS.USER$ table to the migration user.

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

  3. 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 <C##XXX> CONTAINER=ALL;
     

  4. Grant the LOGMINER privilege to the migration user.

     Note

     If the Oracle data source has configured Obtain Incremental Data through Kafka, you do not need to grant the permissions related to LOGMINER.

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

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

     GRANT CREATE TABLE, UNLIMITED TABLESPACE TO <C##XXX> CONTAINER=ALL;
     

  6. If the name of the schema to be migrated is C##XXX, execute the following statement.

     GRANT CREATE SEQUENCE, CREATE VIEW TO <C##XXX> CONTAINER=ALL;
     

     If the name of the migrated schema is not 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;
     

User privileges required on the source when migrating a PL object

When migrating a PL object from an Oracle database to the Oracle compatible mode of OceanBase database, the migration user of the source PL object must have the following privileges:

GRANT CREATE ANY PROCEDURE TO <user_name>;
GRANT CREATE ANY TYPE TO <user_name>;
GRANT SELECT ANY SEQUENCE TO <user_name>;
GRANT CREATE ANY TRIGGER TO <user_name>;

User privileges required when the Oracle compatible mode of OceanBase Database serves as the source

When the Oracle compatible mode of OceanBase Database serves as the source, the migration/synchronization user requires the following privileges:

• For OceanBase Database Oracle compatible mode prior to V2.2.70, the privilege required for the source migration user is GRANT SELECT ON *.* TO '<user_name>';.

• For OceanBase Database Oracle compatible mode V2.2.70 or later, the privilege required for the source migration user is GRANT DBA TO '<user_name>';.

  • For OceanBase Database Oracle compatible mode V4.0.0 or 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 Oracle compatible mode V4.2.0 or 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>';
    

User privileges required when the Oracle compatible mode of OceanBase Database serves as the target

The privileges required vary with the version of the Oracle compatible mode of the target OceanBase Database.

User privileges required for the Oracle compatible mode of OceanBase Database V2.2.5 or V2.2.3

You can grant privileges to the migration user by using one of the following two methods:

• Method 1

  • Execute the following statement to grant all privileges to the migration user. This method is simple but high-level privileges are granted.

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

• Method 2

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

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

  2. Grant all types of privileges to the migration user on the business tables. If multiple business databases exist, grant the privileges separately.

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

User privileges required for the Oracle compatible mode of OceanBase Database V2.2.7 or later

You can grant privileges to the migration user by using one of the following two methods:

• Method 1

  Execute the following statement, which is simple but grants a high level of privileges to the user.

  GRANT DBA TO '<user_name>';
  

• Method 2

  Grant all kinds of privileges on business tables to the user. If there are multiple business databases, grant the privileges 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>';
  

User privileges required on the target when migrating a PL object

When migrating a PL object from an Oracle database to the Oracle compatible mode of OceanBase database, the migration user of the target must have the following privileges:

GRANT CREATE ANY PROCEDURE TO '<user_name>';
GRANT CREATE ANY SYNONYM TO '<user_name>';
GRANT CREATE ANY SEQUENCE TO '<user_name>';
GRANT CREATE ANY TRIGGER TO '<user_name>';
GRANT CREATE ANY TYPE TO '<user_name>';

User privileges required when a DB2 LUW database serves as the source or the target

The migration user must have the DBADM privilege when a DB2 LUW database serves as the source or the target.

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

User privileges required when a PostgreSQL databases serves as the source

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

During the incremental synchronization phase from a PostgreSQL database to the MySQL compatible mode database of OceanBase Database, you must grant the following privileges to the migration user:

• If the whitelist of tables to be migrated contains wildcard characters, you must use the superuser privilege, otherwise an error will be returned when the publication is created. If no wildcard character is contained, the superuser privilege is not required.

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

• The migration user must be the Owner of the table to be migrated.

  // Create replication_group.
  CREATE ROLE <replication_group>;  
  // Add the original Owner of the table to be migrated to replication_group.
  GRANT <replication_group> TO <original_owner>; 
  // Add the migration user to replication_group too.
  GRANT <replication_group> TO <replication_user>; 
  // Modify the Owner of the table to be replication_group.
  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 the PostgreSQL data source, OMS creates and updates the oms_postgres_heartbeat table in the PostgreSQL database. In this case, the PostgreSQL database user must have the privilege to create tables and write to tables. For more information, see Create and update a heartbeat table.

User privileges required when a TiDB database serves as the source

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

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

User privileges required when a DataHub instance serves as the target

Datahub instances are authenticated based on the endpoint, access key ID (AK), and secret access key (SK). For more information, see DataHub access control.

The user of a DataHub instance must have the GetProject, CreateTopic, ListTopic, GetTopic, ListShard, PutRecords, GetRecords, and GetCursor privileges.

User privileges required when a Kafka database serves as the target

If authentication is required for Kafka, see Create a Kafka data source.

When a Kafka server serves as the target, the synchronization user must have the permissions to perform the following operations:

• Create and view topics.

• View topic partition information.

• Write a record.

• Read a record.

User privileges required when a RocketMQ database serves as the target

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

• Create and view topics.

• View the topic message queue information.

• Write records.

• Read records.