Data Transmission Service (DTS) 数据迁移要求源和目标数据库用户具备特定权限，包括 SELECT、CREATE、REPLICATION 等，具体取决于数据库类型及其版本兼容性。 - ApsaraDB for OceanBase

Before you migrate data using Data Transmission Service (DTS), you must create a database user for each data source to serve as the migration or synchronization user. This user must have the required permissions on the source and target data sources.

Permissions required when an OceanBase Database in MySQL-compatible mode is the source

When an OceanBase Database in MySQL-compatible mode is the source, the migration or synchronization user must have the following permissions:

If the target is a message queue, such as Kafka, DataHub, or RocketMQ, the user must have the SELECT permission on the source database.
If the target is a database, such as a MySQL database or an OceanBase Database in MySQL-compatible mode, the user must have the SELECT permission on the source database 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.
When you migrate data between OceanBase Database instances in MySQL-compatible mode, if reverse incremental synchronization is enabled, the migration user must also have the CREATE, SELECT, INSERT, UPDATE, and DELETE permissions on the test database.
```
GRANT CREATE,SELECT,INSERT,UPDATE,DELETE ON test.* TO '<user_name>';
```
For incremental data synchronization of an OceanBase Database instance in a VPC, you must read incremental log data and database object structure information. To do this, create a user in the sys tenant of the source instance and grant the user the SELECT ON *.* permission.
```
GRANT SELECT ON <database_name>.* TO <drc_user>;
```

Permissions required when an OceanBase Database in MySQL-compatible mode is the target

When an OceanBase Database in MySQL-compatible mode is the target, the migration user must have the following permissions:

The CREATE, CREATE VIEW, SELECT, INSERT, UPDATE, ALTER, INDEX, and DELETE permissions on the target 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 required when an OceanBase Database in Oracle-compatible mode is the source

When an OceanBase Database in Oracle-compatible mode is the source, the migration or synchronization user must have the following permissions:

For versions of OceanBase Database in Oracle-compatible mode earlier than V2.2.70, the source user must have the GRANT SELECT ON *.* TO '<user_name>'; permission.
For OceanBase Database in Oracle-compatible mode V2.2.70 and later, the source user must have the GRANT DBA TO '<user_name>'; permission.
- For OceanBase Database in Oracle-compatible mode V4.0.0 and later, the source migration user must also have 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 required when an OceanBase Database in Oracle-compatible mode is the target

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 target database.

Target version

Permission description

V2.2.5 or V2.2.3

You can 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 high-level permissions.
```
GRANT ALL PRIVILEGES ON *.* TO '<user_name>';
```
Method 2
1. If the OceanBase Database instance is in a VPC, grant the migration user the SELECT permission on system views in the sys tenant.
```
GRANT SELECT ON SYS.* TO '<user_name>';
```
2. Grant various permissions on business tables to the migration user. If multiple business databases exist, grant the permissions 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 permissions to the migration user in one of the following two ways:

Method 1
Run the following statement. This method is simple but grants the user high-level permissions.
```
GRANT DBA TO '<user_name>';
```

Method 2

Grant various permissions on business tables to the user. If multiple business databases exist, grant the permissions 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 required when an Oracle database is the source

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

Note

For an Active Data Guard (ADG) secondary database, granted permissions might not take effect immediately. If this occurs, you can run the ALTER SYSTEM FLUSH SHARED_POOL; command in the secondary database to refresh the Shared Pool.
The permissions described in this topic include broad permissions such as SELECT ANY TRANSACTION, SELECT ANY TABLE, and SELECT ANY DICTIONARY. These are not the minimum required permissions. To improve security when an Oracle database is the source, you can grant more granular permissions instead of using `ANY`. For more information, see Minimum permissions for a source Oracle database.

DBA user permissions for versions earlier than Oracle 12c

If your environment lets you assign 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 DBA permissions to the migration user.

GRANT DBA TO <user_name>;

Non-DBA user permissions for versions earlier than Oracle 12c

If your environment requires you to grant only necessary permissions to 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 CREATE SESSION, ALTER SESSION, SELECT ANY TRANSACTION, SELECT ANY TABLE, and SELECT ANY DICTIONARY permissions to the migration user.
```
GRANT CREATE SESSION, ALTER SESSION, 
SELECT ANY TRANSACTION, SELECT ANY TABLE, SELECT ANY DICTIONARY TO <user_name>;
```
Grant LOGMINER-related permissions to the migration user.
```
GRANT EXECUTE ON SYS.DBMS_LOGMNR TO <user_name>;
```
Grant the CREATE TABLE and UNLIMITED TABLESPACE permissions to the migration user.
```
GRANT CREATE TABLE, UNLIMITED TABLESPACE TO <user_name>;
```

If the name of the schema that you want to migrate is the same as user_name, run the following statement.

GRANT CREATE SEQUENCE,CREATE VIEW TO <user_name>;

If the name of the schema that you want to migrate is different from user_name, run 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>;

Alternatively, you can 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 table to be migrated to the Oracle database.
GRANT DELETE, INSERT, UPDATE ON <database_name>.<table_name> TO <user_name>;

DBA user permissions for Oracle 12c and later

If your environment lets you assign the DBA role to the migration user and the Oracle database version is 12c or later, check whether you are using a pluggable database (PDB) of Oracle 12c, 18c, or 19c.

Non-PDB
1. Run the following statement to grant DBA permissions to the migration user.
```
GRANT DBA TO <user_name>;
```
2. Run the following statement to grant the read permission on the SYS.USER$ table to the migration user.
```
GRANT SELECT ON SYS.USER$ TO <user_name>;
```
PDB
If the data migration source is a PDB from Oracle 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, named CDB$ROOT, and any PDB to which the user has connection permissions, and then perform related operations.
2. Run the following statement to grant DBA permissions to the migration user.
```
GRANT DBA TO C##XXX CONTAINER=ALL;
```
3. Run the following statement to grant the read permission on the SYS.USER$ table to the migration user.
```
GRANT SELECT ON SYS.USER$ TO C##XXX CONTAINER=ALL;
```

Non-DBA user permissions for Oracle 12c and later

If your environment requires you to grant only necessary permissions to 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>;
```
Run the following statement to grant the read permission 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 permissions to the migration user.
```
GRANT CREATE SESSION, ALTER SESSION, SELECT ANY TRANSACTION, SELECT ANY TABLE, SELECT ANY DICTIONARY TO <user_name>;
```

Grant LOGMINER-related permissions 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 permissions to the migration user.
```
GRANT CREATE TABLE, UNLIMITED TABLESPACE TO <user_name>;
```

If the name of the schema that you want to migrate is the same as user_name, run the following statement.

GRANT CREATE SEQUENCE,CREATE VIEW TO <user_name>;

If the name of the schema that you want to migrate is different from user_name, run 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 data migration source is a PDB from Oracle 12c, 18c, or 19c, the account used to pull data from the PDB must be a common user.
1. Grant the CONNECT permission.
```
GRANT CONNECT TO <C##XXX> CONTAINER=ALL;
```
2. Run the following statement to grant the read permission 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 permissions 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 LOGMINER-related permissions 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 permissions to the migration user.
```
GRANT CREATE TABLE, UNLIMITED TABLESPACE TO <C##XXX> CONTAINER=ALL;
```
6. If the name of the schema that you want to migrate is the same as C##XXX, run the following statement.
```
GRANT CREATE SEQUENCE,CREATE VIEW TO <C##XXX> CONTAINER=ALL;
```
  If the name of the schema that you want to migrate is different from C##XXX, run 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 required when a MySQL database is the source

The database user must have the read permission on the database that you want to migrate. If you use MySQL 8.0, you must also grant the SHOW VIEW permission.
```
GRANT SELECT ON <database_name>.* TO '<user_name>';
```
For incremental synchronization from 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, the project might be interrupted if the user does not have read permission on all tables in the source database.
- The WITH GRANT OPTION parameter is optional.
When you migrate data from a MySQL database to an OceanBase Database in MySQL-compatible mode, if reverse incremental synchronization is enabled, the migration user must also have the CREATE, SELECT, INSERT, UPDATE, and DELETE permissions on the test database.
```
GRANT CREATE,SELECT,INSERT,UPDATE,DELETE ON test.* TO '<user_name>';
```

Permissions required when a TiDB database is the source

The database user must have the read permission on the database that you want to migrate.
```
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 required when an AnalyticDB for MySQL database is the target

AnalyticDB for MySQL (ADB) lets you grant different permissions at different levels of granularity to control access. 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 required when a Kafka instance is the target

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

A Kafka user must have permissions to perform the following operations:

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

Permissions required when a DataHub instance is the target

DataHub authenticates users based on endpoints, AccessKey IDs, and AccessKey secrets.

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

Permissions required when a RocketMQ instance is the target

When a RocketMQ instance is the target, the synchronization user must have permissions to perform the following operations:

Create and view topics.
View topic message queue information.
Write records.
Read records.