User privileges for data migration

Before you use the data migration feature to migrate data, ensure that a database user has been created for all data sources and possesses the corresponding privileges.

OceanBase Database

-- Create a user in OceanBase Database for data migration.
CREATE USER '<username>' IDENTIFIED BY '<password>';

-- You have 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>';

-- The user has the SELECT privilege on the entire tenant.
GRANT SELECT ON *.* TO '<user_name>';

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

GRANT CREATE ON *.* TO '<user_name>';
GRANT ALTER,SELECT,INSERT,UPDATE,DELETE ON <database_name>.* TO '<user_name>';

MySQL

Source privileges

-- The database user must have read permission on the database to be migrated. For MySQL database V8.0, additionally grant the SHOW VIEW privilege.
GRANT SELECT ON <database_name>.* TO '<user_name>';

-- During incremental synchronization from a MySQL database, the database user must have the REPLICATION CLIENT, REPLICATION SLAVE, and SELECT *.* privileges.
-- During incremental synchronization, lacking read permissions on all tables at the source may cause the task to be interrupted abnormally. The [WITH GRANT OPTION] clause in the following command is optional.
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO '<user_name>' [WITH GRANT OPTION];
GRANT SELECT ON *.* TO '<user_name>';

Destination privileges

When a MySQL database serves as the destination, the migration user must have the CREATE, CREATE VIEW, INSERT, UPDATE, and DELETE privileges on the destination database.

GRANT <privilege_type> ON <database_name>.<table_name> TO '<user_name>'@'<host_name>' [WITH GRANT OPTION];

• Schema migration requires the following privileges to migrate views and indexes:

  • To migrate views, the user must have the SELECT privilege on the database.

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

  • To migrate indexes, the user must have the INDEX privilege on the database.

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

• The database user must have the trigger privilege.

  This privilege is used to check whether triggers exist at the destination. If triggers exist, data inconsistency may occur.

  GRANT TRIGGER ON *.* TO '<user_name>';
  

  Alternatively, run the following statement:

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

• For bidirectional synchronization between OceanBase Database in MySQL-compatible mode and a MySQL database, the destination user must have the SELECT, CREATE, INSERT, and UPDATE privileges on the oms database.

  GRANT SELECT, CREATE, INSERT, UPDATE ON oms.* TO '<user_name>';
  

Oracle

The privileges required for forward migration when an Oracle database serves as the source and for reverse migration when it serves as the destination are the same.

Privilege granting instructions for DBA users in Oracle Database earlier than 12c

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

GRANT DBA TO <user_name>;

Privilege granting instructions for non-DBA users in Oracle databases earlier than 12c

If your environment requires more caution when granting privileges to the migration user and the Oracle database version is earlier than 12c, perform the following operations:

1. Grant the CONNECT privilege.

   GRANT CONNECT TO <user_name>;
   

2. 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>;
   

3. Grant the migration user the LOGMINER related privileges.

   GRANT EXECUTE ON SYS.DBMS_LOGMNR TO <user_name>;
   

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

   GRANT CREATE TABLE, UNLIMITED TABLESPACE TO <user_name>;
   

5. 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>;
   -- Grant privileges on the specific table to be migrated.
   GRANT DELETE, INSERT, UPDATE ON <database_name>.<table_name> TO <user_name>;
   

User privileges required for a DBA user in Oracle Database 12c and later

If your environment allows granting the DBA role to the migration user and the Oracle database version is 12c or later, you must determine whether a pluggable database (PDB) of 12c, 18c, or 19c is used.

• 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 read permission on the SYS.USER$ table.

     GRANT SELECT ON SYS.USER$ TO <user_name>;
     

• PDB

  If the source for migrating from an Oracle database to the Oracle-compatible mode of OceanBase Database is a pluggable database (PDB) of 12c, 18c, or 19c, the account used to pull data from the PDB must be a common user.

  1. Execute the following statement to switch to CDB$ROOT.

     ALTER SESSION SET CONTAINER=CDB$ROOT;
     

     Every common user can connect to the root container (named CDB$ROOT) and any PDB for which they have connection permissions, and perform 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 migration user read permission on the SYS.USER$ table.

     GRANT SELECT ON SYS.USER$ TO C##XXX CONTAINER=ALL;
     

User privileges required for a non-DBA user in Oracle Database 12c and later

If your environment requires more cautious authorization for the migration user and the Oracle database version is 12c or later, proceed as follows:

• Non-PDB

  1. Grant the CONNECT privilege.

     GRANT CONNECT TO <user_name>;
     

  2. Execute the following statement to grant the migration user read permission on the SYS.USER$ table.

     GRANT SELECT ON SYS.USER$ TO <user_name>;
     

  3. 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>;
     

  4. Grant the migration user LOGMINER privileges.

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

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

     GRANT CREATE TABLE, UNLIMITED TABLESPACE TO <user_name>;
     

  6. 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 migrating from an Oracle database to the Oracle-compatible mode of OceanBase Database, if the source is a pluggable database (PDB) of version 12c, 18c, or 19c, the account used to pull data from the PDB must be a common user.

  1. Grant the CONNECT privilege.

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

  2. 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;
     

  3. 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;
     

  4. Grant the migration user LOGMINER privileges.

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

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

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

  6. If the name of the migrated schema matches C##XXX, execute the following statement.

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

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

PostgreSQL

Source privileges

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

• Schema migration or full migration only

  If the data migration task does not include an incremental synchronization step, the migration user must have read permission on the database to be migrated. 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 included

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

  Method 1: Superuser (recommended for self-managed PostgreSQL).

  -- Create User
  CREATE USER <user_name> WITH PASSWORD '<password>';
  
  -- Grant superuser privileges
  ALTER USER <user_name> WITH SUPERUSER;
  

  Method 2: High-privilege account from the cloud provider (for RDS PostgreSQL).

  RDS PostgreSQL instances from different cloud providers do not allow creating true superusers, but provide high-privilege accounts with similar permissions.

  • Alibaba Cloud RDS PostgreSQL: The database user must have the pg_rds_superuser role. You can verify this with the following statement.

    SELECT pg_has_role('<user_name>', 'pg_rds_superuser', 'member');
    

  • AWS RDS PostgreSQL: The database user must have the rds_superuser role.

  • Huawei Cloud RDS PostgreSQL: The default provided high-privilege account root.

  Note

  • Incremental synchronization requires access to the logical replication feature of the PostgreSQL instance, so the user must have superuser privileges or equivalent high privileges provided by the cloud provider.
  • For RDS PostgreSQL instances from cloud providers, some configurations need to be modified in the corresponding cloud console.

Target privileges

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

• Database connection privilege

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

• User privileges on the target schema.

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

• Write privileges on the target table.

  -- Authorize an Existing Table
  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;
  
  -- Automatically grant privileges on tables created in the future
  ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name>
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO <user_name>;
  

TiDB

Create a TiDB database user

1. Log in to the TiDB database.

2. 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 is allowed to log in. To allow the user to 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 to the TiDB database from any host, and set the login password to password.

   CREATE USER 'test'@'%' IDENTIFIED BY 'password';
   

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

Source-side privileges

-- The database user must have read permission on the database to be 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>';

Target-side privileges

When the TiDB database serves 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];

Kafka

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

When Kafka serves as the destination, the migration user must have permissions to perform the following operations:

• Create and view topics

• View topic partition information

• Write records

• Read records

Lindorm

By default, a Lindorm instance has two users with all privileges: root and columnar_internal_user. You can also create dedicated migration users and grant them permissions as needed. The granularity of authorization can be set to global, Namespace, or Table.

Create a Lindorm instance user

1. Log in to the Lindorm console.

2. On the instance list page, click the ID of the target instance.

3. On the instance details page, click Access Control in the left navigation pane to add your client IP to the allowlist.

   1. Run the ifconfig command to obtain the client IP.

   2. On the Access Control > Allowlists tab, click Modify Group next to the target group.

      If you need to add a new group, you can also click Create Group Allowlist to configure it. For details, see Set up an allowlist.

   3. Click OK.

4. Go to the Lindorm Insight page.

   1. On the Access Control page, click Wide Table Engine in the left navigation pane.

   2. In the cluster management area on the right side of the Wide Table Engine page, click ClusterManager Public.

      Note

      If this is your first time logging in to the Lindorm Insight page, click Reset UI Access Password in the cluster management area on the right side of the Wide Table Engine page to set a login password for the Lindorm Insight page, and then click ClusterManager Public.

   3. In the dialog box that appears, confirm that the client IP has been added to the allowlist and that the password has been reset for the first login, then click OK.

   4. In the login dialog box, enter the Username and Password.

   5. Click Log In.

5. Add a user.

   1. On the Lindorm Insight page, click Data Management > User Management in the left navigation pane.

   2. On the User Management page, click Add User.

   3. In the Add User dialog box, enter the Username and Password, then click OK.

   4. After successful creation, you can view the new user on the User Management page.

Source-side permissions

When a Lindorm instance serves as the source, you need to create a Lindorm instance user to fill in the username when creating a Lindorm data source. The source-side user must have the READ permission, which is used for read operations such as GET, SCAN, and table description queries.

• For schema migration, full data migration, and full data verification phases, the READ privilege is required.

• For incremental synchronization phase, no special setting is required; use the default privileges.

The authorization procedure is as follows (using global privileges as an example):

1. On the User Management page, click More > Grant Global Privileges for the target user.

   You can also grant privileges to a namespace or table. For details, see Permission management.

2. In the dialog box that appears, select READ.

3. Click OK to complete the user privilege setting.

Target-side privileges

When a Lindorm instance serves as the target, you need to create a Lindorm instance user to fill in the username when creating a Lindorm data source. The target-side user must have both READ and WRITE privileges. READ is used for read operations such as GET, SCAN, and table description queries; WRITE is used for write operations such as PUT, BATCH, DELETE, INCREMENT, and APPEND.

• Incremental synchronization is currently supported and requires both READ and WRITE privileges.

The authorization procedure is as follows (using global privileges as an example):

1. On the User Management page, click More > Grant Global Privileges for the target user.

   You can also grant privileges to a namespace or table. For details, see Permission management.

2. In the dialog box that appears, select READ and WRITE.

HBase

After creating an HBase Enhanced Edition instance, a default user named root is created with all permissions (including read, write, admin, trash, and system). When using data transfer to migrate data from an HBase Enhanced Edition instance to the HBase API compatibility mode of OceanBase Database, you can create users and grant them permissions based on your actual requirements.

Create a user for an HBase Enhanced Edition instance

1. Log in to the HBase console.

2. On the cluster list page, click the ID of the target HBase Enhanced Edition (Lindorm)/2.0 cluster.

3. On the Basic Information page, click Access Control in the left navigation pane to add your client IP address to the allowlist.

   1. Run the ifconfig command to obtain the client IP address.

   2. On the Access Control > Allowlist Settings tab, click Modify Group Allowlist under the target group.

      If you need to add a new group, you can also click Add Group Allowlist to configure it. For details, see Set up an allowlist.

   3. In the Modify Group Allowlist dialog box, enter the client IP address.

   4. Click OK.

4. Go to the Lindorm Insight page.

   1. On the Access Control page, click Cluster Management in the left navigation pane.

   2. On the Cluster Management page, click ClusterManager Public.

      Note

      If you are logging in to the Lindorm Insight page for the first time, click Reset UI Access Password in the Cluster Management section on the right side of the Wide Table Engine page. Set the login password for the Lindorm Insight page and then click ClusterManager Public.

   3. In the login dialog box, enter the Username and Password.

   4. Click Log In.

5. Add a user.

   1. On the Lindorm Insight page, click Data Management > User Management in the left navigation pane.

   2. On the User Management page, click Add User in the upper-right corner.

   3. In the Add User dialog box, enter the Username and Password.

   4. Click OK.

   5. After successful creation, you can view the new user on the User Management page.

Source permissions

When an HBase instance serves as the source, you need to create a user for the HBase instance to fill in the username when creating the HBase data source. The source user must have the READ permission, which is used for read operations such as GET, SCAN, and table description queries.

• During schema migration, full migration, and full verification phases, the READ permission is required.

• During incremental synchronization phase, no special settings are needed; default permissions are sufficient.

The authorization procedure is as follows (using global permissions as an example):

1. On the User Management page, click More > Grant Global Permissions next to the target user.

   You can also grant permissions to a namespace or table. For details, see Permission management.

2. In the Grant Global Permissions dialog box, select READ.

3. Click OK to complete the user permission setting.

Target-side permission requirements

When an HBase instance serves as the target, you need to create a user for the HBase instance to fill in the username when creating the HBase data source. The target user must have both READ and WRITE permissions. READ is used for read operations such as GET, SCAN, and table description queries; WRITE is used for write operations such as PUT, BATCH, DELETE, INCREMENT, and APPEND.

• Incremental synchronization is currently supported. The READ and WRITE permissions are required.

The authorization procedure is as follows (using global permissions as an example):

1. On the User Management page, click More > Grant Global Permissions next to the target user.

   You can also grant permissions to a namespace or table. For details, see Permission management.

2. In the Grant Global Permissions dialog box, select READ and WRITE.

DataHub

DataHub uses Alibaba Cloud RAM for access control. User access to DataHub resources is authenticated through RAM. The root account of Alibaba Cloud has all permissions on its resources. Sub-users have no permissions upon creation and cannot access any resources. You must grant permissions to the sub-user in RAM. For more information, see DataHub Permission Control.

Target-side permission description

When DataHub serves as the target, the migration user must have the following custom permission policy.

1. Log in to the RAM Access Control Console.

2. Add an IP allowlist to the custom permission policy.

   1. Return to the RAM Access Control Console.

   2. In the left navigation pane, choose Permission Management > Permission Policy.

   3. Click Create Permission Policy.

   4. On the configuration page, click the Script Editing tab.

   5. Clear the existing code in the input box, copy and paste the following JSON code, and configure the required IP addresses.

      {
          "Version": "1",
          "Statement": [
              {
                  "Effect": "Allow",
                  "Action": "datahub:*",
                  "Resource": "*",
                  "Condition": {
                      "IpAddress": {
                          "acs:SourceIp": [
                              "xx.xx.xx.xx",
                              "xx.xx.xx.xx/24"
                          ]
                      }
                  }
              }
          ]
      }
      

      • "Action": "datahub:*": Grants all operation permissions (read/write, management) to DataHub.
      • "Resource": "*": Allows access to all DataHub Projects and Topics under this account.
      • "acs:SourceIp": [...]": Adds an IP allowlist for access. If IP allowlist restrictions are not needed, delete the entire Condition field.

3. Grant DataHub permissions to the RAM user.

   1. In the left navigation pane, click Identity Management > User.

   2. Find the RAM user you want to authorize and click their name.

   3. On the user details page, click the Permission Management tab, then click the Add Permission button.

   4. In the panel that appears, select Custom Policy.

   5. Enter "DataHub" in the search box, select the IP permission policy created in the previous step, and click OK.

4. Obtain or create an AccessKey for the RAM user. If the current user already has an AccessKey, use it directly.

   1. Return to the user details page and click the Authentication Management tab.

   2. Obtain the AccessKey ID and AccessKey Secret in the AccessKey section.

      • If you already have an AccessKey, use it directly.

      • If you do not have an AccessKey, click Create AccessKey. After completing the security verification, the AccessKey ID and AccessKey Secret will be displayed.

        Notice

        Save the AccessKey Secret immediately. The Secret is displayed only once during creation and cannot be retrieved after you close the page.