Create a user

This topic describes how to create a user.

Privilege to create a user

You may need to create users and grant them privileges in OceanBase Database as needed. To create a user, you must have the CREATE USER privilege.

By default, only cluster and tenant administrators have the CREATE USER privilege. Other users can create a user only after they are granted the CREATE USER privilege. For more information, see Modify user privileges.

Naming conventions for usernames

When you specify a name for a user, note the following:

• Username uniqueness in a tenant

  Usernames are unique within a tenant, but users in different tenants can have the same username. Therefore, a user is uniquely identified globally in the system in the username@tenant name format.

  In MySQL mode, to distinguish users in the system tenant from those in a user tenant, it is recommended to use a specific prefix for the username in the system tenant.

• Naming conventions

  • When you create a user through the OBClient or OceanBase Developer Center (ODC), the username cannot exceed 64 bytes in length.

  • When you create a user through the OceanBase Cloud Platform (OCP) console, the username must be 2 to 64 characters in length and start with a letter, and can contain letters, digits, and underscores (_).

Create a user with the minimum database privileges by using an SQL statement

You can use the CREATE USER statement to create a user. Creating a user requires the system privilege CREATE USER. When creating a user, it is recommended to adhere to the principle of least privilege, which means that each user should only have the minimum privileges necessary to perform their tasks.

The SQL statement is as follows:

CREATE USER [IF NOT EXISTS] user_specification_list
    [REQUIRE {NONE | SSL | X509 | tls_option}];

user_specification_list:
    user_specification [, user_specification ...]

user_specification:
    user IDENTIFIED BY  'authstring'
  | user IDENTIFIED BY PASSWORD 'hashstring'

tls_option:
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'

where

• IF NOT EXISTS: If the username that is being created already exists and the IF NOT EXISTS clause is not specified, the system will report an error.

• IDENTIFIED BY: specifies a password for the user. This clause is optional.

  Note the following:

  • The password in the user IDENTIFIED BY 'authstring' clause is in plaintext. However, once the password is saved to the mysql.user table, the server will store it in ciphertext.

  • The password in the user IDENTIFIED BY PASSWORD 'authstring' clause is in ciphertext.

• REQUIRE: specifies an encryption protocol for the user. Valid values: NONE, SSL, X509, and tls_option.

The following example shows how to create the test2 user that has the minimum database privileges:

1. Log on to a MySQL tenant as the root user.

2. Execute the following statement to create a user named test2:

   obclient> CREATE USER 'test2' IDENTIFIED BY '******';
   

3. Execute the following statement to grant the test2 user the privilege to access all tables in the db1 database:

   obclient> GRANT SELECT ON db1.* TO test2;