Risks of Granting MySQL Authentication Table Permissions and How to Mitigate Them

1 Basic Background

When granting permissions we often give an application user full CRUD privileges on all databases using a statement like the one below. While convenient, this approach introduces significant security risks because the user can manipulate MySQL's internal authentication tables, potentially leading to privilege escalation or database compromise.

create user 'app'@'%' identified by 'app';
grant select,update,delete,insert on *.* to 'app'@'%';

2 Demonstration Verification

Scenario 1

We simulate a privilege‑escalation operation where the app user updates its own record in mysql.user . The update succeeds, but because the user lacks RELOAD or SUPER privileges, the changes are not flushed to memory. When the root user later executes FLUSH PRIVILEGES , the modifications become effective and the app user gains elevated rights.

Operation/User

root user

app user

Login

mysql -S /data/mysql/3306/data/mysqld.sock

mysql -h127.0.0.1 -uapp -papp -P3306

app updates mysql.user

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',Lock_tables_priv='Y' where user='app' and host='%';

Query OK, 1 row affected (0.01 sec)

flush privileges;

ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s) for this operation

root flushes privileges

flush privileges;

Query OK, 0 rows affected (0.00 sec)

app checks its grants

show grants\G

Grants for app@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, LOCK TABLES ON *.* TO app@% WITH GRANT OPTION

Scenario 2

In this scenario the same update is performed, but the privilege changes only take effect after the MySQL server is restarted, which forces the authentication tables to be reloaded from disk.

Operation/User

root user

app user

Login

mysql -S /data/mysql/3306/data/mysqld.sock

mysql -h127.0.0.1 -uapp -papp -P3306

app updates mysql.user (same statement as Scenario 1)

... (same output as Scenario 1) ...

Restart MySQL

restart;

Query OK, 0 rows affected (0.00 sec)

app checks its grants after restart

show grants\G

Grants for app@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, LOCK TABLES ON *.* TO app@% WITH GRANT OPTION

3 Scenario Summary

Granting ordinary users permissions on MySQL authentication tables (e.g., user , db , columns_priv , tables_priv , procs_priv , proxies_priv ) introduces serious security risks.

Even read‑only access to the user table can expose the authentication_string column, allowing attackers to obtain password hashes and perform offline cracking.

In practice, a user with only SELECT on mysql.user discovered that the root@localhost account had an empty password, enabling direct root access.

4 Mitigation Methods

Do not grant any access to authentication tables to non‑admin users, even read‑only.

Follow the principle of least privilege when assigning rights.

MySQL 8.0 introduces the partial_revokes parameter, allowing selective revocation of privileges on the mysql database. Example usage:

-- Create app user with full CRUD on all databases
create user 'app'@'%' identified by 'app';
grant select,update,delete,insert on *.* to 'app'@'%';

-- Enable partial revokes
set global partial_revokes=on;

-- Revoke CRUD privileges on the mysql database from app
revoke select,update,delete,insert on mysql.* from 'app'@'%';

-- Verify current grants for app
show grants for app;

-- Attempt to read mysql.user as app (should fail)
select user,host from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'app'@'127.0.0.1' for table 'user'