EVOLUTION-MANAGER

Edit File: is_user_privileges.result

SHOW TABLES FROM information_schema LIKE 'USER_PRIVILEGES'; Tables_in_information_schema (USER_PRIVILEGES) USER_PRIVILEGES ####################################################################### # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT ####################################################################### DROP VIEW IF EXISTS test.v1; DROP PROCEDURE IF EXISTS test.p1; DROP FUNCTION IF EXISTS test.f1; CREATE VIEW test.v1 AS SELECT * FROM information_schema.USER_PRIVILEGES; CREATE PROCEDURE test.p1() SELECT * FROM information_schema.USER_PRIVILEGES; CREATE FUNCTION test.f1() returns BIGINT BEGIN DECLARE counter BIGINT DEFAULT NULL; SELECT COUNT(*) INTO counter FROM information_schema.USER_PRIVILEGES; RETURN counter; END// # Attention: The printing of the next result sets is disabled. SELECT * FROM information_schema.USER_PRIVILEGES; SELECT * FROM test.v1; CALL test.p1; SELECT test.f1(); DROP VIEW test.v1; DROP PROCEDURE test.p1; DROP FUNCTION test.f1; ######################################################################### # Testcase 3.2.16.1: INFORMATION_SCHEMA.USER_PRIVILEGES layout ######################################################################### DESCRIBE information_schema.USER_PRIVILEGES; Field Type Null Key Default Extra GRANTEE varchar(190) NO TABLE_CATALOG varchar(512) NO PRIVILEGE_TYPE varchar(64) NO IS_GRANTABLE varchar(3) NO SHOW CREATE TABLE information_schema.USER_PRIVILEGES; Table Create Table USER_PRIVILEGES CREATE TEMPORARY TABLE `USER_PRIVILEGES` ( `GRANTEE` varchar(190) NOT NULL DEFAULT '', `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '', `PRIVILEGE_TYPE` varchar(64) NOT NULL DEFAULT '', `IS_GRANTABLE` varchar(3) NOT NULL DEFAULT '' ) ENGINE=MEMORY DEFAULT CHARSET=utf8 SHOW COLUMNS FROM information_schema.USER_PRIVILEGES; Field Type Null Key Default Extra GRANTEE varchar(190) NO TABLE_CATALOG varchar(512) NO PRIVILEGE_TYPE varchar(64) NO IS_GRANTABLE varchar(3) NO SELECT grantee, table_catalog, privilege_type FROM information_schema.user_privileges WHERE table_catalog IS NULL OR table_catalog <> 'def'; grantee table_catalog privilege_type ########################################################################## # Testcases 3.2.16.2+3.2.16.3+3.2.16.4: INFORMATION_SCHEMA.USER_PRIVILEGES # accessible information ########################################################################## DROP DATABASE IF EXISTS db_datadict; CREATE DATABASE db_datadict; DROP USER 'testuser1'@'localhost'; CREATE USER 'testuser1'@'localhost'; DROP USER 'testuser2'@'localhost'; CREATE USER 'testuser2'@'localhost'; DROP USER 'testuser3'@'localhost'; CREATE USER 'testuser3'@'localhost'; GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost'; GRANT SELECT ON mysql.user TO 'testuser1'@'localhost'; GRANT INSERT ON *.* TO 'testuser2'@'localhost'; GRANT UPDATE ON *.* TO 'testuser2'@'localhost'; SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE INSERT IS_GRANTABLE NO GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE UPDATE IS_GRANTABLE NO GRANTEE 'testuser3'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; Host localhost User testuser1 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser2 Password Select_priv N Insert_priv Y Update_priv Y Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser3 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 # # Add GRANT OPTION db_datadict.* to testuser1; GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION; SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE INSERT IS_GRANTABLE NO GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE UPDATE IS_GRANTABLE NO GRANTEE 'testuser3'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; Host localhost User testuser1 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser2 Password Select_priv N Insert_priv Y Update_priv Y Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser3 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 connect testuser1, localhost, testuser1, , db_datadict; SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; Host localhost User testuser1 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser2 Password Select_priv N Insert_priv Y Update_priv Y Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser3 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 SHOW GRANTS; Grants for testuser1@localhost GRANT USAGE ON *.* TO 'testuser1'@'localhost' GRANT SELECT, UPDATE ON `db_datadict`.* TO 'testuser1'@'localhost' WITH GRANT OPTION GRANT SELECT ON `mysql`.`user` TO 'testuser1'@'localhost' # Now add SELECT on *.* to testuser1; connection default; GRANT SELECT ON *.* TO 'testuser1'@'localhost'; # # Here <SELECT NO> is shown correctly for testuser1; SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE SELECT IS_GRANTABLE NO GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE INSERT IS_GRANTABLE NO GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE UPDATE IS_GRANTABLE NO GRANTEE 'testuser3'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; Host localhost User testuser1 Password Select_priv Y Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser2 Password Select_priv N Insert_priv Y Update_priv Y Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser3 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 GRANT SELECT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION; # # Here <SELECT YES> is shown correctly for testuser1; SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE SELECT IS_GRANTABLE YES GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE INSERT IS_GRANTABLE NO GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE UPDATE IS_GRANTABLE NO GRANTEE 'testuser3'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; Host localhost User testuser1 Password Select_priv Y Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv Y References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser2 Password Select_priv N Insert_priv Y Update_priv Y Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser3 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 connection testuser1; SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE SELECT IS_GRANTABLE YES SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; Host localhost User testuser1 Password Select_priv Y Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv Y References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser2 Password Select_priv N Insert_priv Y Update_priv Y Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser3 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 SHOW GRANTS; Grants for testuser1@localhost GRANT SELECT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION GRANT SELECT, UPDATE ON `db_datadict`.* TO 'testuser1'@'localhost' WITH GRANT OPTION GRANT SELECT ON `mysql`.`user` TO 'testuser1'@'localhost' connect testuser2, localhost, testuser2, , db_datadict; SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE INSERT IS_GRANTABLE NO GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE UPDATE IS_GRANTABLE NO SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 'user' SHOW GRANTS; Grants for testuser2@localhost GRANT INSERT, UPDATE ON *.* TO 'testuser2'@'localhost' connect testuser3, localhost, testuser3, , test; SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser3'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; ERROR 42000: SELECT command denied to user 'testuser3'@'localhost' for table 'user' SHOW GRANTS; Grants for testuser3@localhost GRANT USAGE ON *.* TO 'testuser3'@'localhost' # Revoke privileges from testuser1; connection default; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost'; SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE INSERT IS_GRANTABLE NO GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE UPDATE IS_GRANTABLE NO GRANTEE 'testuser3'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; Host localhost User testuser1 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser2 Password Select_priv N Insert_priv Y Update_priv Y Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser3 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 connection testuser1; SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'user' SHOW GRANTS; Grants for testuser1@localhost GRANT USAGE ON *.* TO 'testuser1'@'localhost' CREATE TABLE db_datadict.tb_55 ( c1 TEXT ); ERROR 42000: CREATE command denied to user 'testuser1'@'localhost' for table 'tb_55' SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'user' SHOW GRANTS; Grants for testuser1@localhost GRANT USAGE ON *.* TO 'testuser1'@'localhost' CREATE TABLE db_datadict.tb_66 ( c1 TEXT ); ERROR 42000: CREATE command denied to user 'testuser1'@'localhost' for table 'tb_66' # Add ALL on db_datadict.* (and select on mysql.user) to testuser1; connection default; GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION; GRANT SELECT ON mysql.user TO 'testuser1'@'localhost'; SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE INSERT IS_GRANTABLE NO GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE UPDATE IS_GRANTABLE NO GRANTEE 'testuser3'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; Host localhost User testuser1 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser2 Password Select_priv N Insert_priv Y Update_priv Y Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser3 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 connection testuser1; SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; Host localhost User testuser1 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser2 Password Select_priv N Insert_priv Y Update_priv Y Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser3 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 SHOW GRANTS; Grants for testuser1@localhost GRANT USAGE ON *.* TO 'testuser1'@'localhost' GRANT ALL PRIVILEGES ON `db_datadict`.* TO 'testuser1'@'localhost' WITH GRANT OPTION GRANT SELECT ON `mysql`.`user` TO 'testuser1'@'localhost' CREATE TABLE db_datadict.tb_56 ( c1 TEXT ); ERROR 42000: CREATE command denied to user 'testuser1'@'localhost' for table 'tb_56' USE db_datadict; SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; Host localhost User testuser1 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser2 Password Select_priv N Insert_priv Y Update_priv Y Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser3 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 SHOW GRANTS; Grants for testuser1@localhost GRANT USAGE ON *.* TO 'testuser1'@'localhost' GRANT ALL PRIVILEGES ON `db_datadict`.* TO 'testuser1'@'localhost' WITH GRANT OPTION GRANT SELECT ON `mysql`.`user` TO 'testuser1'@'localhost' CREATE TABLE tb_57 ( c1 TEXT ) ENGINE = <other_engine_type>; # Revoke privileges from testuser1; connection default; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost'; SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE INSERT IS_GRANTABLE NO GRANTEE 'testuser2'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE UPDATE IS_GRANTABLE NO GRANTEE 'testuser3'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; Host localhost User testuser1 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser2 Password Select_priv N Insert_priv Y Update_priv Y Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 Host localhost User testuser3 Password Select_priv N Insert_priv N Update_priv N Delete_priv N Create_priv N Drop_priv N Reload_priv N Shutdown_priv N Process_priv N File_priv N Grant_priv N References_priv N Index_priv N Alter_priv N Show_db_priv N Super_priv N Create_tmp_table_priv N Lock_tables_priv N Execute_priv N Repl_slave_priv N Repl_client_priv N Create_view_priv N Show_view_priv N Create_routine_priv N Alter_routine_priv N Create_user_priv N Event_priv N Trigger_priv N Create_tablespace_priv N Delete_history_priv N ssl_type ssl_cipher x509_issuer x509_subject max_questions 0 max_updates 0 max_connections 0 max_user_connections 0 plugin authentication_string password_expired N is_role N default_role max_statement_time 0.000000 connection testuser1; SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' ORDER BY grantee, table_catalog, privilege_type; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO SELECT * FROM mysql.user WHERE user LIKE 'testuser%' ORDER BY host, user; ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'user' SHOW GRANTS; Grants for testuser1@localhost GRANT USAGE ON *.* TO 'testuser1'@'localhost' CREATE TABLE db_datadict.tb_58 ( c1 TEXT ) ENGINE = <other_engine_type>; USE db_datadict; ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'db_datadict' CREATE TABLE db_datadict.tb_59 ( c1 TEXT ) ENGINE = <other_engine_type>; connection default; disconnect testuser1; disconnect testuser2; disconnect testuser3; DROP USER 'testuser1'@'localhost'; DROP USER 'testuser2'@'localhost'; DROP USER 'testuser3'@'localhost'; DROP DATABASE IF EXISTS db_datadict; ######################################################################################## # Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.USER_PRIVILEGES modifications ######################################################################################## SELECT * FROM information_schema.user_privileges WHERE grantee = '''testuser1''@''localhost'''; SHOW GRANTS FOR 'testuser1'@'localhost'; ERROR 42000: There is no such grant defined for user 'testuser1' on host 'localhost' DROP USER 'testuser1'@'localhost'; CREATE USER 'testuser1'@'localhost'; SELECT * FROM information_schema.user_privileges WHERE grantee = '''testuser1''@''localhost'''; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE USAGE IS_GRANTABLE NO SHOW GRANTS FOR 'testuser1'@'localhost'; Grants for testuser1@localhost GRANT USAGE ON *.* TO 'testuser1'@'localhost' GRANT SELECT, FILE ON *.* TO 'testuser1'@'localhost'; SELECT * FROM information_schema.user_privileges WHERE grantee = '''testuser1''@''localhost'''; GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE SELECT IS_GRANTABLE NO GRANTEE 'testuser1'@'localhost' TABLE_CATALOG def PRIVILEGE_TYPE FILE IS_GRANTABLE NO SHOW GRANTS FOR 'testuser1'@'localhost'; Grants for testuser1@localhost GRANT SELECT, FILE ON *.* TO 'testuser1'@'localhost' DROP USER 'testuser1'@'localhost'; SELECT * FROM information_schema.user_privileges WHERE grantee = '''testuser1''@''localhost'''; SHOW GRANTS FOR 'testuser1'@'localhost'; ERROR 42000: There is no such grant defined for user 'testuser1' on host 'localhost' ######################################################################## # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and # DDL on INFORMATION_SCHEMA tables are not supported ######################################################################## DROP USER 'testuser1'@'localhost'; CREATE USER 'testuser1'@'localhost'; INSERT INTO information_schema.user_privileges SELECT * FROM information_schema.user_privileges; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.user_privileges SET PRIVILEGE_TYPE = 'gaming'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DELETE FROM information_schema.user_privileges WHERE grantee = '''testuser1''@''localhost'''; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.user_privileges; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' CREATE INDEX i1 ON information_schema.user_privileges(grantee); ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' ALTER TABLE information_schema.user_privileges ADD f1 INT; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DROP TABLE information_schema.user_privileges; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' ALTER TABLE information_schema.user_privileges RENAME db_datadict.user_privileges; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' ALTER TABLE information_schema.user_privileges RENAME information_schema.xuser_privileges; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DROP USER 'testuser1'@'localhost';