背景：生产环境需要新增一个查询账号，用于查询使用，意外揪出一个其他问题。
篇幅较长，部分无效文档就直接删掉了
首先给予其他账号权限通常是root的工作，牵扯到一个WITH GRANT OPTION的东西。
正常的root权限最后是带WITH GRANT OPTION：
[root@localhost opt]# mysql -uroot -h127.0.0.1 -p
Enter password:
mysql> use mysql
Database changed
mysql> SHOW GRANTS FOR root;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
这里我们把赋予权限的功能关掉后的，最后是不带WITH GRANT OPTION，本次排查也是这个问题，不清楚什么原因，生产环境的root赋权开关被关掉。
mysql> update mysql.user set Grant_priv='N' where user='root' and host='%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR root;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
新建账号测试
mysql> CREATE USER 'cx'@'%' IDENTIFIED BY 'cx*2236';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
此处因为密码不符合复杂度要求，我们调整下
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.00 sec)
mysql> set global validate_password.length=7;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 7 |
| 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.01 sec)
mysql> CREATE USER 'cx'@'%' IDENTIFIED BY 'cx*2236';
Query OK, 0 rows affected (0.01 sec)
此处还可以正常赋权，是因为权限调整之后，对之前的会话和线程是不生效的。
mysql> GRANT SELECT ON *.* TO 'cx'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'cx'@'%' IDENTIFIED WITH mysql_native_password BY 'cx*2236';
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
我们退出线程后，重新登录mysql，接下来就是生产的故障现象，ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)
[root@localhost opt]# mysql -uroot -h127.0.0.1 -p
Enter password:
mysql> CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '123infoschema456';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)
下面是处理方法，将root用户对应Grant_priv字段value值改为Y
mysql> use mysql;
mysql> update mysql.user set Grant_priv='Y' where user='root' and host='%';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| % | cx | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | 0x | 0x | 0x | 0 | 0 | 0 | 0 | mysql_native_password | *008FAE2865A5D66DB67F31B35531C3F438FA2688 | N | 2022-04-28 14:06:49 | NULL | N | N | N | NULL | NULL | NULL | NULL |
| % | cxtest | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | 0x | 0x | 0x | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$[
c%X0i~Z_HbrFBE1.TCmv0CAT6e6FGkny5jE0BoKO9j79AQR8ru48 | N | 2022-04-28 14:17:03 | NULL | N | N | N | NULL | NULL | NULL | NULL |
| % | repl | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | 0x | 0x | 0x | 0 | 0 | 0 | 0 | mysql_native_password | *AB9243EF23BB63FCD1CE491BD16D4C146E6A92CC | N | 2022-04-28 14:14:25 | NULL | N | N | N | NULL | NULL | NULL | NULL |
| % | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | 0x | 0x | 0x | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$vfjG,``&"o/WwHIdvX2bN10CdxujckgA64RahCpQqxRIXppLmyspNh5 | N | 2021-09-15 21:00:36 | NULL | N | Y | Y | NULL | NULL | NULL | NULL |
| localhost | mysql.infoschema | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | 0x | 0x | 0x | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2021-09-15 20:58:54 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.session | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | 0x | 0x | 0x | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2021-09-15 20:58:54 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | 0x | 0x | 0x | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2021-09-15 20:58:54 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
7 rows in set (0.00 sec)
mysql> exit
Bye
您在 /var/spool/mail/root 中有新邮件
[root@localhost opt]# mysql -uroot -h127.0.0.1 -p
Enter password:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
