337 lines
9.2 KiB
Plaintext
337 lines
9.2 KiB
Plaintext
--source include/have_log_bin.inc
|
|
--source include/save_binlog_position.inc
|
|
|
|
# Save the initial number of concurrent sessions
|
|
--source include/count_sessions.inc
|
|
|
|
--echo # ----------------------------------------------------------------------
|
|
|
|
--echo # Begin : Tests for REVOKE on global, db and proxy privileges
|
|
|
|
CREATE USER userX, userY, userA;
|
|
CREATE DATABASE db1;
|
|
|
|
GRANT SUPER ON *.* TO userX, userA;
|
|
GRANT SELECT ON db1.* TO userX, userA;
|
|
GRANT PROXY ON userY TO userX, userA;
|
|
|
|
--source include/save_binlog_position.inc
|
|
|
|
--echo # Initial set of grants for userX
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--echo # Case 1 : Revoke from non-existing user
|
|
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE SUPER ON *.* FROM useX, userZ;
|
|
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE SELECT ON db1.* FROM userX, userZ;
|
|
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE PROXY ON userY FROM userX, userZ;
|
|
|
|
--echo # userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--let $invert= 1
|
|
--let $event= !Q(REVOKE SUPER ON.*FROM.*userX.*userZ.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
|
|
--let $event= !Q(REVOKE SELCT ON.*db1.*FROM.*userX.*userZ.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
|
|
--let $event= !Q(REVOKE PROXY ON .*userY.*FROM.*userX.*userZ.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--let $invert= 0
|
|
|
|
--echo # Case 2 : Revoke non-existing grants
|
|
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE SELECT ON db1.* FROM userY;
|
|
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE PROXY ON userX FROM userY;
|
|
|
|
--echo # userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--let $invert= 1
|
|
--let $event= !Q(REVOKE SELECT ON .*db1.*FROM.*userY.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
|
|
--let $event= !Q(REVOKE PROXY ON .*userX.*FROM.*userY.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--let $invert= 0
|
|
|
|
--echo # Case 3 : Valid revoke
|
|
|
|
REVOKE SUPER ON *.* FROM userX, userA;
|
|
|
|
--let $event= !Q(REVOKE SUPER ON *.* FROM.*userX.*userA.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--source include/save_binlog_position.inc
|
|
|
|
REVOKE SELECT ON db1.* FROM userX, userA;
|
|
|
|
--let $event= !Q(REVOKE SELECT ON .*db1.*FROM.*userX.*userA.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--source include/save_binlog_position.inc
|
|
|
|
REVOKE PROXY ON userY FROM userX, userA;
|
|
|
|
--let $event= !Q(REVOKE PROXY ON.*userY.*FROM.*userX.*userA.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
|
|
--echo # userX's grants must have changed
|
|
SHOW GRANTS FOR userX;
|
|
|
|
DROP USER userX, userY, userA;
|
|
DROP DATABASE db1;
|
|
|
|
--echo # End : Tests for REVOKE on global, db and proxy privileges
|
|
|
|
--echo # ----------------------------------------------------------------------
|
|
|
|
--echo # Begin : Tests for REVOKE on table and column privileges
|
|
|
|
CREATE USER userX, userY, userA;
|
|
CREATE DATABASE db1;
|
|
CREATE TABLE db1.table1(c1 int, c2 int, c3 int);
|
|
|
|
GRANT SELECT ON db1.table1 TO userX, userA;
|
|
GRANT INSERT(c1, c2) ON db1.table1 TO userX, userA;
|
|
|
|
--source include/save_binlog_position.inc
|
|
|
|
--echo # Initial set of grants for userX
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--echo # Case 1 : Revoke from non-existing user
|
|
|
|
--error ER_NONEXISTING_TABLE_GRANT
|
|
REVOKE SELECT ON db1.table1 FROM userX, userZ;
|
|
|
|
--error ER_NONEXISTING_TABLE_GRANT
|
|
REVOKE INSERT(c1, c2) ON db1.table1 FROM userX, userZ;
|
|
|
|
--echo # userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--let $invert= 1
|
|
--let $event= !Q(REVOKE SELECT ON.*db1.*table1.*FROM.*userX.*userZ)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
|
|
--let $event= !Q(REVOKE INSERT(.*c1.*c2.*) ON .*db1.*table1.*FROM.*userX.*userZ.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--let $invert= 0
|
|
|
|
--echo # Case 2 : Revoke non-existing grants
|
|
|
|
--error ER_NONEXISTING_TABLE_GRANT
|
|
REVOKE SELECT ON db1.table1 FROM userX, userY;
|
|
|
|
--error ER_NONEXISTING_TABLE_GRANT
|
|
REVOKE INSERT(c1, c2) ON db1.table1 FROM userX, userY;
|
|
|
|
--echo # userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--let $invert= 1
|
|
--let $event= !Q(REVOKE SELECT ON.*db1.*table1.*FROM.*userX.*userY.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
|
|
--let $event= !Q(REVOKE INSERT(.*c1.*c2.*) ON .*db1.*table1.* FROM.*userX.*userY.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--let $invert= 0
|
|
|
|
--echo # Case 3 : Valid revoke
|
|
|
|
REVOKE SELECT ON db1.table1 FROM userX, userA;
|
|
|
|
--let $event= !Q(REVOKE SELECT ON.*db1.*table1.*FROM.*userX.*userA.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--source include/save_binlog_position.inc
|
|
|
|
REVOKE INSERT(c1, c2) ON db1.table1 FROM userX, userA;
|
|
|
|
--let $event= !Q(REVOKE INSERT(.*c1.*c2.*) ON.*db1.*table1.*FROM.*userX.*userA.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
|
|
--echo # userX's grants must have changed
|
|
SHOW GRANTS FOR userX;
|
|
|
|
DROP USER userX, userY, userA;
|
|
DROP TABLE db1.table1;
|
|
DROP DATABASE db1;
|
|
--source include/save_binlog_position.inc
|
|
|
|
--echo # End : Tests for REVOKE on table and column privileges
|
|
|
|
--echo # ----------------------------------------------------------------------
|
|
|
|
--echo # Begin : Tests for REVOKE on procedure privileges
|
|
|
|
CREATE USER userX, userY, userA;
|
|
CREATE DATABASE db1;
|
|
DELIMITER ||;
|
|
CREATE PROCEDURE db1.proc1()
|
|
BEGIN
|
|
SELECT 1234;
|
|
END ||
|
|
DELIMITER ;||
|
|
|
|
GRANT EXECUTE ON PROCEDURE db1.proc1 to userX, userA;
|
|
--source include/save_binlog_position.inc
|
|
|
|
--echo # Initial set of grants for userX
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--echo Case 1 : Revoke from non-existing user
|
|
|
|
--error ER_NONEXISTING_PROC_GRANT
|
|
REVOKE EXECUTE ON PROCEDURE db1.proc1 FROM userX, userZ;
|
|
|
|
--echo # userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--let $invert= 1
|
|
--let $event= !Q(REVOKE EXECUTE ON PROCEDURE.*db1.*proc1.*FROM.*userX.*userZ.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--let $invert= 0
|
|
|
|
--echo Case 2 : Revoke non-existing grants
|
|
|
|
--error ER_NONEXISTING_PROC_GRANT
|
|
REVOKE EXECUTE ON PROCEDURE db1.proc1 FROM userX, userY;
|
|
|
|
--echo # userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--let $invert= 1
|
|
--let $event= !Q(REVOKE EXECUTE ON PROCEDURE.*db1.*proc1.*FROM.*userX.*userY.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--let $invert= 0
|
|
|
|
--echo Case 3 : Valid revoke
|
|
|
|
REVOKE EXECUTE ON PROCEDURE db1.proc1 FROM userX, userA;
|
|
|
|
--echo # userX's grants must have changed
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--let $event= !Q(REVOKE EXECUTE ON PROCEDURE.*db1.*proc1.*FROM.*userX.*userA.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
|
|
DROP USER userX, userY, userA;
|
|
DROP PROCEDURE db1.proc1;
|
|
DROP DATABASE db1;
|
|
--source include/save_binlog_position.inc
|
|
|
|
--echo # End : Tests for REVOKE on procedures privileges
|
|
|
|
--echo # ----------------------------------------------------------------------
|
|
|
|
--echo # Begin : Tests for REVOKE on roles
|
|
|
|
CREATE USER userX, userY, userA;
|
|
CREATE ROLE roleA, roleB;
|
|
GRANT roleA, roleB TO userX, userA;
|
|
--source include/save_binlog_position.inc
|
|
|
|
--echo # Initial set of grants for userX
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--echo # Case 1 : Revoke from non-existing user
|
|
|
|
--error ER_UNKNOWN_AUTHID
|
|
REVOKE roleA, roleB FROM userX, userZ;
|
|
|
|
--echo # userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--let $invert= 1
|
|
--let $event= !Q(REVOKE.*roleA.*,.*roleB.*FROM.*userX.*,.*userZ.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--let $invert= 0
|
|
|
|
--echo # Case 2 : Valid revoke
|
|
|
|
REVOKE roleA, roleB FROM userX, userA;
|
|
|
|
--echo # userX's grants must have changed
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--let $event= !Q(REVOKE.*roleA.*,.*roleB.*FROM.*userX.*,.*userA.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
|
|
DROP USER userX, userY, userA;
|
|
DROP ROLE roleA, roleB;
|
|
--source include/save_binlog_position.inc
|
|
|
|
--echo # End : Tests for REVOKE on roles
|
|
|
|
--echo # ----------------------------------------------------------------------
|
|
|
|
--echo # Begin : Tests for REVOKE ALL
|
|
|
|
CREATE USER userX, userY, userA;
|
|
CREATE ROLE roleA, roleB;
|
|
CREATE DATABASE db1;
|
|
CREATE TABLE db1.table1(c1 int, c2 int, c3 int);
|
|
DELIMITER ||;
|
|
CREATE PROCEDURE db1.proc1()
|
|
BEGIN
|
|
SELECT 1234;
|
|
END ||
|
|
DELIMITER ;||
|
|
|
|
GRANT SUPER ON *.* TO userX, userA;
|
|
GRANT SELECT ON db1.* TO userX, userA;
|
|
GRANT PROXY ON userY TO userX, userA;
|
|
GRANT SELECT ON db1.table1 TO userX, userA;
|
|
GRANT INSERT(c1, c2) ON db1.table1 TO userX, userA;
|
|
GRANT EXECUTE ON PROCEDURE db1.proc1 to userX, userA;
|
|
GRANT roleA, roleB TO userX, userA;
|
|
|
|
--source include/save_binlog_position.inc
|
|
|
|
--echo # Initial set of grants for userX
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--echo # Case 1 : Revoke from non-existing user
|
|
|
|
--error ER_REVOKE_GRANTS
|
|
REVOKE ALL PRIVILEGES, GRANT OPTION FROM userX, userZ;
|
|
|
|
--echo # userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--let $invert= 1
|
|
--let $event= !Q(REVOKE ALL PRIVILEGES, GRANT OPTION FROM.*userX.*,.*userZ.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--let $invert= 0
|
|
|
|
--echo # Case 2 : Valid revoke
|
|
|
|
REVOKE ALL PRIVILEGES, GRANT OPTION FROM userX, userA;
|
|
|
|
--echo # userX's grants must have changed
|
|
SHOW GRANTS FOR userX;
|
|
|
|
--let $event= !Q(REVOKE ALL PRIVILEGES, GRANT OPTION FROM.*userX.*,.*userA.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
|
|
DROP USER userX, userY, userA;
|
|
DROP ROLE roleA, roleB;
|
|
DROP TABLE db1.table1;
|
|
DROP PROCEDURE db1.proc1;
|
|
DROP DATABASE db1;
|
|
--source include/save_binlog_position.inc
|
|
|
|
--echo # End : Tests for REVOKE ALL
|
|
|
|
--echo # ----------------------------------------------------------------------
|
|
# Wait till we reached the initial number of concurrent sessions
|
|
--source include/wait_until_count_sessions.inc
|