MySQL revokes an authorization

MySQL revokes an authorization

Create test table
Create table t1 (id int );
Create table t2 (id int );
Create table t3 (id int );
Create table t4 (msg varchar (100 ));

If the wildcard is used for authorization.
Grant select, insert, update, delete on mvbox. * to 'xx' @ 'localhost' identified by 'xx ';

If you need to revoke the permissions of a table in the future, it will be troublesome.
Mysql> show grants for xx @ 'localhost ';
+ Shards +
| Grants for xx @ localhost |
+ Shards +
| Grant usage on *. * TO 'xx' @ 'localhost' identified by password' * B30134364A2D14319904C2C807363CF2C81ABD5B '|
| Grant select, INSERT, UPDATE, delete on 'mvbox'. * TO 'xx' @ 'localhost' |
+ Shards +
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>

Because the authorization is a wildcard, the wildcard is also required for revocation.

To revoke the insert permission of table t1, you can use the following trigger.

Delimiter //
 
Create trigger tri1 before insert on t1 FOR EACH ROW

BEGIN

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;
In this case, Log On As xx, and the insert t1 table reports the following error:
Mysql> insert into t1 values (10 );
ERROR 1644 (HY000): xx You have no right to operate data! Please connect DBAs

 

Note that the current_user and user functions return different content when the trigger is called.
Delete the original trigger and create a new trigger for testing.

Drop trigger tri1;
 
Delimiter //

Create trigger 'tri2' before insert on 't1' FOR EACH ROW

BEGIN

Insert into t4 values (concat (current_user (), ',', user (), ',', session_user ()));

END;

//

Delimiter;
Log On As A xx user and run the following command:
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)

In a trigger, current_user () returns the definer of the trigger.
User () and session_user () are connected users.

Mysql>

This article permanently updates the link address: