User privileges

Before you migrate data between databases by using OceanBase Migration Service (OMS), ensure 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 a MySQL tenant of OceanBase Database serves as the source database

To synchronize data from a MySQL tenant of OceanBase Databases to a Kafka, RocketMQ, or DataHub instance, the migration user of the source database must have the following privileges:

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

When you synchronize data from logical tables to physical tables in a MySQL tenant of OceanBase Database, the data source account is not used in the source tables. By default, the drc_user (password: drc_password) in the config.yaml file is used.

• If you need incremental synchronization, you must create the drc account in the sys tenant and grant the SELECT privilege to the account. At the same time, you must create the same account in the business tenant to be synchronized and grant the SELECT privilege to the account.

• If you need full synchronization, you must create the drc_user account under the business tenant to be synchronized and grant the SELECT privilege to the account.

User privileges required when a MySQL tenant of OceanBase Database serves as the destination database

To migrate data from a MySQL database to a MySQL tenant of OceanBase Database, the migration user in the MySQL tenant of OceanBase Database must have the following privileges:

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

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

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

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 destination. This section describes the privileges required for different roles in different versions of Oracle databases.

Note

The user privileges described in this topic are not the minimum privileges. You must grant the following privileges to users: SELECT ANY TRANSACTION, SELECT ANY TABLE, and SELECT ANY DICTIONARY.
When an Oracle database serves as the source database, you can grant the minimum privileges to the migration user to improve security. For more information, see Minimum privileges required when an Oracle database serves as the source.

Privileges required for DBA users in Oracle databases earlier than 12c

If the environment allows you to assign the database administrator (DBA) role to the migration user 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>;

Privileges required for non-DBA users in Oracle databases earlier than 12c

If the environment allows you to grant only the required privileges to the migration user and the Oracle database version is earlier than 12c, perform the following operations:

1. Grant the CONNECT privilege.

   GRANT CONNECT TO <user_name>;
   

2. Grant the migration user with 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>;
   

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

Privileges required for DBA users in Oracle Database 12c and later versions

If the environment allows you to assign the DBA role to the migration user and the version of the Oracle database is 12c or later, determine whether to use the pluggable database (PDB) of Oracle Database 12c, 18c, or 19c.

• Non-PDB

  1. Execute the following 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 data from a PDB of Oracle Database 12c, 18c, or 19c to an Oracle tenant of OceanBase Database, a common user account is required for pulling data from the PDB.

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

     ALTER SESSION SET CONTAINER=CDB$ROOT;
     

     All common users can connect to the root container named CDB$ROOT and any accessible PDB and then 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;
     

Privileges required for non-DBA users in Oracle Database 12c and later versions

If the environment allows you to grant only the required privileges to the migration user and the version of the Oracle database 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 migration user with 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 LOGMINER privilege to the migration user.

     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 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 data from a PDB of Oracle Database 12c, 18c, or 19c to an Oracle tenant of OceanBase Database, a common user account is required for pulling data from the PDB.

  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 migration user with 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 LOGMINER privilege to the migration user.

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

User privileges required when an Oracle tenant of OceanBase Database serves as the source database

To synchronize data from an Oracle tenant of OceanBase Database to a Kafka, RocketMQ, or DataHub instance:

• For OceanBase Database earlier than V2.2.70, the migration user of the source must have the GRANT SELECT ON *.* TO <user_name>; privilege.

• For OceanBase Database V2.2.70 and later, the migration user of the source must have the GRANT DBA TO <user_name>; privilege.

User privileges required when an Oracle tenant of OceanBase Database serves as the destination database

When an Oracle tenant of OceanBase Database serves as the destination, the required user privileges vary with the version of OceanBase Database.

User privileges required for an Oracle tenant 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 kinds of privileges on business tables to the migration user. 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 an Oracle tenant of OceanBase Database V2.2.7 or later versions

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

• Method 1

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

  GRANT DBA TO <user_name>;
  

• Method 2

  Grant all kinds of privileges on business tables to the migration user. If multiple business databases exist, 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,INSERT ANY TABLE,UPDATE ANY TABLE,DELETE ANY TABLE TO <user_name>;
  

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

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

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 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 DataHub instance serves as the destination database

DataHub performs authentication based on the endpoint, access key, or secret key.

A DataHub user must have the following privileges: GetProject, CreateTopic, ListTopic, GetTopic, ListShard, PutRecords, GetRecords, and GetCursor.

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

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 database

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

• Create and view topics.

• View the information about the topic message queue.

• Write records.

• Read records.