File: v_privileges_by_table_by_level.result

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