diff options
Diffstat (limited to 'mysql-test/main/information_schema_db.result')
| -rw-r--r-- | mysql-test/main/information_schema_db.result | 246 |
1 files changed, 246 insertions, 0 deletions
diff --git a/mysql-test/main/information_schema_db.result b/mysql-test/main/information_schema_db.result new file mode 100644 index 00000000000..45ade65c502 --- /dev/null +++ b/mysql-test/main/information_schema_db.result @@ -0,0 +1,246 @@ +set local sql_mode=""; +set global sql_mode=""; +drop table if exists t1,t2; +drop view if exists v1,v2; +drop function if exists f1; +drop function if exists f2; +show tables from INFORMATION_SCHEMA like 'T%'; +Tables_in_information_schema (T%) +TABLES +TABLESPACES +TABLE_CONSTRAINTS +TABLE_PRIVILEGES +TABLE_STATISTICS +TRIGGERS +create database `inf%`; +create database mbase; +use `inf%`; +show tables; +Tables_in_inf% +grant all privileges on `inf%`.* to 'mysqltest_1'@'localhost'; +grant all privileges on `mbase`.* to 'mysqltest_1'@'localhost'; +create table t1 (f1 int); +create function func1(curr_int int) returns int +begin +declare ret_val int; +select max(f1) from t1 into ret_val; +return ret_val; +end| +create view v1 as select f1 from t1 where f1 = func1(f1); +create function func2() returns int return 1; +use mbase; +create procedure p1 () +begin +select table_name from information_schema.key_column_usage +order by table_name; +end| +create table t1 +(f1 int(10) unsigned not null, +f2 varchar(100) not null, +primary key (f1), unique key (f2)); +connect user1,localhost,mysqltest_1,,; +connection user1; +select * from information_schema.tables; +call mbase.p1(); +call mbase.p1(); +call mbase.p1(); +connection default; +use `inf%`; +drop user mysqltest_1@localhost; +drop table t1; +select table_name, table_type, table_comment from information_schema.tables +where table_schema='inf%' and func2(); +table_name table_type table_comment +v1 VIEW VIEW +select table_name, table_type, table_comment from information_schema.tables +where table_schema='inf%' and func2(); +table_name table_type table_comment +v1 VIEW VIEW +drop view v1; +drop function func1; +drop function func2; +drop database `inf%`; +drop procedure mbase.p1; +drop database mbase; +disconnect user1; +use test; +create table t1 (i int); +create function f1 () returns int return (select max(i) from t1); +create view v1 as select f1(); +create table t2 (id int); +create function f2 () returns int return (select max(i) from t2); +create view v2 as select f2(); +drop table t2; +select table_name, table_type, table_comment from information_schema.tables +where table_schema='test' order by table_name; +table_name table_type table_comment +t1 BASE TABLE +v1 VIEW VIEW +v2 VIEW VIEW +drop table t1; +select table_name, table_type, table_comment from information_schema.tables +where table_schema='test' order by table_name; +table_name table_type table_comment +v1 VIEW VIEW +v2 VIEW VIEW +drop function f1; +drop function f2; +drop view v1, v2; +create database testdb_1; +create user testdb_1@localhost; +grant all on testdb_1.* to testdb_1@localhost with grant option; +create user testdb_2@localhost; +grant all on test.* to testdb_2@localhost with grant option; +connect testdb_1,localhost,testdb_1,,test; +use testdb_1; +create table t1 (f1 char(4)); +create view v1 as select f1 from t1; +grant insert on v1 to testdb_2@localhost; +create view v5 as select f1 from t1; +grant select, show view on v5 to testdb_2@localhost; +create definer=`no_such_user`@`no_such_host` view v6 as select f1 from t1; +ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation +connection default; +use testdb_1; +create view v6 as select f1 from t1; +grant select, show view on v6 to testdb_2@localhost; +create table t2 (f1 char(4)); +create definer=`no_such_user`@`no_such_host` view v7 as select * from t2; +Warnings: +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist +show fields from testdb_1.v6; +Field Type Null Key Default Extra +f1 char(4) YES NULL +show create view testdb_1.v6; +View Create View character_set_client collation_connection +v6 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v6` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci +show create view testdb_1.v7; +View Create View character_set_client collation_connection +v7 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v7` AS select `testdb_1`.`t2`.`f1` AS `f1` from `t2` latin1 latin1_swedish_ci +Warnings: +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist +show fields from testdb_1.v7; +Field Type Null Key Default Extra +f1 char(4) YES NULL +Warnings: +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist +connection testdb_1; +create table t3 (f1 char(4), f2 char(4)); +create view v3 as select f1,f2 from t3; +grant insert(f1), insert(f2) on v3 to testdb_2@localhost; +connect testdb_2,localhost,testdb_2,,test; +create view v2 as select f1 from testdb_1.v1; +create view v4 as select f1,f2 from testdb_1.v3; +show fields from testdb_1.v5; +Field Type Null Key Default Extra +f1 char(4) YES NULL +show create view testdb_1.v5; +View Create View character_set_client collation_connection +v5 CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_1`@`localhost` SQL SECURITY DEFINER VIEW `testdb_1`.`v5` AS select `testdb_1`.`t1`.`f1` AS `f1` from `testdb_1`.`t1` latin1 latin1_swedish_ci +show fields from testdb_1.v6; +Field Type Null Key Default Extra +f1 char(4) YES NULL +show create view testdb_1.v6; +View Create View character_set_client collation_connection +v6 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testdb_1`.`v6` AS select `testdb_1`.`t1`.`f1` AS `f1` from `testdb_1`.`t1` latin1 latin1_swedish_ci +connection testdb_1; +show fields from testdb_1.v7; +Field Type Null Key Default Extra +f1 char(4) YES NULL +Warnings: +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist +show create view testdb_1.v7; +View Create View character_set_client collation_connection +v7 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v7` AS select `testdb_1`.`t2`.`f1` AS `f1` from `t2` latin1 latin1_swedish_ci +Warnings: +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist +revoke insert(f1) on v3 from testdb_2@localhost; +revoke select,show view on v5 from testdb_2@localhost; +connection default; +use testdb_1; +revoke select,show view on v6 from testdb_2@localhost; +connection testdb_2; +show fields from testdb_1.v5; +ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v5' +show create view testdb_1.v5; +ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v5' +show fields from testdb_1.v6; +ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v6' +show create view testdb_1.v6; +ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v6' +show fields from testdb_1.v7; +ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v7' +show create view testdb_1.v7; +ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v7' +show create view v4; +View Create View character_set_client collation_connection +v4 CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_2`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `v3`.`f1` AS `f1`,`v3`.`f2` AS `f2` from `testdb_1`.`v3` latin1 latin1_swedish_ci +show fields from v4; +Field Type Null Key Default Extra +f1 char(4) YES NULL +f2 char(4) YES NULL +show fields from v2; +Field Type Null Key Default Extra +f1 char(4) YES NULL +show fields from testdb_1.v1; +Field Type Null Key Default Extra +f1 char(4) YES NULL +show create view v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_2`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `v1`.`f1` AS `f1` from `testdb_1`.`v1` latin1 latin1_swedish_ci +show create view testdb_1.v1; +ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v1' +select table_name from information_schema.columns a +where a.table_name = 'v2'; +table_name +v2 +select view_definition from information_schema.views a +where a.table_name = 'v2'; +view_definition +select `v1`.`f1` AS `f1` from `testdb_1`.`v1` +select view_definition from information_schema.views a +where a.table_name = 'testdb_1.v1'; +view_definition +select * from v2; +ERROR HY000: View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +connection default; +use test; +drop view testdb_1.v1, v2, testdb_1.v3, v4; +drop database testdb_1; +connection testdb_1; +disconnect testdb_1; +connection testdb_2; +disconnect testdb_2; +connection default; +drop user testdb_1@localhost; +drop user testdb_2@localhost; +create database testdb_1; +create table testdb_1.t1 (a int); +create view testdb_1.v1 as select * from testdb_1.t1; +grant show view on testdb_1.* to mysqltest_1@localhost; +grant select on testdb_1.v1 to mysqltest_1@localhost; +connect user1,localhost,mysqltest_1,,test; +connection user1; +select table_schema, table_name, view_definition from information_schema.views +where table_name='v1'; +table_schema table_name view_definition +testdb_1 v1 select `testdb_1`.`t1`.`a` AS `a` from `testdb_1`.`t1` +show create view testdb_1.v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testdb_1`.`v1` AS select `testdb_1`.`t1`.`a` AS `a` from `testdb_1`.`t1` latin1 latin1_swedish_ci +connection default; +revoke select on testdb_1.v1 from mysqltest_1@localhost; +connection user1; +select table_schema, table_name, view_definition from information_schema.views +where table_name='v1'; +table_schema table_name view_definition +testdb_1 v1 +show create view testdb_1.v1; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v1' +connection default; +drop user mysqltest_1@localhost; +drop database testdb_1; +connection user1; +disconnect user1; +connection default; +set global sql_mode=default; |