MySQL Secure Deployment Guide  /  Creating User Accounts

Chapter 13 Creating User Accounts

This section describes how to create user accounts. It demonstrates configuring password history, reuse, and expiration policies, using security-related CREATE USER options, granting user privileges, and verifying user privileges and authentication.

Two user accounts are created: user1 and user2. The user1 account is defined with an SSL/TLS option that requires an encrypted connection. The user2 account is defined without an SSL/TLS option (REQUIRE NONE) so that it can be used to demonstrate RSA key pair-based password exchange with the server over an unencrypted connection.

  1. Define global password history, reuse, and expiration policies:

    1. A global password history policy is defined using the password_history system variable. The default setting is 0, which means that there is no restriction. To require a specified number of password changes before the same password can be reused, add an entry similar to this under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf):

      password_history=12

      A setting of 12 means that a minimum of 12 password changes must occur before a password can be reused.

    2. A global password reuse policy is defined using the password_reuse_interval system variable. The default setting is 0, which means that there is no restriction. To require that a specified number of days pass before the same password can be reused, add an entry similar to this under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf):

      password_reuse_interval=1095

      A setting of 1095 means that a minimum of 1095 days must pass before a password can be reused.

    3. A global automatic password expiration policy is defined using the default_password_lifetime system variable. The default setting is 0, which disables automatic password expiration. To have passwords automatically expire after a specified number of days, add an entry similar to this under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf):

      default_password_lifetime=120

      A setting of 120 means that the lifetime of a password is 120 days, after which it automatically expires.

    Restart the server to apply the configuration changes:

    shell> systemctl restart mysqld
  2. Log in as root.

    shell> cd /usr/local/mysql 
    shell> bin/mysql -u root -p
    Enter password: (enter root password here)
  3. Create the user accounts. The following statements create user accounts named 'user1'@'203.0.113.11' and 'user1'@'203.0.113.12, where 203.0.113.11 and 203.0.113.12 are the IP addresses of the client hosts. The statements include security-related options for enabling authentication, defining SSL/TSL requirements, limiting server resource usage, and managing password expiration.

    mysql> CREATE USER 'user1'@'203.0.113.11' IDENTIFIED WITH caching_sha2_password BY 'password' 
           REQUIRE X509 WITH MAX_USER_CONNECTIONS 3 PASSWORD HISTORY DEFAULT
           PASSWORD REUSE INTERVAL DEFAULT PASSWORD EXPIRE DEFAULT;
    mysql> CREATE USER 'user2'@'203.0.113.12' IDENTIFIED WITH caching_sha2_password BY 'password' 
           REQUIRE NONE WITH MAX_USER_CONNECTIONS 3 PASSWORD HISTORY DEFAULT
           PASSWORD REUSE INTERVAL DEFAULT PASSWORD EXPIRE DEFAULT;

    CREATE USER statement options:

    • IDENTIFIED WITH caching_sha2_password BY 'password'

      Sets the account authentication plugin to sha256_password, passes the cleartext password value to the plugin for hashing, and stores the result in the mysql.user account row.

      Note

      The password value specified must conform to the password policy enabled by the validate_password plugin. The password policy requires that a password be at least 8 characters long, contain at least 1 numeric character, 1 lowercase character, 1 uppercase character, and 1 special (nonalphanumeric) character. See Chapter 6, Installing the MySQL Password Validation Component.

      For more information, see CREATE USER Authentication Options.

    • REQUIRE X509

      This SSL/TLS option is only used for the user1 account.

      MySQL can check X509 certificate attributes in addition to the usual authentication that is based on the user name and credentials. Available SSL/TSL options include SSL, X509, ISSUER, SUBJECT, and CIPHER. The CREATE USER statement for user1 uses the X509 option, which requires that clients present a valid certificate, but the exact certificate, issuer, and subject do not matter. The only requirement is that it should be possible to verify its signature with one of the CA certificates. Use of X509 certificates always implies encryption, so it is unnecessary to also specify the SSL option.

      For more information, see CREATE USER SSL/TLS Options.

    • REQUIRE NONE

      Indicates that the account has no TLS or X509 requirements. Unencrypted connections are permitted if the user name and password are valid. Encrypted connections can be used, at the client's option, if the client has the proper certificate and key files. NONE is the default if no SSL-related REQUIRE options are specified.

      For more information, see CREATE USER SSL/TLS Options.

    • MAX_USER_CONNECTIONS 3

      Restricts the maximum number of simultaneous connections to the server by the account. If the number is 0 (the default), the server determines the number of simultaneous connections for the account from the global value of the max_user_connections system variable. MAX_USER_CONNECTIONS 3 means that the account can have a maximum of 3 simultaneous connections to the server.

      Other resource-limiting options not used here include MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, and MAX_CONNECTIONS_PER_HOUR. For more information, see CREATE USER Resource-Limit Options.

    • PASSWORD HISTORY DEFAULT

      Applies the global password history policy defined by the password_history system variable. In an earlier step, password_history was set to 12 to require that 12 password changes occur before the same password can be reused.

    • PASSWORD REUSE INTERVAL DEFAULT

      Applies the global password reuse policydefined by the password_reuse_interval system variable. In an earlier step, password_reuse_interval was set to 1095 to require that 1095 days pass before the same password can be reused.

    • PASSWORD EXPIRE DEFAULT

      Applies the global automatic password expiration policy defined by the default_password_lifetime system variable. In an earlier step, default_password_lifetime was set to 120 so that passwords automatically expire every 120 days.

      Other password expiration options include PASSWORD EXPIRE, PASSWORD EXPIRE INTERVAL, and PASSWORD EXPIRE NEVER. For more information, see CREATE USER Password-Management Options.

  4. Grant user privileges. The following statements grant the SHOW DATABASES privilege to the user1 and user2 accounts:

    mysql> GRANT SHOW DATABASES ON *.* TO 'user1'@'203.0.113.11';
    mysql> GRANT SHOW DATABASES ON *.* TO 'user2'@'203.0.113.12';
    Note

    The privileges granted to a MySQL account determine which operations the account can perform. Following the principle of least privilege, a MySQL account should only be granted privileges required for its legitimate purposes. To facilitate effective privilege management, MySQL 8.0 provides two new privilege-related features: MySQL Roles and Dynamic Privileges. For information about these features, see Appendix C, SQL Roles and Dynamic Privileges.

  5. To verify the privileges granted to the user accounts, issue a SHOW GRANTS statement. For example:

    mysql> SHOW GRANTS FOR 'user1'@'203.0.113.11';
    +-----------------------------------------------------------------------------------+
    | Grants for user1@203.0.113.11                                                     |
    +-----------------------------------------------------------------------------------+
    | GRANT SHOW DATABASES ON *.* TO 'user1'@'203.0.113.11'                             |
    +-----------------------------------------------------------------------------------+
  6. To verify that the accounts are using the expected authentication plugin, issue this query:

    mysql> SELECT user, plugin FROM mysql.user WHERE user LIKE ('user%')\G 
    *************************** 1. row ***************************
      user: user1
    plugin: caching_sha2_password
    *************************** 2. row ***************************
      user: user2
    plugin: caching_sha2_password