297 lines
12 KiB
Text
297 lines
12 KiB
Text
|
|
--echo #
|
|
--echo # WL#14690: Support IF EXISTS and IGNORE UNKNOWN USER optional clauses
|
|
--echo # in REVOKE statement
|
|
--echo #
|
|
|
|
--source include/have_log_bin.inc
|
|
CREATE DATABASE wl14690;
|
|
CREATE USER u1,u2,u3,u4;
|
|
CREATE ROLE r1,r2;
|
|
CREATE TABLE wl14690.t(i int, j int);
|
|
CREATE FUNCTION wl14690.fun() RETURNS INT DETERMINISTIC CONTAINS SQL RETURN @var1;
|
|
|
|
SET @@global.partial_revokes = OFF;
|
|
|
|
--echo # FR1.1.1: REVOKE ... IGNORE UNKNOWN USER where target user/role does not exists
|
|
RESET BINARY LOGS AND GTIDS;
|
|
--error ER_REVOKE_GRANTS
|
|
REVOKE ALL ON *.* FROM unknown_user;
|
|
REVOKE ALL ON *.* FROM unknown_user IGNORE UNKNOWN USER;
|
|
SELECT COUNT(*) FROM mysql.user where user = 'unknown_user';
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE SELECT ON *.* FROM unknown_user;
|
|
REVOKE SELECT ON *.* FROM unknown_user IGNORE UNKNOWN USER;
|
|
SELECT COUNT(*) FROM mysql.user where user = 'unknown_user';
|
|
--error ER_REVOKE_GRANTS
|
|
REVOKE ALL PRIVILEGES, GRANT OPTION FROM unknown_user;
|
|
REVOKE ALL PRIVILEGES, GRANT OPTION FROM unknown_user IGNORE UNKNOWN USER;
|
|
SELECT COUNT(*) FROM mysql.user where user = 'unknown_user';
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE PROXY ON u1 FROM unknown_user;
|
|
REVOKE PROXY ON u1 FROM unknown_user IGNORE UNKNOWN USER;
|
|
--error ER_UNKNOWN_AUTHID
|
|
REVOKE r1 FROM unknown_user;
|
|
REVOKE r1 FROM unknown_user IGNORE UNKNOWN USER;
|
|
SELECT COUNT(*) FROM mysql.user where user = 'unknown_user';
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
|
|
--echo # FR1.1.2: REVOKE ... IGNORE UNKNOWN USER where privilege does not exist
|
|
RESET BINARY LOGS AND GTIDS;
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE ALL ON wl14690.* FROM u1;
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE ALL ON wl14690.* FROM u1 IGNORE UNKNOWN USER;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
|
|
GRANT SELECT, INSERT (i) ON wl14690.t TO u1, u2;
|
|
GRANT SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN ON *.* TO u2;
|
|
GRANT CREATE ROUTINE ON wl14690.* TO u1;
|
|
GRANT CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO u2;
|
|
GRANT EXECUTE ON FUNCTION wl14690.fun TO u1,u3;
|
|
|
|
--echo # FR1.2.1: REVOKE ... IGNORE UNKNOWN USER where target user/role exists
|
|
--echo # and privilege being revoked is granted
|
|
SHOW GRANTS FOR u1;
|
|
SHOW GRANTS FOR u2;
|
|
RESET BINARY LOGS AND GTIDS;
|
|
REVOKE INSERT (i) ON wl14690.t FROM u1, u2 IGNORE UNKNOWN USER;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
--echo # check that INSERT (i) is revoked.
|
|
SHOW GRANTS FOR u1;
|
|
SHOW GRANTS FOR u2;
|
|
|
|
--echo # FR1.2.2: REVOKE ... IGNORE UNKNOWN USER with missing target user/role
|
|
GRANT INSERT (i) ON wl14690.t TO u1, u2;
|
|
GRANT r1 TO u2;
|
|
SHOW GRANTS FOR u1;
|
|
SHOW GRANTS FOR u2;
|
|
RESET BINARY LOGS AND GTIDS;
|
|
--error ER_NONEXISTING_TABLE_GRANT
|
|
REVOKE SELECT, INSERT (i) ON wl14690.t FROM u1, u2, unknown_user;
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM unknown_user, u2;
|
|
--error ER_UNKNOWN_AUTHID
|
|
REVOKE r1 FROM unknown_user, u2;
|
|
--echo # check that r1 grant exists for u2
|
|
SHOW GRANTS FOR u2;
|
|
REVOKE r1 FROM unknown_user, u2 IGNORE UNKNOWN USER;
|
|
REVOKE SELECT, INSERT (i) ON wl14690.t FROM u1, u2, unknown_user IGNORE UNKNOWN USER;
|
|
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM unknown_user, u2 IGNORE UNKNOWN USER;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
--echo # check that SELECT, INSERT (i) are revoked.
|
|
SHOW GRANTS FOR u1;
|
|
--echo # check that r1 grant is revoked for u2
|
|
SHOW GRANTS FOR u2;
|
|
|
|
--echo # FR1.2.3: REVOKE ... IGNORE UNKNOWN USER with missing target user/role
|
|
--echo # and privilege being revoked is not granted
|
|
GRANT INSERT (i) ON wl14690.t TO u1, u2;
|
|
RESET BINARY LOGS AND GTIDS;
|
|
--error ER_NONEXISTING_TABLE_GRANT
|
|
REVOKE INSERT (i) ON wl14690.t FROM u1, u2, u3, unknown_user IGNORE UNKNOWN USER;
|
|
--echo # check that INSERT (i) is not revoked.
|
|
SHOW GRANTS FOR u1;
|
|
SHOW GRANTS FOR u2;
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE EVENT, TRIGGER ON wl14690.* FROM u2, u3, unknown_user IGNORE UNKNOWN USER;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
--echo # check that existing privileges are not revoked.
|
|
SHOW GRANTS FOR u1;
|
|
SHOW GRANTS FOR u2;
|
|
|
|
--echo # FR1.3.1: REVOKE IF EXISTS where target user/role exists and
|
|
--echo # privilege being revoked is not granted
|
|
RESET BINARY LOGS AND GTIDS;
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE PROXY ON u3 FROM u1;
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE IF EXISTS PROXY ON u3 FROM unknown_user, u1;
|
|
--echo # report warning
|
|
REVOKE IF EXISTS PROXY ON u3 FROM u1;
|
|
REVOKE IF EXISTS PROXY ON u3 FROM r1;
|
|
REVOKE ALL ON *.* FROM u1;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON *.* to u1;
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE SELECT ON wl14690.* FROM u1;
|
|
--echo # report warning
|
|
REVOKE IF EXISTS SELECT ON wl14690.* FROM u1;
|
|
REVOKE IF EXISTS SELECT ON wl14690.* FROM r1;
|
|
REVOKE IF EXISTS INSERT (i) ON wl14690.t FROM u4;
|
|
REVOKE IF EXISTS INSERT (j) ON wl14690.t FROM u4;
|
|
REVOKE IF EXISTS ENCRYPTION_KEY_ADMIN, APPLICATION_PASSWORD_ADMIN ON *.* FROM u4;
|
|
--error ER_UNKNOWN_AUTHID
|
|
REVOKE role1 FROM u1;
|
|
REVOKE IF EXISTS role1 FROM u1;
|
|
GRANT r1 TO u1;
|
|
--error ER_UNKNOWN_AUTHID
|
|
REVOKE r1, role1 FROM u1;
|
|
--echo # check that r1 grant is not revoked to u1
|
|
SHOW GRANTS FOR u1;
|
|
REVOKE IF EXISTS role1, r1 FROM u1;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
--echo # check that r1 grant is revoked from u1
|
|
SHOW GRANTS FOR u1;
|
|
SHOW GRANTS FOR u2;
|
|
|
|
--echo # FR1.3.2: REVOKE IF EXISTS with missing target user/role and
|
|
--echo # privilege being revoked is granted
|
|
RESET BINARY LOGS AND GTIDS;
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE SELECT ON *.* FROM unknown_user;
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE IF EXISTS SELECT ON *.* FROM unknown_user;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
|
|
--echo # FR1.3.3: REVOKE IF EXISTS where target user/role exists and
|
|
--echo # privilege being revoked is granted
|
|
RESET BINARY LOGS AND GTIDS;
|
|
REVOKE IF EXISTS SELECT, INSERT, UPDATE ON *.* FROM u1;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
|
|
--echo # FR1.4.1: REVOKE IF EXISTS where multiple target users/roles exists and
|
|
--echo # privilege being revoked is granted
|
|
REVOKE ALL ON *.* FROM u1;
|
|
REVOKE ALL ON *.* FROM u2;
|
|
REVOKE ALL ON *.* FROM u3;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE on wl14690.* to u1;
|
|
GRANT SELECT ON wl14690.* TO u2;
|
|
GRANT UPDATE, SELECT ON wl14690.* TO u3;
|
|
RESET BINARY LOGS AND GTIDS;
|
|
REVOKE IF EXISTS SELECT ON wl14690.* FROM u1, u2, u3;
|
|
--error ER_UNKNOWN_AUTHID
|
|
REVOKE role1 FROM u1, u2;
|
|
REVOKE IF EXISTS role1 FROM u1, u2;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
--echo # SELECT should be revoked for all 3 users
|
|
SHOW GRANTS FOR u1;
|
|
SHOW GRANTS FOR u2;
|
|
SHOW GRANTS FOR u3;
|
|
|
|
--echo # FR1.4.2: REVOKE IF EXISTS where multiple target users/roles exists and
|
|
--echo # privilege being revoked is not granted
|
|
REVOKE ALL ON *.* FROM u1;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE on *.* to u1;
|
|
GRANT SELECT ON *.* TO u2;
|
|
GRANT SELECT ON wl14690.* TO u3;
|
|
SHOW GRANTS FOR u3;
|
|
RESET BINARY LOGS AND GTIDS;
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE DELETE, SELECT ON wl14690.* FROM u1, u2, u3;
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE IF EXISTS DELETE, SELECT ON wl14690.* FROM unknown_user, u1, u2;
|
|
REVOKE IF EXISTS DELETE, SELECT ON wl14690.* FROM u1, u2, u3;
|
|
REVOKE IF EXISTS DELETE, SELECT ON wl14690.* FROM r1;
|
|
--echo # for global level privleges IF EXISTS is implicit, so no warning
|
|
REVOKE IF EXISTS SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN
|
|
ON *.* FROM u1,u2,u3,u4,r1;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
--echo # SELECT should be revoked for u3
|
|
SHOW GRANTS FOR u3;
|
|
|
|
--echo # FR1.4.3: REVOKE IF EXISTS where multiple target users/roles missing and
|
|
--echo # privilege being revoked is granted
|
|
REVOKE ALL ON *.* FROM u1;
|
|
REVOKE ALL ON *.* FROM u2;
|
|
REVOKE ALL ON *.* FROM u3;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE on *.* to u1;
|
|
GRANT SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO u1;
|
|
GRANT UPDATE, SELECT ON *.* TO u2;
|
|
GRANT SELECT ON wl14690.* TO u3;
|
|
GRANT SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO u3;
|
|
SHOW GRANTS FOR u1;
|
|
SHOW GRANTS FOR u2;
|
|
SHOW GRANTS FOR u3;
|
|
RESET BINARY LOGS AND GTIDS;
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE IF EXISTS UPDATE, SELECT ON *.* FROM u1, u2, unknown_user;
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE IF EXISTS TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* FROM unknown_user, u3, u1;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
--echo # grants should be same
|
|
SHOW GRANTS FOR u1;
|
|
SHOW GRANTS FOR u2;
|
|
SHOW GRANTS FOR u3;
|
|
|
|
--echo # FR1.5: REVOKE IF EXISTS ... IGNORE UNKNOWN USER with single target user
|
|
--echo # and privilege which is not granted
|
|
RESET BINARY LOGS AND GTIDS;
|
|
--echo # missing privileges and target user report warning
|
|
REVOKE IF EXISTS UPDATE, SELECT ON wl14690.* FROM unknown_user IGNORE UNKNOWN USER;
|
|
--echo # missing privileges and target user
|
|
REVOKE IF EXISTS PROXY ON u1 FROM unknown_user IGNORE UNKNOWN USER;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
|
|
--echo # FR1.6.1: REVOKE IF EXISTS ... IGNORE UNKNOWN USER with multiple target
|
|
--echo # user (present) and privilege being revoked is granted
|
|
GRANT SELECT, INSERT (i) ON wl14690.t TO u1, u2;
|
|
SHOW GRANTS FOR u1;
|
|
SHOW GRANTS FOR u2;
|
|
RESET BINARY LOGS AND GTIDS;
|
|
REVOKE IF EXISTS INSERT (i) ON wl14690.t FROM u1, u2 IGNORE UNKNOWN USER;
|
|
REVOKE IF EXISTS TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* FROM u1, u3
|
|
IGNORE UNKNOWN USER;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
--echo # INSERT should be revoked
|
|
SHOW GRANTS FOR u1;
|
|
SHOW GRANTS FOR u2;
|
|
|
|
--echo # FR1.6.2: REVOKE IF EXISTS ... IGNORE UNKNOWN USER with multiple target
|
|
--echo # user (present) and privilege being revoked is not granted
|
|
RESET BINARY LOGS AND GTIDS;
|
|
REVOKE IF EXISTS DELETE, SELECT ON wl14690.* FROM u1, u2, u3
|
|
IGNORE UNKNOWN USER;
|
|
REVOKE IF EXISTS SYSTEM_VARIABLES_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN
|
|
ON *.* FROM u1, u2 IGNORE UNKNOWN USER;
|
|
SHOW GRANTS FOR u1;
|
|
SHOW GRANTS FOR u2;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
|
|
--echo # FR1.6.3: REVOKE IF EXISTS ... IGNORE UNKNOWN USER with multiple target
|
|
--echo # user (not present) and privilege being revoked is granted
|
|
RESET BINARY LOGS AND GTIDS;
|
|
REVOKE IF EXISTS SELECT ON wl14690.t FROM u1, unknown_user IGNORE UNKNOWN USER;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
|
|
--echo #
|
|
--echo # Bug #33899136: WL#14690 Revoking illegal privilege with IF EXISTS
|
|
--echo # doesn't issue warning
|
|
--echo #
|
|
|
|
RESET BINARY LOGS AND GTIDS;
|
|
--error ER_ILLEGAL_PRIVILEGE_LEVEL
|
|
REVOKE DYNAMIC_PRIV1, DYNAMIC_PRIV2 ON wl14690.* FROM u1;
|
|
--echo # should report warning
|
|
REVOKE IF EXISTS DYNAMIC_PRIV1, DYNAMIC_PRIV2 ON wl14690.* FROM u1;
|
|
--error ER_NONEXISTING_GRANT
|
|
REVOKE IF EXISTS DYNAMIC_PRIV1, DYNAMIC_PRIV2 ON wl14690.* FROM unknown_user;
|
|
--error ER_ILLEGAL_PRIVILEGE_LEVEL
|
|
REVOKE DYNAMIC_PRIV1, DYNAMIC_PRIV2 ON wl14690.* FROM unknown_user IGNORE UNKNOWN USER;
|
|
--echo # should report warnings
|
|
REVOKE IF EXISTS DYNAMIC_PRIV1, DYNAMIC_PRIV2 ON wl14690.* FROM unknown_user IGNORE UNKNOWN USER;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
|
|
--echo #
|
|
--echo # Bug #33899156: WL#14690 Revoking mandatory role with IF EXISTS doesn't
|
|
--echo # issue warning
|
|
--echo #
|
|
|
|
SET GLOBAL mandatory_roles=r1;
|
|
RESET BINARY LOGS AND GTIDS;
|
|
--error ER_MANDATORY_ROLE
|
|
REVOKE r1 FROM u1;
|
|
--error ER_MANDATORY_ROLE
|
|
REVOKE r1 FROM r2;
|
|
--error ER_MANDATORY_ROLE
|
|
REVOKE r1 FROM u1,u2,u3,r2;
|
|
REVOKE IF EXISTS r1 FROM u1;
|
|
REVOKE IF EXISTS r1 FROM r2;
|
|
REVOKE IF EXISTS r1 FROM u1,u2,u3,r2;
|
|
--source include/rpl/deprecated/show_binlog_events.inc
|
|
SET GLOBAL mandatory_roles=default;
|
|
SET GLOBAL partial_revokes=default;
|
|
|
|
#cleanup
|
|
DROP USER u1,u2,u3,u4;
|
|
DROP ROLE r1,r2;
|
|
DROP DATABASE wl14690;
|