我已经看了很多类似的问题,所以我证明我已经判断了基础知识.当然,这并不意味着我没有错过一些显而易见的事情.:-)
我的问题是:为什么我被拒绝访问一个有权限做我试图做的事情的用户,以及我已经输入了密码并被授予访问权限的用户?(为了完整起见,我try 输入错误的密码,只是为了确保MySQL客户端在程序启动时拒绝我访问.)
背景:
通过ssh登录到运行MySQL服务器的机器shell ,我以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>
令人惊叹的我对类似问题的答案的阅读表明,我应该确保特权与授权表中的内容保持一致
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql>
接下来,确保我是我认为的自己:
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>
到现在为止,一直都还不错.现在我有什么特权?
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)
现在这有点难理解,所以让我们try 一下这种方式(您还将看到有一个非localhost"root"用户):
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)
令人惊叹的我以为我是root@localhost和root@localhost拥有所有这些特权.这意味着我应该能做我想做的事,对吧?
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)
我怎么能把这么基本的事情搞砸?
旁注:对于任何人都建议我没有一个拥有所有特权的用户命名根,这是伟大的,我会考虑做一次,我可以给其他用户一些特权.
非常感谢.