Create a database user

Descriptions on users

When you create an MySQL-compatible data source of OceanBase Database, three types of users are involved: OceanBase Database users, DRC users (sys tenant users), and __OCEANBASE_INNER_DRC_USER users. You can create these users as needed.

• OceanBase Database user (required): This is a required user that is used for data migration or data synchronization between the source and target.

• DRC user (optional): This is an optional user that is used to read incremental log data and database object schema information from OceanBase Database.

  If you need to perform schema migration, schema synchronization, or incremental synchronization, you must create a DRC user in the sys tenant of the source. If you need to perform reverse incremental synchronization, you must create a DRC user in the sys tenant of the target.

  Note

  In the public cloud data source scenario, you must create a user in the `sys` tenant to configure the username of the `sys` tenant.

• __OCEANBASE_INNER_DRC_USER user (optional): This is an optional user that is created only when you need to migrate tables without unique keys.

Create an OceanBase Database user

When you perform data migration or data synchronization between the source and target, you need to create an OceanBase Database user. This user is used to fill in the Database Username field when you create an OceanBase Database data source (including physical data sources, logical data sources, and public cloud data sources). For example, you can fill in the database username as OMSTEST.

1. Log in to OceanBase Database as a system administrator.

2. Create a user in the business tenant of the source for data migration or data synchronization.

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

3. Use the GRANT statement to grant privileges to the created database user. For more information, see User privileges.

(Optional) Create a DRC user

When you use an OceanBase data source as the source for schema migration, schema synchronization, or incremental synchronization, or as the target for reverse incremental synchronization, you must create a DRC user in the sys tenant of the business cluster. This DRC user is used to read incremental log data and database object schema information from the OceanBase database. This user is used to fill in the Advanced Options section of the DRC User Name field when you create an OceanBase data source (including physical and logical data sources). For example, you can set the DRC user username to user01.

CREATE USER <drc_user> IDENTIFIED BY '<drc_password>';
GRANT SELECT ON <db_name>.* TO <drc_user>;

When you create an OceanBase public cloud data source and the OceanBase cluster version is earlier than V4.2.0, you must create a user in the sys tenant of the business cluster to read the schema information of the database objects in the OceanBase database, in addition to creating an OceanBase database user. This user is used to fill in the sys Tenant Username field of the Advanced Options section. For example, you can set the sys tenant username to user02.

CREATE USER <drc_user> IDENTIFIED BY '<drc_password>';
GRANT SELECT ON <db_name>.* TO <drc_user>;

(Optional) Create the __OCEANBASE_INNER_DRC_USER user

When you need to migrate tables without unique keys in Oracle compatible mode of OceanBase Database to the target, you need to create the __OCEANBASE_INNER_DRC_USER user in the source business tenant. This user is used to specify the __OCEANBASE_INNER_DRC_USER password when you create an OceanBase data source with the Tenant Type set to Oracle.

• Create a user

  CREATE USER '__OCEANBASE_INNER_DRC_USER' IDENTIFIED BY <your_password>;
  

• Grant privileges

  OceanBase Database version	Privilege grant statement
  V2.2.77 and earlier	GRANT CREATE SESSION TO '__OCEANBASE_INNER_DRC_USER'; GRANT SELECT ON *.* TO '__OCEANBASE_INNER_DRC_USER';
  V2.2.77 and later	GRANT CREATE SESSION TO '__OCEANBASE_INNER_DRC_USER'; GRANT SELECT ANY DICTIONARY TO '__OCEANBASE_INNER_DRC_USER'; You can use the following two methods to grant the SELECT privilege to the user for the migrated tables: Grant the system privilege GRANT SELECT ANY TABLE TO '__OCEANBASE_INNER_DRC_USER'; Grant the object privilege (you can only grant the privilege to a specific table) GRANT SELECT ON <schema>.<table> TO '__OCEANBASE_INNER_DRC_USER';

When you need to migrate data between an Oracle database and OceanBase Database, or synchronize data from an Oracle database to DataHub, you need to create an Oracle database user. This user is used to specify the Database Username when you create an Oracle data source.

1. Log in to the Oracle database.

2. Create a user in the Oracle database for data migration or data synchronization.

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

3. Use the GRANT statement to grant privileges to the created database user. For more information, see User privileges.

When you need to migrate data between a MySQL database and OceanBase Database, or synchronize data from a MySQL database to DataHub, you need to create a MySQL database user. This user is used to specify the Database Username when you create a MySQL data source.

1. Log in to the MySQL 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 that allows the user to log in. If the user can log in from any host, use the percent sign (%).
   user_password	The password of the user to be created.

   For example, create a user named test who can log in 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. For more information, see User privileges.

When you need to migrate data from a PostgreSQL database to OceanBase Database in MySQL compatible mode, you need to create a PostgreSQL database user. This user is used to specify the Database Username when you create a PostgreSQL data source.

1. Log in to the PostgreSQL database.

2. Create a user in the PostgreSQL database for data migration or data synchronization.

   CREATE USER <user_name>  ENCRYPTED PASSWORD '<password>';
   

3. Use the GRANT statement to grant privileges to the created database user. For more information, see User privileges.

When you need to migrate data between a DB2 LUW database and OceanBase Database, you need to create a DB2 LUW database user. This user is used to specify the Database Username when you create a DB2 LUW data source.

In DB2 LUW (Linux/UNIX/Windows), you cannot directly create a database user by using an SQL statement. To create a DB2 LUW database user, you must first create a system user or group and then grant privileges to the user or group.

1. Create a user group in the operating system.

   groupadd <user_group_name>
   

2. Create a system user and specify the user group to which the user belongs.

   useradd -g <user_group_name> <user_name>
   

3. Set a login password for the new user.

   passwd <user_name>
   

4. Grant privileges to the created database user. For more information, see User privileges.

When you need to migrate data from a TiDB database to OceanBase Database in MySQL compatible mode, you need to create a TiDB database user. This user is used to specify the Database Username when you create a TiDB data source.

1. Log in to the TiDB database.

2. Create a user in the TiDB database for data migration or data synchronization.

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

   Parameter	Description
   user_name	The name of the user to be created.
   host_name	The host that allows the account to log in. If the account can log in from any host, use the percent sign (%).
   password	The password of the user to be created.

3. Use the GRANT statement to grant privileges to the created database user. For more information, see User privileges.

When you need to migrate data from a PolarDB-X 1.0 database to OceanBase Database in MySQL compatible mode, you need to create a PolarDB-X 1.0 database user. This user is used to specify the Account when you create a PolarDB-X 1.0 data source.

1. Log in to the Apsara Stack console.

2. In the upper-left corner of the page that appears, click Product > Databases > PolarDB-X 1.0.

3. In the Instances section of the target organization, click the name of the PolarDB-X 1.0 instance to go to the basic information page of the instance.

   

4. In the left-side navigation pane, choose Configuration & Management > Account Management.

   Note

   After a database is created, an advanced account is automatically created. The password of the account is the same as that of the database. You can use the account directly or create a normal account as needed.

5. On the Account Management page, click Create Account.

   

6. In the Create Account dialog box, configure the parameters.

   

   Parameter	Description
   Database Account	The database account must be 4 to 20 characters in length and consist of uppercase letters, lowercase letters, digits, and underscores. The account must start with a letter and end with a letter or digit.
   New Password	The new password must be 8 to 32 characters in length and contain at least three types of characters selected from uppercase letters, lowercase letters, digits, and special characters (!@#$%^&*()_+-=).
   Confirm New Password	Enter the new password again for confirmation.
   Grant Database	In the Database List section, select the name of the database to which you want to grant privileges and click Grant to add the name to the Granted Databases section. Note The minimum privilege required for a normal account of a PolarDB-X 1.0 instance in each stage of data migration is Read-only.

7. Click OK.