User permissions required for OceanBase database migration-ApsaraDB for OceanBase (Deprecated)(OceanBase)-阿里云帮助中心

Before you migrate a database using Data Transmission Service, you must create a database user for each data source. This user performs the migration or synchronization and must have the required permissions for both the source and destination data sources.

Permissions for an OceanBase database in MySQL-compatible mode as a source

When an OceanBase database in MySQL-compatible mode is the source, the migration or synchronization user requires the following permissions:

If the destination is a message queue, such as Kafka, DataHub, or RocketMQ, you must have the SELECT permission on the source database to be synchronized.
If the destination is a database, such as a MySQL database or an OceanBase database in MySQL-compatible mode, you must have the SELECT permission on the source database to be migrated and the oceanbase database.
```
GRANT SELECT ON <database_name>.* TO '<user_name>';
GRANT SELECT ON oceanbase.* TO '<user_name>';
```
Important
The SELECT permission on the oceanbase database is required only for OceanBase Database V4.0.0 and later versions.
When you migrate data between OceanBase databases in MySQL-compatible mode, if a reverse incremental migration task exists, the migration user must also have the CREATE, SELECT, INSERT, UPDATE, and DELETE permissions on the source test database.
```
GRANT CREATE,SELECT,INSERT,UPDATE,DELETE ON test.* TO '<user_name>';
```
For incremental data synchronization of an OceanBase database in a VPC, you must create a user in the source sys tenant and grant that user the SELECT ON *.* permission. This permission is required to read incremental log data and database object structure information.
```
GRANT SELECT ON <database_name>.* TO <drc_user>;
```

Permissions for an OceanBase database in MySQL-compatible mode as a destination

When an OceanBase database in MySQL-compatible mode is the destination, the migration user requires the following permissions:

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

Permissions for an OceanBase database in Oracle-compatible mode as a source

When an OceanBase database in Oracle-compatible mode is the source, the migration or synchronization user requires the following permissions:

For versions of OceanBase Database in Oracle-compatible mode earlier than V2.2.70, the source user requires the permission granted by the following statement: GRANT SELECT ON *.* TO '<user_name>';.
For OceanBase Database in Oracle-compatible mode V2.2.70 and later, the source user requires the permission granted by the following statement: GRANT DBA TO '<user_name>';.
- For OceanBase Database in Oracle-compatible mode V4.0.0 and later, the source migration user also requires the SELECT permission 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 permission on DBA_OB_TABLE_LOCATIONS.
```
GRANT SELECT ON DBA_OB_TABLE_LOCATIONS TO '<user_name>';
```

Permissions for an OceanBase database in Oracle-compatible mode as a destination

When you migrate data from an Oracle database to an OceanBase database in Oracle-compatible mode, the required permissions vary based on the version of the destination database.

Destination version

Permission description

V2.2.5 or V2.2.3

Grant permissions to the migration user in one of the following two ways:

Method 1
- Run the following statement to grant all permissions to the migration user. This method is simple but grants extensive permissions.
```
GRANT ALL PRIVILEGES ON *.* TO '<user_name>';
```
Method 2
1. If the OceanBase database is in a VPC, you need to grant the migration user the SELECT permission on the system views under the sys tenant.
```
GRANT SELECT ON SYS.* TO '<user_name>';
```
2. Grant the migration user various permissions on the tables of the business database. If there are multiple business databases, grant permissions for each one 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

Grant permissions to the migration user in one of the following two ways:

Method 1
Run the following statement. This operation is simple but grants extensive permissions to the user.
```
GRANT DBA TO '<user_name>';
```

Method 2

Grant the user various permissions on the tables of the business database. If there are multiple business databases, grant permissions for each one 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>';

Permissions for an Oracle database as a source

After you create the user, the required permissions vary based on the Oracle Database version and the user role.

Note

For Active Data Guard (ADG) secondary databases, permissions may sometimes not take effect after they are granted. In this case, you need to execute the ALTER SYSTEM FLUSH SHARED_POOL; command on the secondary database to refresh the Shared Pool.
This topic describes non-minimal permissions. You must grant the migration user the SELECT ANY TRANSACTION, SELECT ANY TABLE, and SELECT ANY DICTIONARY permissions. When you use an Oracle database as the source, you can refine the permissions that use the `ANY` keyword to grant minimal permissions to the migration user and improve security. For more information, see Minimal permissions for a source Oracle database.

Permissions for a DBA user on versions earlier than 12c

If your environment lets you grant the database administrator (DBA) role to the migration user and the Oracle Database version is earlier than 12c, you can run the following statement to grant the DBA role to the migration user.

GRANT DBA TO <user_name>;

Permissions for a non-DBA user on versions earlier than 12c

If your environment requires strict authorization for the migration user and the Oracle Database version is earlier than 12c, perform the following steps:

Grant the CONNECT permission.
```
GRANT CONNECT TO <user_name>;
```
Grant the migration user the CREATE SESSION, ALTER SESSION, SELECT ANY TRANSACTION, SELECT ANY TABLE, and SELECT ANY DICTIONARY permissions.
```
GRANT CREATE SESSION, ALTER SESSION, 
SELECT ANY TRANSACTION, SELECT ANY TABLE, SELECT ANY DICTIONARY TO <user_name>;
```

Grant the migration user the LOGMINER permissions.

GRANT EXECUTE ON SYS.DBMS_LOGMNR TO <user_name>;

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

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

GRANT CREATE SEQUENCE,CREATE VIEW TO <user_name>;

If the schema name for the migration does not match 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 run 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>;
# Add the specific tables to be migrated to the Oracle database.
GRANT DELETE, INSERT, UPDATE ON <database_name>.<table_name> TO <user_name>;

Permissions for a DBA user on 12c and later

If your environment lets you grant the DBA role to the migration user and the Oracle Database version is 12c or later, the required permissions depend on whether you use a Pluggable Database (PDB).

Non-PDB
1. Run the following statement to grant the DBA role to the migration user.
```
GRANT DBA TO <user_name>;
```
2. Execute the following statement to grant read permission on the SYS.USER$ table to the migration user.
```
GRANT SELECT ON SYS.USER$ TO <user_name>;
```
PDB
If the source for data migration is a PDB in Oracle Database 12c, 18c, or 19c, the account used to pull data from the PDB must be a Common User.
1. Run the following statement to switch to `CDB$ROOT`.
```
ALTER SESSION SET CONTAINER=CDB$ROOT;
```
  Each common user can connect to the root container, which is named CDB$ROOT, and any PDB to which they have connection permissions, and then perform authorized operations.
2. Run 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 migration user read permission on the SYS.USER$ table.
```
GRANT SELECT ON SYS.USER$ TO C##XXX CONTAINER=ALL;
```

Permissions for a non-DBA user on 12c and later

If your environment requires strict authorization for the migration user and the Oracle Database version is 12c or later, perform the following steps:

Non-PDB

Grant the CONNECT permission.
```
GRANT CONNECT TO <user_name>;
```
Execute the following statement to grant the migration user read permission 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 permissions.
```
GRANT CREATE SESSION, ALTER SESSION, SELECT ANY TRANSACTION, SELECT ANY TABLE, SELECT ANY DICTIONARY TO <user_name>;
```

Grant the migration user the LOGMINER permissions.

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

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

If the schema name for the migration is the same as user_name, execute the following statement.

GRANT CREATE SEQUENCE,CREATE VIEW TO <user_name>;

If the schema name for the migration does not match 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 the source for data migration is a PDB in Oracle Database 12c, 18c, or 19c, the account used to pull data from the PDB must be a Common User.

Grant the CONNECT permission.

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

Execute the following statement to grant the migration user read permission 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 permissions.
```
GRANT CREATE SESSION, ALTER SESSION, 
SELECT ANY TRANSACTION, SELECT ANY TABLE, SELECT ANY DICTIONARY TO <C##XXX> CONTAINER=ALL;
```

Grant the migration user the permissions related to LOGMINER.

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

Grant the migration user CREATE TABLE and UNLIMITED TABLESPACE permissions.
```
GRANT CREATE TABLE, UNLIMITED TABLESPACE TO <C##XXX> CONTAINER=ALL;
```

If the schema name for migration matches C##XXX, execute the following statement.

GRANT CREATE SEQUENCE,CREATE VIEW TO <C##XXX> CONTAINER=ALL;

If the schema name for migration does not match 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;

Permissions for a MySQL database as a source

The database user must have read permissions on the source database. For MySQL V8.0 databases, you must also grant the SHOW VIEW permission.
```
GRANT SELECT ON <database_name>.* TO '<user_name>';
```
When you perform incremental synchronization for a MySQL database, the database user must have the REPLICATION CLIENT, REPLICATION SLAVE, and SELECT *.* permissions.
```
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO '<user_name>' [WITH GRANT OPTION];
GRANT SELECT ON *.* TO '<user_name>';
```
Note
- During incremental synchronization, if the user lacks read permission on all source tables, the project may stop unexpectedly.
- WITH GRANT OPTION is an optional parameter.
When you migrate data from a MySQL database to an OceanBase database in MySQL-compatible mode, if a reverse incremental migration task is used, the migration user must also have the CREATE, SELECT, INSERT, UPDATE, and DELETE permissions on the source test database.
```
GRANT CREATE,SELECT,INSERT,UPDATE,DELETE ON test.* TO '<user_name>';
```

Permissions for a TiDB database as a source

The database user requires read permission on the database to be migrated.
```
GRANT SELECT ON <database_name>.* TO '<user_name>';
```
For incremental synchronization from a TiDB database, the database user must have the REPLICATION CLIENT, REPLICATION SLAVE, and SELECT *.* permissions.
```
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO '<user_name>' [WITH GRANT OPTION];
GRANT SELECT ON *.* TO '<user_name>';
```

Permissions for an ADB database as a destination

AnalyticDB for MySQL (ADB) lets you grant permissions at different levels of granularity for access control. For more information, see Permission model.

ADB supports access control at the following four levels of granularity:

GLOBAL: Cluster level
DB: Database level
TABLE: Table level
COLUMN: Column (field) level

Permissions for Kafka as a destination

If authentication is enabled for Kafka, see Create a Kafka data source.

The Kafka user requires permissions to perform the following operations:

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

Permissions for DataHub as a destination

DataHub uses an endpoint, AccessKey ID, and AccessKey secret for authentication. For more information, see Access control.

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

Permissions for RocketMQ as a destination

When RocketMQ is the destination, the synchronization user requires permissions to perform the following operations:

Create and view topics
View topic MessageQueue information
Write records
Read records