Debian Codegraph @

mysql-test/suite/events/events_grant.result

raw
1 CREATE DATABASE IF NOT EXISTS events_test;
2 use events_test;
3 CREATE EVENT one_event ON SCHEDULE EVERY 10 SECOND DO SELECT 123;
4 Warnings:
5 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
6 SHOW EVENTS;
7 Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
8 events_test one_event root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
9 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;
10 EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
11 def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE
12 CREATE DATABASE events_test2;
13 CREATE USER ev_test@localhost;
14 GRANT ALL ON events_test.* to ev_test@localhost;
15 GRANT ALL ON events_test2.* to ev_test@localhost;
16 GRANT ALL ON test.* TO ev_test@localhost;
17 REVOKE EVENT ON events_test2.* FROM ev_test@localhost;
18 connect ev_con1,localhost,ev_test,,events_test2;
19 select "NEW CONNECTION";
20 NEW CONNECTION
21 NEW CONNECTION
22 SELECT USER(), DATABASE();
23 USER() DATABASE()
24 ev_test@localhost events_test2
25 SHOW GRANTS;
26 Grants for ev_test@localhost
27 GRANT USAGE ON *.* TO `ev_test`@`localhost`
28 GRANT ALL PRIVILEGES ON `test`.* TO `ev_test`@`localhost`
29 GRANT ALL PRIVILEGES ON `events_test`.* TO `ev_test`@`localhost`
30 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER, DELETE HISTORY, SHOW CREATE ROUTINE ON `events_test2`.* TO `ev_test`@`localhost`
31 "Here comes an error:";
32 SHOW EVENTS;
33 ERROR 42000: Access denied for user 'ev_test'@'localhost' to database 'events_test2'
34 USE events_test;
35 "We should see one event";
36 SHOW EVENTS;
37 Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
38 events_test one_event root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
39 SELECT CONCAT("Let's create some new events from the name of ", USER());
40 CONCAT("Let's create some new events from the name of ", USER())
41 Let's create some new events from the name of ev_test@localhost
42 CREATE EVENT one_event ON SCHEDULE EVERY 20 SECOND DO SELECT 123;
43 ERROR HY000: Event 'one_event' already exists
44 CREATE EVENT two_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION NOT PRESERVE COMMENT "two event" DO SELECT 123;
45 Warnings:
46 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
47 CREATE EVENT three_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION PRESERVE COMMENT "three event" DO SELECT 123;
48 Warnings:
49 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
50 "Now we should see 3 events:";
51 SHOW EVENTS;
52 Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
53 events_test one_event root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
54 events_test three_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
55 events_test two_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
56 "This should show us only 2 events:";
57 SHOW EVENTS LIKE 't%event';
58 Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
59 events_test three_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
60 events_test two_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
61 "This should show us no events:";
62 SHOW EVENTS FROM test LIKE '%';
63 Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
64 connection default;
65 GRANT EVENT ON events_test2.* TO ev_test@localhost;
66 connection ev_con1;
67 USE events_test2;
68 CREATE EVENT four_event ON SCHEDULE EVERY 20 SECOND DO SELECT 42;
69 Warnings:
70 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
71 connection default;
72 USE events_test;
73 "We should see 4 events : one_event, two_event, three_event & four_event"
74 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;
75 EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
76 def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE
77 def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event
78 def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event
79 def events_test2 four_event ev_test@localhost SQL SELECT 42 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE
80 DROP DATABASE events_test2;
81 "We should see 3 events : one_event, two_event, three_event"
82 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;
83 EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
84 def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE
85 def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event
86 def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event
87 connection default;
88 CREATE DATABASE events_test2;
89 USE events_test2;
90 CREATE EVENT five_event ON SCHEDULE EVERY 20 SECOND DO SELECT 42;
91 Warnings:
92 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
93 connection ev_con1;
94 "Should see 4 events - one, two, three & five"
95 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;
96 EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
97 def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE
98 def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event
99 def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event
100 def events_test2 five_event root@localhost SQL SELECT 42 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE
101 connection default;
102 REVOKE EVENT ON events_test2.* FROM ev_test@localhost;
103 connection ev_con1;
104 USE test;
105 "Should see 3 events - one, two & three"
106 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;
107 EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
108 def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE
109 def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event
110 def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event
111 "Let's test ALTER EVENT which changes the definer"
112 USE events_test;
113 ALTER EVENT one_event ON SCHEDULE EVERY 10 SECOND;
114 "The definer should be ev_test@localhost"
115 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';
116 EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
117 def events_test one_event ev_test@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE
118 connection default;
119 USE events_test;
120 ALTER EVENT one_event COMMENT "comment";
121 connection ev_con1;
122 "The definer should be root@localhost"
123 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';
124 EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
125 def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE comment
126 ALTER EVENT one_event DO SELECT 12;
127 "The definer should be ev_test@localhost"
128 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';
129 EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
130 def events_test one_event ev_test@localhost SQL SELECT 12 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE comment
131 connection default;
132 "make the definer again root@localhost"
133 ALTER EVENT one_event COMMENT "new comment";
134 connection ev_con1;
135 "test DROP by another user"
136 DROP EVENT one_event;
137 connection default;
138 "One event should not be there"
139 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;
140 EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
141 def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event
142 def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event
143 def events_test2 five_event root@localhost SQL SELECT 42 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE
144 connection ev_con1;
145 disconnect ev_con1;
146 connection default;
147 DROP USER ev_test@localhost;
148 DROP DATABASE events_test2;
149 DROP DATABASE events_test;