Summary: In this tutorial, you will learn how to use MySQL SHOW GRANTS
statement to view the privileges previously granted to a user or role.
Introduction to MySQL SHOW GRANTS statement
The MySQL SHOW GRANTS
statement returns all privileges and roles granted to an account user or role.
Here’s the basic syntax of the SHOW GRANTS
statement:
SHOW GRANTS
[FOR {user | role}
[USING role [, role] ...]]
Code language: SQL (Structured Query Language) (sql)
In this syntax:
- First, specify the name of the user account or role for which you want to display the previously granted privileges after the
FOR
keyword. If you omit theFOR
clause, theSHOW GRANTS
statement returns the privileges of the current user. - Second, use the
USING
clause to inspect the privileges associated with roles for the user. The roles specified in theUSING
clause must have been previously granted to the user.
To execute the SHOW GRANTS
statement, you need to have SELECT
privilege for the mysql
system database, except when you show privileges and roles for the current user.
MySQL SHOW GRANTS statement examples
Let’s take some examples of using the MySQL SHOW GRANTS
statement.
1) Using the SHOW GRANTS statement to display the privileges of the current user
The following statement uses the SHOW GRANTS
statement to display the privileges granted to the current user:
SHOW GRANTS;
Code language: SQL (Structured Query Language) (sql)
If you use the mysql tool, the output may not be readable. To fix it, you can display the output in the vertical layout:
SHOW GRANTS\G
It is equivalent to the following statement:
SHOW GRANTS FOR CURRENT_USER\G
Code language: SQL (Structured Query Language) (sql)
and
SHOW GRANTS FOR CURRENT_USER()\G
Code language: SQL (Structured Query Language) (sql)
Note that both CURRENT_USER
and CURRENT_USER()
functions return the currently logged-in user.
2) Using the SHOW GRANTS statement to display the privileges granted to a user
First, create a new database named vehicles
:
CREATE DATABASE vehicles;
Code language: SQL (Structured Query Language) (sql)
Second, select the database vehicles
:
USE vehicles;
Code language: SQL (Structured Query Language) (sql)
Third, create a new table called cars
in the vehicles
database:
CREATE TABLE cars (
id INT AUTO_INCREMENT,
make VARCHAR(100) NOT NULL,
model VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
);
Code language: SQL (Structured Query Language) (sql)
Fourth, create a new user called musk@localhost
:
CREATE USER musk@localhost
IDENTIFIED BY 'Super1Pass!';
Code language: SQL (Structured Query Language) (sql)
Fifth, show the default privileges granted to the user musk@localhost
:
SHOW GRANTS
FOR musk@localhost;
Code language: SQL (Structured Query Language) (sql)
The GRANT USAGE
is the synonym for no privilege. By default, when a new user is created, it has no privilege.
Sixth, grant all privileges on the vehicles
database to the user musk@localhost
:
GRANT ALL
ON vehicles.*
TO musk@localhost;
Code language: SQL (Structured Query Language) (sql)
Finally, show the privileges granted to the user musk@localhost
:
SHOW GRANTS
FOR musk@localhost;
Code language: SQL (Structured Query Language) (sql)
3) Using the SHOW GRANTS statement to display the privileges granted for a role
First, create a new role called writer@localhost
:
CREATE ROLE writer@localhost;
Code language: SQL (Structured Query Language) (sql)
Second, show privileges granted for the role writer@localhost
:
SHOW GRANTS
FOR writer@localhost;
Code language: SQL (Structured Query Language) (sql)
Third, grant SELECT
, INSERT
, UPDATE
, and DELETE
privileges on the vehicles
database to the writer@localhost
:
GRANT
SELECT,
INSERT,
UPDATE,
DELETE
ON vehicles.*
TO writer@localhost;
Code language: SQL (Structured Query Language) (sql)
Fourth, show privileges granted for the role writer@localhost
:
SHOW GRANTS
FOR writer@localhost;
Code language: SQL (Structured Query Language) (sql)
4) Using SHOW GRANTS with USING clause example
First, create a new account user called jame@localhost
:
CREATE USER jame@localhost
IDENTIFIED BY 'Secret@Pass1';
Code language: SQL (Structured Query Language) (sql)
Second, grant the EXECUTE
privilege to the user jame@localhost
:
GRANT EXECUTE
ON vehicles.*
TO jame@localhost;
Code language: SQL (Structured Query Language) (sql)
Third, grant the role writer@localhost
to the user jame@localhost
:
GRANT writer@localhost
TO jame@localhost;
Code language: SQL (Structured Query Language) (sql)
Fourth, display the privileges granted to the user jame@localhost
:
SHOW GRANTS
FOR jame@localhost;
Code language: SQL (Structured Query Language) (sql)
Finally, use the USING
clause in the SHOW GRANTS
statement to display privileges associated with the writer@localhost
role:
SHOW GRANTS
FOR jame@localhost
USING writer@localhost;
Code language: SQL (Structured Query Language) (sql)
Summary
- Use the MySQL
SHOW GRANTS
statement to display privileges granted to a user or role.