Question 305718 in Mysql Question posted in
The official MySQL documentation can be found here.

Unable to drop another MySQL user as root

I’m logged in as root@localhost to my MySQL 8 and can’t revoke super from another super user.

> 1227 - Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

Why is that? And what would I need to do?

Tags:

2

Answers


  1. 0

    This is the problem of privileges where unprivileged user is trying to remove SUPER privilege of another user. To revoke the SUPER privilege, you typically need to have the WITH GRANT OPTION privilege on the SUPER privilege itself.
    Try below commands after connecting to your MySQL:

    // remove super privilege from target user, then try to remove user: 
REVOKE SUPER ON *.* FROM 'target_user'@'localhost';

    If issue still persist, then ensure that the user you are logged in as has the WITH GRANT OPTION on the SUPER privilege.

    GRANT SUPER ON *.* TO 'your_user'@'localhost' WITH GRANT OPTION;

    Then flush the privileges to apply changes

    FLUSH PRIVILEGES;
    Login or Signup to reply.
  2. 0

    The error message tells you exactly what you need: the SYSTEM_USER privilege.

    You can read about it here: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_system-user

    Basically, in MySQL 8.0 a user with SYSTEM_USER privilege is a system user. System users cannot be added or dropped except by another system user.

    So your root@localhost, despite being named root, is apparently not granted the SYSTEM_USER privilege.

    You can check your privileges with SHOW GRANTS;

    MySQL 8.0 changed a lot about accounts and privileges. Even if you’ve used MySQL for a long time, you should study the manual section 6.2 carefully to update your knowledge.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search