Edit File - File Manager

Edit File: innodb_trig_03e.result

USE test;

Testcase for db level:
----------------------
drop database if exists priv_db;
drop database if exists no_priv_db;
create database priv_db;
create database no_priv_db;
use priv_db;
create table t1 (f1 char(20)) engine= innodb;
create User test_yesprivs@localhost;
set password for test_yesprivs@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant select on priv_db.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
create User test_noprivs@localhost;
set password for test_noprivs@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant select,insert on priv_db.* to test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT ON `priv_db`.* TO 'test_noprivs'@'localhost'

no trigger privilege on db level for create:
--------------------------------------------
use priv_db;
create trigger trg1_1 before INSERT on t1 for each row
set new.f1 = 'trig 1_1-no';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
use priv_db;
insert into t1 (f1) values ('insert-yes');
select f1 from t1 order by f1;
f1
insert-yes
select current_user;
current_user
root@localhost
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'

no trigger privilege on db level for drop:
------------------------------------------
select current_user;
current_user
test_yesprivs@localhost
use priv_db;
drop trigger trg1_2;
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select current_user;
current_user
test_noprivs@localhost
use priv_db;

no trigger privilege at activation time:
----------------------------------------
insert into t1 (f1) values ('insert-yes');
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select f1 from t1 order by f1;
f1
insert-yes
trig 1_2-yes
trig 1_2-yes

trigger privilege at activation time:
-------------------------------------
select current_user;
current_user
root@localhost
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
select current_user;
current_user
test_noprivs@localhost
use priv_db;
insert into t1 (f1) values ('insert-no');
select f1 from t1 order by f1;
f1
insert-yes
trig 1_2-yes
trig 1_2-yes
trig 1_2-yes

trigger privilege on db level for drop:
---------------------------------------
select current_user;
current_user
test_yesprivs@localhost
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD <secret>
GRANT SELECT, UPDATE, TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
drop trigger trg1_2;
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'

takes effect after use priv_db:
-------------------------------
use priv_db;
drop trigger trg1_2;
select current_user;
current_user
root@localhost
use priv_db;
insert into t1 (f1) values ('insert-yes');
select f1 from t1 order by f1;
f1
insert-yes
insert-yes
trig 1_2-yes
trig 1_2-yes
trig 1_2-yes

switch to db without having trigger priv for it:
------------------------------------------------
use no_priv_db;
create table t1 (f1 char(20)) engine= innodb;
grant SELECT,UPDATE on no_priv_db.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, UPDATE, TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
GRANT SELECT, UPDATE ON `no_priv_db`.* TO 'test_yesprivs'@'localhost'

use db with trigger privilege on db level and without...:
---------------------------------------------------------
select current_user;
current_user
test_yesprivs@localhost
use no_priv_db;
create trigger trg1_3 before INSERT  on t1 for each row
set new.f1 = 'trig 1_3-no';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
use priv_db;
create trigger trg1_3 before INSERT  on t1 for each row
set new.f1 = 'trig 1_3-yes';
use no_priv_db;
create trigger trg1_4 before UPDATE  on t1 for each row
set new.f1 = 'trig 1_4-no';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
use priv_db;
create trigger trg1_4 before UPDATE  on t1 for each row
set new.f1 = 'trig 1_4-yes';
select current_user;
current_user
test_noprivs@localhost
use no_priv_db;
ERROR 42000: Access denied for user 'test_noprivs'@'localhost' to database 'no_priv_db'
insert into t1 (f1) values ('insert-yes');
select f1 from t1 order by f1;
f1
insert-yes
insert-yes
trig 1_2-yes
trig 1_2-yes
trig 1_2-yes
trig 1_3-yes
use priv_db;
insert into t1 (f1) values ('insert-no');
select f1 from t1 order by f1;
f1
insert-yes
insert-yes
trig 1_2-yes
trig 1_2-yes
trig 1_2-yes
trig 1_3-yes
trig 1_3-yes
select current_user;
current_user
test_yesprivs@localhost
use no_priv_db;
drop trigger trg1_3;
ERROR HY000: Trigger does not exist
use priv_db;
drop trigger trg1_3;
use no_priv_db;
drop trigger trg1_4;
ERROR HY000: Trigger does not exist
use priv_db;
drop trigger trg1_4;
select current_user;
current_user
root@localhost
drop table priv_db.t1;
drop table no_priv_db.t1;
drop database if exists priv_db;
drop database if exists no_priv_db;
drop user test_yesprivs@localhost;
drop user test_noprivs@localhost;

#########      Testcase for table level:   ########
---------------------------------------------------
drop database if exists priv_db;
create database priv_db;
use priv_db;
create table t1 (f1 char(20)) engine= innodb;
create User test_yesprivs@localhost;
set password for test_yesprivs@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
create User test_noprivs@localhost;
set password for test_noprivs@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;

no trigger privilege on table level for create:
-----------------------------------------------
select current_user;
current_user
root@localhost
show triggers;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
grant  select, insert, update on priv_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
grant select, update, insert on priv_db.t1 to test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
select current_user;
current_user
test_yesprivs@localhost
use priv_db;
show tables;
Tables_in_priv_db
t1
create trigger trg1_1 before INSERT on t1 for each row
set new.f1 = 'trig 1_1-no';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select current_user;
current_user
test_noprivs@localhost
use priv_db;
insert into t1 (f1) values ('insert1-yes');
select f1 from t1 order by f1;
f1
insert1-yes
select current_user;
current_user
root@localhost
show triggers;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
show tables;
Tables_in_priv_db
t1
insert into t1 (f1) values ('insert2-yes');
select f1 from t1 order by f1;
f1
insert1-yes
insert2-yes
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'

no trigger privilege on table level for drop:
---------------------------------------------
select current_user;
current_user
test_yesprivs@localhost
drop trigger trg1_2;
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'

no trigger privilege at activation time:
----------------------------------------
select current_user;
current_user
test_noprivs@localhost
insert into t1 (f1) values ('insert5-no');
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select f1 from t1 order by f1;
f1
insert1-yes
insert2-yes
trig 1_2-yes
trig 1_2-yes
select current_user;
current_user
root@localhost
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;

trigger privilege at activation time:
-------------------------------------
select current_user;
current_user
test_noprivs@localhost
insert into t1 (f1) values ('insert6-no');
select f1 from t1 order by f1;
f1
insert1-yes
insert2-yes
trig 1_2-yes
trig 1_2-yes
trig 1_2-yes

trigger privilege on table level for drop:
------------------------------------------
select current_user;
current_user
test_yesprivs@localhost
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD <secret>
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
drop trigger trg1_2;
select current_user;
current_user
test_noprivs@localhost
insert into t1 (f1) values ('insert7-yes');
select f1 from t1 order by f1;
f1
insert1-yes
insert2-yes
insert7-yes
trig 1_2-yes
trig 1_2-yes
trig 1_2-yes
select current_user;
current_user
root@localhost
insert into t1 (f1) values ('insert8-yes');
select f1 from t1 order by f1;
f1
insert1-yes
insert2-yes
insert7-yes
insert8-yes
trig 1_2-yes
trig 1_2-yes
trig 1_2-yes

switch to table without having trigger priv for it:
---------------------------------------------------
create table t2 (f1 char(20)) engine= innodb;
grant SELECT, INSERT, UPDATE on priv_db.t2 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
grant SELECT, INSERT, UPDATE on priv_db.t2 to test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t2` TO 'test_noprivs'@'localhost'

use table with trigger privilege and without...:
------------------------------------------------
select current_user;
current_user
test_yesprivs@localhost
create trigger trg2_1 before INSERT  on t2 for each row
set new.f1 = 'trig 2_1-no';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
create trigger trg1_3 before INSERT  on t1 for each row
set new.f1 = 'trig 1_3-yes';
create trigger trg2_2 before UPDATE  on t2 for each row
set new.f1 = 'trig 2_2-no';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
create trigger trg1_4 before UPDATE  on t1 for each row
set new.f1 = 'trig 1_4-yes';
show triggers;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
trg1_3	INSERT	t1	set new.f1 = 'trig 1_3-yes'	BEFORE	NULL		test_yesprivs@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
trg1_4	UPDATE	t1	set new.f1 = 'trig 1_4-yes'	BEFORE	NULL		test_yesprivs@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
select current_user;
current_user
test_noprivs@localhost
insert into t2 (f1) values ('insert9-yes');
select f1 from t2 order by f1;
f1
insert9-yes
insert into t1 (f1) values ('insert10-no');
select f1 from t1 order by f1;
f1
insert1-yes
insert2-yes
insert7-yes
insert8-yes
trig 1_2-yes
trig 1_2-yes
trig 1_2-yes
trig 1_3-yes
select current_user;
current_user
test_yesprivs@localhost
drop trigger trg2_1;
ERROR HY000: Trigger does not exist
drop trigger trg1_3;
drop trigger trg2_2;
ERROR HY000: Trigger does not exist
drop trigger trg1_4;
select current_user;
current_user
root@localhost
drop database if exists priv_db;
drop user test_yesprivs@localhost;
drop user test_noprivs@localhost;

#### Testcase for mix of user(global) and db level: ####
--------------------------------------------------------
drop database if exists priv_db;
drop database if exists no_priv_db;
create database priv_db;
create database no_priv_db;
use priv_db;
create table t1 (f1 char(20)) engine= innodb;
use no_priv_db;
create table t1 (f1 char(20)) engine= innodb;
create User test_yesprivs@localhost;
set password for test_yesprivs@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant ALL  on *.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT ALL PRIVILEGES ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
create User test_noprivs@localhost;
set password for test_noprivs@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant SELECT,INSERT  on *.* to test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT SELECT, INSERT ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
select current_user;
current_user
test_yesprivs@localhost

trigger privilege on user level for create:
-------------------------------------------
use priv_db;
create trigger trg1_1 before INSERT  on t1 for each row
set new.f1 = 'trig 1_1-yes';
insert into t1 (f1) values ('insert-no');
select f1 from t1 order by f1;
f1
trig 1_1-yes
use no_priv_db;
create trigger priv_db.trg1_5 before UPDATE  on priv_db.t1
for each row
set new.f1 = 'trig 1_5-yes';
insert into priv_db.t1 (f1) values ('insert-no');
select f1 from priv_db.t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
drop trigger priv_db.trg1_5;
select current_user;
current_user
test_noprivs@localhost
use priv_db;
insert into t1 (f1) values ('insert-no');
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
select current_user;
current_user
root@localhost
use priv_db;
insert into t1 (f1) values ('insert-no');
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
revoke TRIGGER on *.* from test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, CREATE TABLESPACE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
select current_user;
current_user
test_yesprivs@localhost
use priv_db;
show triggers;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
select * from information_schema.triggers;
TRIGGER_CATALOG	TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_MANIPULATION	EVENT_OBJECT_CATALOG	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_ORDER	ACTION_CONDITION	ACTION_STATEMENT	ACTION_ORIENTATION	ACTION_TIMING	ACTION_REFERENCE_OLD_TABLE	ACTION_REFERENCE_NEW_TABLE	ACTION_REFERENCE_OLD_ROW	ACTION_REFERENCE_NEW_ROW	CREATED	SQL_MODE	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
drop trigger trg1_1;
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select current_user;
current_user
root@localhost
show grants;
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
drop trigger trg1_1;
use priv_db;

no trigger privilege on db level for create:
--------------------------------------------
select current_user;
current_user
test_yesprivs@localhost
create trigger trg1_1 before INSERT on t1 for each row
set new.f1 = 'trig 1_1-no';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select current_user;
current_user
test_noprivs@localhost
use priv_db;
insert into t1 (f1) values ('insert-yes');
select f1 from t1 order by f1;
f1
insert-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
select current_user;
current_user
root@localhost
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, CREATE TABLESPACE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'

trigger privilege on db level for create:
-----------------------------------------
select current_user;
current_user
test_yesprivs@localhost
use priv_db;
create trigger trg1_2 before INSERT  on t1 for each row
set new.f1 = 'trig 1_2-yes';
create trigger no_priv_db.trg1_9 before insert on no_priv_db.t1
for each row
set new.f1 = 'trig 1_9-yes';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
use no_priv_db;
create trigger trg1_2 before INSERT  on t1 for each row
set new.f1 = 'trig 1_2-no';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
create trigger priv_db.trg1_9 before UPDATE on priv_db.t1
for each row
set new.f1 = 'trig 1_9-yes';
select current_user;
current_user
test_noprivs@localhost
use priv_db;
insert into t1 (f1) values ('insert-yes');
select f1 from t1 order by f1;
f1
insert-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_2-yes
use no_priv_db;
insert into t1 (f1) values ('insert-yes');
select f1 from t1 order by f1;
f1
insert-yes
drop trigger priv_db.trg1_9;
ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table 't1'
select current_user;
current_user
root@localhost
drop trigger priv_db.trg1_9;
revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
use priv_db;
insert into t1 (f1) values ('insert-yes');
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select f1 from t1 order by f1;
f1
insert-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_2-yes
grant TRIGGER on *.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT ALL PRIVILEGES ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
select current_user;
current_user
test_yesprivs@localhost
use no_priv_db;
create trigger trg1_2 before INSERT  on t1 for each row
set new.f1 = 'trig 1_2-no';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select current_user;
current_user
test_noprivs@localhost
use priv_db;
insert into t1 (f1) values ('insert-no');
select f1 from t1 order by f1;
f1
insert-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_2-yes
trig 1_2-yes
use no_priv_db;
insert into t1 (f1) values ('insert-yes');
select f1 from t1 order by f1;
f1
insert-yes
insert-yes
select current_user;
current_user
test_yesprivs@localhost
use no_priv_db;
create trigger trg1_2 before INSERT  on t1 for each row
set new.f1 = 'trig 1_2-yes';
select current_user;
current_user
test_noprivs@localhost
use priv_db;
insert into t1 (f1) values ('insert-no');
select f1 from t1 order by f1;
f1
insert-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_2-yes
trig 1_2-yes
trig 1_2-yes
use no_priv_db;
insert into t1 (f1) values ('insert-no');
select f1 from t1 order by f1;
f1
insert-yes
insert-yes
trig 1_2-yes
select current_user;
current_user
root@localhost
drop database if exists priv_db;
drop database if exists no_priv_db;
drop database if exists h1;
drop user test_yesprivs@localhost;
drop user test_noprivs@localhost;

####### Testcase for mix of db and table level: #######
-------------------------------------------------------
drop database if exists priv1_db;
drop database if exists priv2_db;
create database priv1_db;
create database priv2_db;
use priv1_db;
create table t1 (f1 char(20)) engine= innodb;
create table t2 (f1 char(20)) engine= innodb;
use priv2_db;
create table t1 (f1 char(20)) engine= innodb;
create User test_yesprivs@localhost;
set password for test_yesprivs@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant ALL  on priv1_db.* to test_yesprivs@localhost;
grant SELECT,UPDATE on priv2_db.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT ALL PRIVILEGES ON `priv1_db`.* TO 'test_yesprivs'@'localhost'
GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost'
create User test_noprivs@localhost;
set password for test_noprivs@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant SELECT,INSERT,UPDATE on priv1_db.* to test_noprivs@localhost;
grant SELECT,INSERT on priv2_db.* to test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE ON `priv1_db`.* TO 'test_noprivs'@'localhost'
GRANT SELECT, INSERT ON `priv2_db`.* TO 'test_noprivs'@'localhost'
use priv1_db;
use priv1_db;

trigger privilege on one db1 db level, not on db2
-------------------------------------------------
select current_user;
current_user
test_yesprivs@localhost
use priv1_db;
create trigger trg1_1 before INSERT  on t1 for each row
set new.f1 = 'trig 1_1-yes';
create trigger trg2_1 before INSERT  on t2 for each row
set new.f1 = 'trig 2_1-yes';
use priv2_db;
create trigger trg1_1 before INSERT  on t1 for each row
set new.f1 = 'trig1_1-yes';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select current_user;
current_user
test_noprivs@localhost
insert into t1 (f1) values ('insert1_no');
select f1 from t1 order by f1;
f1
trig 1_1-yes
insert into t2 (f1) values ('insert1_no');
select f1 from t2 order by f1;
f1
trig 2_1-yes
insert into priv2_db.t1 (f1) values ('insert21-yes');
select f1 from priv2_db.t1 order by f1;
f1
insert21-yes
use priv2_db;
insert into t1 (f1) values ('insert1_yes');
select f1 from t1 order by f1;
f1
insert1_yes
insert21-yes
insert into priv1_db.t1 (f1) values ('insert11-no');
select f1 from priv1_db.t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
insert into priv1_db.t2 (f1) values ('insert22-no');
select f1 from priv1_db.t2 order by f1;
f1
trig 2_1-yes
trig 2_1-yes

revoke trigger privilege on table level (not existing)
------------------------------------------------------
select current_user;
current_user
root@localhost
use priv1_db;
revoke TRIGGER on priv1_db.t1 from test_yesprivs@localhost;
ERROR 42000: There is no such grant defined for user 'test_yesprivs' on host 'localhost' on table 't1'
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT ALL PRIVILEGES ON `priv1_db`.* TO 'test_yesprivs'@'localhost'
GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost'
select current_user;
current_user
test_yesprivs@localhost
drop trigger trg1_1;
ERROR HY000: Trigger does not exist
drop trigger trg2_1;
ERROR HY000: Trigger does not exist
use priv1_db;
drop trigger trg1_1;
drop trigger trg2_1;
select current_user;
current_user
root@localhost
use priv1_db;
revoke TRIGGER on priv1_db.* from test_yesprivs@localhost;

no trigger privilege on table level for create:
-----------------------------------------------
select current_user;
current_user
test_yesprivs@localhost
use priv1_db;
create trigger trg1_1 before INSERT on t1 for each row
set new.f1 = 'trig 1_1-no';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select current_user;
current_user
root@localhost
show triggers;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
grant TRIGGER on priv1_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT ON `priv1_db`.* TO 'test_yesprivs'@'localhost'
GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost'
GRANT TRIGGER ON `priv1_db`.`t1` TO 'test_yesprivs'@'localhost'

trigger privilege on table level for create:
--------------------------------------------
select current_user;
current_user
test_yesprivs@localhost
show triggers;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
create trigger trg1_2 before INSERT  on t1 for each row
set new.f1 = 'trig 1_2-yes';
create trigger trg2_1 before INSERT  on t2 for each row
set new.f1 = 'trig 2_1-no';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
select current_user;
current_user
test_noprivs@localhost
use priv1_db;
insert into t1 (f1) values ('insert2-no');
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
trig 1_2-yes
insert into t2 (f1) values ('insert2-yes');
select f1 from t2 order by f1;
f1
insert2-yes
trig 2_1-yes
trig 2_1-yes
insert into priv2_db.t1 (f1) values ('insert22-yes');
select f1 from priv2_db.t1 order by f1;
f1
insert1_yes
insert21-yes
insert22-yes
select current_user;
current_user
root@localhost
grant TRIGGER on priv1_db.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT ALL PRIVILEGES ON `priv1_db`.* TO 'test_yesprivs'@'localhost'
GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost'
GRANT TRIGGER ON `priv1_db`.`t1` TO 'test_yesprivs'@'localhost'
select current_user;
current_user
test_yesprivs@localhost
create trigger trg2_1 before INSERT  on t2 for each row
set new.f1 = 'trig 2_1-yes';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
use priv1_db;
create trigger trg2_1 before INSERT  on t2 for each row
set new.f1 = 'trig 2_1-yes';
select current_user;
current_user
test_noprivs@localhost
use priv1_db;
insert into t1 (f1) values ('insert3-no');
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
trig 1_2-yes
trig 1_2-yes
insert into t2 (f1) values ('insert3-no');
select f1 from t2 order by f1;
f1
insert2-yes
trig 2_1-yes
trig 2_1-yes
trig 2_1-yes
use priv2_db;
insert into priv1_db.t1 (f1) values ('insert12-no');
select f1 from priv1_db.t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
trig 1_2-yes
trig 1_2-yes
trig 1_2-yes
insert into priv1_db.t2 (f1) values ('insert23-no');
select f1 from priv1_db.t2 order by f1;
f1
insert2-yes
trig 2_1-yes
trig 2_1-yes
trig 2_1-yes
trig 2_1-yes
select current_user;
current_user
test_yesprivs@localhost
drop trigger trg1_2;
drop trigger trg2_1;
select current_user;
current_user
root@localhost
drop database if exists priv1_db;
drop database if exists priv2_db;
drop user test_yesprivs@localhost;
drop user test_noprivs@localhost;

#### Testcase for trigger privilege on execution time ########
--------------------------------------------------------------
drop database if exists priv_db;
create database priv_db;
use priv_db;
create table t1 (f1 char(20)) engine= innodb;
create User test_yesprivs@localhost;
set password for test_yesprivs@localhost = password('PWD');
create User test_useprivs@localhost;
set password for test_useprivs@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
revoke ALL PRIVILEGES, GRANT OPTION FROM test_useprivs@localhost;
select current_user;
current_user
root@localhost
show triggers;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
grant  select, insert, update ,trigger
on priv_db.t1 to test_yesprivs@localhost
with grant option;
grant  select
on priv_db.t1 to test_useprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
select current_user;
current_user
test_yesprivs@localhost
use priv_db;
create trigger trg1_1 before INSERT on t1 for each row
set new.f1 = 'trig 1_1-yes';
grant insert on t1 to test_useprivs@localhost;
prepare ins1 from 'insert into t1 (f1) values (''insert1-no'')';
execute ins1;
select f1 from t1 order by f1;
f1
trig 1_1-yes
prepare ins1 from 'insert into t1 (f1) values (''insert2-no'')';
select current_user;
current_user
test_useprivs@localhost
use priv_db;
prepare ins1 from 'insert into t1 (f1) values (''insert3-no'')';
execute ins1;
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
select current_user;
current_user
root@localhost
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
select current_user;
current_user
test_yesprivs@localhost
execute ins1;
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
prepare ins1 from 'insert into t1 (f1) values (''insert4-no'')';
select current_user;
current_user
test_useprivs@localhost
prepare ins1 from 'insert into t1 (f1) values (''insert5-no'')';
execute ins1;
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
select current_user;
current_user
root@localhost
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
select current_user;
current_user
test_yesprivs@localhost
execute ins1;
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
prepare ins1 from 'insert into t1 (f1) values (''insert6-no'')';
select current_user;
current_user
test_useprivs@localhost
execute ins1;
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
prepare ins1 from 'insert into t1 (f1) values (''insert7-no'')';
select current_user;
current_user
root@localhost
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
select current_user;
current_user
test_yesprivs@localhost
execute ins1;
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
select current_user;
current_user
test_useprivs@localhost
execute ins1;
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
select current_user;
current_user
root@localhost
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
select current_user;
current_user
test_yesprivs@localhost
execute ins1;
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
select current_user;
current_user
test_useprivs@localhost
execute ins1;
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
select current_user;
current_user
root@localhost
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
select current_user;
current_user
test_yesprivs@localhost
execute ins1;
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
deallocate prepare ins1;
select current_user;
current_user
test_useprivs@localhost
execute ins1;
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
deallocate prepare ins1;
select current_user;
current_user
root@localhost
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
select current_user;
current_user
test_yesprivs@localhost
drop trigger trg1_1;
select current_user;
current_user
root@localhost
select current_user;
current_user
root@localhost
drop database if exists priv_db;
drop user test_yesprivs@localhost;
drop user test_useprivs@localhost;

#########      Testcase for definer:   ########
-----------------------------------------------
drop database if exists priv_db;
create database priv_db;
use priv_db;
create table t1 (f1 char(20)) engine= innodb;
create User test_yesprivs@localhost;
set password for test_yesprivs@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
select current_user;
current_user
root@localhost
create definer=not_ex_user@localhost trigger trg1_0
before INSERT on t1 for each row
set new.f1 = 'trig 1_0-yes';
Warnings:
Note	1449	The user specified as a definer ('not_ex_user'@'localhost') does not exist
drop trigger trg1_0;
create definer=test_yesprivs@localhost trigger trg1_0
before INSERT on t1 for each row
set new.f1 = 'trig 1_0-yes';
grant  select, insert, update
on priv_db.t1 to test_yesprivs@localhost;
select current_user;
current_user
test_yesprivs@localhost
use priv_db;
insert into t1 (f1) values ('insert-no');
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select f1 from t1 order by f1;
f1
drop trigger trg1_0;
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select current_user;
current_user
root@localhost
grant  select, insert, update ,trigger
on priv_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
select current_user;
current_user
test_yesprivs@localhost
insert into t1 (f1) values ('insert-no');
select f1 from t1 order by f1;
f1
trig 1_0-yes
drop trigger trg1_0;
create definer=not_ex_user@localhost trigger trg1_0
before INSERT on t1 for each row
set new.f1 = 'trig 1_0-yes';
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
create definer=current_user trigger trg1_1
before INSERT on t1 for each row
set new.f1 = 'trig 1_1-yes';
insert into t1 (f1) values ('insert-no');
select f1 from t1 order by f1;
f1
trig 1_0-yes
trig 1_1-yes
create definer=test_yesprivs@localhost trigger trg1_2
before UPDATE on t1 for each row
set new.f1 = 'trig 1_2-yes';
update t1 set f1 = 'update-yes' where f1 like '%trig%';
select f1 from t1 order by f1;
f1
trig 1_2-yes
trig 1_2-yes
select current_user;
current_user
root@localhost
grant trigger on priv_db.* to test_yesprivs@localhost
with grant option;
select current_user;
current_user
test_yesprivs@localhost
show grants;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD <secret>
GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
create definer=not_ex_user@localhost trigger trg1_3
after UPDATE on t1 for each row
set @var1 = 'trig 1_3-yes';
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
select current_user;
current_user
root@localhost
select current_user;
current_user
root@localhost
drop database if exists priv_db;
drop user test_yesprivs@localhost;

#########      Testcase for transactions:   ########
----------------------------------------------------
drop database if exists priv_db;
create database priv_db;
use priv_db;
create table t1 (f1 char(20)) engine= innodb;
create User test_yesprivs@localhost;
set password for test_yesprivs@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
select current_user;
current_user
root@localhost
grant  select, insert, update ,trigger
on priv_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
select current_user;
current_user
test_yesprivs@localhost
use priv_db;
set autocommit=0;
create definer=current_user trigger trg1_1
before INSERT on t1 for each row
set new.f1 = 'trig 1_1-yes';
rollback work;
insert into t1 (f1) values ('insert-no');
select f1 from t1 order by f1;
f1
trig 1_1-yes
create definer=test_yesprivs@localhost trigger trg1_2
before UPDATE on t1 for each row
set new.f1 = 'trig 1_2-yes';
commit work;
update t1 set f1 = 'update-yes' where f1 like '%trig%';
select f1 from t1 order by f1;
f1
trig 1_2-yes
commit work;
drop trigger trg1_1;
rollback work;
drop trigger trg1_1;
ERROR HY000: Trigger does not exist
drop trigger trg1_2;
commit work;
set autocommit=1;
select current_user;
current_user
root@localhost
select current_user;
current_user
root@localhost
drop database if exists priv_db;
drop user test_yesprivs@localhost;

####### Testcase for column privileges of triggers: #######
-----------------------------------------------------------
drop database if exists priv_db;
drop database if exists no_priv_db;
create database priv_db;
use priv_db;
create table t1 (f1 char(20)) engine= innodb;
create table t2 (f1 char(20)) engine= innodb;
create User test_yesprivs@localhost;
set password for test_yesprivs@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
create User test_noprivs@localhost;
set password for test_noprivs@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant SELECT,UPDATE on priv_db.* to test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, UPDATE ON `priv_db`.* TO 'test_noprivs'@'localhost'

update only on column:
----------------------
select current_user;
current_user
root@localhost
grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t1
to test_yesprivs@localhost;
grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t2
to test_yesprivs@localhost;
select current_user;
current_user
test_yesprivs@localhost
use priv_db;
insert into t1 (f1) values ('insert1-yes');
insert into t2 (f1) values ('insert1-yes');
create trigger trg1_1 before UPDATE on t1 for each row
set new.f1 = 'trig 1_1-yes';
create trigger trg2_1 before UPDATE on t2 for each row
set new.f1 = 'trig 2_1-yes';
select current_user;
current_user
test_noprivs@localhost
use priv_db;
select f1 from t1 order by f1;
f1
insert1-yes
update t1 set f1 = 'update1_no'
		where f1 like '%insert%';
select f1 from t1 order by f1;
f1
trig 1_1-yes
select f1 from t2 order by f1;
f1
insert1-yes
update t2 set f1 = 'update1_no'
                where f1 like '%insert%';
select f1 from t2 order by f1;
f1
trig 2_1-yes
select current_user;
current_user
root@localhost
revoke UPDATE     on priv_db.*
from test_yesprivs@localhost;
revoke UPDATE(f1) on priv_db.t2
from test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
GRANT SELECT (f1), INSERT ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
GRANT SELECT (f1), INSERT, UPDATE (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
select current_user;
current_user
test_yesprivs@localhost
use priv_db;
insert into t1 (f1) values ('insert2-yes');
insert into t2 (f1) values ('insert2-yes');
select current_user;
current_user
test_noprivs@localhost
use priv_db;
update t1 set f1 = 'update2_no'
                where f1 like '%insert%';
update t2 set f1 = 'update2_no'
                where f1 like '%insert%';
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't2'
update t1 set f1 = 'update3_no'
                where f1 like '%insert%';
update t2 set f1 = 'update3_no'
                where f1 like '%insert%';
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't2'
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
select f1 from t2 order by f1;
f1
insert2-yes
trig 2_1-yes

check if access only on one of three columns
--------------------------------------------
select current_user;
current_user
root@localhost
alter table priv_db.t1 add f2 char(20), add f3 int;
revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
grant TRIGGER,SELECT on priv_db.t1 to test_yesprivs@localhost;
grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
select current_user;
current_user
test_yesprivs@localhost
use priv_db;
insert into t1 values ('insert2-yes','insert2-yes',1);
insert into t1 values ('insert3-yes','insert3-yes',2);
select * from t1 order by f1;
f1	f2	f3
insert2-yes	insert2-yes	1
insert3-yes	insert3-yes	2
trig 1_1-yes	NULL	NULL
trig 1_1-yes	NULL	NULL
select current_user;
current_user
test_noprivs@localhost
use priv_db;
update t1 set 	f1 = 'update4-no',
f2 = 'update4-yes',
f3 = f3*10
where f2 like '%yes';
select * from t1 order by f1,f2,f3;
f1	f2	f3
trig 1_1-yes	NULL	NULL
trig 1_1-yes	NULL	NULL
trig 1_1-yes	update4-yes	10
trig 1_1-yes	update4-yes	20
select current_user;
current_user
test_yesprivs@localhost
create trigger trg1_2 after UPDATE on t1 for each row
set @f2 = 'trig 1_2-yes';
select current_user;
current_user
test_noprivs@localhost
update t1 set 	f1 = 'update5-yes',
f2 = 'update5-yes'
		where f2 like '%yes';
select * from t1 order by f1,f2,f3;
f1	f2	f3
trig 1_1-yes	NULL	NULL
trig 1_1-yes	NULL	NULL
trig 1_1-yes	update5-yes	10
trig 1_1-yes	update5-yes	20
select @f2;
@f2
trig 1_2-yes
update t1 set f1 = 'update6_no'
                where f1 like '%insert%';
update t2 set f1 = 'update6_no'
                where f1 like '%insert%';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
update t1 set f1 = 'update7_no'
                where f1 like '%insert%';
update t2 set f1 = 'update7_no'
                where f1 like '%insert%';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
select f1 from t1 order by f1;
f1
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
trig 1_1-yes
select f1 from t2 order by f1;
f1
insert2-yes
trig 2_1-yes

check if rejected without trigger privilege:
--------------------------------------------
select current_user;
current_user
root@localhost
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
select current_user;
current_user
test_noprivs@localhost
update t1 set   f1 = 'update8-no',
f2 = 'update8-no'
                where f2 like '%yes';
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select * from t1 order by f1,f2,f3;
f1	f2	f3
trig 1_1-yes	NULL	NULL
trig 1_1-yes	NULL	NULL
trig 1_1-yes	update5-yes	10
trig 1_1-yes	update5-yes	20
select @f2;
@f2
trig 1_2-yes

check trigger, but not update privilege on column:
--------------------------------------------------
select current_user;
current_user
root@localhost
revoke UPDATE(f1) on priv_db.t1 from test_yesprivs@localhost;
grant TRIGGER,UPDATE(f2),UPDATE(f3) on priv_db.t1
to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT (f1), INSERT, UPDATE ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
GRANT SELECT, SELECT (f1), INSERT, UPDATE (f3, f2), TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
select current_user;
current_user
test_yesprivs@localhost
use priv_db;
drop trigger trg1_1;
create trigger trg1_3 before UPDATE on t1 for each row
set new.f1 = 'trig 1_3-yes';
select current_user;
current_user
test_noprivs@localhost
use priv_db;
update t1 set   f1 = 'update9-no',
f2 = 'update9-no'
                where f2 like '%yes';
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
select * from t1 order by f1,f2,f3;
f1	f2	f3
trig 1_1-yes	NULL	NULL
trig 1_1-yes	NULL	NULL
trig 1_1-yes	update5-yes	10
trig 1_1-yes	update5-yes	20
update t1 set f3= f3+1;
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
select f3 from t1 order by f3;
f3
NULL
NULL
10
20
select current_user;
current_user
root@localhost
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
grant UPDATE(f1),UPDATE(f2),UPDATE(f3) on priv_db.t1
to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT (f1), INSERT, UPDATE ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
GRANT SELECT, SELECT (f1), INSERT, UPDATE (f3, f2, f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
select current_user;
current_user
test_noprivs@localhost
use priv_db;
update t1 set f3= f3+1;
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
select f3 from t1 order by f3;
f3
NULL
NULL
10
20

##### trigger privilege on column level? #######
------------------------------------------------
grant TRIGGER(f1) on priv_db.t1 to test_yesprivs@localhost;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(f1) on priv_db.t1 to test_yesprivs@localhost' at line 1
select current_user;
current_user
root@localhost
drop database if exists priv_db;
drop user test_yesprivs@localhost;
drop user test_noprivs@localhost;

Back to File Manager