mysql 数据库权限的一个需求实现

ifeelstupid

最后那个语句应该是 revoke all  on mysql.* from 'test'@'%'; 可以根据需求改

ifeelstupid

第三条语句：revoke all  on mysql.* from 'test'@'localhost';
应该是：revoke all  on mysql.* from 'test'@'%';
可以根据需求改！

xiete

ifeelstupid 发表于 2012-9-20 14:55
第三条语句：revoke all  on mysql.* from 'test'@'localhost';
应该是：revoke all  on mysql.* from 'te ...

您的版本是？
我的：

mysql> GRANT ALL  ON *.* TO ass@'%' IDENTIFIED BY 'ass';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> revoke all  on mysql.* from ass@'%';
ERROR 1141 (42000): There is no such grant defined for user 'ass' on host '%'

ifeelstupid

和版本没关系，你没有按照我的步骤做，不要用grant的方式创建用户，你按照我的步骤做一下就知道了！

xiete

ifeelstupid 发表于 2012-9-20 16:41
和版本没关系，你没有按照我的步骤做，不要用grant的方式创建用户，你按照我的步骤做一下就知道了！

mysql> create user test;
Query OK, 0 rows affected (0.00 sec)

mysql>  grant all on *.* to test@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> revoke all  on mysql.* from 'test'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'test' on host '%'
mysql>
mysql>
mysql> revoke all  on mysql.* from test@'%';
ERROR 1141 (42000): There is no such grant defined for user 'test' on host '%'
mysql> revoke all  on mysql.* from 'test'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'test' on host '%'
mysql>

xiete

mysql> select user,host from user;
+------+--------------+
| user | host         |
+------+--------------+
| test | %            |
| root | 127.0.0.1    |
| root | ::1          |
| root | localhost    |
| root | suse10x64sp4 |
+------+--------------+
5 rows in set (0.00 sec)

xiete

suse10x64sp4:~ # mysql -uroot -pass
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.5.9-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, 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.

ifeelstupid

刚才我试了，如果user后面是主机是%，那种方法就不行，但是可以grant all on xxx.* to 给除mysql库外的库授权了！如果库多就不适用了，但是两三个库是可以接受的吧！

xiete

谢谢，因为是用户行为，库多少全由用户自己建立。

longxibendi

这种需求，权限太大；