summaryrefslogtreecommitdiff
path: root/mysql-test/t/grant3.test
blob: fac577ef0ff4605714f4e84adf9450e43ba0f0bb (plain)
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
# Can't run with embedded server
-- source include/not_embedded.inc

# Test of GRANT commands

SET NAMES binary;
connect (master,localhost,root,,);
connection master;

# Cleanup
--disable_warnings
drop table if exists t1;
--enable_warnings

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;
connect (user_a,localhost,mysqltest_1,,);
connection user_a;
--error 1410
grant select on `my\_1`.* to mysqltest_2@localhost;
create user mysqltest_2@localhost;
disconnect user_a;
connection default;

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;

#
# Bug: #19828 Case sensitivity in Grant/Revoke
#

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;
SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser' order by 1,2;

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'LOCALHOST';
flush privileges;

SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2;
SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser' order by 1,2;

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'localhost';
flush privileges;

SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2;
SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser' order by 1,2;

DROP USER CUser@localhost;
DROP USER CUser@LOCALHOST;

#### table grants
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;
SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2;

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'LOCALHOST';
flush privileges;

SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2;
SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2;

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'localhost';
flush privileges;

SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2;
SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2;

DROP USER CUser@localhost;
DROP USER CUser@LOCALHOST;

### column grants

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;
SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2;

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'LOCALHOST';
flush privileges;

SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2;
SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2;

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'localhost';
flush privileges;

SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2;
SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2;

DROP USER CUser@localhost;
DROP USER CUser@LOCALHOST;

drop table t1;

# revoke on a specific DB only

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;
SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser2' order by 1,2;

REVOKE SELECT ON test.* FROM 'CUser2'@'LOCALHOST';
flush privileges;

SELECT user, host FROM mysql.user where user = 'CUser2' order by 1,2;
SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser2' order by 1,2;

REVOKE SELECT ON test.* FROM 'CUser2'@'localhost';
flush privileges;

SELECT user, host FROM mysql.user where user = 'CUser2' order by 1,2;
SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser2' order by 1,2;

DROP USER CUser2@localhost;
DROP USER CUser2@LOCALHOST;