WORD '*63DAA25989C7E01EB96570FA4DBE154711BEB361' |
| GRANT SELECT (user, host), UPDATE (host) ON `tempdb`.`tb_user` TO 'frank'@'%' |
+------------------------------------------------------------------------------------------------------+
--下面使用frank身份来验证所授予的权限
frank@172.16.6.89[(none)]> desc tempdb.tb_user;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| Host | char(60) | NO | | | |
| User | char(16) | NO | | | |
+-------+----------+------+-----+---------+-------+
frank@172.16.6.89[(none)]> select * from tempdb.tb_user; --访问时不支持通配符，必须指定列名
ERROR 1142 (42000): SELECT command denied to user 'frank'@'suse11a.site' for table 'tb_user'
frank@172.16.6.89[(none)]> select host,user from tempdb.tb_user where user='frank';
+------+-------+
| host | user |
+------+-------+
| % | frank |
+------+-------+
--需要注意的是，如果你的对象创建在test相关数据库下，权限限制可能会失效。
--下面这个查询用于查看db的授权表
root@localhost[(none)]> select host,db,user from mysql.db;
+------+---------+------+
| host | db | user |
+------+---------+------+
| % | test | |
| % | test\_% | |
+------+---------+------+
--根据前面的权限授予,列host可以被更新，而列user不行，如下面的2条SQL语句执行的结果
frank@172.16.6.89[(none)]> update tempdb.tb_user set host='localhost' where user='frank';
Query OK, 1 row affected (0.12 sec)
Rows matched: 1 Changed: 1 Warnings: 0
frank@172.16.6.89[(none)]> update tempdb.tb_user set user='jason' where user='jack';
ERROR 1143 (42000): UPDATE command denied to user 'frank'@'suse11a.site' for column 'user' in table 'tb_user'
--关于WITH GRANT OPTION
root@localhost[(none)]> show grants; --注意root下有WITH GRANT OPTION
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
root@localhost[(none)]> show grants for 'jack'@'localhost'; --注意jack下没有WITH GRANT OPTION
+---------------------------------------------------+ --这就是前面为什么用户自身创建的对象而无法授权的问题
| Grants for jack@localhost |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'jack'@'localhost' |
+---------------------------------------------------+
4、撤销权限
撤销权限使用的是revoke关键字，撤销与授权的权限方式基本类似，
其次有哪些权限可以授予，相应地就有哪些权限可以撤销，原来的to子句呢则变成了from子句。
如下面的示例
mysql> revoke SELECT (user, host), UPDATE (host) ON `tempdb`.`tb_user` from 'frank'@'%';
mysql> revoke all privileges, grant option from 'frank'@'%';
root@localhost[(none)]> revoke SELECT (user, host), UPDATE (host) ON `tempdb`.`tb_user` from 'frank'@'%';
Query OK, 0 rows affected (0.00 sec)
-- Author : Leshami
-- Blog : http://blog.csdn.net/leshami
root@localhost[(none)]> revoke all privileges, grant option from 'frank'@'%';
Query OK, 0 rows affected (0.01 sec)
root@localhost[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
root@localhost[(none)]> show grants for 'frank'; --查看revoke之后仅拥有最基本权限
+------------------------------------------------