User privileges required for data migration in OceanBase Database - ApsaraDB for OceanBase

Before you migrate data between databases by using the data transmission service, make sure that you have created a database user for each data source as the migration user or synchronization user. These users must have the required privileges on the source and destination data sources.

User privileges required when a MySQL tenant of OceanBase Database serves as the source data source

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

If the destination is a Kafka, DataHub, or RocketMQ instance, the user must have the SELECT privilege on the source database to be synchronized.
If the destination is a MySQL database or a MySQL tenant of OceanBase Database, the user must have the SELECT privilege on the source database to be migrated and the oceanbase database.
Important
The SELECT privilege on the oceanbase database is required only for OceanBase Database V4.0.0 and later.
In case of incremental synchronization from an OceanBase database deployed in a virtual private cloud (VPC), you must create a user in the sys tenant of the OceanBase database and grant it the SELECT ON *.* privilege.

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

If a MySQL tenant of OceanBase Database serves as the destination, the migration user must have the following privileges:

The CREATE, CREATE VIEW, SELECT, INSERT, UPDATE, ALTER, INDEX, and DELETE privileges on the destination database.
```
GRANT CREATE,CREATE VIEW,SELECT,INSERT,UPDATE,ALTER,INDEX,DELETE ON <database_name>.* TO '<user_name>';
```
The SELECT privilege on the entire tenant.
```
GRANT SELECT ON *.* TO '<user_name>';
```

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

If an Oracle tenant of OceanBase Database serves as the source, privileges must be granted to the migration or synchronization user in the following ways:

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

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

When you migrate data from an Oracle database to an Oracle tenant of OceanBase Database, the user privileges required vary with the version of OceanBase Database.

Version of OceanBase Database

Privileges

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. If OceanBase Database is deployed in a VPC, grant the migration user the SELECT privilege on system views in the sys tenant.
```
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>;
```

V2.2.7 and later

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

Method 1
Execute the following statement to grant database administrator (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, CREATE ANY INDEX, CREATE ANY VIEW, INSERT ANY TABLE, UPDATE ANY TABLE, ALTER ANY TABLE, DELETE ANY TABLE TO <user_name>;

User privileges required when an Oracle database serves as the source data source

This section describes the privileges required for different roles in different versions of Oracle databases.

Note

For the ADG standby database, granted privileges may fail to take effect. In this case, you must execute the ALTER SYSTEM FLUSH SHARED_POOL; statement in the standby database to refresh the shared pool.
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 data source, 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.

DBA privileges for migration users in Oracle database versions earlier than 12c

If the environment allows you to assign the 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>;

Non-DBA privileges for migration users in Oracle database versions 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:

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

DBA privileges for migration users in Oracle database versions 12c and later

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 12c, 18c, or 19c.

For a non-PDB database
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>;
```
For a PDB
If you migrate data from a PDB of Oracle 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 the 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;
```

Non-DBA privileges for migration users in Oracle database versions 12c and later

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:

For a non-PDB database

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

For a PDB
If you migrate data from a PDB of Oracle 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 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.
```
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 a MySQL database serves as the source data 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 SHOW VIEW privilege is also required.
```
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.
```
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO '<user_name>' [WITH GRANT OPTION];
GRANT SELECT ON *.* TO '<user_name>';
```
Note
- In an incremental synchronization project, if the database user lacks the read privilege on all tables in the source data source, the project may fail.
- The WITH GRANT OPTION parameter is optional.

User privileges required when a TiDB database serves as the source data source

The database user must have the read privilege on the database from which data is migrated.
```
GRANT SELECT ON <database_name>.* TO '<user_name>';
```
During incremental synchronization from a TiDB 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>';
```

User privileges required when an ADB serves as the destination data source

AnalyticDB for MySQL (ADB) allows you to grant different privileges by granularity for privilege control. For more information, see Privilege models.

ADB supports privilege control at the following four levels:

GLOBAL: cluster-level
DB: database-level
TABLE: table-level
COLUMN: column-level or field-level

User privileges required when a Kafka instance serves as the destination data source

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

A Kafka 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 DataHub instance serves as the destination data source

Users of DataHub data sources are authenticated by using an endpoint, an AccessKey, or a SecretKey.

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

User privileges required when a RocketMQ instance serves as the destination data source

To synchronize data to a RocketMQ instance, 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.