How to Show Grants in MySQL

MySQL privileges determine what actions a user can perform within a database. SHOW GRANTS allows you to review these privileges to ensure users have the correct access. You can:

• View all privileges of a specific user.
• Audit users for security purposes.
• Modify privileges based on current grants.

Before you keep reading, I just wanted to let you know about our modern and fast MySQL database manager, Beekeeper Studio. Its SQL editor autocompletes as you type and keeps a history of every query you’ve run, which is handy when you’re auditing permissions, and it’s free to use for MySQL, download it here.

Basic Syntax of SHOW GRANTS

SHOW GRANTS FOR 'username'@'host';

• ‘username’ refers to the MySQL user.
• ‘host’ refers to the hostname or IP address from which the user connects.

Viewing Grants for a Specific User

Suppose you have a user, ‘john’ who connects from localhost. To see their permissions, use the following query:

SHOW GRANTS FOR 'john'@'localhost';

Expected Output:

+---------------------------------------------------------+
| Grants for john@localhost                               |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `john`@`localhost`                |
| GRANT SELECT, INSERT ON `mydb`.* TO `john`@`localhost`  |
+---------------------------------------------------------+

In this output:

• GRANT USAGE indicates the user has no global privileges.
• GRANT SELECT, INSERT ON mydb.* shows that john has SELECT and INSERT permissions on all tables in the ‘mydb’ database.

Show Grants for All Users (MySQL 5.7 and Later)

In MySQL, there isn’t a single command to directly display grants for all users. However, you can generate the necessary query from the mysql.user table and then use SHOW GRANTS for each user:

SELECT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') 
FROM mysql.user;

This query generates a list of SHOW GRANTS statements for each user in the system. Here’s a sample output:

+--------------------------------------------------+
| CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host |
+--------------------------------------------------+
| SHOW GRANTS FOR 'root'@'localhost';              |
| SHOW GRANTS FOR 'john'@'localhost';              |
| SHOW GRANTS FOR 'admin'@'192.168.1.10';          |
+--------------------------------------------------+

You can then execute the SHOW GRANTS query to view the privileges for a specific user.

Show Grants for the Current User

To show the grants for the user currently connected to the database, you can use the CURRENT_USER() function:

SHOW GRANTS FOR CURRENT_USER();

Expected Output:

+---------------------------------------------------------+
| Grants for current user                                 |
+---------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost`       |
+---------------------------------------------------------+

This command is particularly useful for users who are unsure about their own permissions.

Interpreting the Output

• GRANT USAGE: Indicates the user has no global privileges but might have specific privileges on databases.
• ALL PRIVILEGES: Grants all privileges for a database or globally, except for the GRANT OPTION.
• GRANT OPTION: Allows a user to grant privileges to others.
  Database-specific grants: Permissions like SELECT, INSERT, UPDATE, and DELETE may be granted for a particular database or table.

Understanding MySQL GRANTS

MySQL provides the GRANT statement that allows administrators to grant privileges to users.

There’s also the inverse REVOKE statement to revoke privileges.

However, before changing permissions, we need to know what permissions are currently in place – and this is where the SHOW GRANTS command comes in.

The SHOW GRANTS command displays the GRANT statement that must be issued to duplicate a user’s privileges.

This command is compatible with MySQL and MariaDB database engines.

Conclusion

To summarise, the SHOW GRANTS command in MySQL is used to display user privileges. It is an important tool for database administrators to audit and manage user permissions.

Other articles you may enjoy:

• How to Create a User in MariaDB
• How to use MariaDB with Docker