summaryrefslogtreecommitdiff
path: root/mysql-test/t/grant4.test
blob: 2715b7c714562e6008ac665825e0c77dff4e429d (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
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
--source include/not_embedded.inc

# Setup database, tables and user accounts
--disable_warnings
drop database if exists mysqltest_db1;
--enable_warnings
set GLOBAL sql_mode="";
set LOCAL sql_mode="";
create database mysqltest_db1;
use mysqltest_db1;
create table t_column_priv_only (a int, b int);
create table t_select_priv like t_column_priv_only;
create table t_no_priv like t_column_priv_only;
grant all privileges on test.* to mysqltest_u1@localhost;
grant insert (a) on mysqltest_db1.t_column_priv_only to mysqltest_u1@localhost;
grant select on mysqltest_db1.t_select_priv to mysqltest_u1@localhost;

--echo ** Connect as restricted user mysqltest_u1.
--echo
connect (con1,localhost,mysqltest_u1,,);
connection con1;

########################################################################
--echo ** Test column level privileges only. No SELECT privileges on the table.
--echo ** INSERT INTO ... VALUES ...
--echo ** Attempting to insert values to a table with only column privileges
--echo ** should work.
insert into mysqltest_db1.t_column_priv_only (a) VALUES (1);
--echo

#########################################################################
--echo ** SHOW COLUMNS
--echo ** Should succeed because we have privileges (any) on at least one of the columns.
select column_name as 'Field',column_type as 'Type',is_nullable as 'Null',column_key as 'Key',column_default as 'Default',extra as 'Extra' from information_schema.columns where table_schema='mysqltest_db1' and table_name='t_column_priv_only';
show columns from mysqltest_db1.t_column_priv_only;
#########################################################################
--echo ** SHOW COLUMNS
--echo ** Should fail because there are no privileges on any column combination.
--error 1142
show columns from mysqltest_db1.t_no_priv;
--echo ** However, select from I_S.COLUMNS will succeed but not show anything:
select column_name as 'Field',column_type as 'Type',is_nullable as 'Null',column_key as 'Key',column_default as 'Default',extra as 'Extra' from information_schema.columns where table_schema='mysqltest_db1' and table_name='t_no_priv';
--echo 
#########################################################################
--echo ** CREATE TABLE ... LIKE ... require SELECT privleges and will fail.
--error 1142
create table test.t_no_priv like mysqltest_db1.column_priv_only;
--echo  
#########################################################################
--echo ** Just to be sure... SELECT also fails.
--error 1142
select * from mysqltest_db1.t_column_priv_only;
--echo 
#########################################################################
--echo ** SHOW CREATE TABLE ... require any privileges on all columns (the entire table).
--echo ** First we try and fail on a table with only one column privilege.
--error 1142
show create table mysqltest_db1.t_column_priv_only;
--echo
#########################################################################
--echo ** Now we do the same on a table with SELECT privileges.
--echo
#########################################################################
--echo ** SHOW COLUMNS
--echo ** Success because we got some privileges on the table (SELECT_ACL)
show columns from mysqltest_db1.t_select_priv;
--echo
#########################################################################
--echo ** CREATE TABLE ... LIKE ... require SELECT privleges and will SUCCEED.
--disable_warnings
drop table if exists test.t_duplicated;
--enable_warnings
create table test.t_duplicated like mysqltest_db1.t_select_priv;
drop table test.t_duplicated;
--echo
#########################################################################
--echo ** SHOW CREATE TABLE will succeed because we have a privilege on all columns in the table (table-level privilege).
show create table mysqltest_db1.t_select_priv;
--echo
#########################################################################
--echo ** SHOW CREATE TABLE will fail if there is no grants at all: 
--error 1142
show create table mysqltest_db1.t_no_priv;
--echo

connection default;

#
# SHOW INDEX
#
use mysqltest_db1;
CREATE TABLE t5 (s1 INT);
CREATE INDEX i ON t5 (s1);
CREATE TABLE t6 (s1 INT, s2 INT);
CREATE VIEW v5 AS SELECT * FROM t5;
CREATE VIEW v6 AS SELECT * FROM t6;
CREATE VIEW v2 AS SELECT * FROM t_select_priv;
CREATE VIEW v3 AS SELECT * FROM t_select_priv;
CREATE INDEX i ON t6 (s1);
GRANT UPDATE (s2) ON t6 to mysqltest_u1@localhost;
GRANT UPDATE (s2) ON v6 to mysqltest_u1@localhost;
GRANT SHOW VIEW ON v2 to mysqltest_u1@localhost;
GRANT SHOW VIEW, SELECT ON v3 to mysqltest_u1@localhost;

connection con1;
use mysqltest_db1;
--echo ** Connect as restricted user mysqltest_u1.
--echo ** SELECT FROM INFORMATION_SCHEMA.STATISTICS will succeed because any privileges will do (authentication is enough).
--echo ** but will return no rows
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='t5';
#
# Bug27145 EXTRA_ACL trouble
#
--echo ** SHOW INDEX FROM t5 will fail because we don't have any privileges on any column combination.
--error 1142
SHOW INDEX FROM t5;
--echo ** SHOW INDEX FROM t6 will succeed because there exist a privilege on a column combination on t6.
SHOW INDEX FROM t6;

# CHECK TABLE
--echo ** CHECK TABLE requires any privilege on any column combination and should succeed for t6:
CHECK TABLE t6;
--echo ** With no privileges access is naturally denied:
--error 1142
CHECK TABLE t5;

# CHECKSUM
--echo ** CHECKSUM TABLE requires SELECT privileges on the table. The following should fail:
--error 1142
CHECKSUM TABLE t6;
--echo ** And this should work:
CHECKSUM TABLE t_select_priv;

# SHOW CREATE VIEW
--error 1142
SHOW CREATE VIEW v5;
--error 1142
SHOW CREATE VIEW v6;
--error 1142
SHOW CREATE VIEW v2;
SHOW CREATE VIEW v3;

connection default;
disconnect con1;
drop database mysqltest_db1;
drop user mysqltest_u1@localhost;


--echo #
--echo # Additional coverage for refactoring which is made as part
--echo # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege
--echo # to allow temp table operations".
--echo # 
--echo # Check that for statements like CHECK/REPAIR and OPTIMIZE TABLE
--echo # privileges for all tables involved are checked before processing
--echo # any tables. Doing otherwise, i.e. checking privileges for table
--echo # right before processing it might result in lost results for tables
--echo # which were processed by the time when table for which privileges
--echo # are insufficient are discovered.
--echo #
call mtr.add_suppression("Got an error from thread_id=.*ha_myisam.cc:");
call mtr.add_suppression("MySQL thread id .*, query id .* localhost.*mysqltest_u1 Checking table");
--disable_warnings
drop database if exists mysqltest_db1;
--enable_warnings
let $MYSQLD_DATADIR = `SELECT @@datadir`;
create database mysqltest_db1;
--echo # Create tables which we are going to CHECK/REPAIR.
create table mysqltest_db1.t1 (a int, key(a)) engine=myisam;
create table mysqltest_db1.t2 (b int);
insert into mysqltest_db1.t1 values (1), (2);
insert into mysqltest_db1.t2 values (1);
--echo # Create user which will try to do this.
create user mysqltest_u1@localhost;
grant insert, select on mysqltest_db1.t1 to mysqltest_u1@localhost;
connect (con1,localhost,mysqltest_u1,,);
connection default;

--echo # Corrupt t1 by replacing t1.MYI with a corrupt + unclosed one created
--echo # by doing: 'create table t1 (a int key(a))'
--echo #           head -c1024 t1.MYI > corrupt_t1.MYI 
flush table mysqltest_db1.t1;
--remove_file $MYSQLD_DATADIR/mysqltest_db1/t1.MYI
--copy_file std_data/corrupt_t1.MYI $MYSQLD_DATADIR/mysqltest_db1/t1.MYI

connection con1;
check table mysqltest_db1.t1;
--echo # The below statement should fail before repairing t1.
--echo # Otherwise info about such repair will be missing from its result-set.
--error ER_TABLEACCESS_DENIED_ERROR
repair table mysqltest_db1.t1, mysqltest_db1.t2;
--echo # The same is true for CHECK TABLE statement.
--error ER_TABLEACCESS_DENIED_ERROR
check table mysqltest_db1.t1, mysqltest_db1.t2;
check table mysqltest_db1.t1;
repair table mysqltest_db1.t1;

--echo # Clean-up.
disconnect con1;
--source include/wait_until_disconnected.inc
connection default;
drop database mysqltest_db1;
drop user mysqltest_u1@localhost;

create user foo1 identified by password '11111111111111111111111111111111111111111';
create user foo2 identified by password '2222222222222222';
create user foo3 identified via mysql_native_password using '11111111111111111111111111111111111111111';
create user foo4 identified via mysql_old_password using '2222222222222222';

grant select on test.* to foo5 identified by password '11111111111111111111111111111111111111111';
grant select on test.* to foo6 identified by password '2222222222222222';
grant select on test.* to foo7 identified via mysql_native_password using '11111111111111111111111111111111111111111';
grant select on test.* to foo8 identified via mysql_old_password using '2222222222222222';

--sorted_result
select user,password,plugin,authentication_string from mysql.user where user like 'foo%';

drop user foo1;
drop user foo2;
drop user foo3;
drop user foo4;
drop user foo5;
drop user foo6;
drop user foo7;
drop user foo8;

--error ER_PASSWD_LENGTH
create user foo1 identified via mysql_native_password using '00';
--error ER_PASSWD_LENGTH
create user foo2 identified via mysql_native_password using '2222222222222222';
--error ER_PASSWD_LENGTH
create user foo3 identified via mysql_old_password using '00';
--error ER_PASSWD_LENGTH
create user foo4 identified via mysql_old_password using '11111111111111111111111111111111111111111';


set GLOBAL sql_mode=default;