111
114
--disable_warnings
112
115
create database mysqltest;
113
116
--enable_warnings
114
#prepare views and tables
117
# prepare views and tables
115
118
create table mysqltest.t1 (a int, b int);
116
119
create table mysqltest.t2 (a int, b int);
117
120
create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
118
121
create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1;
119
122
create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2;
120
123
create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2;
124
# v5: SHOW VIEW, but no SELECT
125
create view mysqltest.v5 (c,d) as select a+1,b+1 from mysqltest.t1;
121
126
grant select on mysqltest.v1 to mysqltest_1@localhost;
122
127
grant select on mysqltest.v2 to mysqltest_1@localhost;
123
128
grant select on mysqltest.v3 to mysqltest_1@localhost;
124
129
grant select on mysqltest.v4 to mysqltest_1@localhost;
130
grant show view on mysqltest.v5 to mysqltest_1@localhost;
126
132
connection user1;
133
# all SELECTs works, except v5 which lacks SELECT privs
128
134
select c from mysqltest.v1;
129
135
select c from mysqltest.v2;
130
136
select c from mysqltest.v3;
131
137
select c from mysqltest.v4;
138
--error ER_TABLEACCESS_DENIED_ERROR
139
select c from mysqltest.v5;
132
140
# test of show coluns
133
141
show columns from mysqltest.v1;
134
142
show columns from mysqltest.v2;
135
# but explain/show do not
144
--error ER_VIEW_NO_EXPLAIN
136
145
explain select c from mysqltest.v1;
146
--error ER_TABLEACCESS_DENIED_ERROR
137
147
show create view mysqltest.v1;
148
--error ER_VIEW_NO_EXPLAIN
138
149
explain select c from mysqltest.v2;
150
--error ER_TABLEACCESS_DENIED_ERROR
139
151
show create view mysqltest.v2;
152
--error ER_VIEW_NO_EXPLAIN
140
153
explain select c from mysqltest.v3;
154
--error ER_TABLEACCESS_DENIED_ERROR
141
155
show create view mysqltest.v3;
156
--error ER_VIEW_NO_EXPLAIN
142
157
explain select c from mysqltest.v4;
158
--error ER_TABLEACCESS_DENIED_ERROR
143
159
show create view mysqltest.v4;
160
--error ER_TABLEACCESS_DENIED_ERROR
161
explain select c from mysqltest.v5;
162
show create view mysqltest.v5;
164
# missing SELECT on underlying t1, no SHOW VIEW on v1 either.
165
--error ER_VIEW_NO_EXPLAIN
166
explain select c from mysqltest.v1;
168
--error ER_TABLEACCESS_DENIED_ERROR
169
show create view mysqltest.v1;
145
170
# allow to see one of underlying table
172
grant show view on mysqltest.v1 to mysqltest_1@localhost;
147
173
grant select on mysqltest.t1 to mysqltest_1@localhost;
148
174
connection user1;
149
# EXPLAIN of view on above table works
150
176
explain select c from mysqltest.v1;
151
177
show create view mysqltest.v1;
179
--error ER_VIEW_NO_EXPLAIN
152
180
explain select c from mysqltest.v2;
181
--error ER_TABLEACCESS_DENIED_ERROR
153
182
show create view mysqltest.v2;
154
183
# but other EXPLAINs do not
184
--error ER_VIEW_NO_EXPLAIN
155
185
explain select c from mysqltest.v3;
186
--error ER_TABLEACCESS_DENIED_ERROR
156
187
show create view mysqltest.v3;
188
--error ER_VIEW_NO_EXPLAIN
157
189
explain select c from mysqltest.v4;
190
--error ER_TABLEACCESS_DENIED_ERROR
158
191
show create view mysqltest.v4;
192
# we have SHOW VIEW on v5, and SELECT on t1 -- not enough
193
--error ER_TABLEACCESS_DENIED_ERROR
194
explain select c from mysqltest.v5;
195
# we can SHOW CREATE VIEW though
196
show create view mysqltest.v5;
160
198
# allow to see any view in mysqltest database
1213
disconnect bug24040_con;
1182
1215
DROP DATABASE mysqltest1;
1183
1216
DROP DATABASE mysqltest2;
1184
1217
DROP USER mysqltest_u1@localhost;
1221
# Bug#41354 Access control is bypassed when all columns of a view are
1222
# selected by * wildcard
1224
CREATE DATABASE db1;
1226
CREATE TABLE t1(f1 INT, f2 INT);
1227
CREATE VIEW v1 AS SELECT f1, f2 FROM t1;
1229
GRANT SELECT (f1) ON t1 TO foo;
1230
GRANT SELECT (f1) ON v1 TO foo;
1232
connect (addconfoo, localhost, foo,,);
1233
connection addconfoo;
1237
--error ER_COLUMNACCESS_DENIED_ERROR
1239
--error ER_TABLEACCESS_DENIED_ERROR
1243
--error ER_COLUMNACCESS_DENIED_ERROR
1245
--error ER_TABLEACCESS_DENIED_ERROR
1250
disconnect addconfoo;
1252
REVOKE SELECT (f1) ON db1.t1 FROM foo;
1253
REVOKE SELECT (f1) ON db1.v1 FROM foo;
1262
--echo Bug #11765687/#58677:
1263
--echo No privilege on table/view, but can know #rows / underlying table's name
1265
# As a root-like user
1266
connect (root,localhost,root,,test);
1269
create database mysqltest1;
1270
create table mysqltest1.t1 (i int);
1271
create table mysqltest1.t2 (j int);
1272
create table mysqltest1.t3 (k int, secret int);
1274
create user alice@localhost;
1275
create user bob@localhost;
1276
create user cecil@localhost;
1277
create user dan@localhost;
1278
create user eugene@localhost;
1279
create user fiona@localhost;
1280
create user greg@localhost;
1281
create user han@localhost;
1282
create user inga@localhost;
1283
create user jamie@localhost;
1284
create user karl@localhost;
1285
create user lena@localhost;
1286
create user mhairi@localhost;
1287
create user noam@localhost;
1288
create user olga@localhost;
1289
create user pjotr@localhost;
1290
create user quintessa@localhost;
1292
grant all privileges on mysqltest1.* to alice@localhost with grant option;
1296
connect (test11765687,localhost,alice,,mysqltest1);
1297
connection test11765687;
1299
create view v1 as select * from t1;
1300
create view v2 as select * from v1, t2;
1301
create view v3 as select k from t3;
1303
grant select on mysqltest1.v1 to bob@localhost;
1305
grant show view on mysqltest1.v1 to cecil@localhost;
1307
grant select, show view on mysqltest1.v1 to dan@localhost;
1308
grant select on mysqltest1.t1 to dan@localhost;
1310
grant select on mysqltest1.* to eugene@localhost;
1312
grant select, show view on mysqltest1.v2 to fiona@localhost;
1314
grant select, show view on mysqltest1.v2 to greg@localhost;
1315
grant show view on mysqltest1.v1 to greg@localhost;
1317
grant select(k) on mysqltest1.t3 to han@localhost;
1318
grant select, show view on mysqltest1.v3 to han@localhost;
1320
grant select on mysqltest1.t1 to inga@localhost;
1321
grant select on mysqltest1.t2 to inga@localhost;
1322
grant select on mysqltest1.v1 to inga@localhost;
1323
grant select, show view on mysqltest1.v2 to inga@localhost;
1325
grant select on mysqltest1.t1 to jamie@localhost;
1326
grant select on mysqltest1.t2 to jamie@localhost;
1327
grant show view on mysqltest1.v1 to jamie@localhost;
1328
grant select, show view on mysqltest1.v2 to jamie@localhost;
1330
grant select on mysqltest1.t1 to karl@localhost;
1331
grant select on mysqltest1.t2 to karl@localhost;
1332
grant select, show view on mysqltest1.v1 to karl@localhost;
1333
grant select on mysqltest1.v2 to karl@localhost;
1335
grant select on mysqltest1.t1 to lena@localhost;
1336
grant select on mysqltest1.t2 to lena@localhost;
1337
grant select, show view on mysqltest1.v1 to lena@localhost;
1338
grant show view on mysqltest1.v2 to lena@localhost;
1340
grant select on mysqltest1.t1 to mhairi@localhost;
1341
grant select on mysqltest1.t2 to mhairi@localhost;
1342
grant select, show view on mysqltest1.v1 to mhairi@localhost;
1343
grant select, show view on mysqltest1.v2 to mhairi@localhost;
1345
grant select on mysqltest1.t1 to noam@localhost;
1346
grant select, show view on mysqltest1.v1 to noam@localhost;
1347
grant select, show view on mysqltest1.v2 to noam@localhost;
1349
grant select on mysqltest1.t2 to olga@localhost;
1350
grant select, show view on mysqltest1.v1 to olga@localhost;
1351
grant select, show view on mysqltest1.v2 to olga@localhost;
1353
grant select on mysqltest1.t1 to pjotr@localhost;
1354
grant select on mysqltest1.t2 to pjotr@localhost;
1355
grant select, show view on mysqltest1.v2 to pjotr@localhost;
1357
grant select, show view on mysqltest1.v1 to quintessa@localhost;
1359
disconnect test11765687;
1363
connect (test11765687,localhost,bob,,mysqltest1);
1364
connection test11765687;
1366
select * from v1; # Should succeed.
1367
--error ER_VIEW_NO_EXPLAIN
1368
explain select * from v1; # fail, no SHOW_VIEW
1370
disconnect test11765687;
1374
connect (test11765687,localhost,cecil,,mysqltest1);
1375
connection test11765687;
1377
--error ER_TABLEACCESS_DENIED_ERROR
1378
select * from v1; # fail, no SELECT
1379
--error ER_TABLEACCESS_DENIED_ERROR
1380
explain select * from v1; # fail, no SELECT
1382
disconnect test11765687;
1386
connect (test11765687,localhost,dan,,mysqltest1);
1387
connection test11765687;
1389
select * from v1; # Should succeed.
1390
explain select * from v1; # Should succeed.
1392
disconnect test11765687;
1395
--echo ... as eugene
1396
connect (test11765687,localhost,eugene,,mysqltest1);
1397
connection test11765687;
1399
select * from v1; # Should succeed.
1400
--error ER_VIEW_NO_EXPLAIN
1401
explain select * from v1; # fail, no SHOW_VIEW
1403
disconnect test11765687;
1407
connect (test11765687,localhost,fiona,,mysqltest1);
1408
connection test11765687;
1410
select * from v2; # Should succeed.
1411
show create view v2; # Should succeed, but...
1412
--error ER_TABLEACCESS_DENIED_ERROR
1413
explain select * from t1; # fail, shouldn't see t1!
1414
--error ER_TABLEACCESS_DENIED_ERROR
1415
# err msg must give view name, no table names!!
1416
explain select * from v1; # fail, have no privs on v1!
1417
--error ER_TABLEACCESS_DENIED_ERROR
1418
explain select * from t2; # fail, have no privs on t2!
1419
--error ER_VIEW_NO_EXPLAIN
1420
explain select * from v2; # fail, shouldn't see t2!
1422
disconnect test11765687;
1426
connect (test11765687,localhost,greg,,mysqltest1);
1427
connection test11765687;
1429
select * from v2; # Should succeed.
1430
--error ER_TABLEACCESS_DENIED_ERROR
1431
explain select * from v1; # fail; no SELECT on v1!
1432
--error ER_VIEW_NO_EXPLAIN
1433
explain select * from v2; # fail; no SELECT on v1!
1435
disconnect test11765687;
1439
connect (test11765687,localhost,han,,mysqltest1);
1440
connection test11765687;
1442
--error ER_TABLEACCESS_DENIED_ERROR
1443
select * from t3; # don't have privs on all columns,
1444
--error ER_TABLEACCESS_DENIED_ERROR
1445
explain select * from t3; # so EXPLAIN on "forbidden" columns should fail.
1446
select k from t3; # but we do have SELECT on column k though,
1447
explain select k from t3; # so EXPLAIN just on k should work,
1448
select * from v3; # and so should SELECT on view only using allowed columns
1449
explain select * from v3; # as should the associated EXPLAIN
1451
disconnect test11765687;
1455
connect (test11765687,localhost,inga,,mysqltest1);
1456
connection test11765687;
1459
# has sel/show on v2, sel on t1/t2, only sel v1
1460
# fail: lacks show on v1
1461
--error ER_VIEW_NO_EXPLAIN
1462
explain select * from v2;
1463
disconnect test11765687;
1467
connect (test11765687,localhost,jamie,,mysqltest1);
1468
connection test11765687;
1471
# has sel/show on v2, sel on t1/t2, only show v1
1472
# fail: lacks sel on v1
1473
--error ER_VIEW_NO_EXPLAIN
1474
explain select * from v2;
1475
disconnect test11765687;
1479
connect (test11765687,localhost,karl,,mysqltest1);
1480
connection test11765687;
1483
# has sel only on v2, sel on t1/t2, sel/show v1
1484
# fail: lacks show on v2
1485
--error ER_VIEW_NO_EXPLAIN
1486
explain select * from v2;
1487
disconnect test11765687;
1492
connect (test11765687,localhost,lena,,mysqltest1);
1493
connection test11765687;
1494
--error ER_TABLEACCESS_DENIED_ERROR
1496
# has show only on v2, sel on t1/t2, sel/show v1
1497
# fail: lacks sel on v2
1498
--error ER_TABLEACCESS_DENIED_ERROR
1499
explain select * from v2;
1500
disconnect test11765687;
1503
--echo ... as mhairi
1504
connect (test11765687,localhost,mhairi,,mysqltest1);
1505
connection test11765687;
1508
# has sel/show on v2, sel on t1/t2, sel/show v1
1509
explain select * from v2;
1510
disconnect test11765687;
1514
connect (test11765687,localhost,noam,,mysqltest1);
1515
connection test11765687;
1518
# has sel/show on v2, sel only on t1, sel/show v1 (no sel on t2!)
1519
--error ER_VIEW_NO_EXPLAIN
1520
explain select * from v2;
1521
disconnect test11765687;
1525
connect (test11765687,localhost,olga,,mysqltest1);
1526
connection test11765687;
1529
# has sel/show on v2, sel only on t2, sel/show v1 (no sel on t1!)
1530
--error ER_VIEW_NO_EXPLAIN
1531
explain select * from v2;
1532
disconnect test11765687;
1536
connect (test11765687,localhost,pjotr,,mysqltest1);
1537
connection test11765687;
1540
# has sel/show on v2, sel only on t2, nothing on v1
1541
# fail: lacks show on v1
1542
--error ER_VIEW_NO_EXPLAIN
1543
explain select * from v2;
1544
disconnect test11765687;
1547
--echo ... as quintessa
1548
connect (test11765687,localhost,quintessa,,mysqltest1);
1549
connection test11765687;
1551
select * from v1; # Should succeed.
1552
--error ER_VIEW_NO_EXPLAIN
1553
explain select * from v1; # fail: lacks select on t1
1555
disconnect test11765687;
1560
--echo ... as root again at last: clean-up time!
1563
drop user alice@localhost;
1564
drop user bob@localhost;
1565
drop user cecil@localhost;
1566
drop user dan@localhost;
1567
drop user eugene@localhost;
1568
drop user fiona@localhost;
1569
drop user greg@localhost;
1570
drop user han@localhost;
1571
drop user inga@localhost;
1572
drop user jamie@localhost;
1573
drop user karl@localhost;
1574
drop user lena@localhost;
1575
drop user mhairi@localhost;
1576
drop user noam@localhost;
1577
drop user olga@localhost;
1578
drop user pjotr@localhost;
1579
drop user quintessa@localhost;
1581
drop database mysqltest1;
1186
1587
--echo End of 5.0 tests.
1591
# Wait till we reached the initial number of concurrent sessions
1592
--source include/wait_until_count_sessions.inc
added
removed
mysql-test/t/view_grant.test
