Debian Codegraph @

mysql-test/suite/perfschema/r/read_only.result

raw
1 use performance_schema;
2 set @start_read_only= @@global.read_only;
3 set @start_super_read_only= @@global.super_read_only;
4 create user pfsuser@localhost;
5 grant SELECT, UPDATE on performance_schema.* to pfsuser@localhost;
6 flush privileges;
7 connect con1, localhost, pfsuser, , test;
8 connection default;
9 set global read_only=0;
10 connection con1;
11 select @@global.read_only;
12 @@global.read_only
13 0
14 show grants;
15 Grants for pfsuser@localhost
16 GRANT USAGE ON *.* TO `pfsuser`@`localhost`
17 GRANT SELECT, UPDATE ON `performance_schema`.* TO `pfsuser`@`localhost`
18 select * from performance_schema.setup_instruments;
19 update performance_schema.setup_instruments set enabled='NO';
20 update performance_schema.setup_instruments set enabled='YES';
21 connection default;
22 set global read_only=1;
23 connection con1;
24 select @@global.read_only;
25 @@global.read_only
26 1
27 show grants;
28 Grants for pfsuser@localhost
29 GRANT USAGE ON *.* TO `pfsuser`@`localhost`
30 GRANT SELECT, UPDATE ON `performance_schema`.* TO `pfsuser`@`localhost`
31 # Update on perf_schema is allowed in read_only mode.
32 select * from performance_schema.setup_instruments;
33 update performance_schema.setup_instruments set enabled='NO';
34 update performance_schema.setup_instruments set enabled='YES';
35 connection default;
36 grant super on *.* to pfsuser@localhost;
37 Warnings:
38 Warning 1287 The SUPER privilege identifier is deprecated
39 flush privileges;
40 disconnect con1;
41 connect con1, localhost, pfsuser, , test;
42 select @@global.read_only;
43 @@global.read_only
44 1
45 show grants;
46 Grants for pfsuser@localhost
47 GRANT SUPER ON *.* TO `pfsuser`@`localhost`
48 GRANT SELECT, UPDATE ON `performance_schema`.* TO `pfsuser`@`localhost`
49 select * from performance_schema.setup_instruments;
50 update performance_schema.setup_instruments set enabled='NO';
51 update performance_schema.setup_instruments set enabled='YES';
52 connection default;
53 set global super_read_only=1;
54 connection con1;
55 select @@global.super_read_only;
56 @@global.super_read_only
57 1
58 show grants;
59 Grants for pfsuser@localhost
60 GRANT SUPER ON *.* TO `pfsuser`@`localhost`
61 GRANT SELECT, UPDATE ON `performance_schema`.* TO `pfsuser`@`localhost`
62 select * from performance_schema.setup_instruments;
63 # Update is allowed in super_read_only on perf schema for
64 # super user.
65 update performance_schema.setup_instruments set enabled='NO';
66 update performance_schema.setup_instruments set enabled='YES';
67 #
68 # Bug#31080309 - REGRESSION FOR BUG 81009 FIXED IN 5.7.17
69 # Bug23103937(BUG81009) - PS_TRUNCATE_ALL_TABLES() DOES NOT WORK IN
70 # SUPER_READ_ONLY MODE.
71 #
72 connection default;
73 # Reset read_only and super_read_only mode.
74 set global read_only=0;
75 set global super_read_only=0;
76 # Grant truncate table and execute stored procedure privileges to pfsuser.
77 grant DROP on performance_schema.* to pfsuser@localhost;
78 grant EXECUTE on procedure sys.ps_truncate_all_tables to pfsuser@localhost;
79 #-----------------------------------------------------------------------
80 # Test case to verify truncate operation on performance schema table by
81 # super user with super_read_only=ON.
82 #-----------------------------------------------------------------------
83 # Set super_read_only mode.
84 set global super_read_only=1;
85 connection con1;
86 # truncate operation is allowed in super_read_only mode on performance schema
87 # tables for super user.
88 # Without fix following statements fail because of super read only mode.
89 truncate table performance_schema.events_statements_summary_by_digest;
90 call sys.ps_truncate_all_tables(0);
91 #-----------------------------------------------------------------------
92 # Test case to verify truncate operation on performance schema table by
93 # super user with read_only=ON.
94 #-----------------------------------------------------------------------
95 connection default;
96 # Reset super_read_only mode.
97 set global super_read_only=0;
98 # Set read_only mode.
99 set global read_only=1;
100 connection con1;
101 # truncate operation is allowed in read_only mode on performance schema
102 # tables for super user.
103 # Without fix following statements fail because of super read only mode.
104 truncate table performance_schema.events_statements_summary_by_digest;
105 call sys.ps_truncate_all_tables(0);
106 #-----------------------------------------------------------------------
107 # Test case to verify truncate operation on performance schema table by
108 # non-super user with super_read_only=ON.
109 #-----------------------------------------------------------------------
110 connection default;
111 set global super_read_only=0;
112 # Revoke SUPER privilege from pfsuser.
113 revoke SUPER on *.* from pfsuser@localhost;
114 Warnings:
115 Warning 1287 The SUPER privilege identifier is deprecated
116 # Reset super_read_only mode.
117 set global super_read_only=1;
118 connection con1;
119 # truncate operation is allowed in super_read_only mode on performance schema
120 # tables for non-super user.
121 truncate table performance_schema.events_statements_summary_by_digest;
122 call sys.ps_truncate_all_tables(0);
123 #-----------------------------------------------------------------------
124 # Test case to verify truncate operation on performance schema table by
125 # non-super user with read_only=ON.
126 #-----------------------------------------------------------------------
127 connection default;
128 # Reset super_read_only mode.
129 set global super_read_only=0;
130 # Set read_only mode.
131 set global read_only=1;
132 connection con1;
133 # truncate operation is allowed in read_only mode on performance schema
134 # tables for non-super user.
135 truncate table performance_schema.events_statements_summary_by_digest;
136 call sys.ps_truncate_all_tables(0);
137 ########################################################################
138 disconnect con1;
139 connection default;
140 set global read_only= @start_read_only;
141 set global super_read_only= @start_super_read_only;
142 drop user pfsuser@localhost;
143 flush privileges;