MySQL Show User Privileges

Introduction

By default, MySQL creates a root user with full access to the database. For security reasons, database administrators must restrict access for other users.

MySQL provides methods to create new user accounts and grant privileges over the database. A simple command helps you confirm the privileges assigned to each user.

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.

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 shell.

2. If you know the 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.

Show Privileges for All Users in MySQL

MySQL does not have a direct command to show all users' privileges. For a quick overview, you can query the information_schema.user_privileges table:

SELECT * FROM information_schema.user_privileges;

However, this table only shows global user privileges without database-specific grants.

To see database-specific privileges, list all users and hosts with:

SELECT user, host FROM mysql.user;

Then, loop through each username and host and run SHOW GRANTS for each entry, as shown in the previous section.

Show Privileges Using MySQL Stored Procedure

A MySQL stored procedure is a set of SQL statements saved on the database server. MySQL administrators use stored procedures to automate manual tasks and run complex operations with a single command. Instead of checking privileges manually, you can create a procedure to execute SHOW GRANTS for every database user automatically.

Before creating a procedure, select a database to provide MySQL with a context, for example, the default mysql database:

USE mysql;

In the following example, the ShowPrivileges procedure automatically loops through each user-host pair, executing SHOW GRANTS to display privileges. Enter the code below in the MySQL shell to create the ShowPrivileges stored procedure:

DELIMITER $$

CREATE PROCEDURE ShowPrivileges()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE user_name VARCHAR(255);
    DECLARE host_name VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT user, host FROM mysql.user;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;

    user_loop: LOOP
        FETCH cur INTO user_name, host_name;
        IF done THEN
            LEAVE user_loop;
        END IF;
        
        SET @sql = CONCAT('SHOW GRANTS FOR \'', user_name, '\'@\'', host_name, '\';');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur;
END$$

DELIMITER ;

Invoke the stored procedure with the CALL statement anytime you want to review user privileges:

CALL ShowPrivileges();

The output from the ShowPrivileges procedure displays a complete overview of user privileges, including database-specific grants.

Conclusion

You have learned how to check permissions for a specific user in a database, create a procedure, and review privileges for all users. The commands are straightforward and integrate well with other MySQL commands to help you monitor access privileges.

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