This topic describes how to create a user.
Who can create users
During database operation, it is often necessary to create different users and grant them corresponding privileges. Generally, only users granted the CREATE USER privilege can create other users.
Since the CREATE USER privilege is quite powerful, by default, only cluster administrators and tenant administrators possess this system privilege. Other users who need to create users must be granted the CREATE USER privilege. For more information about authorization operations, see Grant privileges directly.
User name rules
When specifying a name for a user, note the following limitations:
Uniqueness of the username: Each username must be unique within the tenant.
Usernames are unique within a tenant. Users in different tenants can have the same name. Therefore, a tenant user can be uniquely identified globally in the system using the format
username@tenant_name.Since the sys tenant and user tenants run in the same compatibility mode, to distinguish between users in the sys tenant and those in user tenants, it is recommended to use a specific prefix for user names in the sys tenant.
Naming conventions for user names:
When creating a user using clients such as OBClient or ODC, the username length must not exceed 64 bytes.
When creating a user using OCP, the username must start with a letter and can contain uppercase letters, lowercase letters, digits, and underscores. The length must be 2 to 64 characters.
Create a user with minimal database privileges 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 system privilege. When creating users, it is recommended to adhere to the principle of least privilege, meaning all users should only have the minimum privileges required to perform their tasks.
The SQL statement for creating a user 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'
The statement is described as follows:
IF NOT EXISTS: If the username to be created already exists andIF NOT EXISTSis not specified, the system returns an error.IDENTIFIED BY: The optionalIDENTIFIED BYclause specifies a password for the account.Where:
In the
user IDENTIFIED BY 'authstring'statement, the password is in plaintext. After being stored in themysql.usertable, it is encrypted on the server side.In the
user IDENTIFIED BY PASSWORD 'authstring'statement, the password is in ciphertext.
REQUIREspecifies the encryption protocol used by the user. Valid values areNONE,SSL,X509, andtls_option.
The following example shows how to create a user named test2 with minimal database privileges.
Log in to the cluster as the
rootuser.Run the following command to create the user
test2.obclient> CREATE USER 'test2' IDENTIFIED BY '******';Run the following command to grant the user
test2the privilege to access all tables in thedb1database.obclient> GRANT SELECT ON db1.* TO test2;