SoFunction
Updated on 2025-05-22

MySQL completely prohibits users from executing KILL

In MySQL, even if the user has onlySELECTPermissions can still be executedSHOW PROCESSLISTandKILLOrder. The reasons are as follows:

1. SHOW PROCESSLIST No special permissions are required

  • By default, any user can executeSHOW PROCESSLIST, even if onlyUSAGEPermissions (minimum permissions).
  • But if MySQL is enabled--skip-show-databaseStartup option, ordinary users may not see processes that do not belong to them.

2. KILL command permission dependency

  • KILLThe execution permission of the command depends onPROCESSorSUPERPermissions：
    • If the user hasPROCESSPermissions, canKILL Your own conversation(i.e., the connection you made yourself).
    • If the user hasSUPERPermissions, canKILL Any conversation(including sessions of other users).
  • Even if only grantedSELECTPermissions, users can stillKILLYour own conversation：
    • existMySQL 5.7+, if the user hasCONNECTION_ADMIN(orSUPERA substitute for permissions), it may still beKILLSession.
    • existMySQL 8.0+, introduces more fine-grained permission control, but by default, some operations may still allowKILL。

3. Why can users with SELECT permissions KILL?

  • Possible reasons：
    1. User implicitly ownedPROCESSPermissions(examinetable confirmation).
    2. Version differences of MySQL(Some versions allow by defaultKILLOwn conversation).
    3. The user belongs to a role (MySQL 8.0+), the role may be awardedPROCESSorKILLPermissions.

How to completely ban users from executing KILL?

Method 1: Clearly revoke PROCESS and SUPER permissions

REVOKE PROCESS, SUPER ON *.* FROM 'query'@'%';
FLUSH PRIVILEGES;
  • This way users can onlySHOW PROCESSLIST, but notKILLAny session (including your own).

Method 2: Limit SHOW PROCESSLIST (optional)

If you want the user to see the process list at all:

REVOKE PROCESS ON *.* FROM 'query'@'%';
FLUSH PRIVILEGES;
  • soSHOW PROCESSLISTOnly display the user's own sessions (not all sessions).

Method 3: Use MySQL 8.0+ fine-grained permissions

In MySQL 8.0+, more precise control can be achieved:

-- prohibit KILL Other sessions
REVOKE SYSTEM_USER, SYSTEM_VARIABLES_ADMIN, SESSION_VARIABLES_ADMIN ON *.* FROM 'query'@'%';

-- Make sure there is no PROCESS Permissions
REVOKE PROCESS ON *.* FROM 'query'@'%';
FLUSH PRIVILEGES;

Verify user permissions

-- View user permissions
SHOW GRANTS FOR 'query'@'%';

-- examine PROCESS Permissions
SELECT * FROM  WHERE user='query'\G
  • ifProcess_priv = 'Y', indicating that the user hasPROCESSPermissions, canKILLOwn conversation.

Summarize

  • SELECTPermissions themselves will not be allowedKILL,butPROCESSPermissions。
  • To prohibitKILL, it must be clearly revokedPROCESSandSUPERPermissions。
  • MySQL 8.0+ provides finer granular permission control, can be more stringently restrictedKILLoperate.

If yourqueryUsers canKILL, please check its full permissions (there may be hiddenPROCESSor role permissions).

The above is the detailed content of the solution to MySQL completely prohibits users from executing KILL. For more information about MySQL prohibiting users from executing KILL, please pay attention to my other related articles!