| Server IP : 172.67.216.182 / Your IP : 172.70.208.58 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/src/mysql-test/suite/funcs_1/r/ |
Upload File : |
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. #################################################################################### SHOW CREATE TABLE processlist; Table Create Table PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` ( `ID` bigint(21) unsigned NOT NULL DEFAULT '0', `USER` varchar(32) 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=TMP_TABLE_ENGINE DEFAULT CHARSET=utf8 SHOW processlist; Id User Host db Command Time State Info ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID root HOST_NAME information_schema Query TIME STATE SHOW processlist SELECT * FROM processlist ORDER BY id; ID USER HOST DB COMMAND TIME STATE INFO ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID root 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 Sleep TIME NULL ID root 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 '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 HY000: The target table v_processlist of the UPDATE is not updatable 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(21) unsigned NOT NULL DEFAULT '0', `USER` varchar(32) 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=TMP_TABLE_ENGINE DEFAULT CHARSET=utf8 SHOW processlist; Id User Host db Command Time State Info ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE 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 HY000: The target table v_processlist of the UPDATE is not updatable 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 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'; Warnings: Warning 1287 Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation. #################################################################################### 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 ddicttestuser1 HOST_NAME information_schema Query TIME STATE 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' SHOW processlist; Id User Host db Command Time State Info ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL ID root HOST_NAME information_schema Sleep TIME NULL 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) #################################################################################### set @orig_sql_mode= @@sql_mode; set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER','')); Warnings: Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. GRANT PROCESS ON *.* TO ''@'localhost'; Warnings: Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. set sql_mode= @orig_sql_mode; Warnings: Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. #################################################################################### 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 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 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'; #################################################################################### 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 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 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' SHOW processlist; Id User Host db Command Time State Info 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 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' SHOW processlist; Id User Host db Command Time State Info 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 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' 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' 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 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 root HOST_NAME information_schema Sleep TIME NULL 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 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 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' SHOW processlist; Id User Host db Command Time State Info ID ddicttestuser2 HOST_NAME information_schema Query TIME STATE SHOW processlist ID ddicttestuser2 HOST_NAME information_schema Sleep TIME NULL 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' 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 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 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' SHOW processlist; Id User Host db Command Time State Info 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 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';