summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/information_schema.result12
-rw-r--r--mysql-test/r/sp.result2
-rw-r--r--mysql-test/r/sql_mode.result3
-rw-r--r--mysql-test/r/view.result26
-rw-r--r--mysql-test/r/view_grant.result194
5 files changed, 216 insertions, 21 deletions
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index a38d5eddd1b..a5852dc31b4 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -648,21 +648,21 @@ select table_name from information_schema.views
where table_schema='test';
table_name
Warnings:
-Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s)
-Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s)
+Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
select table_name from information_schema.views
where table_schema='test';
table_name
Warnings:
-Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s)
-Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s)
+Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
select column_name from information_schema.columns
where table_schema='test';
column_name
f1
Warnings:
-Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s)
-Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s)
+Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
select index_name from information_schema.statistics where table_schema='test';
index_name
f1_key
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index f91aadc02b6..1f3f7dba7da 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -1032,7 +1032,7 @@ a f8()
3 1
drop function f1|
select * from v1|
-ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s)
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
create function f1() returns int
return (select sum(data) from t1) + (select sum(data) from v1)|
drop function f1|
diff --git a/mysql-test/r/sql_mode.result b/mysql-test/r/sql_mode.result
index 83099623e23..7d9d7fb333c 100644
--- a/mysql-test/r/sql_mode.result
+++ b/mysql-test/r/sql_mode.result
@@ -1,4 +1,5 @@
-drop table if exists t1;
+drop table if exists t1,t2,v1,v2;
+drop view if exists t1,t2,v1,v2;
CREATE TABLE `t1` (
a int not null auto_increment,
`pseudo` varchar(35) character set latin2 NOT NULL default '',
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 4d224bf196d..6f15e7af399 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -574,10 +574,10 @@ create view v1 as select * from t1;
drop table t1;
create table t1 (col1 char(5),newcol2 char(5));
insert into v1 values('a','aa');
-ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s)
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
drop table t1;
select * from v1;
-ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s)
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
drop view v1;
create view v1 (a,a) as select 'a','a';
ERROR 42S21: Duplicate column name 'a'
@@ -809,11 +809,11 @@ create table t1 (s1 int);
create view v1 as select x1() from t1;
drop function x1;
select * from v1;
-ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s)
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
show table status;
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
t1 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL
-v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL View 'test.v1' references invalid table(s) or column(s) or function(s)
+v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL View 'test.v1' references invalid table(s) or column(s) or function(s) or define
drop view v1;
drop table t1;
create view v1 as select 99999999999999999999999999999999999999999999999999999 as col1;
@@ -1360,7 +1360,7 @@ test.t1 check status OK
drop table t1;
check table v1;
Table Op Msg_type Msg_text
-test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s)
+test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
drop view v1;
create table t1 (a int);
create table t2 (a int);
@@ -1884,11 +1884,11 @@ CREATE VIEW v6 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2;
DROP TABLE t1;
CHECK TABLE v1, v2, v3, v4, v5, v6;
Table Op Msg_type Msg_text
-test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s)
+test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
test.v2 check status OK
-test.v3 check error View 'test.v3' references invalid table(s) or column(s) or function(s)
+test.v3 check error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
test.v4 check status OK
-test.v5 check error View 'test.v5' references invalid table(s) or column(s) or function(s)
+test.v5 check error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
test.v6 check status OK
drop view v1, v2, v3, v4, v5, v6;
drop table t2;
@@ -1908,11 +1908,11 @@ CREATE VIEW v6 AS SELECT f2() FROM t3;
drop function f1;
CHECK TABLE v1, v2, v3, v4, v5, v6;
Table Op Msg_type Msg_text
-test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s)
+test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
test.v2 check status OK
-test.v3 check error View 'test.v3' references invalid table(s) or column(s) or function(s)
+test.v3 check error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
test.v4 check status OK
-test.v5 check error View 'test.v5' references invalid table(s) or column(s) or function(s)
+test.v5 check error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
test.v6 check status OK
create function f1 () returns int return (select max(col1) from t1);
DROP TABLE t1;
@@ -2154,7 +2154,7 @@ Field Type Null Key Default Extra
f4 char(5) YES NULL
ALTER TABLE t1 CHANGE COLUMN f4 f4x CHAR(5);
DESCRIBE v1;
-ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s)
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
DROP TABLE t1;
DROP VIEW v1;
create table t1 (f1 char);
@@ -2198,7 +2198,7 @@ r_object_id users_names
120001a080000542 guser02
drop view v1, v2;
drop table t1, t2;
-create definer=some_user@__% sql security invoker view v1 as select 1;
+create definer=some_user@`` sql security invoker view v1 as select 1;
ERROR HY000: View definer is not fully qualified
create definer=some_user@localhost sql security invoker view v1 as select 1;
Warnings:
diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result
index bac6e691069..43df5c29f92 100644
--- a/mysql-test/r/view_grant.result
+++ b/mysql-test/r/view_grant.result
@@ -305,5 +305,199 @@ create table mysqltest.t1 (a int);
grant all privileges on mysqltest.* to mysqltest_1@localhost;
use mysqltest;
create view v1 as select * from t1;
+use test;
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
drop database mysqltest;
+create database mysqltest;
+create table mysqltest.t1 (a int, b int);
+grant select on mysqltest.t1 to mysqltest_1@localhost;
+grant create view,select on test.* to mysqltest_1@localhost;
+create view v1 as select * from mysqltest.t1;
+show create view v1;
+View Create View
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1`
+revoke select on mysqltest.t1 from mysqltest_1@localhost;
+select * from v1;
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+grant select on mysqltest.t1 to mysqltest_1@localhost;
+select * from v1;
+a b
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop view v1;
+drop database mysqltest;
+create database mysqltest;
+use mysqltest;
+create table t1 (a int);
+insert into t1 values (1);
+create table t2 (s1 int);
+drop function if exists f2;
+create function f2 () returns int begin declare v int; select s1 from t2
+into v; return v; end//
+create algorithm=TEMPTABLE view v1 as select f2() from t1;
+create algorithm=MERGE view v2 as select f2() from t1;
+create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1;
+create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1;
+create SQL SECURITY INVOKER view v5 as select * from v4;
+grant select on v1 to mysqltest_1@localhost;
+grant select on v2 to mysqltest_1@localhost;
+grant select on v3 to mysqltest_1@localhost;
+grant select on v4 to mysqltest_1@localhost;
+grant select on v5 to mysqltest_1@localhost;
+use mysqltest;
+select * from v1;
+f2()
+NULL
+Warnings:
+Warning 1329 No data to FETCH
+select * from v2;
+f2()
+NULL
+Warnings:
+Warning 1329 No data to FETCH
+select * from v3;
+ERROR HY000: View 'mysqltest.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+select * from v4;
+ERROR HY000: View 'mysqltest.v4' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+select * from v5;
+ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+use test;
+drop view v1, v2, v3, v4, v5;
+drop function f2;
+drop table t1, t2;
+use test;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;
+create database mysqltest;
+use mysqltest;
+create table t1 (a int);
+insert into t1 values (1);
+create table t2 (s1 int);
+drop function if exists f2;
+create function f2 () returns int begin declare v int; select s1 from t2
+into v; return v; end//
+grant select on t1 to mysqltest_1@localhost;
+grant execute on function f2 to mysqltest_1@localhost;
+grant create view on mysqltest.* to mysqltest_1@localhost;
+use mysqltest;
+create algorithm=TEMPTABLE view v1 as select f2() from t1;
+create algorithm=MERGE view v2 as select f2() from t1;
+create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1;
+create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1;
+use test;
+create view v5 as select * from v1;
+revoke execute on function f2 from mysqltest_1@localhost;
+select * from v1;
+ERROR HY000: View 'mysqltest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+select * from v2;
+ERROR HY000: View 'mysqltest.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+select * from v3;
+f2()
+NULL
+Warnings:
+Warning 1329 No data to FETCH
+select * from v4;
+f2()
+NULL
+Warnings:
+Warning 1329 No data to FETCH
+select * from v5;
+ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+drop view v1, v2, v3, v4, v5;
+drop function f2;
+drop table t1, t2;
+use test;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;
+create database mysqltest;
+use mysqltest;
+create table t1 (a int);
+create table v1 (a int);
+insert into t1 values (1);
+grant select on t1 to mysqltest_1@localhost;
+grant select on v1 to mysqltest_1@localhost;
+grant create view on mysqltest.* to mysqltest_1@localhost;
+drop table v1;
+use mysqltest;
+create algorithm=TEMPTABLE view v1 as select *, a as b from t1;
+create algorithm=MERGE view v2 as select *, a as b from t1;
+create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1;
+create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1;
+create view v5 as select * from v1;
+use test;
+revoke select on t1 from mysqltest_1@localhost;
+select * from v1;
+ERROR HY000: View 'mysqltest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+select * from v2;
+ERROR HY000: View 'mysqltest.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+select * from v3;
+a b
+1 1
+select * from v4;
+a b
+1 1
+select * from v5;
+ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+drop table t1;
+use test;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;
+create database mysqltest;
+use mysqltest;
+create table t1 (a int);
+insert into t1 values (1);
+create algorithm=TEMPTABLE view v1 as select *, a as b from t1;
+create algorithm=MERGE view v2 as select *, a as b from t1;
+create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1;
+create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1;
+create SQL SECURITY INVOKER view v5 as select * from v4;
+grant select on v1 to mysqltest_1@localhost;
+grant select on v2 to mysqltest_1@localhost;
+grant select on v3 to mysqltest_1@localhost;
+grant select on v4 to mysqltest_1@localhost;
+grant select on v5 to mysqltest_1@localhost;
+use mysqltest;
+select * from v1;
+a b
+1 1
+select * from v2;
+a b
+1 1
+select * from v3;
+ERROR HY000: View 'mysqltest.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+select * from v4;
+ERROR HY000: View 'mysqltest.v4' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+select * from v5;
+ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+use test;
+drop view v1, v2, v3, v4, v5;
+drop table t1;
+use test;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;
+drop view if exists v1;
+create table t1 as select * from mysql.user where user='';
+delete from mysql.user where user='';
+flush privileges;
+grant all on test.* to 'test14256'@'%';
+use test;
+create view v1 as select 42;
+show create view v1;
+View Create View
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`test14256`@`%` SQL SECURITY DEFINER VIEW `v1` AS select 42 AS `42`
+select definer into @v1def1 from information_schema.views
+where table_schema = 'test' and table_name='v1';
+drop view v1;
+create definer=`test14256`@`%` view v1 as select 42;
+show create view v1;
+View Create View
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`test14256`@`%` SQL SECURITY DEFINER VIEW `v1` AS select 42 AS `42`
+select definer into @v1def2 from information_schema.views
+where table_schema = 'test' and table_name='v1';
+drop view v1;
+select @v1def1, @v1def2, @v1def1=@v1def2;
+@v1def1 @v1def2 @v1def1=@v1def2
+test14256@% test14256@% 1
+drop user test14256;
+insert into mysql.user select * from t1;
+flush privileges;
+drop table t1;
View Lorry Controller Status