How to manage users and their privileges in mysql
- 概述
- 1 密码策略
- 2 用户管理
-
- 2.1 查询用户
- 2.2 添加用户
- 2.3 修改用户
- 2.4 删除用户
- 3 权限管理
-
- 3.1 授予普通权限
- 3.2 授予管理权限
- 3.3 收回权限
概述
??今天在这里简要介绍一下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 - GPLCopyright (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>