How to use trigger forbidden user modify table

Checking table:
create table test_t
(
user char(16),
fullname char(50),
add_date date
)
ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into test_t
values ('u1','u1',NOW());
insert into test_t
values ('u2','u2',NOW());

TEST:
Below is my test result in mysql server. I create a table test_trigger to test.

Authentic  user:
mysql> select * from allow_user;
+--------+------------+------------+
| user   | fullname   | add_date   |
+--------+------------+------------+
| u1     | u1         | 2014-01-05 |
| u2     | u2         | 2014-01-05 |
+--------+------------+------------+
3 rows in set (0.00 sec)

Table script:
create table test_trigger
(
a int(4),
b char(10)
)
ENGINE=InnoDB DEFAULT CHARSET=latin1;

Trigger script:

delimiter ;;
create trigger tr_test_trigger before insert on test_trigger  for each row 
begin
if not exists (select 1 from test_t where user=SUBSTRING_INDEX(user(), '@', 1) ) then
set  @msg = (select concat("DATA change is forbidden. Please contact ",group_concat(fullname separator ','))  from test_t);
SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = @msg;
end if ;
end; 
;;
delimiter ;

Test result:
1. Using login root who have the right of insert data to test_trigger, the insert action is stopped by an user-defined error.
mysql> select user();
+----------------------+
| user()               |
+----------------------+
| root@192.168.1.8     |
+----------------------+
1 row in set (0.00 sec)

mysql> insert into test_trigger values(1,'a');
ERROR 1643 (02000): DATA change is forbidden. Please contact u1,u2
mysql> select * from test_trigger;
Empty set (0.00 sec)

2. Using an authentic account 'u1' which exists in table 'test_t', the action is passed:
mysql> select user();
+----------------------+
| user()               |
+----------------------+
| u1@192.168.1.8       |
+----------------------+
1 row in set (0.00 sec)

mysql> insert into test_trigger values(1,'a');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_trigger;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26735168/viewspace-1067807/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26735168/viewspace-1067807/