trigger-grant.test

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

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
--echo
--echo ---> 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
--echo
--echo ---> 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
--echo
--echo ---> 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
--echo
--echo ---> 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 triggger 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, SUPER 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;

--echo End of 5.0 tests.