How to manage users and their privileges in mysql

概述

  今天在这里简要介绍一下Ubuntu 18.04 LTS / Linux mint 19操作系统环境中MySQL 8.0.x的用户及权限管理。

  MySQL 8.0.x安装好以后，默认为我们创建好了一个密码为空的root超级用户，这里我们以该用户进入mysql系统，如下所示：

lwk@qwfys:~$ sudo mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 134
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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>

  注意，这里密码为空，直接按Enter键即可。

1 密码策略

  MySQL 8.0.x中用户密码部分由以validate_password.开头的几个环境变量来控制。具体如下：

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

mysql>

  其中，最重要的就是环境变量validate_password.policy，它有四个值，分别是LOW, MEDIUM, STRONG，具体定义如下：

Policy	Tests Performed	Is Default
0 or LOW	Length	No
1 or MEDIUM	Length; numeric, lowercase/uppercase, and special characters	Yes
2 or STRONG	Length; numeric, lowercase/uppercase, and special characters; dictionary file	No

  这里我们看到，MySQL 8.0.x的密码规则主要由参数validate_password.policy、validate_password.length、validate_password.number_count、validate_password.mixed_case_count等决定。通常情况下，在开发环境中，为了简化操作，可以通过将validate_password.policy设置为0、validate_password.length设置为一个合理的值来实现。

  设置参数validate_password.policy可以通过以下命令来实现：

mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql>

  设置参数validate_password.length可以通过以下命令来实现，这里假设给它设置为6：

mysql> set global validate_password.length = 6;
Query OK, 0 rows affected (0.00 sec)

mysql> 

  参数设置以后，需要重要登录一次，这样就可以看到状态改变后的值。

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 6     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)

mysql> 

2 用户管理

2.1 查询用户

mysql> select user,authentication_string,plugin,host from mysql.user;
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| user             | authentication_string                                                  | plugin                | host      |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| lwk              | *4F763CCD7253D8C1794D34715CC38B9F3AA6082F                              | mysql_native_password | %         |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| root             |                                                                        | auth_socket           | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
6 rows in set (0.00 sec)

mysql> 

2.2 添加用户

mysql> CREATE USER 'zhangsan'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.04 sec)

mysql> select user,authentication_string,plugin,host from mysql.user;
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| user             | authentication_string                                                  | plugin                | host      |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| lwk              | *4F763CCD7253D8C1794D34715CC38B9F3AA6082F                              | mysql_native_password | %         |
| zhangsan         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              | mysql_native_password | %         |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| root             |                                                                        | auth_socket           | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
7 rows in set (0.00 sec)

mysql> 

2.3 修改用户

mysql> ALTER USER 'zhangsan'@'%' IDENTIFIED WITH mysql_native_password BY '654321';
Query OK, 0 rows affected (0.03 sec)

mysql> select user,authentication_string,plugin,host from mysql.user;
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| user             | authentication_string                                                  | plugin                | host      |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| lwk              | *4F763CCD7253D8C1794D34715CC38B9F3AA6082F                              | mysql_native_password | %         |
| zhangsan         | *2A032F7C5BA932872F0F045E0CF6B53CF702F2C5                              | mysql_native_password | %         |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| root             |                                                                        | auth_socket           | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
7 rows in set (0.00 sec)

mysql> 

2.4 删除用户

mysql> DROP USER 'zhangsan'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,authentication_string,plugin,host from mysql.user;
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| user             | authentication_string                                                  | plugin                | host      |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| lwk              | *4F763CCD7253D8C1794D34715CC38B9F3AA6082F                              | mysql_native_password | %         |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| root             |                                                                        | auth_socket           | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
6 rows in set (0.00 sec)

mysql> 

3 权限管理

3.1 授予普通权限

  可以使用如下命令为用户zhangsan授予管理所有数据库资源的权限：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'zhangsan'@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> 

3.2 授予管理权限

  如果允许被授权用户可以将其权限授予他人，我们在为用户授权的时候可以追加参数WITH GRANT OPTION。例如我们允许用户zhangsan被授权后可以将其权限授予他人，我们可以执行如下命令。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'zhangsan'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.03 sec)

mysql> 

3.3 收回权限

  如果要收回用户zhangsan的所有权限，可以执行如下语句：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'zhangsan'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.04 sec)

mysql>