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
ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v5'
113
grant select on mysqltest.v5 to mysqltest_1@localhost;
114
show create view mysqltest.v5;
115
View Create View character_set_client collation_connection
116
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
117
explain select c from mysqltest.v1;
118
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
119
show create view mysqltest.v1;
120
ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1'
121
grant show view on mysqltest.v1 to mysqltest_1@localhost;
105
122
grant select on mysqltest.t1 to mysqltest_1@localhost;
123
revoke select on mysqltest.v5 from mysqltest_1@localhost;
106
124
explain select c from mysqltest.v1;
107
125
id select_type table type possible_keys key key_len ref rows Extra
108
126
1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found
109
127
show create view mysqltest.v1;
110
ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1'
128
View Create View character_set_client collation_connection
129
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
130
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
131
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
115
132
show create view mysqltest.v2;
116
133
ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2'
117
134
explain select c from mysqltest.v3;
947
963
DROP VIEW db1.v1;
948
964
DROP TABLE db1.t1;
949
965
DROP DATABASE db1;
966
Bug #11765687/#58677:
967
No privilege on table/view, but can know #rows / underlying table's name
968
create database mysqltest1;
969
create table mysqltest1.t1 (i int);
970
create table mysqltest1.t2 (j int);
971
create table mysqltest1.t3 (k int, secret int);
972
create user alice@localhost;
973
create user bob@localhost;
974
create user cecil@localhost;
975
create user dan@localhost;
976
create user eugene@localhost;
977
create user fiona@localhost;
978
create user greg@localhost;
979
create user han@localhost;
980
create user inga@localhost;
981
create user jamie@localhost;
982
create user karl@localhost;
983
create user lena@localhost;
984
create user mhairi@localhost;
985
create user noam@localhost;
986
create user olga@localhost;
987
create user pjotr@localhost;
988
create user quintessa@localhost;
989
grant all privileges on mysqltest1.* to alice@localhost with grant option;
991
create view v1 as select * from t1;
992
create view v2 as select * from v1, t2;
993
create view v3 as select k from t3;
994
grant select on mysqltest1.v1 to bob@localhost;
995
grant show view on mysqltest1.v1 to cecil@localhost;
996
grant select, show view on mysqltest1.v1 to dan@localhost;
997
grant select on mysqltest1.t1 to dan@localhost;
998
grant select on mysqltest1.* to eugene@localhost;
999
grant select, show view on mysqltest1.v2 to fiona@localhost;
1000
grant select, show view on mysqltest1.v2 to greg@localhost;
1001
grant show view on mysqltest1.v1 to greg@localhost;
1002
grant select(k) on mysqltest1.t3 to han@localhost;
1003
grant select, show view on mysqltest1.v3 to han@localhost;
1004
grant select on mysqltest1.t1 to inga@localhost;
1005
grant select on mysqltest1.t2 to inga@localhost;
1006
grant select on mysqltest1.v1 to inga@localhost;
1007
grant select, show view on mysqltest1.v2 to inga@localhost;
1008
grant select on mysqltest1.t1 to jamie@localhost;
1009
grant select on mysqltest1.t2 to jamie@localhost;
1010
grant show view on mysqltest1.v1 to jamie@localhost;
1011
grant select, show view on mysqltest1.v2 to jamie@localhost;
1012
grant select on mysqltest1.t1 to karl@localhost;
1013
grant select on mysqltest1.t2 to karl@localhost;
1014
grant select, show view on mysqltest1.v1 to karl@localhost;
1015
grant select on mysqltest1.v2 to karl@localhost;
1016
grant select on mysqltest1.t1 to lena@localhost;
1017
grant select on mysqltest1.t2 to lena@localhost;
1018
grant select, show view on mysqltest1.v1 to lena@localhost;
1019
grant show view on mysqltest1.v2 to lena@localhost;
1020
grant select on mysqltest1.t1 to mhairi@localhost;
1021
grant select on mysqltest1.t2 to mhairi@localhost;
1022
grant select, show view on mysqltest1.v1 to mhairi@localhost;
1023
grant select, show view on mysqltest1.v2 to mhairi@localhost;
1024
grant select on mysqltest1.t1 to noam@localhost;
1025
grant select, show view on mysqltest1.v1 to noam@localhost;
1026
grant select, show view on mysqltest1.v2 to noam@localhost;
1027
grant select on mysqltest1.t2 to olga@localhost;
1028
grant select, show view on mysqltest1.v1 to olga@localhost;
1029
grant select, show view on mysqltest1.v2 to olga@localhost;
1030
grant select on mysqltest1.t1 to pjotr@localhost;
1031
grant select on mysqltest1.t2 to pjotr@localhost;
1032
grant select, show view on mysqltest1.v2 to pjotr@localhost;
1033
grant select, show view on mysqltest1.v1 to quintessa@localhost;
1037
explain select * from v1;
1038
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1041
ERROR 42000: SELECT command denied to user 'cecil'@'localhost' for table 'v1'
1042
explain select * from v1;
1043
ERROR 42000: SELECT command denied to user 'cecil'@'localhost' for table 'v1'
1047
explain select * from v1;
1048
id select_type table type possible_keys key key_len ref rows Extra
1049
1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found
1053
explain select * from v1;
1054
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1058
show create view v2;
1059
View Create View character_set_client collation_connection
1060
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
1061
explain select * from t1;
1062
ERROR 42000: SELECT command denied to user 'fiona'@'localhost' for table 't1'
1063
explain select * from v1;
1064
ERROR 42000: SELECT command denied to user 'fiona'@'localhost' for table 'v1'
1065
explain select * from t2;
1066
ERROR 42000: SELECT command denied to user 'fiona'@'localhost' for table 't2'
1067
explain select * from v2;
1068
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1072
explain select * from v1;
1073
ERROR 42000: SELECT command denied to user 'greg'@'localhost' for table 'v1'
1074
explain select * from v2;
1075
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1078
ERROR 42000: SELECT command denied to user 'han'@'localhost' for table 't3'
1079
explain select * from t3;
1080
ERROR 42000: SELECT command denied to user 'han'@'localhost' for table 't3'
1083
explain select k from t3;
1084
id select_type table type possible_keys key key_len ref rows Extra
1085
1 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found
1088
explain select * from v3;
1089
id select_type table type possible_keys key key_len ref rows Extra
1090
1 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found
1094
explain select * from v2;
1095
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1099
explain select * from v2;
1100
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1104
explain select * from v2;
1105
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1108
ERROR 42000: SELECT command denied to user 'lena'@'localhost' for table 'v2'
1109
explain select * from v2;
1110
ERROR 42000: SELECT command denied to user 'lena'@'localhost' for table 'v2'
1114
explain select * from v2;
1115
id select_type table type possible_keys key key_len ref rows Extra
1116
1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found
1117
1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found
1121
explain select * from v2;
1122
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1126
explain select * from v2;
1127
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1131
explain select * from v2;
1132
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1136
explain select * from v1;
1137
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
1138
... as root again at last: clean-up time!
1139
drop user alice@localhost;
1140
drop user bob@localhost;
1141
drop user cecil@localhost;
1142
drop user dan@localhost;
1143
drop user eugene@localhost;
1144
drop user fiona@localhost;
1145
drop user greg@localhost;
1146
drop user han@localhost;
1147
drop user inga@localhost;
1148
drop user jamie@localhost;
1149
drop user karl@localhost;
1150
drop user lena@localhost;
1151
drop user mhairi@localhost;
1152
drop user noam@localhost;
1153
drop user olga@localhost;
1154
drop user pjotr@localhost;
1155
drop user quintessa@localhost;
1156
drop database mysqltest1;
950
1157
End of 5.0 tests.
951
1158
DROP VIEW IF EXISTS v1;
952
1159
DROP TABLE IF EXISTS t1;