User privileges|OceanBase Cloud| docs|Distributed Database

User privileges

Last Updated：2025-09-18 06:48:58 Updated

Before you use the data migration feature to migrate databases, the data archiving feature to archive data, or the data cleanup feature to clean up data, make sure that a database user has been created for each data source as the compatibility assessment, migration, performance assessment, archiving, cleanup, or synchronization user. This user must have the required privileges for both the source and target data sources.

User privileges required for compatibility assessment

Before you use the compatibility assessment feature to assess database objects, make sure that a database user has been created for each data source and that the user has the required privileges.

Run the following command to create a database user.

CREATE USER '<user_name>' IDENTIFIED BY '<password>';

Parameter	Description
user_name	The name of the user to be created.
password	The password of the user to be created.

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

The compatibility assessment user must have the read privilege on the database to be assessed. The grant statement is as follows.

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

Parameter	Description
database_name	The name of the database on which the read privilege is to be granted.
user_name	The name of the database user on which the read privilege is to be granted.

If you select Profile Assessment as the assessment type when you create an online assessment task, you must grant the user the SELECT privilege on sys.*.

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

User privileges required for data migration

OceanBase MySQL compatible mode

OceanBase Oracle compatible mode

MySQL

Oracle

Kafka

User privileges for source databases

If OceanBase Database in MySQL compatible mode serves as the source, you must create an OceanBase user.

If the self-managed OceanBase Database in MySQL compatible mode serves as the source, you must create an OceanBase user and a sys tenant user (optional).

Create an OceanBase user and grant privileges

The OceanBase user created here is used to fill in the Database Account field when you create an OceanBase MySQL Compatible Mode data source with the Dedicated (Transactional) or Self-managed Database instance type. For example, you can set the database account to OMSTEST.
1. Log in to OceanBase Database as the system administrator.
2. Create a user in the source business tenant for data migration.
```
CREATE USER '<user_name>' IDENTIFIED BY '<password>';
```
3. Execute the GRANT statement to grant the SELECT privilege on the source database to the created database user.
```
GRANT SELECT ON <database_name>.* TO '<user_name>';
```
  You need to grant the SELECT privilege on the oceanbase database for the MySQL compatible mode of OceanBase Database V4.0.0 and later.
```
GRANT SELECT ON oceanbase.* TO '<user_name>';
```
Create a sys tenant user and grant privileges

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

The sys tenant user created here is used to fill in the Advanced Settings > sys Tenant Account field when you create an OceanBase MySQL Compatible Mode data source with the Self-managed Database instance type and enable Sys Account. For example, you can set the sys account to user01.
1. Log in to OceanBase Database as the system administrator.
2. Create a user in the sys tenant for reading incremental log data and database object schema information from OceanBase Database.
```
CREATE USER <drc_user> IDENTIFIED BY '<drc_password>';
```
3. Execute the GRANT statement to grant the SELECT ON *.* privilege to the created database user.
```
GRANT SELECT ON <database_name>.* TO <drc_user>;
```

User privileges for target databases

The OceanBase user created here is used to fill in the Database Account field when you create an OceanBase MySQL Compatible Mode data source. For example, you can set the database account to OMSTEST.

Log in to OceanBase Database as a system administrator.
Create a user in OceanBase Database for data migration.
```
CREATE USER '<username>' IDENTIFIED BY '<password>';
```
Use the GRANT statement to grant permissions to the database user.
- The user must have 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>';
```
- The user must have the SELECT privilege on the entire tenant.
```
GRANT SELECT ON *.* TO '<user_name>';
```
- If the MySQL compatible mode of OceanBase Database V4.2.4 or later, but earlier than V4.3.0, or V4.3.3 or later serves as the target database:
  - The migration user must have the trigger privilege to check whether a trigger exists in the target database. Otherwise, data may be inconsistent.
  - If you select schema migration when you create a data migration task, and the schema of the database contains foreign key constraints, the migration user must have the REFERENCES privilege on the database that contains the tables referenced by the foreign keys.
  You can grant the trigger and REFERENCES privileges on the corresponding database, or 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>';
```

User privileges for source databases

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

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

Create an OceanBase user and grant privileges

The OceanBase user created here is used to specify the Database Account field when you create an OceanBase Oracle compatible mode data source with the Instance Type parameter set to Dedicated (Transactional) or Self-managed Database. For example, you can set the database account to OMSTEST.
1. Log in to OceanBase Database as the system administrator.
2. Create a user in the source business tenant for data migration.
```
CREATE USER '<user_name>' IDENTIFIED BY <password>;
```
3. Use the GRANT statement to grant the DBA privilege to the created database user.
```
GRANT DBA TO '<user_name>';
```
  - For OceanBase Database V4.0.0 and later in Oracle compatible mode, the source migration user must also have the SELECT privilege on the DBA_OB_ARCHIVELOG table.
```
GRANT SELECT ON DBA_OB_ARCHIVELOG TO '<user_name>';
```
  - For OceanBase Database V4.2.0 and later in Oracle compatible mode, the source migration user must also have the SELECT privilege on the DBA_OB_TABLE_LOCATIONS table.
```
GRANT SELECT ON DBA_OB_TABLE_LOCATIONS TO '<user_name>';
```
Create a sys tenant user and grant privileges

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

The sys tenant user created here is used to specify the Sys Account field when you enable Advanced Settings > sys Tenant Account for an OceanBase Oracle compatible mode data source with the Instance Type parameter set to Self-managed Database. For example, you can set the sys account to user01.
1. Log in to OceanBase Database as the system administrator.
2. Create a user in the sys tenant for reading incremental log data and database object schema information from OceanBase Database.
```
CREATE USER <drc_user> IDENTIFIED BY '<drc_password>';
```
3. Use the GRANT statement to grant the SELECT ON *.* privilege to the created database user.
```
GRANT SELECT ON <database_name>.* TO <drc_user>;
```

User privileges for target databases

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

Method 1

Execute the following statement, which is simple but grants high-level privileges:
```
GRANT DBA TO '<user_name>';
```

Method 2

Grant all types 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 for source databases

The database user must have the SELECT privilege on the database from which data is to be migrated. If the source database is MySQL V8.0, the user also needs the SHOW VIEW privilege.
```
GRANT SELECT ON <database_name>.* TO '<user_name>';
```
During incremental synchronization of 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 user does not have read privileges on all tables at the source, the task may fail.
- The WITH GRANT OPTION parameter is optional.

User privileges for target databases

If a MySQL database serves as the target, the migration 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	Specifies the privileges to grant to the account. If you want 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 which privileges are to be granted.
host_name	The host from which the account is allowed to log in. If you want to allow the account to log in from any host, use a percent sign (%).
WITH GRANT OPTION	Specifies whether to grant the account the privilege to use the `GRANT` command. This parameter is optional.

The required privileges for forward migration from an Oracle database as the source and reverse migration to an Oracle database as the target are the same.

Note

For an ADG standby database, sometimes the granted privileges do not take effect. In this case, you need to execute the ALTER SYSTEM FLUSH SHARED_POOL; command in the standby database to refresh the shared pool.
The following privileges need to be granted to the migration user: SELECT ANY TRANSACTION, SELECT ANY TABLE, and SELECT ANY DICTIONARY.

DBA user privileges for Oracle database versions before 12c

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

GRANT DBA TO <user_name>;

Non-DBA user privileges for Oracle database versions before 12c

If your environment grants privileges to the migration user cautiously and the version of your Oracle database is earlier than 12c, 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 privileges 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 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 statements:

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

DBA user privileges for Oracle databases of 12c or later versions

If you want to grant the database administrator (DBA) role to the migration user and the Oracle database version is 12c or later, you need to determine whether the database is a pluggable database (PDB) of 12c/18c/19c.

Non-PDB
1. Execute the following statement to grant the DBA role 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 the source database is a PDB of 12c/18c/19c, you must use a common user to pull data from the PDB.
1. Execute the following statement to switch to the CDB$ROOT container:
```
ALTER SESSION SET CONTAINER=CDB$ROOT;
```
  Each common user can connect to the root container (named CDB$ROOT) and any PDB to which it has connection privileges and perform operations on the PDB.
2. Execute the following statement to grant the DBA role 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;
```

Non-DBA user privileges for Oracle Database of 12c or later versions

If the environment is cautious about granting privileges to the migration user and the Oracle database version is 12c or later, follow these steps:

Non-PDB

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

GRANT LOGMINING TO <user_name>;
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 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

When you migrate data from a pluggable database (PDB) of Oracle Database 12c, 18c, or 19c to an Oracle compatible mode tenant of OceanBase Database, you need to use a common user to pull 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 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 privileges 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;
```

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

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

Create and view topics.
View topic partition information.
Write records.
Read records.

User privileges required for performance assessment

When you perform performance assessment, the required user privileges vary depending on the replay type.

When you create a performance assessment task, select Replay Type as DQL Replay on the Configure Workload Replay page. The required user privileges are as follows.
```
GRANT SELECT ON <database_name>.* TO '<username>';
```
When you create a performance assessment task, select Replay Type as DML Replay on the Configure Workload Replay page. The required user privileges are as follows.
```
GRANT INSERT, DELETE, UPDATE ON <database_name>.* TO '<username>';
```

User privileges required for data archiving

OceanBase MySQL compatible mode

OceanBase Oracle compatible mode

User privileges for source databases

Table query privilege:

GRANT SELECT ON db.table_name TO target_username;

View query privilege:

GRANT SELECT ON mysql.* TO target_username;

User privileges for target databases

Table write privilege:

GRANT SELECT, INSERT, UPDATE, DELETE ON db.table_name TO target_username;

View query privilege:

GRANT SELECT ON mysql.* TO target_username;

User privileges for source databases

Table query privilege:

GRANT SELECT ON schema_name.table_name TO target_username;

View query privilege:

GRANT SELECT ANY DICTIONARY TO target_username;

User privileges for target databases

Table write privilege:

GRANT SELECT, INSERT, UPDATE, DELETE ON schema_name.table_name TO target_username;

View query privilege:

GRANT SELECT ANY DICTIONARY TO target_username;

User privileges required for data cleanup

OceanBase MySQL compatible mode

OceanBase Oracle compatible mode

User privileges for source databases

Table query privilege:

GRANT SELECT, INSERT, UPDATE, DELETE ON db.table_name TO target_username;

View query privilege:

GRANT SELECT ON mysql.* TO target_username;

User privileges for source databases

Table write privilege:

GRANT SELECT, INSERT, UPDATE, DELETE ON schema_name.table_name TO target_username;

View query privilege:

GRANT SELECT ANY DICTIONARY TO target_username;