[ Avaa Bypassed ]

Upload:

Command:

hmhc3928@18.118.26.7: ~ $ 
SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
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= memory;
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'

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';
select current_user;
current_user
test_noprivs@localhost
use priv_db;
insert into t1 (f1) values ('insert-yes');
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
select f1 from t1 order by f1;
f1
insert-yes
select current_user;
current_user
root@localhost
grant UPDATE on priv_db.* to test_yesprivs@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
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_2-yes
trig 1_2-yes
select current_user;
current_user
root@localhost
revoke TRIGGER on priv_db.* 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, UPDATE 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= memory;
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= memory;
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'

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';
select current_user;
current_user
test_noprivs@localhost
insert into t1 (f1) values ('insert3-no');
select f1 from t1 order by f1;
f1
insert1-yes
insert2-yes
trig 1_2-yes
select current_user;
current_user
root@localhost
insert into t1 (f1) values ('insert4-no');
select f1 from t1 order by f1;
f1
insert1-yes
insert2-yes
trig 1_2-yes
trig 1_2-yes
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'

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= memory;
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	NO_ENGINE_SUBSTITUTION	test_yesprivs@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
trg1_4	UPDATE	t1	set new.f1 = 'trig 1_4-yes'	BEFORE	NULL	NO_ENGINE_SUBSTITUTION	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= memory;
use no_priv_db;
create table t1 (f1 char(20)) engine= memory;
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;
call mtr.add_suppression("Did not write failed .* into binary log while "
                         "storing table level and column level grants "
                         "in the privilege tables.");

####### 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= memory;
create table t2 (f1 char(20)) engine= memory;
use priv2_db;
create table t1 (f1 char(20)) engine= memory;
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= memory;
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= memory;
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 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= memory;
create table t2 (f1 char(20)) engine= memory;
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;

Filemanager

DIR : / usr/ share/ mysql-test/ suite/ funcs_1/ r/
Name Type Size Permission Actions
charset_collation.result File 1.32 KB 0644
innodb_bitdata.result File 117 B 0644
innodb_cursors.result File 116 B 0644
innodb_func_view.result File 308.05 KB 0644
innodb_storedproc_02.result File 36.22 KB 0644
innodb_storedproc_03.result File 15.3 KB 0644
innodb_storedproc_06.result File 13.33 KB 0644
innodb_storedproc_07.result File 7.95 KB 0644
innodb_storedproc_08.result File 22.65 KB 0644
innodb_storedproc_10.result File 13.21 KB 0644
innodb_trig_0102.result File 15.05 KB 0644
innodb_trig_03.result File 25.26 KB 0644
innodb_trig_03e.result File 51.28 KB 0644
innodb_trig_0407.result File 15.4 KB 0644
innodb_trig_08.result File 18.27 KB 0644
innodb_trig_09.result File 9.97 KB 0644
innodb_trig_1011ext.result File 11.75 KB 0644
innodb_trig_frkey.result File 2.23 KB 0644
innodb_views.result File 581.19 KB 0644
is_basics_mixed.result File 30.21 KB 0644
is_character_sets.result File 3.96 KB 0644
is_cml_innodb.result File 4.71 KB 0644
is_cml_memory.result File 3.74 KB 0644
is_cml_myisam.result File 4.76 KB 0644
is_coll_char_set_appl.result File 4.34 KB 0644
is_collations.result File 4.43 KB 0644
is_column_privileges.result File 19.15 KB 0644
is_column_privileges_is_mysql_test.result File 1.44 KB 0644
is_columns.result File 20.23 KB 0644
is_columns_innodb.result File 77.84 KB 0644
is_columns_is.result File 76.59 KB 0644
is_columns_is_embedded.result File 74.61 KB 0644
is_columns_memory.result File 74.64 KB 0644
is_columns_myisam.result File 83.06 KB 0644
is_columns_myisam_embedded.result File 72.43 KB 0644
is_columns_mysql.result File 61.79 KB 0644
is_columns_mysql_embedded.result File 53.77 KB 0644
is_engines.result File 3.88 KB 0644
is_engines_archive.result File 161 B 0644
is_engines_blackhole.result File 205 B 0644
is_engines_csv.result File 149 B 0644
is_engines_federated.result File 173 B 0644
is_engines_innodb.result File 202 B 0644
is_engines_memory.result File 194 B 0644
is_engines_merge.result File 182 B 0644
is_engines_myisam.result File 162 B 0644
is_events.result File 6.37 KB 0644
is_key_column_usage.result File 19.64 KB 0644
is_key_column_usage_embedded.result File 16.94 KB 0644
is_routines.result File 26.6 KB 0644
is_routines_embedded.result File 23.94 KB 0644
is_schema_privileges.result File 15.45 KB 0644
is_schema_privileges_is_mysql_test.result File 1.84 KB 0644
is_schemata.result File 8.66 KB 0644
is_schemata_embedded.result File 8.75 KB 0644
is_schemata_is_mysql_test.result File 1.59 KB 0644
is_statistics.result File 19.97 KB 0644
is_statistics_is.result File 1.01 KB 0644
is_statistics_mysql.result File 5.73 KB 0644
is_statistics_mysql_embedded.result File 10.48 KB 0644
is_table_constraints.result File 15.44 KB 0644
is_table_constraints_is.result File 828 B 0644
is_table_constraints_mysql.result File 2.15 KB 0644
is_table_constraints_mysql_embedded.result File 3.53 KB 0644
is_table_privileges.result File 15.03 KB 0644
is_tables.result File 17.27 KB 0644
is_tables_embedded.result File 17.48 KB 0644
is_tables_innodb.result File 3.98 KB 0644
is_tables_is.result File 29.72 KB 0644
is_tables_memory.result File 4.02 KB 0644
is_tables_myisam.result File 4.03 KB 0644
is_tables_myisam_embedded.result File 4.42 KB 0644
is_tables_mysql.result File 14.39 KB 0644
is_tables_mysql_embedded.result File 27.65 KB 0644
is_triggers.result File 14.46 KB 0644
is_triggers_embedded.result File 11.97 KB 0644
is_user_privileges.result File 39.15 KB 0644
is_views.result File 11.21 KB 0644
is_views_embedded.result File 10.98 KB 0644
memory_bitdata.result File 168 B 0644
memory_cursors.result File 167 B 0644
memory_func_view.result File 308.1 KB 0644
memory_storedproc_02.result File 36.27 KB 0644
memory_storedproc_03.result File 15.35 KB 0644
memory_storedproc_06.result File 13.38 KB 0644
memory_storedproc_07.result File 8 KB 0644
memory_storedproc_08.result File 22.7 KB 0644
memory_storedproc_10.result File 13.26 KB 0644
memory_trig_0102.result File 15.1 KB 0644
memory_trig_03.result File 25.31 KB 0644
memory_trig_03e.result File 49.72 KB 0644
memory_trig_0407.result File 15.45 KB 0644
memory_trig_08.result File 18.32 KB 0644
memory_trig_09.result File 10.02 KB 0644
memory_trig_1011ext.result File 11.81 KB 0644
memory_views.result File 581.25 KB 0644
myisam_bitdata.result File 168 B 0644
myisam_cursors.result File 167 B 0644
myisam_func_view.result File 308.1 KB 0644
myisam_storedproc_02.result File 36.27 KB 0644
myisam_storedproc_03.result File 15.35 KB 0644
myisam_storedproc_06.result File 13.38 KB 0644
myisam_storedproc_07.result File 8 KB 0644
myisam_storedproc_08.result File 22.7 KB 0644
myisam_storedproc_10.result File 13.26 KB 0644
myisam_trig_0102.result File 15.1 KB 0644
myisam_trig_03.result File 25.31 KB 0644
myisam_trig_03e.result File 49.72 KB 0644
myisam_trig_0407.result File 15.45 KB 0644
myisam_trig_08.result File 18.32 KB 0644
myisam_trig_09.result File 10.02 KB 0644
myisam_trig_1011ext.result File 11.81 KB 0644
myisam_views-big.result File 596.24 KB 0644
processlist_priv_no_prot.result File 28.68 KB 0644
processlist_priv_ps.result File 28.71 KB 0644
processlist_val_no_prot.result File 91.97 KB 0644
processlist_val_ps.result File 91.98 KB 0644
row_count_func.result File 1.34 KB 0644
storedproc.result File 896.65 KB 0644