1SHOW TABLES FROM information_schema LIKE 'TABLE_PRIVILEGES'; 2Tables_in_information_schema (TABLE_PRIVILEGES) 3TABLE_PRIVILEGES 4####################################################################### 5# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT 6####################################################################### 7DROP VIEW IF EXISTS test.v1; 8DROP PROCEDURE IF EXISTS test.p1; 9DROP FUNCTION IF EXISTS test.f1; 10CREATE VIEW test.v1 AS SELECT * FROM information_schema.TABLE_PRIVILEGES; 11CREATE PROCEDURE test.p1() SELECT * FROM information_schema.TABLE_PRIVILEGES; 12CREATE FUNCTION test.f1() returns BIGINT 13BEGIN 14DECLARE counter BIGINT DEFAULT NULL; 15SELECT COUNT(*) INTO counter FROM information_schema.TABLE_PRIVILEGES; 16RETURN counter; 17END// 18# Attention: The printing of the next result sets is disabled. 19SELECT * FROM information_schema.TABLE_PRIVILEGES; 20SELECT * FROM test.v1; 21CALL test.p1; 22SELECT test.f1(); 23DROP VIEW test.v1; 24DROP PROCEDURE test.p1; 25DROP FUNCTION test.f1; 26######################################################################### 27# Testcase 3.2.11.1: INFORMATION_SCHEMA.TABLE_PRIVILEGES layout 28######################################################################### 29DESCRIBE information_schema.TABLE_PRIVILEGES; 30Field Type Null Key Default Extra 31GRANTEE varchar(190) NO NULL 32TABLE_CATALOG varchar(512) NO NULL 33TABLE_SCHEMA varchar(64) NO NULL 34TABLE_NAME varchar(64) NO NULL 35PRIVILEGE_TYPE varchar(64) NO NULL 36IS_GRANTABLE varchar(3) NO NULL 37SHOW CREATE TABLE information_schema.TABLE_PRIVILEGES; 38Table Create Table 39TABLE_PRIVILEGES CREATE TEMPORARY TABLE `TABLE_PRIVILEGES` ( 40 `GRANTEE` varchar(190) NOT NULL, 41 `TABLE_CATALOG` varchar(512) NOT NULL, 42 `TABLE_SCHEMA` varchar(64) NOT NULL, 43 `TABLE_NAME` varchar(64) NOT NULL, 44 `PRIVILEGE_TYPE` varchar(64) NOT NULL, 45 `IS_GRANTABLE` varchar(3) NOT NULL 46) ENGINE=MEMORY DEFAULT CHARSET=utf8 47SHOW COLUMNS FROM information_schema.TABLE_PRIVILEGES; 48Field Type Null Key Default Extra 49GRANTEE varchar(190) NO NULL 50TABLE_CATALOG varchar(512) NO NULL 51TABLE_SCHEMA varchar(64) NO NULL 52TABLE_NAME varchar(64) NO NULL 53PRIVILEGE_TYPE varchar(64) NO NULL 54IS_GRANTABLE varchar(3) NO NULL 55SELECT table_catalog, table_schema, table_name, privilege_type 56FROM information_schema.table_privileges WHERE table_catalog IS NOT NULL; 57table_catalog table_schema table_name privilege_type 58def mysql global_priv SELECT 59def mysql global_priv DELETE 60###################################################################### 61# Testcase 3.2.11.2+3.2.11.3+3.2.11.4: 62# INFORMATION_SCHEMA.TABLE_PRIVILEGES accessible information 63###################################################################### 64DROP DATABASE IF EXISTS db_datadict; 65CREATE DATABASE db_datadict; 66CREATE TABLE db_datadict.tb1(f1 INT, f2 INT, f3 INT) 67ENGINE = <engine_type>; 68DROP USER 'testuser1'@'localhost'; 69CREATE USER 'testuser1'@'localhost'; 70GRANT CREATE, SELECT ON db_datadict.* 71TO 'testuser1'@'localhost' WITH GRANT OPTION; 72GRANT SELECT ON db_datadict.tb1 TO 'testuser1'@'localhost'; 73DROP USER 'testuser2'@'localhost'; 74CREATE USER 'testuser2'@'localhost'; 75GRANT ALL ON db_datadict.tb1 TO 'testuser2'@'localhost' WITH GRANT OPTION; 76DROP USER 'testuser3'@'localhost'; 77CREATE USER 'testuser3'@'localhost'; 78connect testuser1, localhost, testuser1, , db_datadict; 79CREATE TABLE tb3 (f1 TEXT) 80ENGINE = <other_engine_type>; 81GRANT SELECT ON db_datadict.tb3 TO 'testuser3'@'localhost'; 82SELECT * FROM information_schema.table_privileges 83WHERE table_name LIKE 'tb%' 84ORDER BY grantee,table_schema,table_name,privilege_type; 85GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 86'testuser1'@'localhost' def db_datadict tb1 SELECT NO 87SHOW GRANTS FOR 'testuser1'@'localhost'; 88Grants for testuser1@localhost 89GRANT USAGE ON *.* TO `testuser1`@`localhost` 90GRANT SELECT, CREATE ON `db_datadict`.* TO `testuser1`@`localhost` WITH GRANT OPTION 91GRANT SELECT ON `db_datadict`.`tb1` TO `testuser1`@`localhost` 92connect testuser2, localhost, testuser2, , db_datadict; 93SELECT * FROM information_schema.table_privileges 94WHERE table_name LIKE 'tb%' 95ORDER BY grantee,table_schema,table_name,privilege_type; 96GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 97'testuser2'@'localhost' def db_datadict tb1 ALTER YES 98'testuser2'@'localhost' def db_datadict tb1 CREATE YES 99'testuser2'@'localhost' def db_datadict tb1 CREATE VIEW YES 100'testuser2'@'localhost' def db_datadict tb1 DELETE YES 101'testuser2'@'localhost' def db_datadict tb1 DELETE HISTORY YES 102'testuser2'@'localhost' def db_datadict tb1 DROP YES 103'testuser2'@'localhost' def db_datadict tb1 INDEX YES 104'testuser2'@'localhost' def db_datadict tb1 INSERT YES 105'testuser2'@'localhost' def db_datadict tb1 REFERENCES YES 106'testuser2'@'localhost' def db_datadict tb1 SELECT YES 107'testuser2'@'localhost' def db_datadict tb1 SHOW VIEW YES 108'testuser2'@'localhost' def db_datadict tb1 TRIGGER YES 109'testuser2'@'localhost' def db_datadict tb1 UPDATE YES 110SHOW GRANTS FOR 'testuser2'@'localhost'; 111Grants for testuser2@localhost 112GRANT USAGE ON *.* TO `testuser2`@`localhost` 113GRANT ALL PRIVILEGES ON `db_datadict`.`tb1` TO `testuser2`@`localhost` WITH GRANT OPTION 114connect testuser3, localhost, testuser3, , db_datadict; 115SELECT * FROM information_schema.table_privileges 116WHERE table_name LIKE 'tb%' 117ORDER BY grantee,table_schema,table_name,privilege_type; 118GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 119'testuser3'@'localhost' def db_datadict tb3 SELECT NO 120SHOW GRANTS FOR 'testuser3'@'localhost'; 121Grants for testuser3@localhost 122GRANT USAGE ON *.* TO `testuser3`@`localhost` 123GRANT SELECT ON `db_datadict`.`tb3` TO `testuser3`@`localhost` 124connection default; 125disconnect testuser1; 126disconnect testuser2; 127disconnect testuser3; 128SELECT * FROM information_schema.table_privileges 129WHERE table_name LIKE 'tb%' 130ORDER BY grantee,table_schema,table_name,privilege_type; 131GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 132'testuser1'@'localhost' def db_datadict tb1 SELECT NO 133'testuser2'@'localhost' def db_datadict tb1 ALTER YES 134'testuser2'@'localhost' def db_datadict tb1 CREATE YES 135'testuser2'@'localhost' def db_datadict tb1 CREATE VIEW YES 136'testuser2'@'localhost' def db_datadict tb1 DELETE YES 137'testuser2'@'localhost' def db_datadict tb1 DELETE HISTORY YES 138'testuser2'@'localhost' def db_datadict tb1 DROP YES 139'testuser2'@'localhost' def db_datadict tb1 INDEX YES 140'testuser2'@'localhost' def db_datadict tb1 INSERT YES 141'testuser2'@'localhost' def db_datadict tb1 REFERENCES YES 142'testuser2'@'localhost' def db_datadict tb1 SELECT YES 143'testuser2'@'localhost' def db_datadict tb1 SHOW VIEW YES 144'testuser2'@'localhost' def db_datadict tb1 TRIGGER YES 145'testuser2'@'localhost' def db_datadict tb1 UPDATE YES 146'testuser3'@'localhost' def db_datadict tb3 SELECT NO 147SHOW GRANTS FOR 'testuser1'@'localhost'; 148Grants for testuser1@localhost 149GRANT USAGE ON *.* TO `testuser1`@`localhost` 150GRANT SELECT, CREATE ON `db_datadict`.* TO `testuser1`@`localhost` WITH GRANT OPTION 151GRANT SELECT ON `db_datadict`.`tb1` TO `testuser1`@`localhost` 152SHOW GRANTS FOR 'testuser2'@'localhost'; 153Grants for testuser2@localhost 154GRANT USAGE ON *.* TO `testuser2`@`localhost` 155GRANT ALL PRIVILEGES ON `db_datadict`.`tb1` TO `testuser2`@`localhost` WITH GRANT OPTION 156SHOW GRANTS FOR 'testuser3'@'localhost'; 157Grants for testuser3@localhost 158GRANT USAGE ON *.* TO `testuser3`@`localhost` 159GRANT SELECT ON `db_datadict`.`tb3` TO `testuser3`@`localhost` 160DROP USER 'testuser1'@'localhost'; 161DROP USER 'testuser2'@'localhost'; 162DROP USER 'testuser3'@'localhost'; 163DROP DATABASE db_datadict; 164################################################################################ 165# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TABLE_PRIVILEGES modifications 166################################################################################ 167DROP TABLE IF EXISTS test.t1_table; 168DROP VIEW IF EXISTS test.t1_view; 169DROP DATABASE IF EXISTS db_datadict; 170CREATE DATABASE db_datadict; 171CREATE TABLE test.t1_table (f1 BIGINT) 172DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci 173COMMENT = 'Initial Comment' ENGINE = <engine_type>; 174CREATE VIEW test.t1_view AS SELECT 1; 175DROP USER 'testuser1'@'localhost'; 176CREATE USER 'testuser1'@'localhost'; 177DROP USER 'the_user'@'localhost'; 178SELECT table_name FROM information_schema.table_privileges 179WHERE table_name LIKE 't1_%'; 180table_name 181GRANT ALL ON test.t1_table TO 'testuser1'@'localhost'; 182GRANT ALL ON test.t1_view TO 'testuser1'@'localhost'; 183SELECT * FROM information_schema.table_privileges 184WHERE table_name LIKE 't1_%' 185ORDER BY grantee, table_schema, table_name, privilege_type; 186GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 187'testuser1'@'localhost' def test t1_table ALTER NO 188'testuser1'@'localhost' def test t1_table CREATE NO 189'testuser1'@'localhost' def test t1_table CREATE VIEW NO 190'testuser1'@'localhost' def test t1_table DELETE NO 191'testuser1'@'localhost' def test t1_table DELETE HISTORY NO 192'testuser1'@'localhost' def test t1_table DROP NO 193'testuser1'@'localhost' def test t1_table INDEX NO 194'testuser1'@'localhost' def test t1_table INSERT NO 195'testuser1'@'localhost' def test t1_table REFERENCES NO 196'testuser1'@'localhost' def test t1_table SELECT NO 197'testuser1'@'localhost' def test t1_table SHOW VIEW NO 198'testuser1'@'localhost' def test t1_table TRIGGER NO 199'testuser1'@'localhost' def test t1_table UPDATE NO 200'testuser1'@'localhost' def test t1_view ALTER NO 201'testuser1'@'localhost' def test t1_view CREATE NO 202'testuser1'@'localhost' def test t1_view CREATE VIEW NO 203'testuser1'@'localhost' def test t1_view DELETE NO 204'testuser1'@'localhost' def test t1_view DELETE HISTORY NO 205'testuser1'@'localhost' def test t1_view DROP NO 206'testuser1'@'localhost' def test t1_view INDEX NO 207'testuser1'@'localhost' def test t1_view INSERT NO 208'testuser1'@'localhost' def test t1_view REFERENCES NO 209'testuser1'@'localhost' def test t1_view SELECT NO 210'testuser1'@'localhost' def test t1_view SHOW VIEW NO 211'testuser1'@'localhost' def test t1_view TRIGGER NO 212'testuser1'@'localhost' def test t1_view UPDATE NO 213SELECT DISTINCT grantee, table_name FROM information_schema.table_privileges 214WHERE table_name LIKE 't1_%' 215ORDER BY grantee, table_name; 216grantee table_name 217'testuser1'@'localhost' t1_table 218'testuser1'@'localhost' t1_view 219RENAME USER 'testuser1'@'localhost' TO 'the_user'@'localhost'; 220FLUSH PRIVILEGES; 221SELECT DISTINCT grantee, table_name FROM information_schema.table_privileges 222WHERE table_name LIKE 't1_%' 223ORDER BY grantee, table_name; 224grantee table_name 225'the_user'@'localhost' t1_table 226'the_user'@'localhost' t1_view 227SHOW GRANTS FOR 'testuser1'@'localhost'; 228ERROR 42000: There is no such grant defined for user 'testuser1' on host 'localhost' 229SHOW GRANTS FOR 'the_user'@'localhost'; 230Grants for the_user@localhost 231GRANT USAGE ON *.* TO `the_user`@`localhost` 232GRANT ALL PRIVILEGES ON `test`.`t1_table` TO `the_user`@`localhost` 233GRANT ALL PRIVILEGES ON `test`.`t1_view` TO `the_user`@`localhost` 234SELECT DISTINCT table_schema,table_name FROM information_schema.table_privileges 235WHERE table_name LIKE 't1_%' 236ORDER BY table_schema,table_name; 237table_schema table_name 238test t1_table 239test t1_view 240RENAME TABLE test.t1_table TO db_datadict.t1_table; 241RENAME TABLE test.t1_view TO db_datadict.t1_view; 242ERROR HY000: Changing schema from 'test' to 'db_datadict' is not allowed 243SELECT DISTINCT table_schema,table_name FROM information_schema.table_privileges 244WHERE table_name LIKE 't1_%' 245ORDER BY table_schema,table_name; 246table_schema table_name 247test t1_table 248test t1_view 249SHOW GRANTS FOR 'the_user'@'localhost'; 250Grants for the_user@localhost 251GRANT USAGE ON *.* TO `the_user`@`localhost` 252GRANT ALL PRIVILEGES ON `test`.`t1_table` TO `the_user`@`localhost` 253GRANT ALL PRIVILEGES ON `test`.`t1_view` TO `the_user`@`localhost` 254REVOKE ALL PRIVILEGES ON test.t1_table FROM 'the_user'@'localhost'; 255REVOKE ALL PRIVILEGES ON test.t1_view FROM 'the_user'@'localhost'; 256DROP VIEW test.t1_view; 257CREATE VIEW db_datadict.t1_view AS SELECT 1; 258GRANT ALL ON db_datadict.t1_table TO 'the_user'@'localhost'; 259GRANT ALL ON db_datadict.t1_view TO 'the_user'@'localhost'; 260SELECT DISTINCT table_name FROM information_schema.table_privileges 261WHERE table_name LIKE 't1_%' 262ORDER BY table_name; 263table_name 264t1_table 265t1_view 266RENAME TABLE db_datadict.t1_table TO db_datadict.t1_tablex; 267RENAME TABLE db_datadict.t1_view TO db_datadict.t1_viewx; 268SELECT DISTINCT table_name FROM information_schema.table_privileges 269WHERE table_name LIKE 't1_%' 270ORDER BY table_name; 271table_name 272t1_table 273t1_view 274RENAME TABLE db_datadict.t1_tablex TO db_datadict.t1_table; 275RENAME TABLE db_datadict.t1_viewx TO db_datadict.t1_view; 276SELECT DISTINCT table_name FROM information_schema.table_privileges 277WHERE table_name LIKE 't1_%' 278ORDER BY table_name; 279table_name 280t1_table 281t1_view 282DROP TABLE db_datadict.t1_table; 283DROP VIEW db_datadict.t1_view; 284SELECT DISTINCT table_name FROM information_schema.table_privileges 285WHERE table_name LIKE 't1_%' 286ORDER BY table_name; 287table_name 288t1_table 289t1_view 290CREATE TABLE db_datadict.t1_table 291ENGINE = <engine_type> AS 292SELECT 1; 293CREATE VIEW db_datadict.t1_view AS SELECT 1; 294GRANT ALL ON db_datadict.t1_table TO 'the_user'@'localhost'; 295GRANT ALL ON db_datadict.t1_view TO 'the_user'@'localhost'; 296SELECT DISTINCT table_name FROM information_schema.table_privileges 297WHERE table_name LIKE 't1_%' 298ORDER BY table_name; 299table_name 300t1_table 301t1_view 302DROP DATABASE db_datadict; 303SELECT DISTINCT table_name FROM information_schema.table_privileges 304WHERE table_name LIKE 't1_%' 305ORDER BY table_name; 306table_name 307t1_table 308t1_view 309DROP USER 'the_user'@'localhost'; 310######################################################################## 311# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and 312# DDL on INFORMATION_SCHEMA table are not supported 313######################################################################## 314DROP DATABASE IF EXISTS db_datadict; 315CREATE DATABASE db_datadict; 316CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT) 317ENGINE = <engine_type>; 318DROP USER 'testuser1'@'localhost'; 319CREATE USER 'testuser1'@'localhost'; 320GRANT SELECT (f1) ON db_datadict.t1 TO 'testuser1'@'localhost'; 321INSERT INTO information_schema.table_privileges 322SELECT * FROM information_schema.table_privileges; 323ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 324UPDATE information_schema.table_privileges SET table_schema = 'test' 325WHERE table_name = 't1'; 326ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 327DELETE FROM information_schema.table_privileges WHERE table_name = 't1'; 328ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 329TRUNCATE information_schema.table_privileges; 330ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 331CREATE INDEX my_idx_on_tables 332ON information_schema.table_privileges(table_schema); 333ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 334ALTER TABLE information_schema.table_privileges ADD f1 INT; 335ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 336DROP TABLE information_schema.table_privileges; 337ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 338ALTER TABLE information_schema.table_privileges 339RENAME db_datadict.table_privileges; 340ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 341ALTER TABLE information_schema.table_privileges 342RENAME information_schema.xtable_privileges; 343ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 344DROP DATABASE db_datadict; 345DROP USER 'testuser1'@'localhost'; 346