MySQL Show User Privileges

Introduction

By default, MySQL installs and works under the root user with all privileges. However, not everyone who accesses the database should have all rights over the data for security reasons.

MySQL provides methods to create new user accounts and grant privileges over the database. A simple command helps provide valuable information about what privileges the users currently have.

This tutorial shows how to check the user privileges on a MySQL server.

Prerequisites

• Access to the command line/terminal.
• MySQL installed and configured.
• Access to the MySQL root user account.

How to Show Privileges for a User in MySQL?

To show privileges for a user in MySQL:

1. Open the terminal (CTRL+ALT+T) and log into the MySQL server as root:

mysql -u root -p

Provide the root password when prompted, and press Enter to start the MySQL monitor.

GRANT SELECT ON *.* TO <username>;

2. If you know the exact username and host for which you'd like to check the privileges, skip this step. Otherwise, show all users and hosts:

SELECT user,host FROM mysql.user;

Locate the exact username and host for the next step.

3. Use the following statement to check the privileges for a specific user:

SHOW GRANTS FOR <username>@<host>;

For example, to check the permissions for test_user:

SHOW GRANTS FOR test_user;

Without a hostname, the command checks for the default host '%'.

Alternatively, check the permissions for the currently logged in user with:

SHOW GRANTS;

The output prints a table with all the access privileges. The first grant was auto-generated when the user was created, and the administrator assigned all the following rights later.

Conclusion

After this tutorial, you now know how to check the permissions for a specific user in a database. The command is simple to use and works together with other MySQL commands to monitor privilege access.

For more database security topics, check out our list of 10 Database Security Best practices.