User privileges for data migration|OceanBase Cloud| docs|Distributed Database

User privileges for data migration

Last Updated：2026-01-06 02:55:27 Updated

Before you use the data migration feature to migrate data, make sure that a database user with the required privileges has been created in each data source.

OceanBase MySQL Compatible

OceanBase Oracle Compatible

MySQL

Oracle

Kafka

Source privileges

When the source is a Dedicated (Analytical) instance of OceanBase Database in MySQL compatible mode, you must create an OceanBase Database user.

When the source is a Self-managed Database of OceanBase Database in MySQL compatible mode, you must create an OceanBase Database user and a sys tenant user (optional).

Create an OceanBase Database user and grant privileges to it

The OceanBase Database user created here is used for filling in the Database Account when creating an OceanBase MySQL Compatible Mode data source with the Instance Type set to Dedicated (Analytical) or Self-managed Database.

-- Create a user in the source business tenant for data migration.
CREATE USER '<user_name>' IDENTIFIED BY '<password>';

-- Grant the SELECT privilege on the source database to the created database user by using the GRANT statement.
GRANT SELECT ON <database_name>.* TO '<user_name>';

-- For OceanBase Database in MySQL compatible mode V4.0.0 and later, you also need to add the SELECT privilege on the oceanbase database.
GRANT SELECT ON oceanbase.* TO '<user_name>';

Create a sys tenant user and grant privileges to it

If you choose an OceanBase self-managed database as the source and need to read incremental log data and database object structure information, create a user in the sys tenant of the source.

The sys tenant user created here is used for filling in the Sys Account when creating an OceanBase MySQL Compatible Mode data source with the Instance Type set to Self-managed Database and the Advanced Settings > Sys Tenant Account option enabled.

Log in to OceanBase Database as a system administrator and execute the following commands.
```
-- Create a user in the sys tenant for reading incremental log data and database object structure information from OceanBase Database.
CREATE USER <drc_user> IDENTIFIED BY '<drc_password>';

-- Grant the SELECT ON *.* privilege to the created database user by using the GRANT statement.
GRANT SELECT ON <database_name>.* TO <drc_user>;
```

Target privileges

The OceanBase Database user created here is used to fill in the Database Account field when you create an OceanBase MySQL compatible data source.

-- Create a user for data migration in the OceanBase Database.
CREATE USER '<username>' IDENTIFIED BY '<password>';

-- Grant the CREATE, CREATE VIEW, SELECT, INSERT, UPDATE, ALTER, INDEX, and DELETE privileges on the target database.
GRANT CREATE,CREATE VIEW,SELECT,INSERT,UPDATE,ALTER,INDEX,DELETE ON <database_name>.* TO '<user_name>';

-- Grant the SELECT privilege on all tables of the entire tenant.
GRANT SELECT ON *.* TO '<user_name>';

For versions of OceanBase Database MySQL compatible mode from V4.2.4 to V4.3.0 (excluding V4.3.0) or from V4.3.3 onwards, if these versions are used as the target:

The migration user must have the TRIGGER privilege to check whether triggers exist in the target database. If triggers exist, it may cause data inconsistency.
If you select schema migration when creating a new data migration task, and the database's table structure includes foreign key constraints, the migration user also needs the REFERENCES privilege on the database where the referenced tables are located.

You can grant the TRIGGER and REFERENCES privileges on the corresponding database, or grant the global TRIGGER and REFERENCES privileges.
- Grant privileges on the corresponding database
```
GRANT TRIGGER,REFERENCES ON <database_name>.* TO '<user_name>';
```
- Grant global privileges
```
GRANT TRIGGER,REFERENCES ON *.* TO '<user_name>';
```

Source privileges

When you use an Dedicated (Analytical) instance of OceanBase Database in Oracle compatible mode as the source, you must create an OceanBase user.

When you use an Self-managed Database of OceanBase Database in Oracle compatible mode as the source, you must create an OceanBase user and a sys tenant user (optional).

Create an OceanBase user and grant privileges to it

The OceanBase user created here is used to fill in the Database Account when you create an OceanBase data source in Oracle compatible mode with the Instance Type set to Dedicated (Analytical) or Self-managed Database.

-- Create a user in the source business tenant for data migration.
CREATE USER '<user_name>' IDENTIFIED BY <password>;

-- Grant the DBA privilege to the created database user by using the GRANT statement.
GRANT DBA TO '<user_name>';

-- For OceanBase Database in Oracle compatible mode V4.0.0 and later, the source migration user must also have 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 must also have the SELECT privilege on DBA_OB_TABLE_LOCATIONS.
GRANT SELECT ON DBA_OB_TABLE_LOCATIONS TO '<user_name>';

Create a sys tenant user and grant privileges to it

If you choose an OceanBase self-managed database and need to read incremental log data and database object structure information, create a user in the sys tenant of the source.

The sys tenant user created here is used to fill in the Sys Account when you enable Advanced Settings > Sys Tenant Account for creating an OceanBase data source in Oracle compatible mode with the Instance Type set to Self-managed Database.

Log in to OceanBase Database as the system administrator and execute the following commands.
```
-- Create a user in the sys tenant for reading incremental log data and database object structure information from OceanBase Database.
CREATE USER <drc_user> IDENTIFIED BY '<drc_password>';

-- Grant the SELECT ON *.* privilege to the created database user by using the GRANT statement.
GRANT SELECT ON <database_name>.* TO <drc_user>;
```

Target privileges

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

Method 1

Execute the following statement. This method is simple but grants the user extensive privileges.
```
GRANT DBA TO '<user_name>';
```

Method 2

Grant the user various privileges on business databases. If multiple business databases exist, grant privileges separately for each database.

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

Source privileges

-- The database user must have read privileges on the database to be migrated. For MySQL V8.0, you also need to grant the SHOW VIEW privilege.
GRANT SELECT ON <database_name>.* TO '<user_name>';

-- During incremental synchronization in a MySQL database, the database user must have the REPLICATION CLIENT, REPLICATION SLAVE, and SELECT *.* privileges.
-- If the source database user lacks read privileges on all tables during incremental synchronization, the task may abnormally terminate. The [WITH GRANT OPTION] parameter is optional.
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO '<user_name>' [WITH GRANT OPTION];
GRANT SELECT ON *.* TO '<user_name>';

Target privileges

When the target is a MySQL database, the migration user must have the following privileges on the target database: CREATE, CREATE VIEW, INSERT, UPDATE, and DELETE.

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

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

The required privileges for forward migration when the source is an Oracle database and for reverse migration when the target is an Oracle database are the same.

Note

For standby databases in Active Data Guard (ADG) mode, the granted privileges may not take effect. In this case, you need to execute the ALTER SYSTEM FLUSH SHARED_POOL; command on the standby database to refresh the Shared Pool.
The privileges described in this topic are not minimal. You need to grant the SELECT ANY TRANSACTION, SELECT ANY TABLE, and SELECT ANY DICTIONARY privileges to the migration user.

Privileges for DBA users in versions earlier than 12c

If your environment allows granting the Database Administrator (DBA) role to the migration user and your Oracle database version is earlier than 12c, you only need to execute the following statement to grant the DBA privileges to the migration user:

GRANT DBA TO <user_name>;

Granting privileges to non-DBA users before migrating data from an Oracle database of a version earlier than 12c

If you want to grant privileges to the migration user in a cautious manner, perform the following operations:

Grant the CONNECT privilege.
```
GRANT CONNECT TO <user_name>;
```
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>;
```

Grant the LOGMINER privilege to the migration user.

GRANT EXECUTE ON SYS.DBMS_LOGMNR TO <user_name>;

Grant the CREATE TABLE and UNLIMITED TABLESPACE privileges to the migration user.
```
GRANT CREATE TABLE, UNLIMITED TABLESPACE TO <user_name>;
```

If the schema name to be migrated is the same as user_name, execute the following statement.

GRANT CREATE SEQUENCE,CREATE VIEW TO <user_name>;

If the schema name 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 tables to be migrated to the specified Oracle database.
GRANT DELETE, INSERT, UPDATE ON <database name>.<table name> TO <user_name>;

Granting DBA privileges to the migration user in 12c or later versions

If your environment allows you to grant the database administrator (DBA) role to the migration user and your Oracle database is of version 12c or later, you need to determine whether you are using a pluggable database (PDB) of 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 migration user SELECT privileges on the SYS.USER$ table.
```
GRANT SELECT ON SYS.USER$ TO <user_name>;
```
PDB

If you migrate an Oracle database of 12c, 18c, or 19c that is a pluggable database (PDB) to the Oracle compatible mode of OceanBase Database, the common user must be used to pull the PDB account.
1. Execute the following statement to switch to the root container named CDB$ROOT.
```
ALTER SESSION SET CONTAINER=CDB$ROOT;
```
  A common user can connect to the root container (CDB$ROOT) and any PDB with connection privileges and perform relevant 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 migration user SELECT privileges on the SYS.USER$ table.
```
GRANT SELECT ON SYS.USER$ TO C##XXX CONTAINER=ALL;
```

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

If your environment is cautious about granting privileges to the migration user and you are using Oracle Database 12c or later, follow these steps:

Non-PDB

Grant the CONNECT privilege.
```
GRANT CONNECT TO <user_name>;
```
Execute the following statement to grant the migration user SELECT privileges on the SYS.USER$ table.
```
GRANT SELECT ON SYS.USER$ TO <user_name>;
```
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>;
```

Grant the migration user LOGMINER privileges.

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

Grant the migration user CREATE TABLE and UNLIMITED TABLESPACE privileges.
```
GRANT CREATE TABLE, UNLIMITED TABLESPACE TO <user_name>;
```

If the name of the schema being migrated matches the user_name, execute the following statement.

GRANT CREATE SEQUENCE,CREATE VIEW TO <user_name>;

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

PDB

If you are migrating from an Oracle Database 12c, 18c, or 19c pluggable database (PDB) to an Oracle-compatible mode of OceanBase Database, the account used to pull 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 migration user SELECT privileges 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 LOGMINER privileges.

Core Principles

Prioritize target-language idioms over literal translation
Preserve tone, intent, and contextual nuance

Prioritize target-language idioms over literal translation
Preserve tone, intent, and contextual nuance

Create a TiDB database user

Use the CREATE USER statement to create a new user.

CREATE USER '<user_name>'@'<host_name>' IDENTIFIED BY '<user_password>';

Parameter	Description
user_name	The name of the user to be created.
host_name	The host from which the user can log in. If the user can log in from any host, use the percent sign (%).
user_password	The password for the user to be created.

For example, create a user named test who can log in from any host with the password password.

CREATE USER 'test'@'%' IDENTIFIED BY 'password';

Use the GRANT statement to grant privileges to the created database user.

Source privileges

-- The database user must have SELECT privileges on the database to be migrated.
GRANT SELECT ON <database_name>.* TO '<user_name>';

-- When synchronizing data incrementally from the TiDB database, the database user must have 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>';

Target privileges

When using the TiDB database as the target, the migration user must have the CREATE, CREATE VIEW, INSERT, UPDATE, and DELETE privileges.

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

Parameter	Description
privilege_type	The privileges to grant to the account, such as CREATE, INSERT, and UPDATE. To grant all privileges, use ALL.
database_name	The name of the database. To grant privileges on all databases, use the asterisk (*).
table_name	The name of the table. To grant privileges on all tables, use the asterisk (*).
user_name	The account to be granted privileges.
host_name	The host from which the account can log in. If the account can log in from any host, use the percent sign (%).
WITH GRANT OPTION	Grants the account the ability to use the `GRANT` command. This parameter is optional.

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

When using Kafka as the target, the migration user must have the following privileges:

Create and view Topics
View Topic Partition information
Write Records
Read Records