273 lines
12 KiB
Plaintext
273 lines
12 KiB
Plaintext
include/save_binlog_position.inc
|
|
# ----------------------------------------------------------------------
|
|
# Begin : Tests for REVOKE on global, db and proxy privileges
|
|
CREATE USER userX, userY, userA;
|
|
CREATE DATABASE db1;
|
|
GRANT SUPER ON *.* TO userX, userA;
|
|
Warnings:
|
|
Warning 1287 The SUPER privilege identifier is deprecated
|
|
GRANT SELECT ON db1.* TO userX, userA;
|
|
GRANT PROXY ON userY TO userX, userA;
|
|
include/save_binlog_position.inc
|
|
# Initial set of grants for userX
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT SUPER ON *.* TO `userX`@`%`
|
|
GRANT SELECT ON `db1`.* TO `userX`@`%`
|
|
GRANT PROXY ON 'userY'@'%' TO 'userX'@'%'
|
|
# Case 1 : Revoke from non-existing user
|
|
REVOKE SUPER ON *.* FROM useX, userZ;
|
|
ERROR 42000: There is no such grant defined for user 'useX' on host '%'
|
|
REVOKE SELECT ON db1.* FROM userX, userZ;
|
|
ERROR 42000: There is no such grant defined for user 'userZ' on host '%'
|
|
REVOKE PROXY ON userY FROM userX, userZ;
|
|
ERROR 42000: There is no such grant defined for user 'userZ' on host '%'
|
|
# userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT SUPER ON *.* TO `userX`@`%`
|
|
GRANT SELECT ON `db1`.* TO `userX`@`%`
|
|
GRANT PROXY ON 'userY'@'%' TO 'userX'@'%'
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE SUPER ON.*FROM.*userX.*userZ.*)
|
|
include/assert_binlog_events.inc
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE SELCT ON.*db1.*FROM.*userX.*userZ.*)
|
|
include/assert_binlog_events.inc
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE PROXY ON .*userY.*FROM.*userX.*userZ.*)
|
|
include/assert_binlog_events.inc
|
|
# Case 2 : Revoke non-existing grants
|
|
REVOKE SELECT ON db1.* FROM userY;
|
|
ERROR 42000: There is no such grant defined for user 'userY' on host '%'
|
|
REVOKE PROXY ON userX FROM userY;
|
|
ERROR 42000: There is no such grant defined for user 'userY' on host '%'
|
|
# userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT SUPER ON *.* TO `userX`@`%`
|
|
GRANT SELECT ON `db1`.* TO `userX`@`%`
|
|
GRANT PROXY ON 'userY'@'%' TO 'userX'@'%'
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE SELECT ON .*db1.*FROM.*userY.*)
|
|
include/assert_binlog_events.inc
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE PROXY ON .*userX.*FROM.*userY.*)
|
|
include/assert_binlog_events.inc
|
|
# Case 3 : Valid revoke
|
|
REVOKE SUPER ON *.* FROM userX, userA;
|
|
Warnings:
|
|
Warning 1287 The SUPER privilege identifier is deprecated
|
|
# This event sequence pattern MUST be present in binlog: !Q(REVOKE SUPER ON *.* FROM.*userX.*userA.*)
|
|
include/assert_binlog_events.inc
|
|
include/save_binlog_position.inc
|
|
REVOKE SELECT ON db1.* FROM userX, userA;
|
|
# This event sequence pattern MUST be present in binlog: !Q(REVOKE SELECT ON .*db1.*FROM.*userX.*userA.*)
|
|
include/assert_binlog_events.inc
|
|
include/save_binlog_position.inc
|
|
REVOKE PROXY ON userY FROM userX, userA;
|
|
# This event sequence pattern MUST be present in binlog: !Q(REVOKE PROXY ON.*userY.*FROM.*userX.*userA.*)
|
|
include/assert_binlog_events.inc
|
|
# userX's grants must have changed
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT USAGE ON *.* TO `userX`@`%`
|
|
DROP USER userX, userY, userA;
|
|
DROP DATABASE db1;
|
|
# End : Tests for REVOKE on global, db and proxy privileges
|
|
# ----------------------------------------------------------------------
|
|
# 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;
|
|
include/save_binlog_position.inc
|
|
# Initial set of grants for userX
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT USAGE ON *.* TO `userX`@`%`
|
|
GRANT SELECT, INSERT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
|
|
# Case 1 : Revoke from non-existing user
|
|
REVOKE SELECT ON db1.table1 FROM userX, userZ;
|
|
ERROR 42000: There is no such grant defined for user 'userZ' on host '%' on table 'table1'
|
|
REVOKE INSERT(c1, c2) ON db1.table1 FROM userX, userZ;
|
|
ERROR 42000: There is no such grant defined for user 'userZ' on host '%' on table 'table1'
|
|
# userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT USAGE ON *.* TO `userX`@`%`
|
|
GRANT SELECT, INSERT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE SELECT ON.*db1.*table1.*FROM.*userX.*userZ)
|
|
include/assert_binlog_events.inc
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE INSERT(.*c1.*c2.*) ON .*db1.*table1.*FROM.*userX.*userZ.*)
|
|
include/assert_binlog_events.inc
|
|
# Case 2 : Revoke non-existing grants
|
|
REVOKE SELECT ON db1.table1 FROM userX, userY;
|
|
ERROR 42000: There is no such grant defined for user 'userY' on host '%' on table 'table1'
|
|
REVOKE INSERT(c1, c2) ON db1.table1 FROM userX, userY;
|
|
ERROR 42000: There is no such grant defined for user 'userY' on host '%' on table 'table1'
|
|
# userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT USAGE ON *.* TO `userX`@`%`
|
|
GRANT SELECT, INSERT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE SELECT ON.*db1.*table1.*FROM.*userX.*userY.*)
|
|
include/assert_binlog_events.inc
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE INSERT(.*c1.*c2.*) ON .*db1.*table1.* FROM.*userX.*userY.*)
|
|
include/assert_binlog_events.inc
|
|
# Case 3 : Valid revoke
|
|
REVOKE SELECT ON db1.table1 FROM userX, userA;
|
|
# This event sequence pattern MUST be present in binlog: !Q(REVOKE SELECT ON.*db1.*table1.*FROM.*userX.*userA.*)
|
|
include/assert_binlog_events.inc
|
|
include/save_binlog_position.inc
|
|
REVOKE INSERT(c1, c2) ON db1.table1 FROM userX, userA;
|
|
# This event sequence pattern MUST be present in binlog: !Q(REVOKE INSERT(.*c1.*c2.*) ON.*db1.*table1.*FROM.*userX.*userA.*)
|
|
include/assert_binlog_events.inc
|
|
# userX's grants must have changed
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT USAGE ON *.* TO `userX`@`%`
|
|
DROP USER userX, userY, userA;
|
|
DROP TABLE db1.table1;
|
|
DROP DATABASE db1;
|
|
include/save_binlog_position.inc
|
|
# End : Tests for REVOKE on table and column privileges
|
|
# ----------------------------------------------------------------------
|
|
# Begin : Tests for REVOKE on procedure privileges
|
|
CREATE USER userX, userY, userA;
|
|
CREATE DATABASE db1;
|
|
CREATE PROCEDURE db1.proc1()
|
|
BEGIN
|
|
SELECT 1234;
|
|
END ||
|
|
GRANT EXECUTE ON PROCEDURE db1.proc1 to userX, userA;
|
|
include/save_binlog_position.inc
|
|
# Initial set of grants for userX
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT USAGE ON *.* TO `userX`@`%`
|
|
GRANT EXECUTE ON PROCEDURE `db1`.`proc1` TO `userX`@`%`
|
|
Case 1 : Revoke from non-existing user
|
|
REVOKE EXECUTE ON PROCEDURE db1.proc1 FROM userX, userZ;
|
|
ERROR 42000: There is no such grant defined for user 'userZ' on host '%' on routine 'proc1'
|
|
# userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT USAGE ON *.* TO `userX`@`%`
|
|
GRANT EXECUTE ON PROCEDURE `db1`.`proc1` TO `userX`@`%`
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE EXECUTE ON PROCEDURE.*db1.*proc1.*FROM.*userX.*userZ.*)
|
|
include/assert_binlog_events.inc
|
|
Case 2 : Revoke non-existing grants
|
|
REVOKE EXECUTE ON PROCEDURE db1.proc1 FROM userX, userY;
|
|
ERROR 42000: There is no such grant defined for user 'userY' on host '%' on routine 'proc1'
|
|
# userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT USAGE ON *.* TO `userX`@`%`
|
|
GRANT EXECUTE ON PROCEDURE `db1`.`proc1` TO `userX`@`%`
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE EXECUTE ON PROCEDURE.*db1.*proc1.*FROM.*userX.*userY.*)
|
|
include/assert_binlog_events.inc
|
|
Case 3 : Valid revoke
|
|
REVOKE EXECUTE ON PROCEDURE db1.proc1 FROM userX, userA;
|
|
# userX's grants must have changed
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT USAGE ON *.* TO `userX`@`%`
|
|
# This event sequence pattern MUST be present in binlog: !Q(REVOKE EXECUTE ON PROCEDURE.*db1.*proc1.*FROM.*userX.*userA.*)
|
|
include/assert_binlog_events.inc
|
|
DROP USER userX, userY, userA;
|
|
DROP PROCEDURE db1.proc1;
|
|
DROP DATABASE db1;
|
|
include/save_binlog_position.inc
|
|
# End : Tests for REVOKE on procedures privileges
|
|
# ----------------------------------------------------------------------
|
|
# Begin : Tests for REVOKE on roles
|
|
CREATE USER userX, userY, userA;
|
|
CREATE ROLE roleA, roleB;
|
|
GRANT roleA, roleB TO userX, userA;
|
|
include/save_binlog_position.inc
|
|
# Initial set of grants for userX
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT USAGE ON *.* TO `userX`@`%`
|
|
GRANT `roleA`@`%`,`roleB`@`%` TO `userX`@`%`
|
|
# Case 1 : Revoke from non-existing user
|
|
REVOKE roleA, roleB FROM userX, userZ;
|
|
ERROR HY000: Unknown authorization ID `userZ`@`%`
|
|
# userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT USAGE ON *.* TO `userX`@`%`
|
|
GRANT `roleA`@`%`,`roleB`@`%` TO `userX`@`%`
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE.*roleA.*,.*roleB.*FROM.*userX.*,.*userZ.*)
|
|
include/assert_binlog_events.inc
|
|
# Case 2 : Valid revoke
|
|
REVOKE roleA, roleB FROM userX, userA;
|
|
# userX's grants must have changed
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT USAGE ON *.* TO `userX`@`%`
|
|
# This event sequence pattern MUST be present in binlog: !Q(REVOKE.*roleA.*,.*roleB.*FROM.*userX.*,.*userA.*)
|
|
include/assert_binlog_events.inc
|
|
DROP USER userX, userY, userA;
|
|
DROP ROLE roleA, roleB;
|
|
include/save_binlog_position.inc
|
|
# End : Tests for REVOKE on roles
|
|
# ----------------------------------------------------------------------
|
|
# 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);
|
|
CREATE PROCEDURE db1.proc1()
|
|
BEGIN
|
|
SELECT 1234;
|
|
END ||
|
|
GRANT SUPER ON *.* TO userX, userA;
|
|
Warnings:
|
|
Warning 1287 The SUPER privilege identifier is deprecated
|
|
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;
|
|
include/save_binlog_position.inc
|
|
# Initial set of grants for userX
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT SUPER ON *.* TO `userX`@`%`
|
|
GRANT SELECT ON `db1`.* TO `userX`@`%`
|
|
GRANT SELECT, INSERT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
|
|
GRANT EXECUTE ON PROCEDURE `db1`.`proc1` TO `userX`@`%`
|
|
GRANT PROXY ON 'userY'@'%' TO 'userX'@'%'
|
|
GRANT `roleA`@`%`,`roleB`@`%` TO `userX`@`%`
|
|
# Case 1 : Revoke from non-existing user
|
|
REVOKE ALL PRIVILEGES, GRANT OPTION FROM userX, userZ;
|
|
ERROR HY000: Can't revoke all privileges for one or more of the requested users
|
|
# userX's grants must not have changed
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT SUPER ON *.* TO `userX`@`%`
|
|
GRANT SELECT ON `db1`.* TO `userX`@`%`
|
|
GRANT SELECT, INSERT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
|
|
GRANT EXECUTE ON PROCEDURE `db1`.`proc1` TO `userX`@`%`
|
|
GRANT PROXY ON 'userY'@'%' TO 'userX'@'%'
|
|
GRANT `roleA`@`%`,`roleB`@`%` TO `userX`@`%`
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE ALL PRIVILEGES, GRANT OPTION FROM.*userX.*,.*userZ.*)
|
|
include/assert_binlog_events.inc
|
|
# Case 2 : Valid revoke
|
|
REVOKE ALL PRIVILEGES, GRANT OPTION FROM userX, userA;
|
|
# userX's grants must have changed
|
|
SHOW GRANTS FOR userX;
|
|
Grants for userX@%
|
|
GRANT USAGE ON *.* TO `userX`@`%`
|
|
GRANT PROXY ON 'userY'@'%' TO 'userX'@'%'
|
|
GRANT `roleA`@`%`,`roleB`@`%` TO `userX`@`%`
|
|
# This event sequence pattern MUST be present in binlog: !Q(REVOKE ALL PRIVILEGES, GRANT OPTION FROM.*userX.*,.*userA.*)
|
|
include/assert_binlog_events.inc
|
|
DROP USER userX, userY, userA;
|
|
DROP ROLE roleA, roleB;
|
|
DROP TABLE db1.table1;
|
|
DROP PROCEDURE db1.proc1;
|
|
DROP DATABASE db1;
|
|
include/save_binlog_position.inc
|
|
# End : Tests for REVOKE ALL
|
|
# ----------------------------------------------------------------------
|