information_schema_db.result 4.89 KB
Newer Older
1 2 3 4
drop table if exists t1,t2;
drop view if exists v1,v2;
drop function if exists f1;
drop function if exists f2;
5 6
use INFORMATION_SCHEMA;
show tables;
7
Tables_in_information_schema
8 9 10
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
11 12
COLUMNS
COLUMN_PRIVILEGES
13
ENGINES
14
EVENTS
15
FILES
16 17
GLOBAL_STATUS
GLOBAL_VARIABLES
18
KEY_COLUMN_USAGE
19
PARTITIONS
20
PLUGINS
21
PROCESSLIST
22
REFERENTIAL_CONSTRAINTS
23
ROUTINES
24
SCHEMATA
25
SCHEMA_PRIVILEGES
26 27
SESSION_STATUS
SESSION_VARIABLES
28 29
STATISTICS
TABLES
30
TABLE_CONSTRAINTS
31
TABLE_PRIVILEGES
32
TRIGGERS
33
USER_PRIVILEGES
34
VIEWS
35
show tables from INFORMATION_SCHEMA like 'T%';
36
Tables_in_information_schema (T%)
37 38
TABLES
TABLE_CONSTRAINTS
39
TABLE_PRIVILEGES
40
TRIGGERS
41
create database `inf%`;
42
create database mbase;
43 44 45
use `inf%`;
show tables;
Tables_in_inf%
46
grant all privileges on `inf%`.* to 'mysqltest_1'@'localhost';
47
grant all privileges on `mbase`.* to 'mysqltest_1'@'localhost';
48 49 50 51 52 53 54 55
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);
56 57 58 59 60 61 62 63 64 65 66
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));
67
select * from information_schema.tables;
68 69 70 71
call mbase.p1();
call mbase.p1();
call mbase.p1();
use `inf%`;
72
drop user mysqltest_1@localhost;
73 74 75 76 77 78 79 80 81
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 'inf%.v1' references invalid table(s) or column(s) or function(s) or define
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 'inf%.v1' references invalid table(s) or column(s) or function(s) or define
82 83
drop view v1;
drop function func1;
84
drop function func2;
85
drop database `inf%`;
86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110
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 'test.v2' references invalid table(s) or column(s) or function(s) or define
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 'test.v1' references invalid table(s) or column(s) or function(s) or define
v2	VIEW	View 'test.v2' references invalid table(s) or column(s) or function(s) or define
drop function f1;
drop function f2;
drop view v1, v2;
111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156
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 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;
revoke insert(f1) on v3 from testdb_2@localhost;
show create view v4;
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
show fields from v4;
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
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
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_2`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v2` AS select `v1`.`f1` AS `f1` from `testdb_1`.`v1`
show create view testdb_1.v1;
ERROR 42000: SHOW VIEW 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
/* ALGORITHM=UNDEFINED */ 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
drop view testdb_1.v1,v2, testdb_1.v3, v4;
drop database testdb_1;
drop user testdb_1@localhost;
gluh@mysql.com/gluh.(none)'s avatar
gluh@mysql.com/gluh.(none) committed
157
drop user testdb_2@localhost;