尝试授予特权时，拒绝用户'root'@'localhost'的访问.如何授予特权? [英] Access denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges?
问题描述
我研究了许多类似的问题，因此表明我已经检查了基础知识.当然，这并不意味着我没有错过任何显而易见的事情. :-)
I've looked at a number of similar questions and so I'm demonstrating that I've checked the basics. Though of course, that doesn't mean I haven't missed something totally obvious. :-)
我的问题是:为什么我拒绝具有特权的用户进行我想做的事情的访问，而我已经在其中键入密码并被授予访问权限? (为了完整起见，我尝试输入错误的密码只是为了确保MySQL客户端在程序启动时拒绝我访问.)
My question is: why am I denied access on a user with the privileges to do what I'm trying to do and where I have already typed the password and been granted access? (For the sake of completeness, I tried typing the wrong password just to make sure that MySQL client would deny me access at program start.)
背景:
通过ssh登录到运行MySQL服务器的计算机的外壳，我以root用户身份登录:
Logged in to the shell of the machine running the MySQL server via ssh, I log in as root:
[myname@host ~]$ mysql -u root -p -hlocalhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 62396
Server version: 5.5.18-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
太棒了.通过阅读类似问题的答案，我建议确保特权列表中的特权是最新的
Awesome. My reading of the answers to similar questions suggests that I should make sure the the privileges are current with what is in the grant tables
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql>
接下来，请确保我是我想的那个人:
Next make sure I am who I think I am:
mysql> SELECT user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
...真的真的确保:
mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>
到目前为止，一切都很好.现在我拥有什么特权?
So far so good. Now what privileges do I have?
mysql> SHOW GRANTS FOR 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '[OBSCURED]' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
现在有点难以理解，所以让我们尝试这种方式(您还将看到有一个非本地"root"用户):
Now that's a little hard to read, so lets try this way (you will also get to see that there is a non-localhost 'root' user):
mysql> SELECT * FROM mysql.user WHERE User='root'\G
*************************** 1. row ***************************
Host: localhost
User: root
Password: *[OBSCURED]
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
*************************** 2. row ***************************
Host: [HOSTNAME].com
User: root
Password: *[OBSCURED]
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
2 rows in set (0.00 sec)
太棒了！ MySQL认为我是root @ localhost，而root @ localhost拥有所有这些特权.那意味着我应该能够做我想做的事，对吧?
Awesome! MySQL thinks that I am root@localhost and root@localhost has all those privileges. That means I ought to be able to do what I want, right?
mysql> GRANT ALL PRIVILEGES ON *.* TO 'steves'@'[hostname].com' IDENTIFIED BY '[OBSCURED]' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
我怎么能弄糟这个基本的东西?
How could I have screwed up something this basic?
旁注:对于任何想建议我没有名为root的用户都具有所有特权的用户，这很棒，而且一旦我可以给另一个用户一些特权，我就会考虑做这件事.
Side note: for anyone who wants to suggest that I not have a user named root with all privileges, that's great and something I'll consider doing once I can give another user some privileges.
谢谢！
推荐答案
注意输出情况
SHOW GRANTS FOR 'root'@'localhost';
没有说所有特权"，而是不得不说出root @ localhost拥有什么.
did not say 'ALL PRIVILEGES' but had to spell out what root@localhost has.
GRANT ALL PRIVILEGES将失败，因为用户无法授予他/她没有的权限， 并且服务器似乎认为这里没有东西...
GRANT ALL PRIVILEGES will fail, because a user can not grant what he/she does not have, and the server seem to think something is not here ...
现在，接下来要缺少什么?
Now, what's missing then ?
在我的系统上，我得到了:
On my system, I get this:
mysql> select version();
+------------+
| version() |
+------------+
| 5.5.21-log |
+------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM mysql.user WHERE User='root' and Host='localhost'\G
*************************** 1. row ***************************
Host: localhost
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y <----------------------------- new column in 5.5
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: <------------------------------- new column in 5.5
authentication_string: <------------------------------- new column in 5.5
1 row in set (0.00 sec)
5.5中也有新表，例如mysql.proxies_user:确保您拥有它们.
There are also new tables in 5.5, such as mysql.proxies_user: make sure you have them.
在安装全新的mysql服务器实例时，安装脚本将创建具有适当结构的所有mysql.*表.
When installing a brand new mysql server instance, the install script will create all the mysql.* tables with the proper structure.
从旧版本升级时，请确保使用正确的升级过程(mysql_upgrade)，它将添加缺少的表/列.
When upgrading from an old version, make sure the proper upgrade procedure (mysql_upgrade) is used, which will add the missing tables / columns.
这只是一个猜测，但是看来该实例的mysql_upgrade没有完成，导致出现了该行为.
It is only a guess, but it seems mysql_upgrade was not done for this instance, causing the behavior seen.
这篇关于尝试授予特权时，拒绝用户'root'@'localhost'的访问.如何授予特权?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
