Check MySQL User Permissions

Luke Hebert

- 2 minutes read - 292 words

When a MySQL application starts throwing Access denied or command denied errors, the fastest way to confirm what a user can actually do is to query the mysql.user table directly. You do not need to guess from GRANT output; the privilege columns tell you exactly which operations are allowed.

Querying the mysql.user Table

Connect as a user with sufficient privileges (typically root or an account with SELECT on the mysql schema), then run:

SELECT host, user, select_priv, update_priv
FROM mysql.user
WHERE user = 'username';

Replace username with the account you are investigating. The result set shows:

  • host – the host pattern the account is bound to (e.g., localhost, %, 10.0.1.%).
  • user – the account name.
  • select_priv – Y if the account has global SELECT privilege, N otherwise.
  • update_priv – Y if the account has global UPDATE privilege, N otherwise.

Reading the Output

Each privilege column uses a simple Y/N flag. A Y means the privilege is granted at the global level. A N does not necessarily mean the user cannot perform the action; they may still hold a database-level or table-level grant. To see those, follow up with:

SELECT * FROM mysql.db WHERE user = 'username';
SELECT * FROM mysql.tables_priv WHERE user = 'username';

Tips

  • If the query returns no rows, the account does not exist in the mysql.user table at all.
  • The host column matters. An account created for localhost will not match a connection coming from a remote IP, even if the username is the same.
  • After making changes with GRANT or REVOKE, run FLUSH PRIVILEGES; so the in-memory grant tables pick up the update.

This approach gives you a fast, scriptable check that works across MySQL 5.7 and 8.0 without relying on the human-readable SHOW GRANTS output.