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
|
SET NAMES binary;
drop table if exists t1;
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
delete from mysql.tables_priv where user like 'mysqltest\_%';
delete from mysql.columns_priv where user like 'mysqltest\_%';
flush privileges;
create user mysqltest_1@localhost;
grant create user on *.* to mysqltest_1@localhost;
grant select on `my\_1`.* to mysqltest_1@localhost with grant option;
grant select on `my\_1`.* to mysqltest_2@localhost;
ERROR 42000: You are not allowed to create a user with GRANT
create user mysqltest_2@localhost;
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
delete from mysql.tables_priv where user like 'mysqltest\_%';
delete from mysql.columns_priv where user like 'mysqltest\_%';
flush privileges;
grant select on test.* to CUser@localhost;
grant select on test.* to CUser@LOCALHOST;
flush privileges;
SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2;
user host
CUser LOCALHOST
CUser localhost
SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser' order by 1,2;
user host db select_priv
CUser LOCALHOST test Y
CUser localhost test Y
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'LOCALHOST';
flush privileges;
SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2;
user host
CUser LOCALHOST
CUser localhost
SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser' order by 1,2;
user host db select_priv
CUser localhost test Y
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'localhost';
flush privileges;
SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2;
user host
CUser LOCALHOST
CUser localhost
SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser' order by 1,2;
user host db select_priv
DROP USER CUser@localhost;
DROP USER CUser@LOCALHOST;
create table t1 (a int);
grant select on test.t1 to CUser@localhost;
grant select on test.t1 to CUser@LOCALHOST;
flush privileges;
SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2;
user host
CUser LOCALHOST
CUser localhost
SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2;
user host db Table_name Table_priv Column_priv
CUser LOCALHOST test t1 Select
CUser localhost test t1 Select
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'LOCALHOST';
flush privileges;
SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2;
user host
CUser LOCALHOST
CUser localhost
SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2;
user host db Table_name Table_priv Column_priv
CUser localhost test t1 Select
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'localhost';
flush privileges;
SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2;
user host
CUser LOCALHOST
CUser localhost
SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2;
user host db Table_name Table_priv Column_priv
DROP USER CUser@localhost;
DROP USER CUser@LOCALHOST;
grant select(a) on test.t1 to CUser@localhost;
grant select(a) on test.t1 to CUser@LOCALHOST;
flush privileges;
SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2;
user host
CUser LOCALHOST
CUser localhost
SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2;
user host db Table_name Table_priv Column_priv
CUser LOCALHOST test t1 Select
CUser localhost test t1 Select
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'LOCALHOST';
flush privileges;
SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2;
user host
CUser LOCALHOST
CUser localhost
SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2;
user host db Table_name Table_priv Column_priv
CUser localhost test t1 Select
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'localhost';
flush privileges;
SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2;
user host
CUser LOCALHOST
CUser localhost
SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2;
user host db Table_name Table_priv Column_priv
DROP USER CUser@localhost;
DROP USER CUser@LOCALHOST;
drop table t1;
grant select on test.* to CUser2@localhost;
grant select on test.* to CUser2@LOCALHOST;
flush privileges;
SELECT user, host FROM mysql.user where user = 'CUser2' order by 1,2;
user host
CUser2 LOCALHOST
CUser2 localhost
SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser2' order by 1,2;
user host db select_priv
CUser2 LOCALHOST test Y
CUser2 localhost test Y
REVOKE SELECT ON test.* FROM 'CUser2'@'LOCALHOST';
flush privileges;
SELECT user, host FROM mysql.user where user = 'CUser2' order by 1,2;
user host
CUser2 LOCALHOST
CUser2 localhost
SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser2' order by 1,2;
user host db select_priv
CUser2 localhost test Y
REVOKE SELECT ON test.* FROM 'CUser2'@'localhost';
flush privileges;
SELECT user, host FROM mysql.user where user = 'CUser2' order by 1,2;
user host
CUser2 LOCALHOST
CUser2 localhost
SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser2' order by 1,2;
user host db select_priv
DROP USER CUser2@localhost;
DROP USER CUser2@LOCALHOST;
CREATE DATABASE mysqltest_1;
CREATE TABLE mysqltest_1.t1 (a INT);
CREATE USER 'mysqltest1'@'%';
GRANT SELECT, UPDATE ON `mysqltest_1`.* TO 'mysqltest1'@'%';
REVOKE SELECT ON `mysqltest_1`.* FROM 'mysqltest1'@'%';
GRANT SELECT, UPDATE ON `mysqltest\_1`.* TO 'mysqltest1'@'%';
FLUSH PRIVILEGES;
SHOW GRANTS;
Grants for mysqltest1@%
GRANT USAGE ON *.* TO 'mysqltest1'@'%'
GRANT SELECT, UPDATE ON `mysqltest\_1`.* TO 'mysqltest1'@'%'
GRANT UPDATE ON `mysqltest_1`.* TO 'mysqltest1'@'%'
SELECT * FROM mysqltest_1.t1;
a
DROP USER 'mysqltest1'@'%';
DROP DATABASE mysqltest_1;
End of 5.0 tests
|
