Edit File: information_schema_db.result
drop table if exists t1,t2; drop view if exists v1,v2; drop function if exists f1; drop function if exists f2; use INFORMATION_SCHEMA; show tables where Tables_in_information_schema NOT LIKE 'Innodb%'; Tables_in_information_schema CHARACTER_SETS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY COLUMNS COLUMN_PRIVILEGES ENGINES EVENTS FILES GLOBAL_STATUS GLOBAL_VARIABLES KEY_COLUMN_USAGE PARAMETERS PARTITIONS PLUGINS PROCESSLIST PROFILING REFERENTIAL_CONSTRAINTS ROUTINES SCHEMATA SCHEMA_PRIVILEGES SESSION_STATUS SESSION_VARIABLES STATISTICS TABLES TABLESPACES TABLE_CONSTRAINTS TABLE_PRIVILEGES TRIGGERS USER_PRIVILEGES VIEWS show tables from INFORMATION_SCHEMA like 'T%'; Tables_in_information_schema (T%) TABLES TABLESPACES TABLE_CONSTRAINTS TABLE_PRIVILEGES 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)); select * from information_schema.tables; call mbase.p1(); call mbase.p1(); call mbase.p1(); 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; 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'; 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'; 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; 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 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 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; 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 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; use testdb_1; revoke select,show view on v6 from testdb_2@localhost; 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 use test; drop view testdb_1.v1, v2, testdb_1.v3, v4; drop database testdb_1; 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; 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 revoke select on testdb_1.v1 from mysqltest_1@localhost; 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' drop user mysqltest_1@localhost; drop database testdb_1;
Back to File Manager