1 2# Can't test with embedded server that doesn't support grants 3-- source include/not_embedded.inc 4 5CREATE DATABASE IF NOT EXISTS events_test; 6use events_test; 7# 8# Events grants test begin 9# 10CREATE EVENT one_event ON SCHEDULE EVERY 10 SECOND DO SELECT 123; 11--replace_column 8 # 9 # 12SHOW EVENTS; 13SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; 14CREATE DATABASE events_test2; 15CREATE USER ev_test@localhost; 16GRANT ALL ON events_test.* to ev_test@localhost; 17GRANT ALL ON events_test2.* to ev_test@localhost; 18REVOKE EVENT ON events_test2.* FROM ev_test@localhost; 19#now we are on con1 20connect (ev_con1,localhost,ev_test,,events_test2); 21select "NEW CONNECTION"; 22SELECT USER(), DATABASE(); 23SHOW GRANTS; 24--echo "Here comes an error:"; 25#NO EVENT_ACL on events_test2 26--error 1044 27SHOW EVENTS; 28USE events_test; 29--echo "We should see one event"; 30--replace_column 8 # 9 # 31SHOW EVENTS; 32#now create an event with the same name but we are different user 33SELECT CONCAT("Let's create some new events from the name of ", USER()); 34--error ER_EVENT_ALREADY_EXISTS 35CREATE EVENT one_event ON SCHEDULE EVERY 20 SECOND DO SELECT 123; 36CREATE EVENT two_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION NOT PRESERVE COMMENT "two event" DO SELECT 123; 37CREATE EVENT three_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION PRESERVE COMMENT "three event" DO SELECT 123; 38 39--echo "Now we should see 3 events:"; 40--replace_column 8 # 9 # 41SHOW EVENTS; 42 43--echo "This should show us only 2 events:"; 44--replace_column 8 # 9 # 45SHOW EVENTS LIKE 't%event'; 46 47--echo "This should show us no events:"; 48--replace_column 8 # 9 # 49SHOW EVENTS FROM test LIKE '%'; 50#ok, we are back 51connection default; 52GRANT EVENT ON events_test2.* TO ev_test@localhost; 53connection ev_con1; 54USE events_test2; 55CREATE EVENT four_event ON SCHEDULE EVERY 20 SECOND DO SELECT 42; 56connection default; 57USE events_test; 58--echo "We should see 4 events : one_event, two_event, three_event & four_event" 59SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; 60DROP DATABASE events_test2; 61--echo "We should see 3 events : one_event, two_event, three_event" 62SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; 63 64connection default; 65CREATE DATABASE events_test2; 66USE events_test2; 67CREATE EVENT five_event ON SCHEDULE EVERY 20 SECOND DO SELECT 42; 68 69connection ev_con1; 70--echo "Should see 4 events - one, two, three & five" 71SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; 72connection default; 73REVOKE EVENT ON events_test2.* FROM ev_test@localhost; 74connection ev_con1; 75USE test; 76--echo "Should see 3 events - one, two & three" 77SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; 78--echo "Let's test ALTER EVENT which changes the definer" 79USE events_test; 80ALTER EVENT one_event ON SCHEDULE EVERY 10 SECOND; 81--echo "The definer should be ev_test@localhost" 82SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; 83connection default; 84USE events_test; 85ALTER EVENT one_event COMMENT "comment"; 86connection ev_con1; 87--echo "The definer should be root@localhost" 88SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; 89ALTER EVENT one_event DO SELECT 12; 90--echo "The definer should be ev_test@localhost" 91SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; 92connection default; 93--echo "make the definer again root@localhost" 94ALTER EVENT one_event COMMENT "new comment"; 95connection ev_con1; 96--echo "test DROP by another user" 97DROP EVENT one_event; 98connection default; 99--echo "One event should not be there" 100SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; 101connection ev_con1; 102disconnect ev_con1; 103--source include/wait_until_disconnected.inc 104connection default; 105DROP USER ev_test@localhost; 106DROP DATABASE events_test2; 107 108# 109# End of tests 110# 111 112--source include/check_events_off.inc 113 114DROP DATABASE events_test; 115