Managing user privileges in MySQL is a critical task for database administrators. MySQL provides multiple ways to view what permissions a user has, including the SHOW GRANTS statement and querying system tables. This guide will walk you through these methods, complete with examples to help you manage and audit user privileges effectively.
TL;DR
The simplest way to view privileges for a user is by using the SHOW GRANTS statement. This command displays the exact privileges granted to a user in a readable format.
SHOW GRANTS FOR 'username'@'host'; Why Check User Privileges?
Understanding user privileges is critical for:
- Security: Prevent unauthorized access to sensitive data.
- Troubleshooting: Identify issues with permission-related errors.
- Auditing: Ensure compliance with organizational or regulatory policies.
Method 1: Using the SHOW GRANTS Statement
The simplest way to view privileges for a user is by using the SHOW GRANTS statement. This command displays the exact privileges granted to a user in a readable format.
Syntax
SHOW GRANTS FOR 'username'@'host'; username: The MySQL user whose privileges you want to view.host: The host from which the user connects (e.g.,'localhost').
Example
To view privileges for a user named kiran connecting from localhost:
mysql> SHOW GRANTS FOR 'kiran'@'localhost';
+------------------------------------------------------+
| Grants for kiran@localhost |
+------------------------------------------------------+
| GRANT SELECT, INSERT ON company_db.* TO 'kiran'@'localhost' |
| GRANT USAGE ON *.* TO 'kiran'@'localhost' |
+------------------------------------------------------+ Output Breakdown:
GRANT SELECT, INSERT ON company_db.*: The user can read (SELECT) and write (INSERT) to all tables in thecompany_dbdatabase.GRANT USAGE ON *.*: A default privilege allowing the user to connect to the database without specific object-level permissions.
Method 2: Using SHOW GRANTS for the Current User
If you want to check the privileges of the currently logged-in user, use the shorthand command:
SHOW GRANTS; Example
mysql> SHOW GRANTS;
+--------------------------------------------------+
| Grants for kiran@localhost |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'kiran'@'localhost' |
+--------------------------------------------------+This displays the privileges for the user connected to the session.
Method 3: Querying the MySQL System Tables
For more granular privilege details, you can query the mysql.user, mysql.db, and related tables in the mysql system database.
Example: View All User Privileges
SELECT user, host, authentication_string, plugin FROM mysql.user; Output Example:
This shows user credentials, authentication methods, and hosts for all MySQL users.
Example: View Database-Specific Privileges
To see privileges granted at the database level:
SELECT user, host, Db, Select_priv, Insert_priv, Update_priv FROM mysql.db; Output Example:
Combining Methods for Auditing
You can combine the SHOW GRANTS command with system table queries to perform a thorough privilege audit. For instance, verify all users with access to a specific database:
SELECT user, host FROM mysql.db WHERE Db = 'company_db'; Common Use Cases
- Troubleshooting Permission Errors: If a user reports a “permission denied” error, check their privileges with
SHOW GRANTSand adjust as necessary. - Auditing Privileges: Regularly review privileges to ensure no user has more access than required.
- Identifying Superuser Access: Query the
mysql.usertable for users with theGRANT OPTIONprivilege:
SELECT user, host FROM mysql.user WHERE Super_priv = 'Y'; Best Practices
- Grant Minimum Privileges: Follow the principle of least privilege by giving users only the permissions they need.
- Audit Regularly: Use
SHOW GRANTSand system tables to review user access periodically. - Monitor Privilege Escalation: Check for unexpected changes in superuser privileges or grants.
