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
|
create user foo;
create role bar;
#
# Test deny format for global privileges.
#
deny select on *.* to foo;
deny insert on *.* to bar;
select user, host, JSON_EXTRACT(priv, '$.deny') from mysql.global_priv where user = 'foo';
user host JSON_EXTRACT(priv, '$.deny')
foo % {"global": 1, "version_id": VERSION_ID}
select user, host, JSON_EXTRACT(priv, '$.deny') from mysql.global_priv where user = 'bar';
user host JSON_EXTRACT(priv, '$.deny')
bar {"global": 2, "version_id": VERSION_ID}
flush privileges;
deny select on some_db.* to foo;
deny show view on some_db.* to foo;
deny insert on some_other_db.* to foo;
deny insert on some_other_db.* to bar;
deny select on some_other_db.some_table to foo;
deny insert on some_other_db.some_table_second to foo;
deny update on some_other_db.some_table_third to foo;
deny select(a, b) on some_other_db.some_table_third to foo;
deny select, insert(a, b, c) on some_other_db.some_table_fourth to foo;
select user, host, JSON_DETAILED(JSON_EXTRACT(priv, '$.deny')) from mysql.global_priv where user = 'foo';
user host JSON_DETAILED(JSON_EXTRACT(priv, '$.deny'))
foo % {
"global": 1,
"db":
[
{
"name": "`some_db`",
"access": 4194305
},
{
"name": "`some_other_db`",
"access": 2
}
],
"table":
[
{
"name": "`some_other_db`.`some_table_second`",
"access": 2
},
{
"name": "`some_other_db`.`some_table`",
"access": 1
},
{
"name": "`some_other_db`.`some_table_third`",
"access": 4
},
{
"name": "`some_other_db`.`some_table_fourth`",
"access": 1
}
],
"column":
[
{
"name": "`some_other_db`.`some_table_third`.`a`",
"access": 1
},
{
"name": "`some_other_db`.`some_table_fourth`.`b`",
"access": 2
},
{
"name": "`some_other_db`.`some_table_fourth`.`c`",
"access": 2
},
{
"name": "`some_other_db`.`some_table_third`.`b`",
"access": 1
},
{
"name": "`some_other_db`.`some_table_fourth`.`a`",
"access": 2
}
],
"version_id": VERSION_ID
}
select user, host, JSON_DETAILED(JSON_EXTRACT(priv, '$.deny')) from mysql.global_priv where user = 'bar';
user host JSON_DETAILED(JSON_EXTRACT(priv, '$.deny'))
bar {
"global": 2,
"db":
[
{
"name": "`some_other_db`",
"access": 2
}
],
"version_id": VERSION_ID
}
drop user foo;
drop role bar;
|
