USE information_schema; #################################################################################### 1 Prepare test. connection default (user=root) #################################################################################### #################################################################################### 1.1 Create two user #################################################################################### DROP USER ddicttestuser1@'localhost'; DROP USER ddicttestuser2@'localhost'; CREATE USER ddicttestuser1@'localhost'; CREATE USER ddicttestuser2@'localhost'; SET PASSWORD FOR ddicttestuser1@'localhost' = 'ddictpass'; SET PASSWORD FOR ddicttestuser2@'localhost' = 'ddictpass'; #################################################################################### 1.2 Establish connection con100 (user=ddicttestuser1 with no PROCESS privilege): #################################################################################### #################################################################################### 2 connection default(user=root with default privileges): SHOW/SELECT shows all processes/threads. #################################################################################### Timeout in wait_condition.inc for SELECT COUNT(*) = 3 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE DB = 'information_schema' AND COMMAND = 'Sleep' AND USER = 'ddicttestuser1' SHOW CREATE TABLE processlist; Table Create Table PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` ( `ID` bigint unsigned NOT NULL DEFAULT '0', `USER` varchar(32) NOT NULL DEFAULT '', `HOST` varchar(261) NOT NULL DEFAULT '', `DB` varchar(64) DEFAULT NULL, `COMMAND` varchar(16) NOT NULL DEFAULT '', `TIME` int NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, `INFO` longtext ) ENGINE=TMP_TABLE_ENGINE DEFAULT CHARSET=utf8mb3 SHOW processlist; Id User Host db Command Time State Info ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL ID root HOST_NAME information_schema Query TIME STATE SHOW processlist Warnings: SELECT * FROM processlist ORDER BY id; ID USER HOST DB COMMAND TIME STATE INFO ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL ID root HOST_NAME information_schema Execute TIME executing SELECT * FROM processlist ORDER BY id Warnings: SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id; ID USER HOST DB COMMAND TIME STATE INFO ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL ID root HOST_NAME information_schema Execute TIME executing SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id Warnings: CREATE TEMPORARY TABLE test.t_processlist AS SELECT * FROM processlist; Warnings: Warning 1287 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead UPDATE test.t_processlist SET user='horst' WHERE id=1 ; INSERT INTO processlist SELECT * FROM test.t_processlist; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DROP TABLE test.t_processlist; CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist WITH CHECK OPTION; ERROR HY000: CHECK OPTION on non-updatable view 'test.v_processlist' CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist; UPDATE test.v_processlist SET TIME=NOW() WHERE id = 1; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DROP VIEW test.v_processlist; UPDATE processlist SET user='any_user' WHERE id=1 ; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DELETE FROM processlist WHERE id=1 ; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' REVOKE ALL ON processlist FROM current_user; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' GRANT INSERT,UPDATE ON processlist TO current_user; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' SHOW GRANTS; Grants for root@localhost GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION GRANT ALLOW_NONEXISTENT_DEFINER,APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_ANY_DEFINER,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION CREATE INDEX i_processlist ON processlist (user); ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DROP TABLE processlist; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' ALTER TABLE processlist DROP COLUMN user; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' ALTER TABLE processlist ADD COLUMN (my_column INT); ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' RENAME TABLE processlist TO new_processlist; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' RENAME TABLE processlist TO files; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' CREATE TABLE new_processlist AS SELECT * FROM processlist; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DROP DATABASE information_schema; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' RENAME DATABASE information_schema TO info_schema; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE information_schema TO info_schema' at line 1 #################################################################################### 3 Switch to connection con100 (user=ddicttestuser1 with no PROCESS privilege): SHOW/SELECT shows only the processes (1) of the user. #################################################################################### SHOW CREATE TABLE processlist; Table Create Table PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` ( `ID` bigint unsigned NOT NULL DEFAULT '0', `USER` varchar(32) NOT NULL DEFAULT '', `HOST` varchar(261) NOT NULL DEFAULT '', `DB` varchar(64) DEFAULT NULL, `COMMAND` varchar(16) NOT NULL DEFAULT '', `TIME` int NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, `INFO` longtext ) ENGINE=TMP_TABLE_ENGINE DEFAULT CHARSET=utf8mb3 SHOW processlist; Id User Host db Command Time State Info ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist Warnings: SELECT * FROM processlist ORDER BY id; ID USER HOST DB COMMAND TIME STATE INFO ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Execute TIME executing SELECT * FROM processlist ORDER BY id Warnings: SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id; ID USER HOST DB COMMAND TIME STATE INFO ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Execute TIME executing SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id Warnings: CREATE TEMPORARY TABLE test.t_processlist AS SELECT * FROM processlist; Warnings: Warning 1287 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead UPDATE test.t_processlist SET user='horst' WHERE id=1 ; INSERT INTO processlist SELECT * FROM test.t_processlist; ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' DROP TABLE test.t_processlist; CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist WITH CHECK OPTION; ERROR HY000: CHECK OPTION on non-updatable view 'test.v_processlist' CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist; UPDATE test.v_processlist SET TIME=NOW() WHERE id = 1; ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' DROP VIEW test.v_processlist; UPDATE processlist SET user='any_user' WHERE id=1 ; ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' DELETE FROM processlist WHERE id=1 ; ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' REVOKE ALL ON processlist FROM current_user; ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' GRANT INSERT,UPDATE ON processlist TO current_user; ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' SHOW GRANTS; Grants for ddicttestuser1@localhost GRANT USAGE ON *.* TO `ddicttestuser1`@`localhost` CREATE INDEX i_processlist ON processlist (user); ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' DROP TABLE processlist; ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' ALTER TABLE processlist DROP COLUMN user; ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' ALTER TABLE processlist ADD COLUMN (my_column INT); ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' RENAME TABLE processlist TO new_processlist; ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' RENAME TABLE processlist TO files; ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' CREATE TABLE new_processlist AS SELECT * FROM processlist; ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' DROP DATABASE information_schema; ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' RENAME DATABASE information_schema TO info_schema; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE information_schema TO info_schema' at line 1 #################################################################################### 4 Grant PROCESS privilege to ddicttestuser1 connection default (user=root) #################################################################################### GRANT PROCESS ON *.* TO ddicttestuser1@'localhost'; #################################################################################### 4.1 Existing connection con100 (ddicttestuser1) The user ddicttestuser1 has the PROCESS privilege, but the connection was established before PROCESS was granted. SHOW/SELECT shows only the processes (1) of the user. #################################################################################### SHOW GRANTS; Grants for ddicttestuser1@localhost GRANT PROCESS ON *.* TO `ddicttestuser1`@`localhost` SHOW processlist; Id User Host db Command Time State Info ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist Warnings: SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Execute TIME executing SELECT * FROM information_schema.processlist Warnings: #################################################################################### 4.2 New connection con101 (ddicttestuser1 with PROCESS privilege) SHOW/SELECT shows all processes/threads. #################################################################################### SHOW GRANTS; Grants for ddicttestuser1@localhost GRANT PROCESS ON *.* TO `ddicttestuser1`@`localhost` SHOW processlist; Id User Host db Command Time State Info ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL ID root HOST_NAME information_schema Sleep TIME NULL Warnings: SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Execute TIME executing SELECT * FROM information_schema.processlist ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL ID root HOST_NAME information_schema Sleep TIME NULL Warnings: #################################################################################### 5 Grant PROCESS privilege to anonymous user. connection default (user=root) #################################################################################### CREATE USER ''@'localhost'; GRANT PROCESS ON *.* TO ''@'localhost'; #################################################################################### 5.1 Establish connection (anonymous1,localhost,'',,information_schema) anonymous user with PROCESS privilege SHOW/SELECT shows all processes/threads. #################################################################################### SHOW GRANTS; Grants for @localhost GRANT PROCESS ON *.* TO ``@`localhost` SHOW processlist; Id User Host db Command Time State Info ID HOST_NAME information_schema Query TIME STATE SHOW processlist ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL ID root HOST_NAME information_schema Sleep TIME NULL Warnings: SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO ID HOST_NAME information_schema Execute TIME executing SELECT * FROM information_schema.processlist ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL ID root HOST_NAME information_schema Sleep TIME NULL Warnings: #################################################################################### 6 Revoke PROCESS privilege from ddicttestuser1 connection default (user=root) #################################################################################### REVOKE PROCESS ON *.* FROM ddicttestuser1@'localhost'; #################################################################################### 6.1 New connection con102 (ddicttestuser1 has no more PROCESS privilege) Again (compared to state before GRANT PROCESS) only the processes of ddicttestuser1 are visible. #################################################################################### SHOW GRANTS; Grants for ddicttestuser1@localhost GRANT USAGE ON *.* TO `ddicttestuser1`@`localhost` SHOW processlist; Id User Host db Command Time State Info ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL Warnings: SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Execute TIME executing SELECT * FROM information_schema.processlist ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL Warnings: #################################################################################### 7 Revoke PROCESS privilege from anonymous user connection default (user=root) #################################################################################### REVOKE PROCESS ON *.* FROM ''@'localhost'; #################################################################################### 7.1 New connection (anonymous2,localhost,'',,information_schema) The anonymous user has no more the PROCESS privilege Again only the processes of the anonymous user are visible. #################################################################################### SHOW GRANTS FOR ''@'localhost'; Grants for @localhost GRANT USAGE ON *.* TO ``@`localhost` SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO ID HOST_NAME information_schema Execute TIME executing SELECT * FROM information_schema.processlist ID HOST_NAME information_schema Sleep TIME NULL ID 1287 HOST_NAME Warnings: #################################################################################### 8 Grant SUPER (does not imply PROCESS) privilege to ddicttestuser1 connection default (user=root) #################################################################################### GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost'; Warnings: Warning 1287 The SUPER privilege identifier is deprecated #################################################################################### 8.1 New connection con103 (ddicttestuser1 with SUPER privilege) Only the processes of ddicttestuser1 user are visible. #################################################################################### SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; Grants for ddicttestuser1@localhost GRANT SUPER ON *.* TO `ddicttestuser1`@`localhost` SHOW processlist; Id User Host db Command Time State Info ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL Warnings: SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Execute TIME executing SELECT * FROM information_schema.processlist ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL Warnings: #################################################################################### 9 Revoke SUPER privilege from user ddicttestuser1 connection default (user=root) #################################################################################### REVOKE SUPER ON *.* FROM 'ddicttestuser1'@'localhost'; Warnings: Warning 1287 The SUPER privilege identifier is deprecated #################################################################################### 9.1 New connection con104 (ddicttestuser1 without SUPER privilege) ddicttestuser1 has no more the SUPER privilege. Only the processes of ddicttestuser1 are visible. #################################################################################### SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; Grants for ddicttestuser1@localhost GRANT USAGE ON *.* TO `ddicttestuser1`@`localhost` SHOW processlist; Id User Host db Command Time State Info ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL Warnings: SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Execute TIME executing SELECT * FROM information_schema.processlist ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL Warnings: #################################################################################### 10 Grant SUPER privilege with grant option to user ddicttestuser1. connection default (user=root) #################################################################################### GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost' WITH GRANT OPTION; Warnings: Warning 1287 The SUPER privilege identifier is deprecated #################################################################################### 10.1 New connection con105 (ddicttestuser1 with SUPER privilege and GRANT OPTION) Try to grant PROCESS privilege to user ddicttestuser2 without having it. #################################################################################### SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; Grants for ddicttestuser1@localhost GRANT SUPER ON *.* TO `ddicttestuser1`@`localhost` WITH GRANT OPTION GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost'; ERROR 28000: Access denied for user 'ddicttestuser1'@'localhost' (using password: YES) #################################################################################### 10.2 Grant SUPER and PROCESS privilege with grant option to user ddicttestuser1 connection default (user=root) #################################################################################### GRANT SUPER,PROCESS ON *.* TO 'ddicttestuser1'@'localhost' WITH GRANT OPTION; Warnings: Warning 1287 The SUPER privilege identifier is deprecated #################################################################################### 10.3 New connection con106 (ddicttestuser1 with SUPER,PROCESS WITH GRANT OPTION) Grant PROCESS privilege to user ddicttestuser2 #################################################################################### SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; Grants for ddicttestuser1@localhost GRANT PROCESS, SUPER ON *.* TO `ddicttestuser1`@`localhost` WITH GRANT OPTION GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost'; #################################################################################### 10.4 New connection con200 (ddicttestuser2 with PROCESS privilege) ddicttestuser2 has now the PROCESS privilege and sees all connections #################################################################################### SHOW GRANTS FOR 'ddicttestuser2'@'localhost'; Grants for ddicttestuser2@localhost GRANT PROCESS ON *.* TO `ddicttestuser2`@`localhost` SHOW processlist; Id User Host db Command Time State Info ID HOST_NAME information_schema Sleep TIME NULL ID HOST_NAME information_schema Sleep TIME NULL ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser2 HOST_NAME information_schema Query TIME STATE SHOW processlist ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL ID root HOST_NAME information_schema Sleep TIME NULL Warnings: SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO ID HOST_NAME information_schema Sleep TIME NULL ID HOST_NAME information_schema Sleep TIME NULL ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser2 HOST_NAME information_schema Execute TIME executing SELECT * FROM information_schema.processlist ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL ID root HOST_NAME information_schema Sleep TIME NULL Warnings: #################################################################################### 11 User ddicttestuser1 revokes PROCESS privilege from user ddicttestuser2 connection ddicttestuser1; #################################################################################### REVOKE PROCESS ON *.* FROM 'ddicttestuser2'@'localhost'; #################################################################################### 11.1 New connection con201 (ddicttestuser2) ddicttestuser2 has no more the PROCESS privilege and can only see own connects #################################################################################### SHOW GRANTS; Grants for ddicttestuser2@localhost GRANT USAGE ON *.* TO `ddicttestuser2`@`localhost` SHOW processlist; Id User Host db Command Time State Info ID 1287 HOST_NAME ID ddicttestuser2 HOST_NAME information_schema Query TIME STATE SHOW processlist ID ddicttestuser2 HOST_NAME information_schema Sleep TIME NULL Warnings: SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO ID 1287 HOST_NAME ID ddicttestuser2 HOST_NAME information_schema Execute TIME executing SELECT * FROM information_schema.processlist ID ddicttestuser2 HOST_NAME information_schema Sleep TIME NULL Warnings: #################################################################################### 11.2 Revoke SUPER,PROCESS,GRANT OPTION privilege from user ddicttestuser1 connection default (user=root) #################################################################################### REVOKE SUPER,PROCESS,GRANT OPTION ON *.* FROM 'ddicttestuser1'@'localhost'; Warnings: Warning 1287 The SUPER privilege identifier is deprecated #################################################################################### 11.3 New connection con107 (ddicttestuser1) ddicttestuser1 has no more the PROCESS privilege and can only see own connects He is also unable to GRANT the PROCESS privilege to ddicttestuser2 #################################################################################### SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; Grants for ddicttestuser1@localhost GRANT USAGE ON *.* TO `ddicttestuser1`@`localhost` GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost'; ERROR 28000: Access denied for user 'ddicttestuser1'@'localhost' (using password: YES) SHOW processlist; Id User Host db Command Time State Info ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL Warnings: SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Execute TIME executing SELECT * FROM information_schema.processlist ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL Warnings: #################################################################################### 12 Revoke the SELECT privilege from user ddicttestuser1 connection default (user=root) #################################################################################### REVOKE SELECT ON *.* FROM 'ddicttestuser1'@'localhost'; #################################################################################### 12.1 New connection con108 (ddicttestuser1) ddicttestuser1 has neither PROCESS nor SELECT privilege Manual says: Each MySQL user has the right to access these tables, but can see only the rows ... Therefore the missing SELECT privilege does not affect SELECTs on PROCESSLIST. #################################################################################### SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; Grants for ddicttestuser1@localhost GRANT USAGE ON *.* TO `ddicttestuser1`@`localhost` SHOW processlist; Id User Host db Command Time State Info ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL Warnings: SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO ID 1287 HOST_NAME ID ddicttestuser1 HOST_NAME information_schema Execute TIME executing SELECT * FROM information_schema.processlist ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL Warnings: #################################################################################### 12.2 Revoke only the SELECT privilege on the information_schema from ddicttestuser1. connection default (user=root) #################################################################################### REVOKE SELECT ON information_schema.* FROM 'ddicttestuser3'@'localhost'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' #################################################################################### connection default (user=root) Cleanup: close connections, DROP USER etc. #################################################################################### DROP USER ddicttestuser1@'localhost'; DROP USER ddicttestuser2@'localhost'; REVOKE USAGE ON *.* FROM ''@'localhost'; DROP USER ''@'localhost';