User privileges for data migration|OceanBase Cloud

User privileges for data migration

Last Updated：2026-04-21 06:20:29 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

PostgreSQL

TiDB

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.

Log in to OceanBase Database as a system administrator and execute the following commands.
```
-- 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>';
```
Notice

For OceanBase Database in MySQL compatible mode earlier than V4.0.0, you need to add the SELECT privilege on oceanbase.gv$table if the oceanbase.* privilege is not added.
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 *.* 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, 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>';

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

When you perform schema migration, the migration user must have the following privileges:
- To migrate views, the migration user must have the SELECT privilege on the database.
```
GRANT SELECT ON <database_name>.* TO '<user_name>';
```
- To migrate indexes, the migration user must have the INDEX privilege on the database.
```
GRANT INDEX ON <database_name>.* TO '<user_name>';
```
The migration user must have the trigger privilege.

This privilege is used to check whether triggers exist on the target database. If triggers exist, data inconsistency may occur.
```
GRANT TRIGGER ON *.* TO '<user_name>';
```
or
```
GRANT TRIGGER ON <database_name>.* TO '<user_name>';
```
In a bidirectional synchronization task from an OceanBase Database in MySQL compatible mode to a MySQL database, the migration user must have the SELECT, CREATE, INSERT, and UPDATE privileges on the oms database.
```
GRANT SELECT, CREATE, INSERT, UPDATE ON oms.* TO '<user_name>';
```

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.

Grant the CONNECT privilege.

GRANT CONNECT TO <C##XXX> CONTAINER=ALL;

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

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;

Grant the migration user LOGMINER privileges.

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

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

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

If the schema name 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 schema name 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;

Source privileges

When you use a PostgreSQL database as the source database for data migration, the privileges required for the migration user vary depending on whether incremental synchronization is required.

Schema migration or full migration

If the data migration task does not include incremental synchronization, the migration user must have the SELECT privilege on the source database. The authorization statement is as follows.
```
GRANT CONNECT ON DATABASE <database_name> TO <user_name>;
GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;
```
Incremental synchronization

If the data migration task contains incremental synchronization, the migration user must meet one of the following conditions.

Method 1: Superuser (recommended for self-managed PostgreSQL).
```
-- Create a user.
CREATE USER <user_name> WITH PASSWORD '<password>';

-- Grant the superuser privilege.
ALTER USER <user_name> WITH SUPERUSER;
```
Method 2: High-privilege account provided by the cloud vendor (for RDS PostgreSQL).

RDS PostgreSQL instances of different cloud vendors do not allow the creation of real superusers, but provide high-privilege accounts with equivalent permissions.
- Alibaba Cloud RDS PostgreSQL: The database user must have the pg_rds_superuser role. You can run the following statement to verify the role.
```
SELECT pg_has_role('<user_name>', 'pg_rds_superuser', 'member');
```
- AWS RDS PostgreSQL: The database user must have the rds_superuser role.
- GCP Cloud SQL PostgreSQL: The database user must have the cloudsqlsuperuser role, and the replication privilege must be granted.
```
ALTER USER <user_name> WITH REPLICATION;
```
- Huawei Cloud RDS PostgreSQL: The database user is the default high-privilege account root.
Note
- Incremental synchronization requires access to the logical replication feature of the PostgreSQL instance. Therefore, the user must have superuser privileges or equivalent high privileges provided by the cloud vendor.
- For RDS PostgreSQL instances of cloud vendors, some configurations need to be modified in the corresponding cloud console.

Target privileges

When you use a PostgreSQL database as the target database, the migration user must have the following privileges.

Database connection privilege

GRANT CONNECT ON DATABASE <database_name> TO <user_name>;

Privilege to use the target schema

GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;

Privilege to write to the target table

-- Grant privileges to existing tables.
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;

-- Automatically grant privileges to tables created in the future.
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name>
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO <user_name>;

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

OceanBase

Customer Stories

Documentation

User privileges for data migration

Source privileges

Notice

Target privileges

Source privileges

Target privileges

Source privileges

Target privileges

Note

Privileges for DBA users in versions earlier than 12c

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

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

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

Source privileges

Note

Target privileges

Create a TiDB database user

Source privileges

Target privileges