Create a user

This topic describes how to create a user.

Who can create a user

During database operation, you often need to create different users and assign appropriate permissions to them. Generally, a user who is granted the CREATE USER privilege can create a user.

By default, only the administrator has the CREATE USER privilege. If you want to create a user, you must be granted the CREATE USER privilege. For more information, see Grant privileges.

User name rules

When you specify a username, note the following rules:

• Each username must be unique in seekdb.

• The username must follow the naming conventions.

  • When you use a client to create a user, the username must be 64 bytes or shorter.

Create a user with minimal database privileges

You can use the CREATE USER statement to create a user. To create a user, you must have the CREATE USER system privilege. It is recommended that you follow the principle of least privilege. That is, all users have only 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'

Statement description:

• IF NOT EXISTS: If the username already exists and you do not specify IF NOT EXISTS, an error is returned.

• IDENTIFIED BY: You can specify a password for the account by using the optional IDENTIFIED BY clause.

  The following two statements specify passwords:

  • user IDENTIFIED BY 'authstring': The password is stored in the mysql.user table in plain text. After the server receives the password, it is stored in encrypted text.

  • user IDENTIFIED BY PASSWORD 'authstring': The password is stored in encrypted text.

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

The following example creates a user named test2 with minimal database privileges.

1. Log in to seekdb as the root user.

2. Run the following command to create the test2 user.

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

3. Run the following command to grant the test2 user the privilege to access all tables in the db1 database.

   GRANT SELECT ON db1.* TO test2;