The official version of MySQL 8.0 has been released. MySQL 8.0 has added many new functions, which can be referred to specifically. MySQL 8.0 official version 8.0.11 release! "Article.

MySQL 8.0 adds role management to user management, and the default password encryption mode has been adjusted from SHA1 to SHA2. At the same time, MySQL 5.7 disables users and user expiration functions, MySQL user management functions and security are greatly enhanced compared with previous versions.

In this tutorial, we will introduce some new features of user management under MySQL and how to use roles to simplify privilege management.

MySQL User Management

Verify changes in plug-ins and password encryption

In MySQL 8.0, caching_sha2_password is the default authentication plug-in instead of the previous version of mysql_native_password, and the default encryption method is SHA2.

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec) 
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
|user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | caching_sha2_password |
| mysql.infoschema | localhost | mysql_native_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

If you need to maintain the previous authentication and password encryption, you need to modify the following configuration items in my.cnf configuration file and restart the service.

[mysqld]
default_authentication_plugin = mysql_native_password

Modify the existing SHA2 password in MySQL 8.0 to SHA1 mode.

# Update the user's password encryption to the previous version
mysql> ALTER USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'password';
# Refresh permissions
mysql> FLUSH PRIVILEGES;

User Authorization and Password Modification

User authorization statements in MySQL 8.0 are different from those in previous versions. Common authorization statements in old versions can not be used in MySQL 8.0 versions, such as using old versions of authorization statements will cause errors.

• Create users in MySQL 8.0 with previous version authorization statements.

mysql> GRANT ALL PRIVILEGES ON *.* TO `mike`@`%` IDENTIFIED BY '000000' WITH GRANT OPTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY '000000' WITH GRANT OPTION' at line 1

• Correct authorization statement in MySQL version 8.0.

mysql> CREATE USER 'mike'@'%' IDENTIFIED BY '000000';
mysql> GRANT ALL ON *.* TO 'mike'@'%' WITH GRANT OPTION;Copy code

Password expiration time management

MySQL introduced the new function of automatic password expiration from 5.6.6, and improved the feature of user password expiration time in MySQL 5.7.4. Now you can set up a global automatic password expiration policy by using a global variable default_password_lifetime.

Default_password_lifetime has a default value of 0, indicating that automatic password expiration is disabled. If the default_password_lifetime value is a positive integer N, it means that the allowable set password lifetime is N, in days.

• The default_password_lifetime global password expiration policy defaults to permanent but expires.

mysql> show variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 0     |
+---------------------------+-------+
1 row in set (0.00 sec)

• If you want to establish a global policy that allows all users to use passwords for six months, you can modify the default_password_lifetime configuration item in the server-side configuration file my.cnf to a value of 180.

[mysqld]
default_password_lifetime=180

• If you want to restore the global policy so that all users'passwords never expire, you can modify the default_password_lifetime configuration item to 0 in the server-side configuration file my.cnf.

[mysqld]
default_password_lifetime=0

• The default_password_lifetime parameter supports permanent dynamic settings. You can also set it to take effect directly from the MySQL command line with the following commands.

# Set default password expiration policy to expire after 180 days
mysql> SET PERSIST default_password_lifetime = 180;
 
# Set default password expiration policy to never expire
mysql> SET PERSIST default_password_lifetime = 0;
 
# MySQL 8.0 permanent dynamic modification parameters are saved in the configuration file mysqld-auto.cnf in JSON string format.
$ cat  /var/lib/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "default_password_lifetime" : { "Value" : "180" , "Metadata" : { "Timestamp" : 1525663928688419 , "User" : "root" , "Host" : "" } } } }Copy code

• Create and modify user examples with password expiration time

The password expiration time for creating or modifying a user is 90 days.

mysql> CREATE USER 'mike'@'%' IDENTIFIED BY '000000' PASSWORD EXPIRE INTERVAL 90 DAY;
mysql> ALTER USER `mike`@`%` PASSWORD EXPIRE INTERVAL 90 DAY;

The password expiration time for creating or modifying a user is never expired.

mysql> CREATE USER 'mike'@'%' PASSWORD EXPIRE NEVER;
mysql> ALTER USER 'mike'@'%' PASSWORD EXPIRE NEVER;

Create or modify a user who follows a global expiration policy.

mysql> CREATE USER 'mike'@'%' PASSWORD EXPIRE DEFAULT;
mysql> ALTER USER 'mike'@'%' PASSWORD EXPIRE DEFAULT;

View user password expiration time.

mysql> select user,host,password_last_changed,password_lifetime,password_expired from mysql.user;
+--------------+--------+---------------------+-----------------+------------------+
| user         | host   | password_last_changed | password_lifetime | password_expired |
+--------------+--------+-----------------------+-------------------+------------------+
| mike         | %      | 2018-05-07 11:13:39   |                90 | N                |
| root         | %      | 2018-05-04 16:46:05   |              NULL | N                |
| mysql.infoschema | localhost | 2018-05-04 16:45:55   |       NULL | N                |
| mysql.session    | localhost | 2018-05-04 16:45:55   |       NULL | N                |
| mysql.sys        | localhost | 2018-05-04 16:45:55   |       NULL | N                |
| root             | localhost | 2018-05-04 16:46:05   |       NULL | N                |
+-----------------+-----------+-----------------------+-------------+------------------+
6 rows in set (0.00 sec)

Lock/unlock user accounts

Starting with MySQL 5.7.8, user management has added new features to lock/unlock user accounts. Let's look at some specific examples of this feature.

• Create a user with an account lock

mysql> CREATE USER 'mike-temp1'@'%' IDENTIFIED BY '000000' ACCOUNT LOCK;

Next, try logging in with the newly created user, and you get an ERROR 3118 error message.

$ mysql -umike-temp1 -p000000
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3118 (HY000): Access denied for user 'mike-temp1'@'172.22.0.1'. Account is locked.

If you need to unlock this user, you need to unlock it with the following statement.

mysql> ALTER USER 'mike-temp1'@'%' ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.00 sec)

Now the user has unlocked and tried to log in again.

$ mysql -umike-temp1 -p000000
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.11 MySQL Community Server - GPL
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

• Modify a user to a locked state

If the user has been established, you can also lock the user account in this way.

mysql> ALTER USER 'mike'@'%' ACCOUNT LOCK;
Query OK, 0 rows affected (0.00 sec)

Setting MySQL User Password Reuse Policy

Starting with MySQL 8.0, restrictions on reusing previous passwords are allowed. Password reuse restrictions can be established based on the number of password changes, time spent, or both. Account password history consists of passwords previously assigned, and MySQL can restrict the selection of new passwords from this history.

• If the account is limited by the number of password changes, a new password cannot be selected from the specified number of latest passwords. For example, if the minimum number of password changes is set to 3, the new password cannot be the same as any of the last three passwords.
• If the account is limited by the password modification time, the password in the specified time history cannot be selected as the new password. For example, if the password reuse interval is set to 60, the new password may not be the same as the password selected in the last 60 days.

To establish a global password reuse policy, you can modify password_history and password_reuse_interval system variables. This variable can be configured in my.cnf, the service configuration file, for example, to prohibit the reuse of the last six passwords or any passwords that have been used in the last 180 days.

[mysqld]
password_history=6
password_reuse_interval=180

This parameter supports permanent dynamic settings or can be set directly with the following statement.

mysql> SET PERSIST password_history = 6;
mysql> SET PERSIST password_reuse_interval = 180;

MySQL Role Management

There are usually multiple users with the same set of privileges in MySQL database. In previous versions, only by granting and revoking privileges to multiple users can the privileges of each user be changed independently. When the number of users is large, such operations are very time-consuming.

MySQL 8.0 provides a new role management function to make user rights management easier. A role is a set of specified permissions, which, like a user account, can grant and revoke permissions to a role. If a user is granted role privileges, the user has the privileges of that role.

MySQL 8.0 provides the following role management functions:

CREATE ROLE Role Creation
 DROP ROLE role deletion
 GRANT assigns privileges to users and roles
 REVOKE revokes privileges for users and roles
 SHOW GRANTS Displays User and Role Permissions
 SET DEFAULT ROLE specifies which account roles are active by default
 SET ROLE Change Active Roles in Current Session
 CURRENT_ROLE() displays the active roles in the current session

Create roles and grant user role permissions

Here we take several common scenarios as examples.

• The application needs read/write permissions.
• Operations and maintenance personnel need full access to the database.
• Some developers need read permissions.
• Some developers need read and write permissions.

If you want to grant the same permission set to multiple users, you should follow the following steps.

• Create new roles
• Granting role privileges
• Granting user roles

First, we create four roles. In order to clearly distinguish the Roles'rights, it is suggested that the Roles' names be named more intuitively.

mysql> CREATE ROLE 'app', 'ops', 'dev_read', 'dev_write';

Once the role is created, we grant the role the corresponding permissions. To grant role permissions, you can use GRANT statements.

# The following statement grants read and write permission to the wordpress database to the app role
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON wordpress.* TO 'app';
# The following statement grants all permissions to the wordpress database to the ops role
mysql> GRANT ALL PRIVILEGES ON wordpress.* TO 'ops';
# The following statement grants read-only access to the wordpress database to the dev_read role
mysql> GRANT SELECT ON wordpress.* TO 'dev_read';
# The following statement grants write permission to the wordpress database to the dev_write role
mysql> GRANT INSERT, UPDATE, DELETE ON wordpress.* TO 'dev_write';

Finally, according to the actual situation, we will designate users to join the corresponding role. Suppose you need an account for an application, an Operations and Maintenance Personnel Account, a Developer Read-Only Account, and two Developer Read-Write Accounts.

• Create new users

# Application Account
mysql> CREATE USER 'app01'@'%' IDENTIFIED BY '000000';
# Operations and Maintenance Personnel Account
mysql> CREATE USER 'ops01'@'%' IDENTIFIED BY '000000';
# Developer Read-Only Account
mysql> CREATE USER 'dev01'@'%' IDENTIFIED BY '000000';
# Developing Read and Write Accounts
mysql> CREATE USER 'dev02'@'%' IDENTIFIED BY '000000';
mysql> CREATE USER 'dev03'@'%' IDENTIFIED BY '000000';

• Assign roles to users

mysql> GRANT app TO 'app01'@'%';
mysql> GRANT ops TO 'ops01'@'%';
mysql> GRANT dev_read TO 'dev01'@'%';

If you want to add multiple users to multiple roles at the same time, you can use similar statements.

mysql> GRANT dev_read, dev_write TO 'dev02'@'%', 'dev03'@'%';

Check role permissions

To verify that roles are assigned correctly, you can use the SHOW GRANTS statement.

mysql> SHOW GRANTS FOR 'dev01'@'%';
+-------------------------------------+
| Grants for dev01@%                  |
+-------------------------------------+
| GRANT USAGE ON *.* TO `dev01`@`%`   |
| GRANT `dev_read`@`%` TO `dev01`@`%` |
+-------------------------------------+
2 rows in set (0.00 sec)

As you can see, unlike previous versions, SHOW GRANTS only returns the granted role. If you want to show the permissions represented by the role, you need to add the USING clause and the name of the authorized role.

mysql> SHOW GRANTS FOR 'dev01'@'%' USING dev_read;
+----------------------------------------------+
| Grants for dev01@%                           |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `dev01`@`%`            |
| GRANT SELECT ON `wordpress`.* TO `dev01`@`%` |
| GRANT `dev_read`@`%` TO `dev01`@`%`          |
+----------------------------------------------+
3 rows in set (0.00 sec)

Setting default roles

Now, if you use the dev01 user account to connect to MySQL and try to access the wordpress database, the following error will occur.

$ mysql -u dev01 -p000000
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.11 MySQL Community Server - GPL
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> use wordpress;
ERROR 1044 (42000): Access denied for user 'dev01'@'%' to database 'wordpress'

This is because when a role is granted to a user account, it does not automatically make the role active when the user account is connected to the database server.

# Call the CURRENT_ROLE() function to view the current role.
mysql> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

Returning NONE here means that no roles are currently enabled. To specify which roles should be active each time a user account connects to the database server, you need to use the SET DEFAULT ROLE statement to specify.

# The following statement sets all roles assigned to the dev01 account to default values.
mysql> SET DEFAULT ROLE ALL TO 'dev01'@'%';

Once again, use the dev01 user account to connect to the MySQL database server and call the CURRENT_ROLE() function, and you will see the default role of the dev01 user account.

$ mysql -u dev01 -p000000
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.11 MySQL Community Server - GPL
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>
 
# View the default role of dev01 user account.
mysql> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| `dev_read`@`%` |
+----------------+
1 row in set (0.00 sec)

Finally, we test the rights of dev01 account by switching the current database to wordpress database and executing SELECT and DELETE statements.

mysql> use wordpress;
Database changed
 
mysql> select  count(*) from wp_terms;
+----------+
| count(*) |
+----------+
|      357 |
+----------+
1 row in set (0.00 sec)
 
mysql> DELETE from wp_terms;
ERROR 1142 (42000): DELETE command denied to user 'dev01'@'172.22.0.1' for table 'wp_terms'

As shown in the above results, when we issue a DELETE statement, we receive an error. Because dev01 user account has read access only.

Setting Active Roles

User accounts can modify the current user's valid permissions in the current session by specifying which authorization role is active.

• Setting the active role to NONE indicates that there is no active role.

mysql> SET ROLE NONE;

• Set active roles to all granted roles.

mysql> SET ROLE ALL;

• Set the active role to the default role set by the SET DEFAULT ROLE statement.

mysql> SET ROLE DEFAULT;

• Set up multiple roles at the same time.

mysql> SET ROLE granted_role_1, granted_role_2, ...

Revoke roles or role permissions

Just as roles can be authorized for a user, they can also be revoked from a user account. To revoke a role from a user account, you need to use the REVOKE statement.

mysql> REVOKE role FROM user;

REVOKE can also be used to modify role permissions. This affects not only the role's own permissions, but also any user permissions granted to that role. Assuming that you want all development users to read-only temporarily, you can use REVOKE to revoke modification permissions from the dev_write role. Let's first look at the permissions of the user account dev02 before it is revoked.

mysql> SHOW GRANTS FOR 'dev02'@'%' USING 'dev_read', 'dev_write';
+----------------------------------------------------------------------+
| Grants for dev02@%                                                   |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev02`@`%`                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `wordpress`.* TO `dev02`@`%` |
| GRANT `dev_read`@`%`,`dev_write`@`%` TO `dev02`@`%`                  |
+----------------------------------------------------------------------+
3 rows in set (0.00 sec)

Next, remove the modification permission from the dev_write role.

mysql> REVOKE INSERT, UPDATE, DELETE ON wordpress.* FROM 'dev_write';
Query OK, 0 rows affected (0.03 sec)

Finally, let's look at the current permissions for dev02 user accounts.

mysql> SHOW GRANTS FOR 'dev02'@'%' USING 'dev_read', 'dev_write';
+-----------------------------------------------------+
| Grants for dev02@%                                  |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `dev02`@`%`                   |
| GRANT SELECT ON `wordpress`.* TO `dev02`@`%`        |
| GRANT `dev_read`@`%`,`dev_write`@`%` TO `dev02`@`%` |
+-----------------------------------------------------+
3 rows in set (0.00 sec)

As can be seen from the above results, revoking permissions in a role affects the permissions of any user in that role. So dev02 now has no table modification permissions (INSERT, UPDATE, and DELETE permissions have been removed). If you want to restore the role's modification permissions, you just need to re-grant them.

# Grant dev_write role modification permission.
mysql> GRANT INSERT, UPDATE, DELETE ON wordpress.* TO 'dev_write';
 
# Look at dev02 user rights again and the modification permissions have been restored.
mysql> SHOW GRANTS FOR 'dev02'@'%' USING 'dev_read', 'dev_write';
+----------------------------------------------------------------------+
| Grants for dev02@%                                                   |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev02`@`%`                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `wordpress`.* TO `dev02`@`%` |
| GRANT `dev_read`@`%`,`dev_write`@`%` TO `dev02`@`%`                  |
+----------------------------------------------------------------------+
3 rows in set (0.00 sec)

Delete roles

To delete one or more roles, you can use the DROP ROLE statement.

mysql> DROP ROLE 'role_name', 'role_name', ...;

Like the REVOKE statement, deleting a role revokes that role from each account that authorizes it. For example, to delete the dev_read, dev_write role, you can use the following statement.

mysql> DROP ROLE 'dev_read', 'dev_write';

Copy user account permissions to another user

MySQL 8.0 treats each user account as a role, so you can grant a user account to another user account. For example, copy the developer account rights to another developer account.

• Create a new development user account

mysql> CREATE USER 'dev04'@'%' IDENTIFIED BY '000000';
Query OK, 0 rows affected (0.04 sec)

• Copy the rights of dev02 user account to dev04 user account

mysql> GRANT 'dev02'@'%' TO 'dev04'@'%';
Query OK, 0 rows affected (0.09 sec)

• Permissions to view dev04 user accounts

mysql> SHOW GRANTS FOR 'dev04'@'%' USING 'dev02';
+----------------------------------------------------------------------+
| Grants for dev04@%                                                   |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev04`@`%`                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `wordpress`.* TO `dev04`@`%` |
| GRANT `dev02`@`%` TO `dev04`@`%`                                     |
+----------------------------------------------------------------------+
3 rows in set (0.00 sec)