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 Grant direct privileges.

Naming conventions for usernames

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

• Unique username in a tenant

  Users in the same tenant must have unique names. However, users in different tenants can have the same name. You can globally and uniquely identify a tenant user by specifying the username in the username@tenant name format.

  The sys tenant uses the MySQL mode. To distinguish users in the sys tenant from those in a user tenant in MySQL mode, we recommend that you use a specific prefix for the name of a sys tenant user.

• Naming conventions

  • When you create a user by using OceanBase Command-Line Client (OBClient) or OceanBase Developer Center (ODC), the username cannot exceed 64 bytes in length.

  • When you create a user in the OceanBase Cloud Platform (OCP) console, the username must be 2 to 64 characters in length and start with a letter, and can contain uppercase and lowercase 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. To create a user, you must have the CREATE USER privilege. When you create a user, we recommend that you grant the user the minimum database privileges.

Execute the following statement to create a user:

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 already exists and you do not specify the IF NOT EXISTS clause, an error is reported.

• IDENTIFIED BY: You can use the IDENTIFIED BY clause to specify a password for the user.

  Note the following:

  • The password in the user IDENTIFIED BY 'authstring' clause is in plaintext. However, after the password is saved to the mysql.user table, the server stores the password 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 are NONE, SSL, X509, and tls_option.

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

1. Log in to a MySQL tenant of the cluster 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;