0

After doing some tests and despite not seeing any warning in MySQL documentation, it seems that a user who has UPDATE right on all databases can do updates in the mysql.user table, and so, this user can grant themselves any right they want (they only need to wait for the next FLUSH PRIVILEGES for their changes to take effect).

So taking this setup:

CREATE USER 'nogrant'@'localhost';
GRANT USAGE ON *.* TO 'nogrant'@'localhost';
GRANT UPDATE, SELECT  ON `%`.* TO 'nogrant'@'localhost';
FLUSH PRIVILEGES;

Then the user nogrant can run the following query:

UPDATE mysql.user
SET
    select_priv = 'Y',
    insert_priv = 'Y',
    update_priv = 'Y',
    delete_priv = 'Y',
    create_priv = 'Y',
    drop_priv = 'Y',
    reload_priv = 'Y',
    shutdown_priv = 'Y',
    process_priv = 'Y',
    file_priv = 'Y',
    grant_priv = 'Y',
    references_priv = 'Y',
    index_priv = 'Y',
    alter_priv = 'Y',
    show_db_priv = 'Y',
    super_priv = 'Y',
    create_tmp_table_priv = 'Y',
    lock_tables_priv = 'Y',
    execute_priv = 'Y',
    repl_slave_priv = 'Y',
    repl_client_priv = 'Y', 
    create_view_priv = 'Y',
    show_view_priv = 'Y',
    create_routine_priv = 'Y',
    alter_routine_priv = 'Y',
    create_user_priv = 'Y',
    event_priv = 'Y',
    trigger_priv = 'Y',
    create_tablespace_priv = 'Y'
WHERE user = 'nogrant';

And then, they just have to wait for the next FLUSH PRIVILEGES to get a full access to the database?!

After doing such flush from another legit admin account, SHOW GRANTS on the nogrant user returns:

GRANT ALL PRIVILEGES ON *.* TO 'nogrant'@'localhost' WITH GRANT OPTION

Is there a way to prevent this, and give SELECT and UPDATE right on all databases but the internal ones?

Edit: Alternatively, you can alter other account's rights, change their passwords, host, etc

  • Sorry, no. And REVOKE won't achieve your goal. With a newer version, you might be able to do something tricky with "roles". – Rick James Oct 18 at 23:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.