MySQL 8.0 has been released with a number of new features. For details, see MySQL 8.0 8.0.11 Release! The article.

MySQL 8.0 adds role management to user management, and changes the default password encryption mode from SHA1 to SHA2. Along with MySQL 5.7’s ability to disable users and expire users, MySQL has greatly improved user management and security over previous versions.

In this tutorial, we’ll look at some of the new user management features in MySQL and how roles can be used to simplify permission management.

Note: Most of the features in this tutorial are supported by MySQL 8.0 + or later.

MySQL User Management

Verify plug-in and password encryption changes

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

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in setMysql > 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 rowsin set (0.00 sec)Copy the code

To retain the previous authentication mode and password encryption mode, modify the following configuration items in the configuration file my. CNF and restart the service to take effect.

[mysqld]
default_authentication_plugin = mysql_native_passwordCopy the code

Note: Dynamic change features of MySQL 8.0 are not supported with this option.

Example Change the password of SHA2 in MySQL 8.0 to SHA1.

Update user password encryption mode to the previous version
mysql> ALTER USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'password';
# refresh permission
mysql> FLUSH PRIVILEGES;Copy the code

Note: If there is no specific reason, it is recommended to use a new more secure encryption method.

User authorization and password change

The user authorization statements in MySQL 8.0 are different from those in MySQL 8.0. Common authorization statements in MySQL 8.0 are unavailable. If you use authorization statements in MySQL 8.0, an error occurs.

  • Create users in MySQL 8.0 with authorization statements from previous versions.

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 1Copy the code

  • Correct authorization statement in MySQL 8.0.

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

Password expiration time management

MySQL has introduced automatic password expiration since 5.6.6, and improved the user password expiration feature in MySQL 5.7.4. You can now set a global automatic password expiration policy using a global variable default_password_lifetime.

Default_password_lifetime The default value is 0, indicating that automatic password expiration is disabled. If the value of default_password_lifetime is a positive integer N, it indicates that the allowed password lifetime is N (unit: day).

  • Default_password_lifetime The global password expiration policy is permanent by default.

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

  • If you want to create a global policy that sets the lifetime of passwords for all users to six months, change the default_password_lifetime value to 180 in the server configuration file my.cnf.

[mysqld]
default_password_lifetime=180Copy the code

  • If you want to restore the global policy so that all user passwords never expire, change the value of default_password_lifetime to 0 in the server configuration file my.cnf.

[mysqld]
default_password_lifetime=0Copy the code

  • The default_password_lifetime parameter supports permanent dynamic Settings. You can also use the following command from the MySQL command line to set this parameter to take effect.

# Set the default password expiration policy to 180 days
mysql> SET PERSIST default_password_lifetime = 180;

# Set the default password expiration policy to never expire
mysql> SET PERSIST default_password_lifetime = 0;

MySQL 8.0 permanent dynamic change parameters will be saved in the mysqld-auto. CNF configuration file 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 the code

  • Create and modify a user example with a password expiration time

Example Create or change the password expiration time of a user to 90 days.

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

Example Create or change the password expiration time of a user to never expire.

mysql> CREATE USER 'mike'@The '%' PASSWORD EXPIRE NEVER;
mysql> ALTER USER 'mike'@The '%' PASSWORD EXPIRE NEVER;Copy the code

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

mysql> CREATE USER 'mike'@The '%' PASSWORD EXPIRE DEFAULT;
mysql> ALTER USER 'mike'@The '%' PASSWORD EXPIRE DEFAULT;Copy the code

View the expiration time of a user password.

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 rowsin set (0.00 sec)Copy the code

Lock or unlock a user account

Starting with MySQL 5.7.8, a new feature to lock/unlock user accounts has been added to user management. Let’s look at some concrete examples of this feature.

  • Create a user with an account lock

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

Next, you try to log in as the newly created user and get an ERROR message ERROR 3118.

$ 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.Copy the code

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

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

The user is now unlocked and tries 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>Copy the code

  • Example Change the lock status of a user

If the user is already established, you can also lock the user account in this way.

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

Example Set a password reuse policy for MySQL users

Restricted reuse of previous passwords has been allowed since MySQL 8.0. Password reuse limits can be established based on the number of password changes, time elapsed, or both. The password history of an account consists of passwords assigned in the past, and MySQL can restrict the selection of new passwords from this history.

  • If you limit an account by the number of times it can be changed, you cannot select a new password from a specified number of recent passwords. For example, if the minimum number of password changes is set to 3, the new password cannot be the same as the last three passwords.

  • If you limit an account based on the password change time, you cannot select the new password from the history record at the specified time. For example, if the password reuse interval is set to 60, the new password cannot be the same within the latest 60 days.

Note: Empty passwords are not recorded in password history and can be reused at any time.

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

[mysqld]
password_history=6
password_reuse_interval=180Copy the code

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

mysql> SET PERSIST password_history = 6;
mysql> SET PERSIST password_reuse_interval = 180;Copy the code

MySQL Role Management

MySQL databases usually have multiple users with the same set of permissions. In previous versions, only granting and revoking permissions to multiple users could change the permissions of each user individually. This operation is time-consuming when the number of users is large.

MySQL 8.0 provides a new role management feature to make user rights management easier. A role is a specified set of rights. The rights of a role can be granted or revoked just like those of a user account. If a user is granted role rights, the user has the rights of the role.

MySQL 8.0 provides the following role management functions:

GRANT GRANT GRANTS permissions to users and roles. REVOKE REVOKE permissions to users and roles. SHOW GRANTS Displays permissions of users and roles Specifies which account roles are active by default. SET ROLE Changes the active roles in the current session. CURRENT_ROLE() Displays the active roles in the current sessionCopy the code

Create roles and grant user role permissions

Here we take a few common scenarios as examples.

  • The application requires read/write permissions.

  • Operations 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 set of permissions to multiple users, follow these steps.

  • Creating a New role

  • Granting Role Rights

  • Granting user roles

First, we create four characters. To clearly distinguish role rights, you are advised to name roles intuitively.

mysql> CREATE ROLE 'app'.'ops'.'dev_read'.'dev_write';Copy the code

Note: The role name format is similar to that of a user account consisting of a user and a host part, for example, role_name@host_name. If the host part is omitted, the default value is %, indicating any host.

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

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

Note: It is assumed that the database name to be authorized is wordpress.

Finally, according to the actual situation, we add the specified user to the corresponding role. Suppose you need one account for application use, one operations person account, one developer read-only account, and two developer read-write accounts.

  • Creating a New user

# Application account
mysql> CREATE USER 'app01'@The '%' IDENTIFIED BY '000000';
# Operation personnel account
mysql> CREATE USER 'ops01'@The '%' IDENTIFIED BY '000000';
# developer read-only account
mysql> CREATE USER 'dev01'@The '%' IDENTIFIED BY '000000';
# Develop read and write accounts
mysql> CREATE USER 'dev02'@The '%' IDENTIFIED BY '000000';
mysql> CREATE USER 'dev03'@The '%' IDENTIFIED BY '000000';Copy the code

  • Assign roles to users

mysql> GRANT app TO 'app01'@The '%';
mysql> GRANT ops TO 'ops01'@The '%';
mysql> GRANT dev_read TO 'dev01'@The '%';Copy the code

If you want to add multiple users to multiple roles, you can use a similar statement.

mysql> GRANT dev_read, dev_write TO 'dev02'@The '%'.'dev03'@The '%';Copy the code

Checking Role Rights

To verify that roles are properly assigned, use the SHOW GRANTS statement.

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

As you can see, unlike the previous version, SHOW GRANTS only return the granted role. To display the permissions represented by the role, add the USING clause and the name of the authorization role.

mysql> SHOW GRANTS FOR 'dev01'@The '%' 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)Copy the code

Setting the Default Role

Now, if you connect to MySQL using the Dev01 user account and try to access the wordpress database, the following error occurs.

$ 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'@The '%' to database 'wordpress'Copy the code

This is because after a role is granted to a user account, it does not automatically make the role active when the user account connects 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)Copy the code

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

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

Connect to the MySQL database server again using the dev01 user account and call the CURRENT_ROLE() function, and you’ll see the default role for 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 the dev01 user account.
mysql> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| `dev_read`@`%` |
+----------------+
1 row in set (0.00 sec)Copy the code

Finally, test the permissions of the Dev01 account by switching the current database to the wordpress database and executing the 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'Copy the code

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

Set active Roles

A user account can modify the valid permissions of the current user in the current session by specifying which authorization role is active.

  • Set the active role to NONE to indicate that there are no active roles.

mysql> SET ROLE NONE;Copy the code

  • Set the active role to all granted roles.

mysql> SET ROLE ALL;Copy the code

  • SET the active ROLE to the DEFAULT ROLE SET by the SET DEFAULT ROLE statement.

mysql> SET ROLE DEFAULT;Copy the code

  • Set up multiple active roles at the same time.

mysql> SET ROLE granted_role_1, granted_role_2, ...Copy the code

Revoking roles or role permissions

Just as you can authorize a user’s roles, you can revoke those roles from a user account. To REVOKE a role from a user account, use the REVOKE statement.

mysql> REVOKE role FROM user;Copy the code

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 all development users are temporarily read-only, REVOKE can be used to REVOKE change permissions from the dev_write role. Let’s first look at the permissions of user account dev02 before it was revoked.

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

Next, revoke 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)Copy the code

Finally, let’s look at the current permissions of the dev02 user account.

mysql> SHOW GRANTS FOR 'dev02'@The '%' 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)Copy the code

As you can see from the above results, revoking permissions in a role affects the permissions of any user in that role. As a result, Dev02 now has no table modification permissions (INSERT, UPDATE, and DELETE permissions have been removed). If you want to restore a role’s modification rights, simply regrant them.

# grant dev_write role modification permission.
mysql> GRANT INSERT, UPDATE, DELETE ON wordpress.* TO 'dev_write';

Dev02 user permissions have been restored.
mysql> SHOW GRANTS FOR 'dev02'@The '%' USING 'dev_read'.'dev_write';
+----------------------------------------------------------------------+
| Grants fordev02@% | +----------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `dev02`@`%` |  | GRANT SELECT, INSERT, UPDATE, DELETE ON `wordpress`.* TO `dev02`@`%` | | GRANT `dev_read`@`%`,`dev_write`@`%` TO `dev02`@`%` | +----------------------------------------------------------------------+ 3 rowsin set (0.00 sec)Copy the code

Delete the role

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

mysql> DROP ROLE 'role_name'.'role_name'. ;Copy the code

As with the REVOKE statement, deleting a role revokes that role from each account that authorized it. For example, to delete the dev_read, dev_write roles, use the following statement.

mysql> DROP ROLE 'dev_read'.'dev_write';Copy the code

Copy user account permissions to another user

MySQL 8.0 treats each user account as a role, so a user account can be granted to another user account. For example, copy permissions from one developer account to another.

  • Create a new development user account

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

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

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

  • View the permissions of user dev04

mysql> SHOW GRANTS FOR 'dev04'@The '%' USING 'dev02';
+----------------------------------------------------------------------+
| Grants fordev04@% | +----------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `dev04`@`%` |  | GRANT SELECT, INSERT, UPDATE, DELETE ON `wordpress`.* TO `dev04`@`%` | | GRANT `dev02`@`%` TO `dev04`@`%` | +----------------------------------------------------------------------+ 3 rowsin set (0.00 sec)Copy the code

Reference documentation

http://www.google.com

http://t.cn/RuTna0t

http://t.cn/RuTnEPH

http://t.cn/RuTnFGz

http://t.cn/RuTnFGz


If you think it’s great, what are you waiting for? Click on the tip as soon as possible, iOS rich people can also yo!