MySQL: Show grants for a user in MySQL

Question:Is there a query to run in MySQL that will show all grants for a User?

Answer: In MySQL, you can use the SHOW GRANTS command to display all grant information for a user. This would display privileges that were assigned to the user using the GRANT command.

Syntax

The syntax for the SHOW GRANTS command in MySQL is:

SHOW GRANTS [ FOR username ]

Parameters or Arguments

user_name

The name of the database account for which to display the grant information.

Note

• To view the privileges of a user (that is not the CURRENT_USER), you must have SELECT privilege in the MySQL database.

Example

Let's look at an example of how to use the SHOW GRANTS command in MySQL to display grant information for a user.

For example:

SHOW GRANTS FOR 'techonthenet';

This example would display all grant information for the user called 'techonthenet. Each row that is returned by the SHOW GRANTS command is the GRANT statement that can be used to recreate the privileges. This is a great way to capture privileges that you may want to save for later.

In this first example, when you don't specify a host for the username, MySQL assumes '%' as the host. So the example above would be equivalent to the following SHOW GRANTS command.

SHOW GRANTS FOR 'techonthenet'@'%';

Now let's look at an example of how to use the SHOW GRANTS command when we want to specify the host.

For example:

SHOW GRANTS FOR 'techonthenet'@'localhost';

This SHOW GRANTS example would return the grant information for the user called 'techonthenet' on the host called