Debian Codegraph @

mysql-test/suite/sysschema/r/v_privileges_by_table_by_level.result

raw
1 DESC sys.privileges_by_table_by_level;
2 Field Type Null Key Default Extra
3 TABLE_SCHEMA varchar(64) NO NULL
4 TABLE_NAME varchar(64) NO NULL
5 GRANTEE varchar(385) NO
6 PRIVILEGE varchar(64) NO
7 LEVEL varchar(6) NO
8 CREATE DATABASE test1;
9 USE test1;
10 CREATE TABLE t1 (a int);
11 CREATE USER test1_user;
12 GRANT ALL PRIVILEGES ON test1.* TO test1_user;
13 # Grant all table privileges
14 GRANT ALL ON t1 to test1_user;
15 SELECT table_schema, table_name,
16 CASE WHEN grantee LIKE '\'root%' THEN '<some-root-user>' ELSE GRANTEE END,
17 privilege, level
18 FROM sys.privileges_by_table_by_level WHERE table_name='t1';
19 table_schema table_name CASE WHEN grantee LIKE '\'root%' THEN '<some-root-user>' ELSE GRANTEE END privilege level
20 test1 t1 <some-root-user> SELECT GLOBAL
21 test1 t1 <some-root-user> INSERT GLOBAL
22 test1 t1 <some-root-user> UPDATE GLOBAL
23 test1 t1 <some-root-user> DELETE GLOBAL
24 test1 t1 <some-root-user> CREATE GLOBAL
25 test1 t1 <some-root-user> DROP GLOBAL
26 test1 t1 <some-root-user> REFERENCES GLOBAL
27 test1 t1 <some-root-user> INDEX GLOBAL
28 test1 t1 <some-root-user> ALTER GLOBAL
29 test1 t1 <some-root-user> SHOW VIEW GLOBAL
30 test1 t1 <some-root-user> TRIGGER GLOBAL
31 test1 t1 <some-root-user> DELETE HISTORY GLOBAL
32 test1 t1 <some-root-user> SELECT GLOBAL
33 test1 t1 <some-root-user> INSERT GLOBAL
34 test1 t1 <some-root-user> UPDATE GLOBAL
35 test1 t1 <some-root-user> DELETE GLOBAL
36 test1 t1 <some-root-user> CREATE GLOBAL
37 test1 t1 <some-root-user> DROP GLOBAL
38 test1 t1 <some-root-user> REFERENCES GLOBAL
39 test1 t1 <some-root-user> INDEX GLOBAL
40 test1 t1 <some-root-user> ALTER GLOBAL
41 test1 t1 <some-root-user> SHOW VIEW GLOBAL
42 test1 t1 <some-root-user> TRIGGER GLOBAL
43 test1 t1 <some-root-user> DELETE HISTORY GLOBAL
44 test1 t1 <some-root-user> SELECT GLOBAL
45 test1 t1 <some-root-user> INSERT GLOBAL
46 test1 t1 <some-root-user> UPDATE GLOBAL
47 test1 t1 <some-root-user> DELETE GLOBAL
48 test1 t1 <some-root-user> CREATE GLOBAL
49 test1 t1 <some-root-user> DROP GLOBAL
50 test1 t1 <some-root-user> REFERENCES GLOBAL
51 test1 t1 <some-root-user> INDEX GLOBAL
52 test1 t1 <some-root-user> ALTER GLOBAL
53 test1 t1 <some-root-user> SHOW VIEW GLOBAL
54 test1 t1 <some-root-user> TRIGGER GLOBAL
55 test1 t1 <some-root-user> DELETE HISTORY GLOBAL
56 test1 t1 <some-root-user> SELECT GLOBAL
57 test1 t1 <some-root-user> INSERT GLOBAL
58 test1 t1 <some-root-user> UPDATE GLOBAL
59 test1 t1 <some-root-user> DELETE GLOBAL
60 test1 t1 <some-root-user> CREATE GLOBAL
61 test1 t1 <some-root-user> DROP GLOBAL
62 test1 t1 <some-root-user> REFERENCES GLOBAL
63 test1 t1 <some-root-user> INDEX GLOBAL
64 test1 t1 <some-root-user> ALTER GLOBAL
65 test1 t1 <some-root-user> SHOW VIEW GLOBAL
66 test1 t1 <some-root-user> TRIGGER GLOBAL
67 test1 t1 <some-root-user> DELETE HISTORY GLOBAL
68 test1 t1 'test1_user'@'%' SELECT SCHEMA
69 test1 t1 'test1_user'@'%' INSERT SCHEMA
70 test1 t1 'test1_user'@'%' UPDATE SCHEMA
71 test1 t1 'test1_user'@'%' DELETE SCHEMA
72 test1 t1 'test1_user'@'%' CREATE SCHEMA
73 test1 t1 'test1_user'@'%' DROP SCHEMA
74 test1 t1 'test1_user'@'%' REFERENCES SCHEMA
75 test1 t1 'test1_user'@'%' INDEX SCHEMA
76 test1 t1 'test1_user'@'%' ALTER SCHEMA
77 test1 t1 'test1_user'@'%' SHOW VIEW SCHEMA
78 test1 t1 'test1_user'@'%' TRIGGER SCHEMA
79 test1 t1 'test1_user'@'%' DELETE HISTORY SCHEMA
80 test1 t1 'test1_user'@'%' SELECT TABLE
81 test1 t1 'test1_user'@'%' INSERT TABLE
82 test1 t1 'test1_user'@'%' UPDATE TABLE
83 test1 t1 'test1_user'@'%' DELETE TABLE
84 test1 t1 'test1_user'@'%' CREATE TABLE
85 test1 t1 'test1_user'@'%' DROP TABLE
86 test1 t1 'test1_user'@'%' REFERENCES TABLE
87 test1 t1 'test1_user'@'%' INDEX TABLE
88 test1 t1 'test1_user'@'%' ALTER TABLE
89 test1 t1 'test1_user'@'%' SHOW VIEW TABLE
90 test1 t1 'test1_user'@'%' TRIGGER TABLE
91 test1 t1 'test1_user'@'%' DELETE HISTORY TABLE
92 # Revoke some table privileges
93 REVOKE REFERENCES, DELETE, ALTER, DROP ON t1 FROM test1_user;
94 SELECT table_schema, table_name,
95 CASE WHEN grantee LIKE '\'root%' THEN '<some-root-user>' ELSE GRANTEE END,
96 privilege, level
97 FROM sys.privileges_by_table_by_level WHERE table_name='t1';
98 table_schema table_name CASE WHEN grantee LIKE '\'root%' THEN '<some-root-user>' ELSE GRANTEE END privilege level
99 test1 t1 <some-root-user> SELECT GLOBAL
100 test1 t1 <some-root-user> INSERT GLOBAL
101 test1 t1 <some-root-user> UPDATE GLOBAL
102 test1 t1 <some-root-user> DELETE GLOBAL
103 test1 t1 <some-root-user> CREATE GLOBAL
104 test1 t1 <some-root-user> DROP GLOBAL
105 test1 t1 <some-root-user> REFERENCES GLOBAL
106 test1 t1 <some-root-user> INDEX GLOBAL
107 test1 t1 <some-root-user> ALTER GLOBAL
108 test1 t1 <some-root-user> SHOW VIEW GLOBAL
109 test1 t1 <some-root-user> TRIGGER GLOBAL
110 test1 t1 <some-root-user> DELETE HISTORY GLOBAL
111 test1 t1 <some-root-user> SELECT GLOBAL
112 test1 t1 <some-root-user> INSERT GLOBAL
113 test1 t1 <some-root-user> UPDATE GLOBAL
114 test1 t1 <some-root-user> DELETE GLOBAL
115 test1 t1 <some-root-user> CREATE GLOBAL
116 test1 t1 <some-root-user> DROP GLOBAL
117 test1 t1 <some-root-user> REFERENCES GLOBAL
118 test1 t1 <some-root-user> INDEX GLOBAL
119 test1 t1 <some-root-user> ALTER GLOBAL
120 test1 t1 <some-root-user> SHOW VIEW GLOBAL
121 test1 t1 <some-root-user> TRIGGER GLOBAL
122 test1 t1 <some-root-user> DELETE HISTORY GLOBAL
123 test1 t1 <some-root-user> SELECT GLOBAL
124 test1 t1 <some-root-user> INSERT GLOBAL
125 test1 t1 <some-root-user> UPDATE GLOBAL
126 test1 t1 <some-root-user> DELETE GLOBAL
127 test1 t1 <some-root-user> CREATE GLOBAL
128 test1 t1 <some-root-user> DROP GLOBAL
129 test1 t1 <some-root-user> REFERENCES GLOBAL
130 test1 t1 <some-root-user> INDEX GLOBAL
131 test1 t1 <some-root-user> ALTER GLOBAL
132 test1 t1 <some-root-user> SHOW VIEW GLOBAL
133 test1 t1 <some-root-user> TRIGGER GLOBAL
134 test1 t1 <some-root-user> DELETE HISTORY GLOBAL
135 test1 t1 <some-root-user> SELECT GLOBAL
136 test1 t1 <some-root-user> INSERT GLOBAL
137 test1 t1 <some-root-user> UPDATE GLOBAL
138 test1 t1 <some-root-user> DELETE GLOBAL
139 test1 t1 <some-root-user> CREATE GLOBAL
140 test1 t1 <some-root-user> DROP GLOBAL
141 test1 t1 <some-root-user> REFERENCES GLOBAL
142 test1 t1 <some-root-user> INDEX GLOBAL
143 test1 t1 <some-root-user> ALTER GLOBAL
144 test1 t1 <some-root-user> SHOW VIEW GLOBAL
145 test1 t1 <some-root-user> TRIGGER GLOBAL
146 test1 t1 <some-root-user> DELETE HISTORY GLOBAL
147 test1 t1 'test1_user'@'%' SELECT SCHEMA
148 test1 t1 'test1_user'@'%' INSERT SCHEMA
149 test1 t1 'test1_user'@'%' UPDATE SCHEMA
150 test1 t1 'test1_user'@'%' DELETE SCHEMA
151 test1 t1 'test1_user'@'%' CREATE SCHEMA
152 test1 t1 'test1_user'@'%' DROP SCHEMA
153 test1 t1 'test1_user'@'%' REFERENCES SCHEMA
154 test1 t1 'test1_user'@'%' INDEX SCHEMA
155 test1 t1 'test1_user'@'%' ALTER SCHEMA
156 test1 t1 'test1_user'@'%' SHOW VIEW SCHEMA
157 test1 t1 'test1_user'@'%' TRIGGER SCHEMA
158 test1 t1 'test1_user'@'%' DELETE HISTORY SCHEMA
159 test1 t1 'test1_user'@'%' SELECT TABLE
160 test1 t1 'test1_user'@'%' INSERT TABLE
161 test1 t1 'test1_user'@'%' UPDATE TABLE
162 test1 t1 'test1_user'@'%' CREATE TABLE
163 test1 t1 'test1_user'@'%' INDEX TABLE
164 test1 t1 'test1_user'@'%' SHOW VIEW TABLE
165 test1 t1 'test1_user'@'%' TRIGGER TABLE
166 test1 t1 'test1_user'@'%' DELETE HISTORY TABLE
167 CREATE ROLE test1_role;
168 GRANT SELECT, UPDATE, DELETE, DROP, INDEX ON t1 to test1_role;
169 # Must show both the user and the role
170 SELECT * FROM sys.privileges_by_table_by_level
171 WHERE table_schema='test1' AND table_name='t1' AND grantee LIKE '%test1%';
172 TABLE_SCHEMA TABLE_NAME GRANTEE PRIVILEGE LEVEL
173 test1 t1 'test1_user'@'%' SELECT SCHEMA
174 test1 t1 'test1_user'@'%' INSERT SCHEMA
175 test1 t1 'test1_user'@'%' UPDATE SCHEMA
176 test1 t1 'test1_user'@'%' DELETE SCHEMA
177 test1 t1 'test1_user'@'%' CREATE SCHEMA
178 test1 t1 'test1_user'@'%' DROP SCHEMA
179 test1 t1 'test1_user'@'%' REFERENCES SCHEMA
180 test1 t1 'test1_user'@'%' INDEX SCHEMA
181 test1 t1 'test1_user'@'%' ALTER SCHEMA
182 test1 t1 'test1_user'@'%' SHOW VIEW SCHEMA
183 test1 t1 'test1_user'@'%' TRIGGER SCHEMA
184 test1 t1 'test1_user'@'%' DELETE HISTORY SCHEMA
185 test1 t1 'test1_user'@'%' SELECT TABLE
186 test1 t1 'test1_user'@'%' INSERT TABLE
187 test1 t1 'test1_user'@'%' UPDATE TABLE
188 test1 t1 'test1_user'@'%' CREATE TABLE
189 test1 t1 'test1_user'@'%' INDEX TABLE
190 test1 t1 'test1_user'@'%' SHOW VIEW TABLE
191 test1 t1 'test1_user'@'%' TRIGGER TABLE
192 test1 t1 'test1_user'@'%' DELETE HISTORY TABLE
193 test1 t1 'test1_role'@'' SELECT TABLE
194 test1 t1 'test1_role'@'' UPDATE TABLE
195 test1 t1 'test1_role'@'' DELETE TABLE
196 test1 t1 'test1_role'@'' DROP TABLE
197 test1 t1 'test1_role'@'' INDEX TABLE
198 CREATE VIEW v1 AS SELECT * FROM t1;
199 SELECT * FROM sys.privileges_by_table_by_level
200 WHERE table_schema='test1' AND table_name='v1' AND grantee LIKE '%test1%';
201 TABLE_SCHEMA TABLE_NAME GRANTEE PRIVILEGE LEVEL
202 test1 v1 'test1_user'@'%' SELECT SCHEMA
203 test1 v1 'test1_user'@'%' INSERT SCHEMA
204 test1 v1 'test1_user'@'%' UPDATE SCHEMA
205 test1 v1 'test1_user'@'%' DELETE SCHEMA
206 test1 v1 'test1_user'@'%' CREATE SCHEMA
207 test1 v1 'test1_user'@'%' DROP SCHEMA
208 test1 v1 'test1_user'@'%' REFERENCES SCHEMA
209 test1 v1 'test1_user'@'%' INDEX SCHEMA
210 test1 v1 'test1_user'@'%' ALTER SCHEMA
211 test1 v1 'test1_user'@'%' SHOW VIEW SCHEMA
212 test1 v1 'test1_user'@'%' TRIGGER SCHEMA
213 test1 v1 'test1_user'@'%' DELETE HISTORY SCHEMA
214 GRANT SELECT ON v1 TO test1_role;
215 SELECT * FROM sys.privileges_by_table_by_level
216 WHERE table_schema='test1' AND table_name='v1' AND grantee LIKE '%test1%';
217 TABLE_SCHEMA TABLE_NAME GRANTEE PRIVILEGE LEVEL
218 test1 v1 'test1_user'@'%' SELECT SCHEMA
219 test1 v1 'test1_user'@'%' INSERT SCHEMA
220 test1 v1 'test1_user'@'%' UPDATE SCHEMA
221 test1 v1 'test1_user'@'%' DELETE SCHEMA
222 test1 v1 'test1_user'@'%' CREATE SCHEMA
223 test1 v1 'test1_user'@'%' DROP SCHEMA
224 test1 v1 'test1_user'@'%' REFERENCES SCHEMA
225 test1 v1 'test1_user'@'%' INDEX SCHEMA
226 test1 v1 'test1_user'@'%' ALTER SCHEMA
227 test1 v1 'test1_user'@'%' SHOW VIEW SCHEMA
228 test1 v1 'test1_user'@'%' TRIGGER SCHEMA
229 test1 v1 'test1_user'@'%' DELETE HISTORY SCHEMA
230 test1 v1 'test1_role'@'' SELECT TABLE
231 GRANT ALL ON v1 TO test1_user;
232 SELECT * FROM sys.privileges_by_table_by_level
233 WHERE table_schema='test1' AND table_name='v1' AND grantee LIKE '%test1%';
234 TABLE_SCHEMA TABLE_NAME GRANTEE PRIVILEGE LEVEL
235 test1 v1 'test1_user'@'%' SELECT SCHEMA
236 test1 v1 'test1_user'@'%' INSERT SCHEMA
237 test1 v1 'test1_user'@'%' UPDATE SCHEMA
238 test1 v1 'test1_user'@'%' DELETE SCHEMA
239 test1 v1 'test1_user'@'%' CREATE SCHEMA
240 test1 v1 'test1_user'@'%' DROP SCHEMA
241 test1 v1 'test1_user'@'%' REFERENCES SCHEMA
242 test1 v1 'test1_user'@'%' INDEX SCHEMA
243 test1 v1 'test1_user'@'%' ALTER SCHEMA
244 test1 v1 'test1_user'@'%' SHOW VIEW SCHEMA
245 test1 v1 'test1_user'@'%' TRIGGER SCHEMA
246 test1 v1 'test1_user'@'%' DELETE HISTORY SCHEMA
247 test1 v1 'test1_user'@'%' SELECT TABLE
248 test1 v1 'test1_user'@'%' INSERT TABLE
249 test1 v1 'test1_user'@'%' UPDATE TABLE
250 test1 v1 'test1_user'@'%' DELETE TABLE
251 test1 v1 'test1_user'@'%' CREATE TABLE
252 test1 v1 'test1_user'@'%' DROP TABLE
253 test1 v1 'test1_user'@'%' REFERENCES TABLE
254 test1 v1 'test1_user'@'%' INDEX TABLE
255 test1 v1 'test1_user'@'%' ALTER TABLE
256 test1 v1 'test1_user'@'%' SHOW VIEW TABLE
257 test1 v1 'test1_user'@'%' TRIGGER TABLE
258 test1 v1 'test1_user'@'%' DELETE HISTORY TABLE
259 test1 v1 'test1_role'@'' SELECT TABLE
260 DROP TABLE t1;
261 DROP VIEW v1;
262 DROP USER test1_user;
263 DROP ROLE test1_role;
264 DROP DATABASE test1;