mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-11-04 04:46:15 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/trigger_notembedded.test
Sergei Golubchik 78d23a3e60 fix error messages 
when a definer for SP/view is wrong - it shold be ER_MALFORMED_DEFINER,
not ER_NO_SUCH_USER

when one uses current_role as a definer or grantee but there's no
current role - it should be ER_INVALID_ROLE not ER_MALFORMED_DEFINER

when a non-existent user is specified - it should be ER_NO_SUCH_USER,
which should say "The user does not exist", not "Definer does not exist"

clarify ER_CANT_CHANGE_TX_CHARACTERISTICS to say what cannot be changed
2025-05-02 13:56:25 +02:00

1017 lines
26 KiB
Text
Raw Permalink Blame History

# Test case(s) in this file contain(s) GRANT/REVOKE statements, which are not
# supported in embedded server. So, this test should not be run on embedded
# server.
--source include/not_embedded.inc
--source include/default_charset.inc
###########################################################################
#
# Tests for WL#2818:
# - Check that triggers are executed under the authorization of the definer.
# - Check DEFINER clause of CREATE TRIGGER statement;
# - Check that SUPER privilege required to create a trigger with different
# definer.
# - Check that if the user specified as DEFINER does not exist, a warning
# is emitted.
# - Check that the definer of a trigger does not exist, the trigger will
# not be activated.
# - Check that SHOW TRIGGERS statement provides "Definer" column.
# - Check that if trigger contains NEW/OLD variables, the definer must have
# SELECT privilege on the subject table (aka BUG#15166/BUG#15196).
#
# Let's also check that user name part of definer can contain '@' symbol (to
# check that triggers are not affected by BUG#13310 "incorrect user parsing
# by SP").
#
###########################################################################
#
# Prepare environment.
#
DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
FLUSH PRIVILEGES;
--disable_warnings
DROP DATABASE IF EXISTS mysqltest_db1;
--enable_warnings
CREATE DATABASE mysqltest_db1;
CREATE USER mysqltest_dfn@localhost;
CREATE USER mysqltest_inv@localhost;
GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
--connection wl2818_definer_con
CREATE TABLE t1(num_value INT);
CREATE TABLE t2(user_str TEXT);
--disconnect wl2818_definer_con
--connection default
GRANT INSERT, DROP ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
GRANT INSERT, DROP ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
#
# Check that the user must have TRIGGER privilege to create a trigger.
#
--connection default
GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
--connection wl2818_definer_con
--error ER_TABLEACCESS_DENIED_ERROR
CREATE TRIGGER trg1 AFTER INSERT ON t1
FOR EACH ROW
INSERT INTO t2 VALUES(CURRENT_USER());
--disconnect wl2818_definer_con
#
# Check that the user must have TRIGGER privilege to drop a trigger.
#
--connection default
GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
--connection wl2818_definer_con
CREATE TRIGGER trg1 AFTER INSERT ON t1
FOR EACH ROW
INSERT INTO t2 VALUES(CURRENT_USER());
--disconnect wl2818_definer_con
--connection default
REVOKE TRIGGER ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost;
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
--connection wl2818_definer_con
--error ER_TABLEACCESS_DENIED_ERROR
DROP TRIGGER trg1;
--disconnect wl2818_definer_con
#
# Check that the definer must have TRIGGER privilege to activate a trigger.
#
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
--connection wl2818_definer_con
--error ER_TABLEACCESS_DENIED_ERROR
INSERT INTO t1 VALUES(0);
--disconnect wl2818_definer_con
--connection default
GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
--connection wl2818_definer_con
INSERT INTO t1 VALUES(0);
# Cleanup for further tests.
DROP TRIGGER trg1;
TRUNCATE TABLE t1;
TRUNCATE TABLE t2;
--disconnect wl2818_definer_con
--connection default
REVOKE SUPER ON *.* FROM mysqltest_dfn@localhost;
#
# Check that triggers are executed under the authorization of the definer:
# - create two tables under "definer";
# - grant all privileges on the test db to "definer";
# - grant all privileges on the first table to "invoker";
# - grant only select privilege on the second table to "invoker";
# - create a trigger, which inserts a row into the second table after
# inserting into the first table.
# - insert a row into the first table under "invoker". A row also should be
# inserted into the second table.
#
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
--connection wl2818_definer_con
CREATE TRIGGER trg1 AFTER INSERT ON t1
FOR EACH ROW
INSERT INTO t2 VALUES(CURRENT_USER());
--connection default
# Setup definer's privileges.
GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
# Setup invoker's privileges.
GRANT ALL PRIVILEGES ON mysqltest_db1.t1
TO 'mysqltest_inv'@localhost;
GRANT SELECT ON mysqltest_db1.t2
TO 'mysqltest_inv'@localhost;
--connection wl2818_definer_con
use mysqltest_db1;
INSERT INTO t1 VALUES(1);
SELECT * FROM t1;
SELECT * FROM t2;
--connect (wl2818_invoker_con,localhost,mysqltest_inv,,mysqltest_db1)
--connection wl2818_invoker_con
use mysqltest_db1;
INSERT INTO t1 VALUES(2);
SELECT * FROM t1;
SELECT * FROM t2;
#
# Check that if definer lost some privilege required to execute (activate) a
# trigger, the trigger will not be activated:
# - create a trigger on insert into the first table, which will insert a row
# into the second table;
# - revoke INSERT privilege on the second table from the definer;
# - insert a row into the first table;
# - check that an error has been risen;
# - check that no row has been inserted into the second table;
#
--connection default
use mysqltest_db1;
REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost;
--connection wl2818_invoker_con
use mysqltest_db1;
--error ER_TABLEACCESS_DENIED_ERROR
INSERT INTO t1 VALUES(3);
SELECT * FROM t1;
SELECT * FROM t2;
#
# Check DEFINER clause of CREATE TRIGGER statement.
#
# - Check that SUPER privilege required to create a trigger with different
# definer:
# - try to create a trigger with DEFINER="definer@localhost" under
# "invoker";
# - analyze error code;
# - Check that if the user specified as DEFINER does not exist, a warning is
# emitted:
# - create a trigger with DEFINER="non_existent_user@localhost" from
# "definer";
# - check that a warning emitted;
# - Check that the definer of a trigger does not exist, the trigger will not
# be activated:
# - activate just created trigger;
# - check error code;
#
--connection wl2818_definer_con
use mysqltest_db1;
DROP TRIGGER trg1;
# Check that SUPER is required to specify different DEFINER.
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
CREATE DEFINER='mysqltest_inv'@'localhost'
TRIGGER trg1 BEFORE INSERT ON t1
FOR EACH ROW
SET @new_sum = 0;
--connection default
use mysqltest_db1;
GRANT SET USER ON *.* TO mysqltest_dfn@localhost;
--disconnect wl2818_definer_con
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
--connection wl2818_definer_con
CREATE DEFINER='mysqltest_inv'@'localhost'
TRIGGER trg1 BEFORE INSERT ON t1
FOR EACH ROW
SET @new_sum = 0;
# Create with non-existent user.
CREATE DEFINER='mysqltest_nonexs'@'localhost'
TRIGGER trg2 AFTER INSERT ON t1
FOR EACH ROW
SET @new_sum = 0;
# Check that trg2 will not be activated.
--error ER_MALFORMED_DEFINER
INSERT INTO t1 VALUES(6);
#
# Check that SHOW TRIGGERS statement provides "Definer" column.
#
--replace_column 6 #
SHOW TRIGGERS;
#
# Check that weird definer values do not break functionality. I.e. check the
# following definer values:
# - '';
# - '@';
# - '@abc@def@@';
# - '@hostname';
# - '@abc@def@@@hostname';
#
DROP TRIGGER trg1;
DROP TRIGGER trg2;
CREATE TRIGGER trg1 BEFORE INSERT ON t1
FOR EACH ROW
SET @a = 1;
CREATE TRIGGER trg2 AFTER INSERT ON t1
FOR EACH ROW
SET @a = 2;
CREATE TRIGGER trg3 BEFORE UPDATE ON t1
FOR EACH ROW
SET @a = 3;
CREATE TRIGGER trg4 AFTER UPDATE ON t1
FOR EACH ROW
SET @a = 4;
CREATE TRIGGER trg5 BEFORE DELETE ON t1
FOR EACH ROW
SET @a = 5;
# Replace definers with the "weird" definers
let MYSQLD_DATADIR= `select @@datadir`;
perl;
use strict;
use warnings;
my $fname= "$ENV{'MYSQLD_DATADIR'}/mysqltest_db1/t1.TRG";
open(FILE, "<", $fname) or die;
my @content= grep($_ !~ /^definers=/, <FILE>);
close FILE;
open(FILE, ">", $fname) or die;
# Use binary file mode to avoid CR/LF's being added on windows
binmode FILE;
print FILE @content;
print FILE "definers='' '\@' '\@abc\@def\@\@' '\@hostname' '\@abcdef\@\@\@hostname'\n";
close FILE;
EOF
--echo
SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
--echo
--replace_column 17 #
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
#
# Cleanup
#
--connection default
DROP USER mysqltest_dfn@localhost;
DROP USER mysqltest_inv@localhost;
DROP DATABASE mysqltest_db1;
###########################################################################
#
# BUG#15166: Wrong update [was: select/update] permissions required to execute
# triggers.
#
# BUG#15196: Wrong select permission required to execute triggers.
#
###########################################################################
#
# Prepare environment.
#
DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
FLUSH PRIVILEGES;
--disable_warnings
DROP DATABASE IF EXISTS mysqltest_db1;
--enable_warnings
CREATE DATABASE mysqltest_db1;
use mysqltest_db1;
# Tables for testing table-level privileges:
CREATE TABLE t1(col CHAR(20)); # table for "read-value" trigger
CREATE TABLE t2(col CHAR(20)); # table for "write-value" trigger
# Tables for testing column-level privileges:
CREATE TABLE t3(col CHAR(20)); # table for "read-value" trigger
CREATE TABLE t4(col CHAR(20)); # table for "write-value" trigger
CREATE USER mysqltest_u1@localhost;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
GRANT TRIGGER ON mysqltest_db1.* TO mysqltest_u1@localhost;
SET @mysqltest_var = NULL;
--connect (bug15166_u1_con,localhost,mysqltest_u1,,mysqltest_db1)
# parsing (CREATE TRIGGER) time:
# - check that nor SELECT either UPDATE is required to execute trigger w/o
# NEW/OLD variables.
--connection default
use mysqltest_db1;
GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;
SHOW GRANTS FOR mysqltest_u1@localhost;
--connection bug15166_u1_con
use mysqltest_db1;
CREATE TRIGGER t1_trg_after_delete AFTER DELETE ON t1
FOR EACH ROW
SET @mysqltest_var = 'Hello, world!';
# parsing (CREATE TRIGGER) time:
# - check that UPDATE is not enough to read the value;
# - check that UPDATE is required to modify the value;
--connection default
use mysqltest_db1;
GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
--connection bug15166_u1_con
use mysqltest_db1;
# - table-level privileges
# TODO: check privileges at CREATE TRIGGER time.
# --error ER_COLUMNACCESS_DENIED_ERROR
CREATE TRIGGER t1_trg_err_1 BEFORE INSERT ON t1
FOR EACH ROW
SET @mysqltest_var = NEW.col;
DROP TRIGGER t1_trg_err_1;
# TODO: check privileges at CREATE TRIGGER time.
# --error ER_COLUMNACCESS_DENIED_ERROR
CREATE TRIGGER t1_trg_err_2 BEFORE DELETE ON t1
FOR EACH ROW
SET @mysqltest_var = OLD.col;
DROP TRIGGER t1_trg_err_2;
CREATE TRIGGER t2_trg_before_insert BEFORE INSERT ON t2
FOR EACH ROW
SET NEW.col = 't2_trg_before_insert';
# - column-level privileges
# TODO: check privileges at CREATE TRIGGER time.
# --error ER_COLUMNACCESS_DENIED_ERROR
CREATE TRIGGER t3_trg_err_1 BEFORE INSERT ON t3
FOR EACH ROW
SET @mysqltest_var = NEW.col;
DROP TRIGGER t3_trg_err_1;
# TODO: check privileges at CREATE TRIGGER time.
# --error ER_COLUMNACCESS_DENIED_ERROR
CREATE TRIGGER t3_trg_err_2 BEFORE DELETE ON t3
FOR EACH ROW
SET @mysqltest_var = OLD.col;
DROP TRIGGER t3_trg_err_2;
CREATE TRIGGER t4_trg_before_insert BEFORE INSERT ON t4
FOR EACH ROW
SET NEW.col = 't4_trg_before_insert';
# parsing (CREATE TRIGGER) time:
# - check that SELECT is required to read the value;
# - check that SELECT is not enough to modify the value;
--connection default
use mysqltest_db1;
REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
GRANT SELECT(col) on mysqltest_db1.t3 TO mysqltest_u1@localhost;
GRANT SELECT(col) on mysqltest_db1.t4 TO mysqltest_u1@localhost;
--connection bug15166_u1_con
use mysqltest_db1;
# - table-level privileges
CREATE TRIGGER t1_trg_after_insert AFTER INSERT ON t1
FOR EACH ROW
SET @mysqltest_var = NEW.col;
CREATE TRIGGER t1_trg_after_update AFTER UPDATE ON t1
FOR EACH ROW
SET @mysqltest_var = OLD.col;
# TODO: check privileges at CREATE TRIGGER time.
# --error ER_COLUMNACCESS_DENIED_ERROR
CREATE TRIGGER t2_trg_err_1 BEFORE UPDATE ON t2
FOR EACH ROW
SET NEW.col = 't2_trg_err_1';
DROP TRIGGER t2_trg_err_1;
# TODO: check privileges at CREATE TRIGGER time.
# --error ER_COLUMNACCESS_DENIED_ERROR
CREATE TRIGGER t2_trg_err_2 BEFORE UPDATE ON t2
FOR EACH ROW
SET NEW.col = CONCAT(OLD.col, '(updated)');
DROP TRIGGER t2_trg_err_2;
# - column-level privileges
CREATE TRIGGER t3_trg_after_insert AFTER INSERT ON t3
FOR EACH ROW
SET @mysqltest_var = NEW.col;
CREATE TRIGGER t3_trg_after_update AFTER UPDATE ON t3
FOR EACH ROW
SET @mysqltest_var = OLD.col;
# TODO: check privileges at CREATE TRIGGER time.
# --error ER_COLUMNACCESS_DENIED_ERROR
CREATE TRIGGER t4_trg_err_1 BEFORE UPDATE ON t4
FOR EACH ROW
SET NEW.col = 't4_trg_err_1';
DROP TRIGGER t4_trg_err_1;
# TODO: check privileges at CREATE TRIGGER time.
# --error ER_COLUMNACCESS_DENIED_ERROR
CREATE TRIGGER t4_trg_err_2 BEFORE UPDATE ON t4
FOR EACH ROW
SET NEW.col = CONCAT(OLD.col, '(updated)');
DROP TRIGGER t4_trg_err_2;
# execution time:
# - check that UPDATE is not enough to read the value;
# - check that UPDATE is required to modify the value;
--connection default
use mysqltest_db1;
REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
REVOKE SELECT ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
REVOKE SELECT(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
REVOKE SELECT(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
# - table-level privileges
--error ER_COLUMNACCESS_DENIED_ERROR
INSERT INTO t1 VALUES('line1');
SELECT * FROM t1;
SELECT @mysqltest_var;
INSERT INTO t2 VALUES('line2');
SELECT * FROM t2;
# - column-level privileges
--error ER_COLUMNACCESS_DENIED_ERROR
INSERT INTO t3 VALUES('t3_line1');
SELECT * FROM t3;
SELECT @mysqltest_var;
INSERT INTO t4 VALUES('t4_line2');
SELECT * FROM t4;
# execution time:
# - check that SELECT is required to read the value;
# - check that SELECT is not enough to modify the value;
--connection default
use mysqltest_db1;
REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
GRANT SELECT(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
GRANT SELECT(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
# - table-level privileges
INSERT INTO t1 VALUES('line3');
SELECT * FROM t1;
SELECT @mysqltest_var;
--error ER_COLUMNACCESS_DENIED_ERROR
INSERT INTO t2 VALUES('line4');
SELECT * FROM t2;
# - column-level privileges
INSERT INTO t3 VALUES('t3_line2');
SELECT * FROM t3;
SELECT @mysqltest_var;
--error ER_COLUMNACCESS_DENIED_ERROR
INSERT INTO t4 VALUES('t4_line2');
SELECT * FROM t4;
# execution time:
# - check that nor SELECT either UPDATE is required to execute trigger w/o
# NEW/OLD variables.
DELETE FROM t1;
SELECT @mysqltest_var;
#
# Cleanup.
#
DROP USER mysqltest_u1@localhost;
DROP DATABASE mysqltest_db1;
#
# Test for bug #14635 Accept NEW.x as INOUT parameters to stored
# procedures from within triggers
#
# We require UPDATE privilege when NEW.x passed as OUT parameter, and
# SELECT and UPDATE when NEW.x passed as INOUT parameter.
#
DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
FLUSH PRIVILEGES;
--disable_warnings
DROP DATABASE IF EXISTS mysqltest_db1;
--enable_warnings
CREATE DATABASE mysqltest_db1;
USE mysqltest_db1;
CREATE TABLE t1 (i1 INT);
CREATE TABLE t2 (i1 INT);
CREATE USER mysqltest_dfn@localhost;
CREATE USER mysqltest_inv@localhost;
GRANT EXECUTE, CREATE ROUTINE, TRIGGER ON *.* TO mysqltest_dfn@localhost;
GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost;
connect (definer,localhost,mysqltest_dfn,,mysqltest_db1);
connect (invoker,localhost,mysqltest_inv,,mysqltest_db1);
connection definer;
CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3;
CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5;
# Check that having no privilege won't work.
connection definer;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
CALL p1(NEW.i1);
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
CALL p2(NEW.i1);
connection invoker;
--error ER_COLUMNACCESS_DENIED_ERROR
INSERT INTO t1 VALUES (7);
--error ER_COLUMNACCESS_DENIED_ERROR
INSERT INTO t2 VALUES (11);
connection definer;
DROP TRIGGER t2_bi;
DROP TRIGGER t1_bi;
# Check that having only SELECT privilege is not enough.
connection default;
GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost;
connection definer;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
CALL p1(NEW.i1);
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
CALL p2(NEW.i1);
connection invoker;
--error ER_COLUMNACCESS_DENIED_ERROR
INSERT INTO t1 VALUES (13);
--error ER_COLUMNACCESS_DENIED_ERROR
INSERT INTO t2 VALUES (17);
connection default;
REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
connection definer;
DROP TRIGGER t2_bi;
DROP TRIGGER t1_bi;
# Check that having only UPDATE privilege is enough for OUT parameter,
# but not for INOUT parameter.
connection default;
GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
connection definer;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
CALL p1(NEW.i1);
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
CALL p2(NEW.i1);
connection invoker;
INSERT INTO t1 VALUES (19);
--error ER_COLUMNACCESS_DENIED_ERROR
INSERT INTO t2 VALUES (23);
connection default;
REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
connection definer;
DROP TRIGGER t2_bi;
DROP TRIGGER t1_bi;
# Check that having SELECT and UPDATE privileges is enough.
connection default;
GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
connection definer;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
CALL p1(NEW.i1);
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
CALL p2(NEW.i1);
connection invoker;
INSERT INTO t1 VALUES (29);
INSERT INTO t2 VALUES (31);
connection default;
REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
connection definer;
DROP TRIGGER t2_bi;
DROP TRIGGER t1_bi;
connection default;
DROP PROCEDURE p2;
DROP PROCEDURE p1;
# Check that late procedure redefining won't open a security hole.
connection default;
GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
connection definer;
CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
CALL p1(NEW.i1);
connection invoker;
INSERT INTO t1 VALUES (41);
connection definer;
DROP PROCEDURE p1;
CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43;
connection invoker;
--error ER_COLUMNACCESS_DENIED_ERROR
INSERT INTO t1 VALUES (47);
connection definer;
DROP PROCEDURE p1;
CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51;
connection invoker;
--error ER_COLUMNACCESS_DENIED_ERROR
INSERT INTO t1 VALUES (53);
connection default;
DROP PROCEDURE p1;
REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
connection definer;
DROP TRIGGER t1_bi;
# Cleanup.
disconnect definer;
disconnect invoker;
connection default;
DROP USER mysqltest_inv@localhost;
DROP USER mysqltest_dfn@localhost;
DROP TABLE t2;
DROP TABLE t1;
DROP DATABASE mysqltest_db1;
USE test;
#
# Bug #26162: Trigger DML ignores low_priority_updates setting
#
--disable_ps2_protocol
CREATE TABLE t1 (id INTEGER);
CREATE TABLE t2 (id INTEGER);
INSERT INTO t2 VALUES (1),(2);
# trigger that produces the high priority insert, but should be low, adding
# LOW_PRIORITY fixes this
CREATE TRIGGER t1_test AFTER INSERT ON t1 FOR EACH ROW
INSERT INTO t2 VALUES (new.id);
CONNECT (rl_holder, localhost, root,,);
CONNECT (rl_acquirer, localhost, root,,);
CONNECT (wl_acquirer, localhost, root,,);
CONNECT (rl_contender, localhost, root,,);
CONNECTION rl_holder;
SELECT GET_LOCK('B26162',120);
CONNECTION rl_acquirer;
let $rl_acquirer_thread_id = `SELECT @@pseudo_thread_id`;
--send
SELECT 'rl_acquirer', GET_LOCK('B26162',120), id FROM t2 WHERE id = 1;
CONNECTION wl_acquirer;
let $wl_acquirer_thread_id = `SELECT @@pseudo_thread_id`;
SET SESSION LOW_PRIORITY_UPDATES=1;
SET GLOBAL LOW_PRIORITY_UPDATES=1;
#need to wait for rl_acquirer to lock on the B26162 lock
let $wait_condition=
SELECT STATE = 'User lock' FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE ID = $rl_acquirer_thread_id;
--source include/wait_condition.inc
--send
INSERT INTO t1 VALUES (5);
CONNECTION rl_contender;
# Wait until wl_acquirer is waiting for the read lock on t2 to be released.
let $wait_condition=
SELECT STATE = 'Waiting for table level lock' FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE ID = $wl_acquirer_thread_id;
--source include/wait_condition.inc
# must not "see" the row inserted by the INSERT (as it must run before the
# INSERT)
--send
SELECT 'rl_contender', id FROM t2 WHERE id > 1;
CONNECTION rl_holder;
#need to wait for wl_acquirer and rl_contender to lock on t2
sleep 2;
SELECT RELEASE_LOCK('B26162');
CONNECTION rl_acquirer;
--reap
SELECT RELEASE_LOCK('B26162');
CONNECTION wl_acquirer;
--reap
CONNECTION rl_contender;
--reap
CONNECTION default;
DISCONNECT rl_acquirer;
DISCONNECT wl_acquirer;
DISCONNECT rl_contender;
DISCONNECT rl_holder;
DROP TRIGGER t1_test;
DROP TABLE t1,t2;
SET SESSION LOW_PRIORITY_UPDATES=DEFAULT;
SET GLOBAL LOW_PRIORITY_UPDATES=DEFAULT;
--enable_ps2_protocol
--echo End of 5.0 tests.
#
# Bug#23713 LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
#
--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (i int);
connect (flush,localhost,root,,test,,);
connection default;
lock tables t1 write;
connection flush;
--send flush tables with read lock;
connection default;
let $wait_condition=
select count(*) = 1 from information_schema.processlist
where state = "Waiting for backup lock";
--source include/wait_condition.inc
create trigger t1_bi before insert on t1 for each row begin end;
unlock tables;
connection flush;
--reap
unlock tables;
connection default;
select * from t1;
drop table t1;
disconnect flush;
#
# Bug#45412 SHOW CREATE TRIGGER does not require privileges to disclose trigger data
#
CREATE DATABASE db1;
CREATE TABLE db1.t1 (a char(30)) ENGINE=MEMORY;
CREATE TRIGGER db1.trg AFTER INSERT ON db1.t1 FOR EACH ROW
INSERT INTO db1.t1 VALUES('Some very sensitive data goes here');
CREATE USER 'no_rights'@'localhost';
REVOKE ALL ON *.* FROM 'no_rights'@'localhost';
FLUSH PRIVILEGES;
connect (con1,localhost,no_rights,,);
SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS
WHERE trigger_schema = 'db1';
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SHOW CREATE TRIGGER db1.trg;
connection default;
disconnect con1;
DROP USER 'no_rights'@'localhost';
DROP DATABASE db1;
#
# Bug#55421 Protocol::end_statement(): Assertion `0' on multi-table UPDATE IGNORE
# To reproduce a crash we need to provoke a trigger execution with
# the following conditions:
# - active SELECT statement during trigger execution
# (i.e. LEX::current_select != NULL);
# - IGNORE option (i.e. LEX::current_select->no_error == TRUE);
--disable_warnings
DROP DATABASE IF EXISTS mysqltest_db1;
--enable_warnings
CREATE DATABASE mysqltest_db1;
USE mysqltest_db1;
CREATE USER mysqltest_u1@localhost;
GRANT ALL ON mysqltest_db1.* TO mysqltest_u1@localhost;
--connect(con1,localhost,mysqltest_u1,,mysqltest_db1)
CREATE TABLE t1 (
a1 int,
a2 int
);
INSERT INTO t1 VALUES (1, 20);
CREATE TRIGGER mysqltest_db1.upd_t1
BEFORE UPDATE ON t1 FOR EACH ROW SET new.a2 = 200;
CREATE TABLE t2 (
a1 int
);
INSERT INTO t2 VALUES (2);
--connection default
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
--error ER_TABLEACCESS_DENIED_ERROR
UPDATE IGNORE t1, t2 SET t1.a1 = 2, t2.a1 = 3 WHERE t1.a1 = 1 AND t2.a1 = 2;
# Cleanup
DROP DATABASE mysqltest_db1;
DROP USER mysqltest_u1@localhost;
--disconnect con1
--connection default
USE test;
--echo End of 5.1 tests.
--echo #
--echo # MDEV-34551: Column list in the trigger definition
--echo #
--echo # Check that a FOR UPDATE trigger with the `OF` clause is dumped correctly.
CREATE TABLE t1 (a INT, b INT);
CREATE TRIGGER t1_bu BEFORE UPDATE OF a ON t1 FOR EACH ROW SET @a = NEW.a;
CREATE TRIGGER t1_au AFTER UPDATE OF a ON t1 FOR EACH ROW SET @a = NEW.a;
--echo # dump the table t1 and its triggers. The `OF` clause should be dumped out
--echo # for triggers t1_bu, t1_au.
--exec $MYSQL_DUMP --compact test t1
--echo # Clean up
DROP TABLE t1;
--echo #
--echo # End of 11.7 tests
--echo #
Reference in a new issue View git blame Copy permalink