Skip to main content

CREATE USER

Create a EloqSQL account

Prerequisites​

The current user must have the global CREATE USER permission or the INSERT permission for the mysql database.

Synopsis​

CREATE [OR REPLACE] USER [IF NOT EXISTS]
 user_specification [,authentication_option ...]
  [REQUIRE {NONE | tls_option [[AND] tls_option ...] }]
  [WITH resource_option [resource_option ...] ]
  [lock_option] [password_option]
  • OR REPLACE: Use OR REPLACE to make the created user overwrite the user with the same name created in mysql.user.
  • user_specification: set the account name. The account name is composed of two parts: the user name and the host name, which are connected by the @ symbol. It is recommended to add quotation marks between the user name and the host name to meet the requirement that the user name contains special characters or wildcards. When creating an account, if you do not specify a host name, it defaults to localhost.
  • authentication_option: Specifies the authentication method of the account, which supports the following three methods in EloqSQL
    • IDENTIFIED BY 'password': The account password is specified in clear text.
    • IDENTIFIED BY PASSWORD 'password_hash': The account password is specified by a hashed password.
    • IDENTIFIED {VIA|WITH} authentication_plugin: specify the use of a specific authentication plugin to authenticate the account through the authentication plugin (authentication plugin)
  • TLS option: Encrypt data between server and client using the Transport Layer Security (TLS) protocol.
  • resource_option: Set specific resource limits for certain accounts.
  • lock_option: EloqSQL supports account locking, allowing privileged administrators to lock or unlock user accounts
  • password_option: used to set the expiration time of the account password.

Examples​

  • Create a user named eloq1@localhost with host name localhost, specified password as eloqpasssword1, and use OR REPLACE to delete possible existing users with the same name

    CREATE OR REPLACE USER 'eloq1'@'localhost' IDENTIFIED BY 'eloqpassword1';

  • Create a user named eloq2@% with host name % and specify password as eloqpassword2

     CREATE OR REPLACE USER 'eloq2'@'%' IDENTIFIED BY 'eloqpassword2';

  • Create a user named eloq3 without hostname, and specify password as eloqpassword3

    CREATE USER 'eloq3' IDENTIFIED BY 'eloqpassword3';

    Use the following SQL statement to show the hostname of the generated user

    select user,host from mysql.user where user='eloq3';

    output

    +-------+------+
    | User | Host |
    +-------+------+
    | eloq3 | % |
    +-------+------+
    1 row in set (0.005 sec)

    It can be seen that not specifying a host name is equivalent to creating an account with host name %.

  • Create a user named eloq4, specify hashed password First, use the PASSWORD function to calculate the hash value password corresponding to the plaintext password. For example, use the following statement to calculate the hash value of eloq

    SELECT PASSWORD('eloq');

    The output looks like this

    +-------------------------------------------+
    | PASSWORD('eloq') |
    +-------------------------------------------+
    | *70EAEE5007749F475555BADD67A4F93B02B98000 |
    +-------------------------------------------+
    1 row in set (0.008 sec)

    Use the following SQL statement to create eloq4 user

    CREATE USER eloq4 IDENTIFIED BY PASSWORD '*70EAEE5007749F475555BADD67A4F93B02B98000';

  • Create an account and use the identity plugin method of user authentication For example, create a user eloq5 and use the USING or AS keywords to provide the plain text password to the plug-in method for corresponding authentication

CREATE USER eloq IDENTIFIED VIA ed25519 USING PASSWORD('secret');

Note The premise of using the ed25519 plugin is that EloqSQL has loaded the corresponding plugin. If it is not loaded, you need to execute the following SQL statement to install it.

INSTALL SONAME 'auth_ed25519';
  • Create an anonymous account Anonymous accounts are accounts which the username of the account is blank. These accounts act as special universal accounts. If a user attempts to connect to the database from a certain host, and an anonymous account exists whose hostname part matches the user's host, then the user will be logged in as the anonymous account if no more specific account matches the username entered by the user. Create an anonymous user with the host name localhost using the following statement 
    CREATE USER ''@'localhost';
    Connect to EloqSQL anonymously using the following statement 
    mysql -u '' -h localhost
    Note that since mysql.db contains the default anonymous account ''@'%' with the host name %, if you want to recreate ''@'%' by yourself, you need to use DROP USER to delete the existing account Anonymous users, or use OR REPLACE.
  • Set the account password expiration time In addition to the automatic password expiration determined by default_password_lifetime, password expiration can also be set on an individual user basis, overriding the global setting. When creating the user 'eloq'@'localhost', specify a password expiration time of 100 days. 
    CREATE USER 'eloq'@'localhost' PASSWORD EXPIRE INTERVAL 100 DAY;
  • Lock account Account Lockout allows privileged administrators to lock or unlock user accounts. If the account is locked (existing connections are not affected), new client connections are not allowed. 
    CREATE OR REPLACE USER 'eloq'@'localhost' ACCOUNT LOCK;

MySQL Compatibility​

The CREATE USER statement is almost fully compatible with the MySQL 8.0 "CREATE USER" feature.

For more details, please refer to mariadb