MySQL - SHOW GRANTS Statement



MySQL SHOW GRANTS Statement

You can assign various privileges or roles to MySQL accounts. You cannot assign both in one statement. To grant privileges to users using this statement you need to have GRANT OPTION privilege.

The MySQL SHOW GRANTS statement is used to display/ retrieve the privileges and roles assigned to a role or an account. To execute this statement, you need SELECT privilege.

Syntax

Following is the syntax of the MySQL SHOW GRANTS Statement −

SHOW GRANTS
   [FOR user_or_role
      [USING role [, role] ...]]

Example

Following example list out all the grants and privileges assigned to the current user −

SHOW GRANTS\G;

Output

Once the query is executed, it will produce the following output −

*************************** 1. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, 
                           DROP, RELOAD, SHUTDOWN, PROCESS, FILE, 
						   REFERENCES,INDEX, ALTER, SHOW DATABASES, SUPER, 
						   CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, 
						   REPLICATION SLAVE, REPLICATION CLIENT, 
						   CREATE VIEW, SHOW VIEW, CREATE ROUTINE, 
						   ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, 
						   CREATE TABLESPACE, CREATE ROLE, DROP ROLE 
						   ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT APPLICATION_PASSWORD_ADMIN,
                           AUDIT_ADMIN, BACKUP_ADMIN,BINLOG_ADMIN,
						   BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN,
						   CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,
						   GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,
						   INNODB_REDO_LOG_ENABLE,
						   PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,
						   REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,
						   RESOURCE_GROUP_USER,ROLE_ADMIN, 
						   SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN
						   ,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,
						   SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,
						   XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` 
						   WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON `db1`.* 
                           TO `root`@`localhost`
*************************** 4. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON `test`.* 
                           TO `root`@`localhost`
*************************** 5. row ***************************
Grants for root@localhost: GRANT PROXY ON ''@'' 
                           TO 'root'@'localhost' WITH GRANT OPTION
*************************** 6. row ***************************
Grants for root@localhost: GRANT `TestRole`@`%`,`TestRole_ReadOnly`@`%` 
                           TO `root`@`localhost`
6 rows in set (0.00 sec)

The FOR clause

Using the FOR clause you can retrieve the list of grants or privileges assigned to a particular user.

Example

Assume we have created a user named 'test_user'@'localhost' in MySQL using the CREATE USER statement −

CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'testpassword';

Now, let us create a database and create a table in it.

CREATE DATABASE test_database;

USE test_database;
Database changed

CREATE TABLE MyTable(data VARCHAR(255));

Following query grants SELECT and INSERT privileges on the table created above, to the user 'test_user'@'localhost −

GRANT SELECT, INSERT ON test_database.MyTable TO 'test_user'@'localhost';

You can verify the granted privileges using the SHOW GRANTS statements −

SHOW GRANTS FOR 'test_user'@'localhost';

Output

Following is the output of the above query −

Grants for test_user@localhost
GRANT USAGE ON *.* TO `test_user`@`localhost`
GRANT SELECT, INSERT ON `test_database`.`mytable` TO `test_user`@`localhost`

Listing the privileges assigned to a role

Using the SHOW GRANTS statement, you can also display the Grants assigned for a role

Example

Following query creates a role named TestRole_ReadOnly.

CREATE ROLE 'TestRole_ReadOnly';

Now, let’s grant read only privilege to the created role using the GRANT statement as −

GRANT SELECT, UPDATE, INSERT ON * . * TO 'TestRole_ReadOnly';

Now, let’s grant read only privilege to the created role using the GRANT statement as −

SHOW GRANTS for 'TestRole_ReadOnly';

Output

The above query produces the output shown below −

Grants for TestRole_ReadOnly@%
GRANT SELECT, INSERT, UPDATE ON *.* TO `TestRole_ReadOnly`@`%`

Example

Assume we have created a user, a procedure and a table with the name sample in the database as follows −

CREATE USER 'sample_user'@'localhost';
//Creating a procedure
DELIMITER //
CREATE PROCEDURE sample ()
   BEGIN
      SELECT 'This is a sample procedure';
   END//
DELIMITER ;

CREATE TABLE sample(data INT);

Following queries grants ALTER ROUTINE, EXECUTE privileges on the above created procedure to the user named 'sample_user'@'localhost'.

GRANT ALTER ROUTINE, EXECUTE ON PROCEDURE test_database.sample 
TO 'sample_user'@'localhost';

Similarly, following query grants SELECT, INSERT and UPDATE privileges on the table sample to the user 'sample_user'@'localhost −

GRANT SELECT, INSERT, UPDATE ON test.sample TO 'sample_user'@'localhost';

You can verify the list of all privileges granted for the user −

SHOW GRANTS FOR 'sample_user'@'localhost';

Output

After executing the above query, it will generate the following output −

Grants for sample_user@localhost
GRANT USAGE ON *.* TO `sample_user`@`localhost`
GRANT SELECT, INSERT, UPDATE ON `test`.`sample` TO `sample_user`@`localhost`
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test_database`.`sample` TO `sample_user`@`localhost`

Following query revokes all the privileges granted to the user 'sample_user'@'localhost −

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'sample_user'@'localhost';

Now, if you verify the grants for the user again you can observe all the above grans are removed from the list −

SHOW GRANTS FOR 'sample_user'@'localhost';

Output

Once the query is executed, it will produce the output shown below −

Grants for sample_user@localhost
GRANT USAGE ON *.* TO `sample_user`@`localhost`

The USING clause

If you use the USING clause in the SHOW GRANTS statement, it lists out the privileges granted to role(s) specified in the clause.

Example

Assume we have created two roles and a user using CREATE statements as −

create user demo;

CREATE ROLE TestRole1;

CREATE ROLE TestRole2;

Now, let’s grant privileges to the created roles using the GRANT statement as −

GRANT SELECT ON * . * TO TestRole1;
GRANT INSERT, UPDATE ON * . * TO TestRole2;

Then, you can GRANT the created role to the user as follows −

GRANT TestRole1, TestRole2 TO demo;

Following query lists the privileges granted for the roles created −

SHOW GRANTS FOR demo USING TestRole1, TestRole2;

Output

The above query produces the following output −

Grants for demo@%
GRANT SELECT, INSERT, UPDATE ON *.* TO `demo`@`%`
GRANT `TestRole1`@`%`,`TestRole2`@`%` TO `demo`@`%`
Advertisements