MySQL Secure Deployment Guide / Creating User Accounts

Chapter 13 Creating User Accounts

This section describes how to create user accounts. It demonstrates configuring a password expiration policy, 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.

Define a global automatic password expiration policy. The user accounts created in the following steps use the policy defined by the default_password_lifetime system variable. The default setting is 0, which disables automatic password expiration. To have passwords automatically expire at a globally defined interval such as every 120 days, add an entry similar to this under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf):
```
default_password_lifetime=120
```
Restart the server to apply the configuration change:
```
$> systemctl restart mysqld
```

$> cd /usr/local/mysql 
$> bin/mysql -u root -p
Enter password: (enter root password here)

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, limiting server resource usage, and managing password expiration.
```
mysql> CREATE USER 'user1'@'203.0.113.11' IDENTIFIED WITH sha256_password BY 'password' 
       REQUIRE X509 WITH MAX_USER_CONNECTIONS 3 PASSWORD EXPIRE DEFAULT;
```
```
mysql> CREATE USER 'user2'@'203.0.113.12' IDENTIFIED WITH sha256_password BY 'password' 
       REQUIRE NONE WITH MAX_USER_CONNECTIONS 3 PASSWORD EXPIRE DEFAULT;
```
CREATE USER statement options:
- IDENTIFIED WITH sha256_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 Plugin.
  
  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 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.
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';
```
For more information about granting privileges, see GRANT Statement.

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'                             |
+-----------------------------------------------------------------------------------+

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: sha256_password
*************************** 2. row ***************************
  user: user2
plugin: sha256_password

PREV HOME UP NEXT