SHOW GRANTS in MySQL: Verify User Permissions with Real Syntax

mysql show user permissions is the SHOW GRANTS statement used to display privileges and roles assigned to a MySQL user account, outputting GRANT statements.

SHOW GRANTS [FOR user_or_role [USING role [, role] ...]];

Tested on MySQL 8.0.32 on Ubuntu 22.04 with mysql client 8.0.

Where user_or_role follows the format 'user'@'host'. Omitting FOR shows grants for the current user. CURRENT_USER and CURRENT_USER() are synonyms.

Syntax

SHOW GRANTS [FOR user_or_role [USING role [, role] ...]];

Options and Flags

Usage Examples

1. Display grants for the current user

mysql -u admin -p -e "SHOW GRANTS FOR CURRENT_USER;"
# Output example:
# GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION

Run this in any session to confirm your own privileges, particularly after role activation or FLUSH PRIVILEGES.

2. Show grants for a specific user from a remote host

mysql -u root -p -e "SHOW GRANTS FOR 'app_user'@'192.168.1.%';"
# Error if user does not exist:
# ERROR 1141 (42000): There is no such grant defined for user 'app_user'@'192.168.1.%'

Always specify both user and host – omitting host defaults to '%'. Use this to audit production accounts during access reviews.

3. Check grants with explicit role inclusion

mysql -u root -p -e "SHOW GRANTS FOR 'readonly'@'%' USING 'analytic_role';"
# Shows additional privileges that come from the activated role

In MySQL 8.0+, roles are not automatically included unless USING is added. This command reveals effective privileges of a user after role activation.

Troubleshooting & Common Errors

mysql show user permissions — Performance Considerations and Tuning

When retrieving MySQL user permissions via SHOW GRANTS, query latency is typically under 1ms for a single user. Environments with hundreds of users or complex role hierarchies may benefit from explicit tuning. Key performance drivers include network buffer sizing, connection timeouts, and parallelism for bulk lookups.

• Buffer sizes: The client-side mysql tool uses the max_allowed_packet setting to control the maximum result set chunk. To view or adjust: SHOW VARIABLES LIKE 'max_allowed_packet'; (default 64 MB). For large grant lists, increasing this reduces fragmentation.
• Timeouts: Connection and read timeouts abort slow operations. The MySQL Reference Manual (§5.1.8) documents connect_timeout (default 10 s) and net_read_timeout (default 30 s). Tune if repeated SHOW GRANTS calls fail on congested links.
• Parallelism: When scripting permission audits across many users, spawn multiple mysql -e "SHOW GRANTS FOR ..." processes. Monitor with SHOW STATUS LIKE 'Threads_connected'; to avoid exhausting the max_connections limit.
• Batch sizing with roles: Instead of issuing separate SHOW GRANTS for each user, use the USING clause to resolve multiple roles in one call (doc: MySQL 8.2.5). This reduces round trips: SHOW GRANTS FOR 'admin'@'%' USING 'developer', 'readonly';.

The MySQL Reference Manual (Section 15.7.7.22) explicitly warns that SHOW GRANTS accesses the mysql.user and mysql.tables_priv system tables. Regular OPTIMIZE TABLE on those tables can improve scan speed. For TCP-level tuning, the Linux kernel’s net.core.rmem_default and net.core.wmem_default (documented in the kernel networking guide) affect MTU-sized transfers; check with sysctl net.core.rmem_default.

# Example: Tune MySQL client buffer and timeout for permission bulk dump
mysql --defaults-file=/etc/my.cnf 
  --max_allowed_packet=256M 
  --connect-timeout=5 
  -e "SHOW GRANTS FOR CURRENT_USER();"

Multi-Cloud Comparison

# View all grants for a specific user
SHOW GRANTS FOR 'app_user'@'192.168.1.%';

# Query basic user record (no DB/table grants)
SELECT User, Host, plugin, password_last_changed FROM mysql.user WHERE User='app_user';

SELECT User, Host, plugin, account_locked, password_last_changed
FROM mysql.user
WHERE User NOT IN ('mysql.sys', 'mysql.session');

GRANT SHOW GRANTS ON *.* TO 'devops_user'@'localhost';
FLUSH PRIVILEGES;

# Alternatively grant full SELECT (more permissive)
GRANT SELECT ON mysql.* TO 'devops_user'@'localhost';
FLUSH PRIVILEGES;

SHOW GRANTS FOR CURRENT_USER();

# To view all users (requires rds_superuser)
SELECT User, Host FROM mysql.user;

# Generate and execute SHOW GRANTS for all non-system users
mysql -e "SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') FROM mysql.user WHERE user NOT IN ('mysql.sys','mysql.session','root')" | mysql

# For structured output, use a JOIN on privilege tables
SELECT u.User, u.Host, 
       GROUP_CONCAT(DISTINCT CONCAT(db, '.', 'ALL') ORDER BY db SEPARATOR ', ') AS db_grants
FROM mysql.user u
LEFT JOIN mysql.db ON u.User = db.User AND u.Host = db.Host
GROUP BY u.User, u.Host;