mysql GRANT with ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
问题现象：
mysql> GRANT ALL PRIVILEGES ON *.* TO 'lixora'@'%' ;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
问题重现及问题分析：
缺省使用 mysql 登入 mysql server：
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4296
Server version: 5.6.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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.
----对lixora 用户授权 ：
mysql> GRANT ALL PRIVILEGES ON *.* TO 'lixora'@'%' ;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
报错了显示用户'root'@'localhost' 没有使用密码
----查看下用户密码信息：
mysql> select host,user,password from mysql.user;
+--------------+--------+-------------------------------------------+
| host | user | password |
+--------------+--------+-------------------------------------------+
| % | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| lixora | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| 127.0.0.1 | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| ::1 | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| | root | |
| localhost | zabbix | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |
| localhost | root | |
| % | zabbix | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |
| 10.80.18.237 | zabbix | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |
| % | lixora | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
+--------------+--------+-------------------------------------------+
10 rows in set (0.00 sec)
-----查看当前窗口用户信息
mysql> status
--------------
mysql Ver 14.14 Distrib 5.6.28, for Linux (x86_64) using EditLine wrapper
Connection id: 4296
Current database:
Current user: root@localhost---------注意！！！
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.28-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 4 hours 16 min 12 sec
Threads: 26 Questions: 123774 Slow queries: 0 Opens: 193 Flush tables: 1 Open tables: 186 Queries per second avg: 8.051
--------------
mysql> select user(), current_user();
+----------------+----------------+
| user() | current_user() |
+----------------+----------------+
| root@localhost | root@localhost | --------注意！！！
+----------------+----------------+
1 row in set (0.00 sec)
mysql> show grants;
+--------------------------------------------------------------+
| Grants for root@localhost |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' |----好像少了点什么？
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)
我们发现当前连接的用户为'root'@'localhost' ，且该用户的权限为【GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' 】
到这里我想应该找到问题的源头了。。。。。
对，没错就是with grant option 权限；
关于with grant option 有 mysql reference的官方解释
WITH GRANT OPTION ：clause gives the user the ability to give to other users any privileges the user has at the specified privilege leve
出现这个问题时会茫然，不能快速找到根本原因是没有理解懂mysql 的用户管理体系：
mysql 的用户不是一个简单的username 来唯一确定，一个真正的mysql user是 ‘username’@‘hostname’ 组合来唯一确定的；
也就是说 root@localhost \\ root@127.0.0.1 不是同一个用户是2个用户，且用户的权限也是完全独立的：
mysql> show grants for 'root'@'localhost';
+--------------------------------------------------------------+
| Grants for root@localhost |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for 'root'@'127.0.0.1';
+---------------------------------------------------------------------------------------------------------------------------
| Grants for root@127.0.0.1 |
+---------------------------------------------------------------------------------------------------------------------------
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
这里清楚了吧。
----总结：
下次出现这个问题时，先看下当前是什么用户？
mysql> select current_user;
+----------------+
| current_user |
+----------------+
| root@127.0.0.1 |
+----------------+
1 row in set (0.00 sec)
status 命令看到不一定是真的
看下当前用户权限；
show grants;
如果发现没有with grant 权限的；如果知道其他mysql root 用户密码可以使用 mysql -uroot -p -h127.0.0.1 登入；
如果实在不知道，也没关系,直接改mysql的 root 密码吧：
/etc/init.d/mysql stop
mysqld_safe --skip-grants-table &
--修改root用户密码
use mysql;
select host, user, password from user;
update mysql.user set password=password("mysql") where user="root" and host='127.0.0.1';
重启mysql
/etc/init.d/mysql stop
mysqld_safe &
问题现象：
mysql> GRANT ALL PRIVILEGES ON *.* TO 'lixora'@'%' ;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
问题重现及问题分析：
缺省使用 mysql 登入 mysql server：
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4296
Server version: 5.6.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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.
----对lixora 用户授权 ：
mysql> GRANT ALL PRIVILEGES ON *.* TO 'lixora'@'%' ;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
报错了显示用户'root'@'localhost' 没有使用密码
----查看下用户密码信息：
mysql> select host,user,password from mysql.user;
+--------------+--------+-------------------------------------------+
| host | user | password |
+--------------+--------+-------------------------------------------+
| % | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| lixora | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| 127.0.0.1 | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| ::1 | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| | root | |
| localhost | zabbix | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |
| localhost | root | |
| % | zabbix | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |
| 10.80.18.237 | zabbix | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |
| % | lixora | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
+--------------+--------+-------------------------------------------+
10 rows in set (0.00 sec)
-----查看当前窗口用户信息
mysql> status
--------------
mysql Ver 14.14 Distrib 5.6.28, for Linux (x86_64) using EditLine wrapper
Connection id: 4296
Current database:
Current user: root@localhost---------注意！！！
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.28-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 4 hours 16 min 12 sec
Threads: 26 Questions: 123774 Slow queries: 0 Opens: 193 Flush tables: 1 Open tables: 186 Queries per second avg: 8.051
--------------
mysql> select user(), current_user();
+----------------+----------------+
| user() | current_user() |
+----------------+----------------+
| root@localhost | root@localhost | --------注意！！！
+----------------+----------------+
1 row in set (0.00 sec)
mysql> show grants;
+--------------------------------------------------------------+
| Grants for root@localhost |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' |----好像少了点什么？
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)
我们发现当前连接的用户为'root'@'localhost' ，且该用户的权限为【GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' 】
到这里我想应该找到问题的源头了。。。。。
对，没错就是with grant option 权限；
关于with grant option 有 mysql reference的官方解释
WITH GRANT OPTION ：clause gives the user the ability to give to other users any privileges the user has at the specified privilege leve
出现这个问题时会茫然，不能快速找到根本原因是没有理解懂mysql 的用户管理体系：
mysql 的用户不是一个简单的username 来唯一确定，一个真正的mysql user是 ‘username’@‘hostname’ 组合来唯一确定的；
也就是说 root@localhost \\ root@127.0.0.1 不是同一个用户是2个用户，且用户的权限也是完全独立的：
mysql> show grants for 'root'@'localhost';
+--------------------------------------------------------------+
| Grants for root@localhost |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for 'root'@'127.0.0.1';
+---------------------------------------------------------------------------------------------------------------------------
| Grants for root@127.0.0.1 |
+---------------------------------------------------------------------------------------------------------------------------
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
这里清楚了吧。
----总结：
下次出现这个问题时，先看下当前是什么用户？
mysql> select current_user;
+----------------+
| current_user |
+----------------+
| root@127.0.0.1 |
+----------------+
1 row in set (0.00 sec)
status 命令看到不一定是真的
看下当前用户权限；
show grants;
如果发现没有with grant 权限的；如果知道其他mysql root 用户密码可以使用 mysql -uroot -p -h127.0.0.1 登入；
如果实在不知道，也没关系,直接改mysql的 root 密码吧：
/etc/init.d/mysql stop
mysqld_safe --skip-grants-table &
--修改root用户密码
use mysql;
select host, user, password from user;
update mysql.user set password=password("mysql") where user="root" and host='127.0.0.1';
重启mysql
/etc/init.d/mysql stop
mysqld_safe &