mysql grant 命令无效，已 flush privileges

Question

一开始建了个数据库 ftp,里面有张表 user,root 授权 用户 vhost 可以查询这张表.
GRANT select ON ftp.user TO 'vhost'@'localhost' identified by PASSWORD('xx');刷新授权flush privileges;测试ok.然后 我在phpmyadmin 里面删掉了这个数据库.drop database ftp;好像系统提示 什么授权不会被删除....(没注意看)之后，我重建了这个数据库，新建了两个表 user,log.可是问题出现了当我再次授权 vhost 可以去查询 这个两张表时候，发现vhost 只能查user表了，刷新重读授权都没用了。
GRANT select ON ftp.* TO 'vhost'@'localhost' identified by PASSWORD('xx');最后，我重启了mysql.
./mysqladmin -u root -p shutdown不过还是没用。已vhost 登录
./mysql -u vhost -p --default-character=latin1
不过use ftp;
show tables;还是只能看到user表。我哭了。。硬查询就报 Access Denied...有人知道 这是什么原因吗？？？哦，对了,我用的是 mysql 5.4.3-beta 源码自编译安装版本.

Answer 1

SHOW GRANTS FOR 'vhost'@'localhost'检查一下你的授权另外，用grant 其实不需要 flush privileges; 的。 当你直接把授权表 user, db , 等才需要

Answer 2

猜不出。如果有用户说它的权限不足。
首先，我们会 SHOW GRANTS FOR username 
来检查，看了之后再做下一步分析。如果这一步都不做，那就是直接闭着眼睛猜了。把几十种，甚至几百种可能的原因一个一个问过来太浪费时间了。不是处理问题的科学方法。 比如，有人问为什么电灯不这。做为专业的工程师，显然你要做的事是去量电压，而不是猜是开关？是灯泡？还是线路？。等原因。

Answer 3

grant all on *.* to 'vhost'@'localhost' identified by PASSWORD('xx');
再DROP试试

Answer 4

[SQL]
mysql> show grants for 'vhost'@'localhost';
+-------------------------------------------------------------------------------              -------------------------------+
| Grants for vhost@localhost                                                                                                 |
+-------------------------------------------------------------------------------              -------------------------------+
| GRANT USAGE ON *.* TO 'vhost'@'localhost' IDENTIFIED BY PASSWORD '*653E55BC343              28FD9504096B9DFB2434DE24AAE86' |
| GRANT SELECT ON `vsftpd`.`users` TO 'vhost'@'localhost'                                                                    |
+-------------------------------------------------------------------------------              -------------------------------+
2 rows in set (0.01 sec)
[/SQL]

Answer 5

只有 select 权限，为什么呢?

Answer 6

不是你自己授的权吗？GRANT select ON ftp.user TO 'vhost'@'localhost' identified by PASSWORD('xx');你只给了 user 表上的 select 权限啊

Answer 7

ls 你看来没有认真看我的帖子，我新增了一个log表，也授权了呀GRANT select ON ftp.* TO 'vhost'@'localhost' identified by PASSWORD('xx');

Answer 8

你将所有权限都允许，再试
grant all on *.* to 'vhost'@'localhost' identified by PASSWORD('xx'); 

Answer 9

这样不好吧都授权了 ，我还要它干嘛？ 直接用root 不是来的更方便.

Answer 10

第二次的时候，你不需要加  identified by PASSWORD('xx');直接 GRANT select ON ftp.* TO 'vhost'@'localhost' 
因为你的用户已经建立了。如果你是在mysql命令行工具中应该就容易看到了

Answer 11

那就只给数据库权限
GRANT ALL ON ftp.* TO 'vhost'@'localhost';

Answer 12

mysql> grant all on vsftpd.* to 'vhost'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)mysql> show grants for 'vhost'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for vhost@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'vhost'@'localhost' IDENTIFIED BY PASSWORD '*653E55BC34328FD9504096B9DFB2434DE24AAE86' |
| GRANT SELECT ON `vsftpd`.`users` TO 'vhost'@'localhost'                                                      |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Answer 13

grant all on vsftpd.* to 'vhost'@'127.0.0.1';show grants for 'vhost'@'localhost';这是同一个用户吗？！！！

Answer 14

好吧，我承认是我疏忽了。可是，为什么 @ localhost 还是 只有 select 权限呢?mysql> show grants for 'vhost'@'127.0.0.1'
    -> ;
+--------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'vhost'@'127.0.0.1' IDENTIFIED BY PASSWORD '*653E55BC34328FD9504096B9DFB2434DE24AAE86' |
| GRANT ALL PRIVILEGES ON `vsftpd`.* TO 'vhost'@'127.0.0.1'                                                    |
| GRANT SELECT, INSERT ON `vsftpd`.`t1` TO 'vhost'@'127.0.0.1'                                                 |
| GRANT SELECT, INSERT ON `vsftpd`.`logs` TO 'vhost'@'127.0.0.1'                                               |
| GRANT SELECT ON `vsftpd`.`users` TO 'vhost'@'127.0.0.1'                                                      |
+--------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)mysql> show grants for 'vhost'@'localhost'
    -> ;
+--------------------------------------------------------------------------------------------------------------+
| Grants for vhost@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'vhost'@'localhost' IDENTIFIED BY PASSWORD '*653E55BC34328FD9504096B9DFB2434DE24AAE86' |
| GRANT ALL PRIVILEGES ON `vsftpd`.* TO 'vhost'@'localhost'                                                    |
| GRANT SELECT ON `vsftpd`.`users` TO 'vhost'@'localhost'                                                      |
+--------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Answer 15

ok,问题至此彻底解决.看来我的确是杯具了....昨个居然看了半天没看出来
正确权限如下:
mysql> show grants for 'vhost'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for vhost@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'vhost'@'localhost' IDENTIFIED BY PASSWORD '*653E55BC34328FD9504096B9DFB2434DE24AAE86' |
| GRANT INSERT ON `vsftpd`.`logs` TO 'vhost'@'localhost'                                                       |
| GRANT SELECT ON `vsftpd`.`users` TO 'vhost'@'localhost'                                                      |
+--------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)mysql> show grants for 'vhost'@'127.0.0.1';
+--------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'vhost'@'127.0.0.1' IDENTIFIED BY PASSWORD '*653E55BC34328FD9504096B9DFB2434DE24AAE86' |
| GRANT SELECT ON `vsftpd`.`users` TO 'vhost'@'127.0.0.1'                                                      |
| GRANT INSERT ON `vsftpd`.`logs` TO 'vhost'@'127.0.0.1'                                                       |
+--------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)