Chapter 13 Creating User Accounts

This section describes how to create user accounts. It demonstrates configuring global password 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, expiration, and verification-required 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.

   4. A global password verification-required policy is defined using the password_require_current system variable. The default setting is 0, which means that password changes do not require specifying the current password. To require that password changes specify the current password, add the following entry under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf):

      password_require_current=1

   Restart the server to apply the configuration changes:

   $> systemctl restart mysqld

2. Log in as root.

   $> cd /usr/local/mysql 
   $> 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/TLS requirements, generating a random password, limiting server resource usage, and managing password expiration.

   mysql> CREATE USER 'user1'@'203.0.113.11' IDENTIFIED WITH caching_sha2_password BY 
          RANDOM PASSWORD REQUIRE X509 WITH MAX_USER_CONNECTIONS 3 PASSWORD HISTORY DEFAULT
          PASSWORD REUSE INTERVAL DEFAULT PASSWORD EXPIRE DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
          FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNBOUNDED;
   +-------+--------------+----------------------+
   | user  | host         | generated password   |
   +-------+--------------+----------------------+
   | user1 | 203.0.113.11 | e6<]aR3he*XPg3o6ML<7 |
   +-------+--------------+----------------------+

   mysql> CREATE USER 'user2'@'203.0.113.12' IDENTIFIED WITH caching_sha2_password BY 
          RANDOM PASSWORD REQUIRE NONE WITH MAX_USER_CONNECTIONS 3 PASSWORD HISTORY DEFAULT
          PASSWORD REUSE INTERVAL DEFAULT PASSWORD EXPIRE DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
          FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNBOUNDED;
   +-------+--------------+----------------------+
   | user  | host         | generated password   |
   +-------+--------------+----------------------+
   | user2 | 203.0.113.12 | VT@jNXB3@CvVB>/vMbke |
   +-------+--------------+----------------------+

   CREATE USER statement options:

   • IDENTIFIED WITH caching_sha2_password BY RANDOM PASSWORD

     Sets the account authentication plugin to sha256_password, generates a random password that is passed as a cleartext value to the plugin for hashing, and stores the result in the mysql.user account row. The cleartext random password is also returned in a row of a result set (as shown above) to make it available to the user executing the statement.

     Note

     The RANDOM PASSWORD option is used as an alternative to an administrator-specified literal password. By default, generated random passwords have a length of 20 characters. This length is controlled by the generated_random_password_length system variable, which has a range from 5 to 255. The default length is used in this deployment.

     If an administrator-specified literal password is specified instead of the RANDOM PASSWORD option, the literal password value must conform to the password policy enabled by the validate_password component. (See Chapter 6, Installing the MySQL Password Validation Component.) The policy that the validate_password component implements has no effect on generated passwords. The purpose of a validate_password policy is to help humans create better passwords.

     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/TLS 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 policy defined 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.

   • PASSWORD REQUIRE CURRENT DEFAULT

     Causes the account to defer to the global password verification-required policy defined by the password_require_current system variable. In an earlier step, password_require_current was enabled to require that password changes must specify the current password.

     Verification of the current password occurs when a user changes a password using the ALTER USER or SET PASSWORD statement. For more information, see Password Verification-Required Policy.

   • FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNBOUNDED

     The FAILED_LOGIN_ATTEMPTS option defines how many consecutive incorrect passwords cause the account to become locked. The PASSWORD_LOCK_TIME option defines how long the account is locked after too many consecutive login attempts provide an incorrect password. PASSWORD_LOCK_TIME can be set to a number of days or to UNBOUNDED, which specifies that the duration of locked state is unbounded and does not end until the account is unlocked.

     For more information, including the conditions under which unlocking occurs, see Failed-Login Tracking and Temporary Account Locking.

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 D, 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