Edit File: processlist_priv_ps.result
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' = PASSWORD('ddictpass'); SET PASSWORD FOR ddicttestuser2@'localhost' = PASSWORD('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. #################################################################################### SHOW CREATE TABLE processlist; Table Create Table PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` ( `ID` bigint(4) NOT NULL DEFAULT '0', `USER` varchar(16) NOT NULL DEFAULT '', `HOST` varchar(64) NOT NULL DEFAULT '', `DB` varchar(64) DEFAULT NULL, `COMMAND` varchar(16) NOT NULL DEFAULT '', `TIME` int(7) NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, `INFO` longtext ) ENGINE=MyISAM DEFAULT CHARSET=utf8 SHOW processlist; Id User Host db Command Time State Info ID root HOST_NAME information_schema Query TIME NULL SHOW processlist ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL SELECT * FROM processlist ORDER BY id; ID USER HOST DB COMMAND TIME STATE INFO ID root HOST_NAME information_schema Execute TIME executing SELECT * FROM processlist ORDER BY id ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id; ID USER HOST DB COMMAND TIME STATE INFO ID root HOST_NAME information_schema Execute TIME executing SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL CREATE TEMPORARY TABLE test.t_processlist AS SELECT * FROM processlist; 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 ALL PRIVILEGES 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 ALTER DATABASE information_schema UPGRADE DATA DIRECTORY NAME; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' #################################################################################### 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(4) NOT NULL DEFAULT '0', `USER` varchar(16) NOT NULL DEFAULT '', `HOST` varchar(64) NOT NULL DEFAULT '', `DB` varchar(64) DEFAULT NULL, `COMMAND` varchar(16) NOT NULL DEFAULT '', `TIME` int(7) NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, `INFO` longtext ) ENGINE=MyISAM DEFAULT CHARSET=utf8 SHOW processlist; Id User Host db Command Time State Info ID ddicttestuser1 HOST_NAME information_schema Query TIME NULL SHOW processlist SELECT * FROM processlist ORDER BY id; ID USER HOST DB COMMAND TIME STATE INFO ID ddicttestuser1 HOST_NAME information_schema Execute TIME executing SELECT * FROM processlist ORDER BY id SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id; ID USER HOST DB COMMAND TIME STATE INFO ID ddicttestuser1 HOST_NAME information_schema Execute TIME executing SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id CREATE TEMPORARY TABLE test.t_processlist AS SELECT * FROM processlist; 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' IDENTIFIED BY PASSWORD <secret> 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 ALTER DATABASE information_schema UPGRADE DATA DIRECTORY NAME; ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' #################################################################################### 4 Grant PROCESS privilege to ddicttestuser1 connection default (user=root) #################################################################################### GRANT PROCESS ON *.* TO ddicttestuser1@'localhost' IDENTIFIED BY 'ddictpass'; #################################################################################### 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' IDENTIFIED BY PASSWORD <secret> SHOW processlist; Id User Host db Command Time State Info ID ddicttestuser1 HOST_NAME information_schema Query TIME NULL SHOW processlist SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO ID ddicttestuser1 HOST_NAME information_schema Execute TIME executing SELECT * FROM information_schema.processlist #################################################################################### 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' IDENTIFIED BY PASSWORD <secret> SHOW processlist; Id User Host db Command Time State Info ID root HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Query TIME NULL SHOW processlist SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO ID ddicttestuser1 HOST_NAME information_schema Execute TIME executing SELECT * FROM information_schema.processlist ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID root HOST_NAME information_schema Sleep TIME NULL #################################################################################### 5 Grant PROCESS privilege to anonymous user. connection default (user=root) #################################################################################### 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 root 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 HOST_NAME information_schema Query TIME NULL SHOW processlist 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 ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID root HOST_NAME information_schema Sleep TIME NULL #################################################################################### 6 Revoke PROCESS privilege from ddicttestuser1 connection default (user=root) #################################################################################### REVOKE PROCESS ON *.* FROM ddicttestuser1@'localhost' IDENTIFIED BY 'ddictpass'; #################################################################################### 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' IDENTIFIED BY PASSWORD <secret> SHOW processlist; Id User Host db Command Time State Info ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Query TIME NULL SHOW processlist SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO 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 #################################################################################### 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 #################################################################################### 8 Grant SUPER (does not imply PROCESS) privilege to ddicttestuser1 connection default (user=root) #################################################################################### GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost'; #################################################################################### 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' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1' SHOW processlist; Id User Host db Command Time State Info 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 Query TIME NULL SHOW processlist SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO 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 #################################################################################### 9 Revoke SUPER privilege from user ddicttestuser1 connection default (user=root) #################################################################################### REVOKE SUPER ON *.* FROM 'ddicttestuser1'@'localhost'; #################################################################################### 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' IDENTIFIED BY PASSWORD <secret> SHOW processlist; Id User Host db Command Time State Info 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 Query TIME NULL SHOW processlist SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO 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 #################################################################################### 10 Grant SUPER privilege with grant option to user ddicttestuser1. connection default (user=root) #################################################################################### GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost' WITH GRANT OPTION; #################################################################################### 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' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1' 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; #################################################################################### 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' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1' 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' IDENTIFIED BY PASSWORD <secret> SHOW processlist; Id User Host db Command Time State Info ID root 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 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID 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 NULL SHOW processlist SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO ID ddicttestuser2 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 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID 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 root HOST_NAME information_schema Sleep TIME NULL #################################################################################### 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' IDENTIFIED BY PASSWORD <secret> SHOW processlist; Id User Host db Command Time State Info ID ddicttestuser2 HOST_NAME information_schema Sleep TIME NULL ID ddicttestuser2 HOST_NAME information_schema Query TIME NULL SHOW processlist SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO ID ddicttestuser2 HOST_NAME information_schema Execute TIME executing SELECT * FROM information_schema.processlist ID ddicttestuser2 HOST_NAME information_schema Sleep TIME NULL #################################################################################### 11.2 Revoke SUPER,PROCESS,GRANT OPTION privilege from user ddicttestuser1 connection default (user=root) #################################################################################### REVOKE SUPER,PROCESS,GRANT OPTION ON *.* FROM 'ddicttestuser1'@'localhost'; #################################################################################### 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' IDENTIFIED BY PASSWORD <secret> 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 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 Query TIME NULL SHOW processlist SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO 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 #################################################################################### 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' IDENTIFIED BY PASSWORD <secret> SHOW processlist; Id User Host db Command Time State Info 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 ID ddicttestuser1 HOST_NAME information_schema Query TIME NULL SHOW processlist SELECT * FROM information_schema.processlist; ID USER HOST DB COMMAND TIME STATE INFO 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 #################################################################################### 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';
Back to File Manager