Debian Codegraph @

mysql-test/suite/events/events_grant.test

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