102
106
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
103
107
show create view mysqltest.v4;
104
108
ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v4'
109
explain select c from mysqltest.v5;
110
ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v5'
111
show create view mysqltest.v5;
112
View Create View character_set_client collation_connection
113
v5 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v5` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` latin1 latin1_swedish_ci
114
explain select c from mysqltest.v1;
115
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
116
show create view mysqltest.v1;
117
ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1'
118
grant show view on mysqltest.v1 to mysqltest_1@localhost;
105
119
grant select on mysqltest.t1 to mysqltest_1@localhost;
106
120
explain select c from mysqltest.v1;
107
121
id select_type table type possible_keys key key_len ref rows Extra
108
122
1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found
109
123
show create view mysqltest.v1;
110
ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1'
124
View Create View character_set_client collation_connection
125
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v1` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` latin1 latin1_swedish_ci
111
126
explain select c from mysqltest.v2;
112
id select_type table type possible_keys key key_len ref rows Extra
113
1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
114
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
127
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
115
128
show create view mysqltest.v2;
116
129
ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2'
117
130
explain select c from mysqltest.v3;
947
962
DROP VIEW db1.v1;
948
963
DROP TABLE db1.t1;
949
964
DROP DATABASE db1;
965
Bug #11765687/#58677:
966
No privilege on table/view, but can know #rows / underlying table's name
967
create database mysqltest1;
968
create table mysqltest1.t1 (i int);
969
create table mysqltest1.t2 (j int);
970
create table mysqltest1.t3 (k int, secret int);
971
create user alice@localhost;
972
create user bob@localhost;
973
create user cecil@localhost;
974
create user dan@localhost;
975
create user eugene@localhost;
976
create user fiona@localhost;
977
create user greg@localhost;
978
create user han@localhost;
979
create user inga@localhost;
980
create user jamie@localhost;
981
create user karl@localhost;
982
create user lena@localhost;
983
create user mhairi@localhost;
984
create user noam@localhost;
985
create user olga@localhost;
986
create user pjotr@localhost;
987
create user quintessa@localhost;
988
grant all privileges on mysqltest1.* to alice@localhost with grant option;
990
create view v1 as select * from t1;
991
create view v2 as select * from v1, t2;
992
create view v3 as select k from t3;
993
grant select on mysqltest1.v1 to bob@localhost;
994
grant show view on mysqltest1.v1 to cecil@localhost;
995
grant select, show view on mysqltest1.v1 to dan@localhost;
996
grant select on mysqltest1.t1 to dan@localhost;
997
grant select on mysqltest1.* to eugene@localhost;
998
grant select, show view on mysqltest1.v2 to fiona@localhost;
999
grant select, show view on mysqltest1.v2 to greg@localhost;
1000
grant show view on mysqltest1.v1 to greg@localhost;
1001
grant select(k) on mysqltest1.t3 to han@localhost;
1002
grant select, show view on mysqltest1.v3 to han@localhost;
1003
grant select on mysqltest1.t1 to inga@localhost;
1004
grant select on mysqltest1.t2 to inga@localhost;
1005
grant select on mysqltest1.v1 to inga@localhost;
1006
grant select, show view on mysqltest1.v2 to inga@localhost;
1007
grant select on mysqltest1.t1 to jamie@localhost;
1008
grant select on mysqltest1.t2 to jamie@localhost;
1009
grant show view on mysqltest1.v1 to jamie@localhost;
1010
grant select, show view on mysqltest1.v2 to jamie@localhost;
1011
grant select on mysqltest1.t1 to karl@localhost;
1012
grant select on mysqltest1.t2 to karl@localhost;
1013
grant select, show view on mysqltest1.v1 to karl@localhost;
1014
grant select on mysqltest1.v2 to karl@localhost;
1015
grant select on mysqltest1.t1 to lena@localhost;
1016
grant select on mysqltest1.t2 to lena@localhost;
1017
grant select, show view on mysqltest1.v1 to lena@localhost;
1018
grant show view on mysqltest1.v2 to lena@localhost;
1019
grant select on mysqltest1.t1 to mhairi@localhost;
1020
grant select on mysqltest1.t2 to mhairi@localhost;
1021
grant select, show view on mysqltest1.v1 to mhairi@localhost;
1022
grant select, show view on mysqltest1.v2 to mhairi@localhost;
1023
grant select on mysqltest1.t1 to noam@localhost;
1024
grant select, show view on mysqltest1.v1 to noam@localhost;
1025
grant select, show view on mysqltest1.v2 to noam@localhost;
1026
grant select on mysqltest1.t2 to olga@localhost;
1027
grant select, show view on mysqltest1.v1 to olga@localhost;
1028
grant select, show view on mysqltest1.v2 to olga@localhost;
1029
grant select on mysqltest1.t1 to pjotr@localhost;
1030
grant select on mysqltest1.t2 to pjotr@localhost;
1031
grant select, show view on mysqltest1.v2 to pjotr@localhost;
1032
grant select, show view on mysqltest1.v1 to quintessa@localhost;
1036
explain select * from v1;
1037
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1040
ERROR 42000: SELECT command denied to user 'cecil'@'localhost' for table 'v1'
1041
explain select * from v1;
1042
ERROR 42000: SELECT command denied to user 'cecil'@'localhost' for table 'v1'
1046
explain select * from v1;
1047
id select_type table type possible_keys key key_len ref rows Extra
1048
1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found
1052
explain select * from v1;
1053
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1057
show create view v2;
1058
View Create View character_set_client collation_connection
1059
v2 CREATE ALGORITHM=UNDEFINED DEFINER=`alice`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `v1`.`i` AS `i`,`t2`.`j` AS `j` from (`v1` join `t2`) latin1 latin1_swedish_ci
1060
explain select * from t1;
1061
ERROR 42000: SELECT command denied to user 'fiona'@'localhost' for table 't1'
1062
explain select * from v1;
1063
ERROR 42000: SELECT command denied to user 'fiona'@'localhost' for table 'v1'
1064
explain select * from t2;
1065
ERROR 42000: SELECT command denied to user 'fiona'@'localhost' for table 't2'
1066
explain select * from v2;
1067
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1071
explain select * from v1;
1072
ERROR 42000: SELECT command denied to user 'greg'@'localhost' for table 'v1'
1073
explain select * from v2;
1074
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1077
ERROR 42000: SELECT command denied to user 'han'@'localhost' for table 't3'
1078
explain select * from t3;
1079
ERROR 42000: SELECT command denied to user 'han'@'localhost' for table 't3'
1082
explain select k from t3;
1083
id select_type table type possible_keys key key_len ref rows Extra
1084
1 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found
1087
explain select * from v3;
1088
id select_type table type possible_keys key key_len ref rows Extra
1089
1 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found
1093
explain select * from v2;
1094
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1098
explain select * from v2;
1099
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1103
explain select * from v2;
1104
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1107
ERROR 42000: SELECT command denied to user 'lena'@'localhost' for table 'v2'
1108
explain select * from v2;
1109
ERROR 42000: SELECT command denied to user 'lena'@'localhost' for table 'v2'
1113
explain select * from v2;
1114
id select_type table type possible_keys key key_len ref rows Extra
1115
1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found
1116
1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found
1120
explain select * from v2;
1121
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1125
explain select * from v2;
1126
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1130
explain select * from v2;
1131
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1135
explain select * from v1;
1136
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1137
... as root again at last: clean-up time!
1138
drop user alice@localhost;
1139
drop user bob@localhost;
1140
drop user cecil@localhost;
1141
drop user dan@localhost;
1142
drop user eugene@localhost;
1143
drop user fiona@localhost;
1144
drop user greg@localhost;
1145
drop user han@localhost;
1146
drop user inga@localhost;
1147
drop user jamie@localhost;
1148
drop user karl@localhost;
1149
drop user lena@localhost;
1150
drop user mhairi@localhost;
1151
drop user noam@localhost;
1152
drop user olga@localhost;
1153
drop user pjotr@localhost;
1154
drop user quintessa@localhost;
1155
drop database mysqltest1;
950
1156
End of 5.0 tests.
951
1157
DROP VIEW IF EXISTS v1;
952
1158
DROP TABLE IF EXISTS t1;
1248
1454
LOCK TABLES v1 READ;
1249
1455
ERROR HY000: The user specified as a definer ('unknown'@'unknown') does not exist
1458
# Bug #58499 "DEFINER-security view selecting from INVOKER-security view
1459
# access check wrong".
1461
# Check that we correctly handle privileges for various combinations
1462
# of INVOKER and DEFINER-security views using each other.
1463
DROP DATABASE IF EXISTS mysqltest1;
1464
CREATE DATABASE mysqltest1;
1466
CREATE TABLE t1 (i INT);
1467
CREATE TABLE t2 (j INT);
1468
INSERT INTO t1 VALUES (1);
1469
INSERT INTO t2 VALUES (2);
1471
# 1) DEFINER-security view uses INVOKER-security view (covers
1472
# scenario originally described in the bug report).
1473
CREATE SQL SECURITY INVOKER VIEW v1_uses_t1 AS SELECT * FROM t1;
1474
CREATE SQL SECURITY INVOKER VIEW v1_uses_t2 AS SELECT * FROM t2;
1475
CREATE USER 'mysqluser1'@'%';
1476
GRANT CREATE VIEW ON mysqltest1.* TO 'mysqluser1'@'%';
1477
GRANT SELECT ON t1 TO 'mysqluser1'@'%';
1478
# To be able create 'v2_uses_t2' we also need select on t2.
1479
GRANT SELECT ON t2 TO 'mysqluser1'@'%';
1480
GRANT SELECT ON v1_uses_t1 TO 'mysqluser1'@'%';
1481
GRANT SELECT ON v1_uses_t2 TO 'mysqluser1'@'%';
1483
# Connection 'mysqluser1'.
1484
CREATE SQL SECURITY DEFINER VIEW v2_uses_t1 AS SELECT * FROM v1_uses_t1;
1485
CREATE SQL SECURITY DEFINER VIEW v2_uses_t2 AS SELECT * FROM v1_uses_t2;
1487
# Connection 'default'.
1488
CREATE USER 'mysqluser2'@'%';
1489
GRANT SELECT ON v2_uses_t1 TO 'mysqluser2'@'%';
1490
GRANT SELECT ON v2_uses_t2 TO 'mysqluser2'@'%';
1491
GRANT SELECT ON t2 TO 'mysqluser2'@'%';
1492
GRANT CREATE VIEW ON mysqltest1.* TO 'mysqluser2'@'%';
1493
# Make 'mysqluser1' unable to access t2.
1494
REVOKE SELECT ON t2 FROM 'mysqluser1'@'%';
1496
# Connection 'mysqluser2'.
1497
# The below statement should succeed thanks to suid nature of v2_uses_t1.
1498
SELECT * FROM v2_uses_t1;
1501
# The below statement should fail due to suid nature of v2_uses_t2.
1502
SELECT * FROM v2_uses_t2;
1503
ERROR HY000: View 'mysqltest1.v2_uses_t2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
1505
# 2) INVOKER-security view uses INVOKER-security view.
1507
# Connection 'default'.
1508
DROP VIEW v2_uses_t1, v2_uses_t2;
1509
CREATE SQL SECURITY INVOKER VIEW v2_uses_t1 AS SELECT * FROM v1_uses_t1;
1510
CREATE SQL SECURITY INVOKER VIEW v2_uses_t2 AS SELECT * FROM v1_uses_t2;
1511
GRANT SELECT ON v2_uses_t1 TO 'mysqluser1'@'%';
1512
GRANT SELECT ON v2_uses_t2 TO 'mysqluser1'@'%';
1513
GRANT SELECT ON v1_uses_t1 TO 'mysqluser2'@'%';
1514
GRANT SELECT ON v1_uses_t2 TO 'mysqluser2'@'%';
1516
# Connection 'mysqluser1'.
1517
# For both versions of 'v2' 'mysqluser1' privileges should be used.
1518
SELECT * FROM v2_uses_t1;
1521
SELECT * FROM v2_uses_t2;
1522
ERROR HY000: View 'mysqltest1.v2_uses_t2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
1524
# Connection 'mysqluser2'.
1525
# And now for both versions of 'v2' 'mysqluser2' privileges should
1527
SELECT * FROM v2_uses_t1;
1528
ERROR HY000: View 'mysqltest1.v2_uses_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
1529
SELECT * FROM v2_uses_t2;
1533
# 3) INVOKER-security view uses DEFINER-security view.
1535
# Connection 'default'.
1536
DROP VIEW v1_uses_t1, v1_uses_t2;
1537
# To be able create 'v1_uses_t2' we also need select on t2.
1538
GRANT SELECT ON t2 TO 'mysqluser1'@'%';
1540
# Connection 'mysqluser1'.
1541
CREATE SQL SECURITY DEFINER VIEW v1_uses_t1 AS SELECT * FROM t1;
1542
CREATE SQL SECURITY DEFINER VIEW v1_uses_t2 AS SELECT * FROM t2;
1544
# Connection 'default'.
1545
# Make 'mysqluser1' unable to access t2.
1546
REVOKE SELECT ON t2 FROM 'mysqluser1'@'%';
1548
# Connection 'mysqluser2'.
1549
# Due to suid nature of v1_uses_t1 and v1_uses_t2 the first
1550
# select should succeed and the second select should fail.
1551
SELECT * FROM v2_uses_t1;
1554
SELECT * FROM v2_uses_t2;
1555
ERROR HY000: View 'mysqltest1.v2_uses_t2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
1557
# 4) DEFINER-security view uses DEFINER-security view.
1559
# Connection 'default'.
1560
DROP VIEW v2_uses_t1, v2_uses_t2;
1561
# To be able create 'v2_uses_t2' we also need select on t2.
1562
GRANT SELECT ON t2 TO 'mysqluser1'@'%';
1564
# Connection 'mysqluser2'.
1565
CREATE SQL SECURITY DEFINER VIEW v2_uses_t1 AS SELECT * FROM v1_uses_t1;
1566
CREATE SQL SECURITY DEFINER VIEW v2_uses_t2 AS SELECT * FROM v1_uses_t2;
1568
# Connection 'default'.
1569
# Make 'mysqluser1' unable to access t2.
1570
REVOKE SELECT ON t2 FROM 'mysqluser1'@'%';
1572
# Connection 'mysqluser2'.
1573
# Again privileges of creator of innermost views should apply.
1574
SELECT * FROM v2_uses_t1;
1577
SELECT * FROM v2_uses_t2;
1578
ERROR HY000: View 'mysqltest1.v2_uses_t2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
1580
DROP DATABASE mysqltest1;
1581
DROP USER 'mysqluser1'@'%';
1582
DROP USER 'mysqluser2'@'%';