information_schema_db.result 9.06 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
unknown's avatar
unknown committed
13
ENGINES
14
EVENTS
unknown's avatar
unknown committed
15
FILES
16 17
GLOBAL_STATUS
GLOBAL_VARIABLES
18
KEY_COLUMN_USAGE
19
PARTITIONS
unknown's avatar
unknown committed
20
PLUGINS
21
PROCESSLIST
22
PROFILING
23
REFERENTIAL_CONSTRAINTS
24
ROUTINES
25
SCHEMATA
26
SCHEMA_PRIVILEGES
27 28
SESSION_STATUS
SESSION_VARIABLES
29 30
STATISTICS
TABLES
31
TABLE_CONSTRAINTS
32
TABLE_PRIVILEGES
33
TRIGGERS
34
USER_PRIVILEGES
35
VIEWS
36
show tables from INFORMATION_SCHEMA like 'T%';
37
Tables_in_information_schema (T%)
38 39
TABLES
TABLE_CONSTRAINTS
40
TABLE_PRIVILEGES
41
TRIGGERS
42
create database `inf%`;
43
create database mbase;
44 45 46
use `inf%`;
show tables;
Tables_in_inf%
47
grant all privileges on `inf%`.* to 'mysqltest_1'@'localhost';
48
grant all privileges on `mbase`.* to 'mysqltest_1'@'localhost';
49 50 51 52 53 54 55 56
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);
57 58 59 60 61 62 63 64 65 66 67
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));
68
select * from information_schema.tables;
69 70 71 72
call mbase.p1();
call mbase.p1();
call mbase.p1();
use `inf%`;
73
drop user mysqltest_1@localhost;
74 75 76 77
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
78
v1	VIEW	VIEW
79 80 81
select table_name, table_type, table_comment from information_schema.tables
where table_schema='inf%' and func2();
table_name	table_type	table_comment
82
v1	VIEW	VIEW
83 84
drop view v1;
drop function func1;
85
drop function func2;
86
drop database `inf%`;
87 88 89 90 91 92 93 94 95 96 97 98 99 100 101
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
102
v2	VIEW	VIEW
103 104 105 106
drop table t1;
select table_name, table_type, table_comment from information_schema.tables
where table_schema='test';
table_name	table_type	table_comment
107 108
v1	VIEW	VIEW
v2	VIEW	VIEW
109 110 111
drop function f1;
drop function f2;
drop view v1, v2;
112 113 114 115 116 117 118 119 120
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;
121 122 123 124 125 126 127 128 129 130
create view v5 as select f1 from t1;
grant 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 the SUPER privilege for this operation
use testdb_1;
create view v6 as select f1 from t1;
grant 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:
unknown's avatar
unknown committed
131
Note	1449	The user specified as a definer ('no_such_user'@'no_such_host') does not exist
132 133 134 135
show fields from testdb_1.v6;
Field	Type	Null	Key	Default	Extra
f1	char(4)	YES		NULL	
show create view testdb_1.v6;
unknown's avatar
unknown committed
136 137
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
138
show create view testdb_1.v7;
unknown's avatar
unknown committed
139 140
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
141 142 143 144
Warnings:
Warning	1356	View 'testdb_1.v7' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
show fields from testdb_1.v7;
Field	Type	Null	Key	Default	Extra
145
f1	char(4)	YES		NULL	
146
Warnings:
unknown's avatar
unknown committed
147
Note	1449	The user specified as a definer ('no_such_user'@'no_such_host') does not exist
148 149 150 151 152
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;
153 154 155
show fields from testdb_1.v5;
Field	Type	Null	Key	Default	Extra
show create view testdb_1.v5;
unknown's avatar
unknown committed
156 157
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
158 159 160
show fields from testdb_1.v6;
Field	Type	Null	Key	Default	Extra
show create view testdb_1.v6;
unknown's avatar
unknown committed
161 162
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
163 164
show fields from testdb_1.v7;
Field	Type	Null	Key	Default	Extra
165
f1	char(4)	YES		NULL	
166
Warnings:
unknown's avatar
unknown committed
167
Note	1449	The user specified as a definer ('no_such_user'@'no_such_host') does not exist
168
show create view testdb_1.v7;
unknown's avatar
unknown committed
169 170
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
171 172
Warnings:
Warning	1356	View 'testdb_1.v7' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
173
revoke insert(f1) on v3 from testdb_2@localhost;
174 175 176 177 178 179 180 181 182 183 184 185 186 187 188
revoke show view on v5 from testdb_2@localhost;
use testdb_1;
revoke 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'
189 190 191
show create view v4;
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
show fields from v4;
192
Field	Type	Null	Key	Default	Extra
193
f1	char(4)	YES		NULL	
194
f2	char(4)	YES		NULL	
195 196 197 198 199 200 201
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;
unknown's avatar
unknown committed
202 203
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_2`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v2` AS select `v1`.`f1` AS `f1` from `testdb_1`.`v1`	latin1	latin1_swedish_ci
204 205 206 207 208 209 210 211 212
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
213
select `v1`.`f1` AS `f1` from `testdb_1`.`v1`
214 215 216 217 218
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
219 220
use test;
drop view testdb_1.v1, v2, testdb_1.v3, v4;
221 222
drop database testdb_1;
drop user testdb_1@localhost;
unknown's avatar
unknown committed
223
drop user testdb_2@localhost;
224 225 226 227 228 229 230 231
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
Sergey Glukhov's avatar
Sergey Glukhov committed
232
testdb_1	v1	select `testdb_1`.`t1`.`a` AS `a` from `testdb_1`.`t1`
233
show create view testdb_1.v1;
Sergey Glukhov's avatar
Sergey Glukhov committed
234 235
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
236 237 238 239 240 241 242 243 244
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;