MySQL回收某一授权

创建测试表
create table t1（id int）;
create table t2（id int）;
create table t3（id int）;
create table t4（msg varchar（100））;如果授权的时候图省事儿,使用通配符授权.
grant select,insert,update,delete on mvbox.* to "xx"@"localhost" identified by "xx";如果以后需要回收某一张表权限的时候,就会比较麻烦.
mysql> show grants for xx@"localhost";
+-----------------------------------------------------------------------------------------------------------+
| Grants for xx@localhost |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO "xx"@"localhost" IDENTIFIED BY PASSWORD "*B30134364A2D14319904C2C807363CF2C81ABD5B" |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mvbox`.* TO "xx"@"localhost" |
+-----------------------------------------------------------------------------------------------------------+
2 rows in set （0.00 sec） mysql> revoke insert on mvbox.t1 from xx@"localhost";
ERROR 1147 （42000）: There is no such grant defined for user "xx" on host "localhost" on table "t1"
mysql>因为授权是使用的通配符,回收也需要使用通配符.如果需要回收t1表的insert权限,可以使用如下的触发器.delimiter //

CREATE TRIGGER tri1 BEFORE INSERT ON t1 FOR EACH ROWBEGIN DECLARE msg varchar（100）; DECLARE cu varchar（40）; set cu=（select substring_index（（select user（））,"@",1）） ; IF cu="xx" THEN set msg = concat（cu,"You have no right to operate data！please connect DBAs"）; SIGNAL SQLSTATE "HY000" SET MESSAGE_TEXT = msg; END IF;END;//delimiter ;
这时,以xx登录,insert t1表则报错如下
mysql> insert into t1 values（10）;
ERROR 1644 （HY000）: xx You have no right to operate data！please connect DBAs
这里需要注意的是current_user,user函数,在触发器调用的时候,返回的内容是不一样的.
删除原来的触发器,新建一个触发器测试
drop trigger tri1;

delimiter //CREATE TRIGGER `tri2` BEFORE INSERT ON `t1` FOR EACH ROWBEGIN insert into t4 values（concat（current_user（）,",",user（）,",",session_user（）））;END;//delimiter ;
使用xx用户登录,执行如下命令
mysql> insert into t1 values（10）;
Query OK, 1 row affected （0.00 sec） mysql> select * from t4;
+------------------------------------------+
| msg |
+------------------------------------------+
| root@localhost,xx@localhost,xx@localhost |
+------------------------------------------+
1 row in set （0.00 sec）可以发现在触发器中,current_user（）返回的是触发器的定义者.
而user（）,session_user（）才是连接的用户.mysql>本文永久更新链接地址