Movatterモバイル変換

SHOW GRANTS    [FORuser_or_role        [USINGrole [,role] ...]]user_or_role: {user (see Section 8.2.4, “Specifying Account Names”)  |role (see Section 8.2.5, “Specifying Role Names”.}

This statement displays the privileges and roles that are assigned to a MySQL user account or role, in the form ofGRANT statements that must be executed to duplicate the privilege and role assignments.

SHOW GRANTS requires theSELECT privilege for themysql system schema, except to display privileges and roles for the current user.

To name the account or role forSHOW GRANTS, use the same format as for theGRANT statement (for example,'jeffrey'@'localhost'):

mysql> SHOW GRANTS FOR 'jeffrey'@'localhost';+------------------------------------------------------------------+| Grants for jeffrey@localhost                                     |+------------------------------------------------------------------+| GRANT USAGE ON *.* TO `jeffrey`@`localhost`                      || GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `jeffrey`@`localhost` |+------------------------------------------------------------------+

The host part, if omitted, defaults to'%'. For additional information about specifying account and role names, seeSection 8.2.4, “Specifying Account Names”, andSection 8.2.5, “Specifying Role Names”.

To display the privileges granted to the current user (the account you are using to connect to the server), you can use any of the following statements:

SHOW GRANTS;SHOW GRANTS FOR CURRENT_USER;SHOW GRANTS FOR CURRENT_USER();

IfSHOW GRANTS FOR CURRENT_USER (or any equivalent syntax) is used in definer context, such as within a stored procedure that executes with definer rather than invoker privileges, the grants displayed are those of the definer and not the invoker.

In MySQL 8.0 compared to previous series,SHOW GRANTS no longer displaysALL PRIVILEGES in its global-privileges output because the meaning ofALL PRIVILEGES at the global level varies depending on which dynamic privileges are defined. Instead,SHOW GRANTS explicitly lists each granted global privilege:

mysql> SHOW GRANTS FOR 'root'@'localhost';+---------------------------------------------------------------------+| 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                                                              || GRANT PROXY ON ''@'' TO `root`@`localhost` WITH GRANT OPTION        |+---------------------------------------------------------------------+

Applications that processSHOW GRANTS output should be adjusted accordingly.

At the global level,GRANT OPTION applies to all granted static global privileges if granted for any of them, but applies individually to granted dynamic privileges.SHOW GRANTS displays global privileges this way:

With the optionalUSING clause,SHOW GRANTS enables you to examine the privileges associated with roles for the user. Each role named in theUSING clause must be granted to the user.

Suppose that useru1 is assigned rolesr1 andr2, as follows:

CREATE ROLE 'r1', 'r2';GRANT SELECT ON db1.* TO 'r1';GRANT INSERT, UPDATE, DELETE ON db1.* TO 'r2';CREATE USER 'u1'@'localhost' IDENTIFIED BY 'u1pass';GRANT 'r1', 'r2' TO 'u1'@'localhost';

SHOW GRANTS withoutUSING shows the granted roles:

mysql> SHOW GRANTS FOR 'u1'@'localhost';+---------------------------------------------+| Grants for u1@localhost                     |+---------------------------------------------+| GRANT USAGE ON *.* TO `u1`@`localhost`      || GRANT `r1`@`%`,`r2`@`%` TO `u1`@`localhost` |+---------------------------------------------+

Adding aUSING clause causes the statement to also display the privileges associated with each role named in the clause:

mysql> SHOW GRANTS FOR 'u1'@'localhost' USING 'r1';+---------------------------------------------+| Grants for u1@localhost                     |+---------------------------------------------+| GRANT USAGE ON *.* TO `u1`@`localhost`      || GRANT SELECT ON `db1`.* TO `u1`@`localhost` || GRANT `r1`@`%`,`r2`@`%` TO `u1`@`localhost` |+---------------------------------------------+mysql> SHOW GRANTS FOR 'u1'@'localhost' USING 'r2';+-------------------------------------------------------------+| Grants for u1@localhost                                     |+-------------------------------------------------------------+| GRANT USAGE ON *.* TO `u1`@`localhost`                      || GRANT INSERT, UPDATE, DELETE ON `db1`.* TO `u1`@`localhost` || GRANT `r1`@`%`,`r2`@`%` TO `u1`@`localhost`                 |+-------------------------------------------------------------+mysql> SHOW GRANTS FOR 'u1'@'localhost' USING 'r1', 'r2';+---------------------------------------------------------------------+| Grants for u1@localhost                                             |+---------------------------------------------------------------------+| GRANT USAGE ON *.* TO `u1`@`localhost`                              || GRANT SELECT, INSERT, UPDATE, DELETE ON `db1`.* TO `u1`@`localhost` || GRANT `r1`@`%`,`r2`@`%` TO `u1`@`localhost`                         |+---------------------------------------------------------------------+

MySQL 8.0.16 and higher supports partial revokes of global privileges, such that a global privilege can be restricted from applying to particular schemas (seeSection 8.2.12, “Privilege Restriction Using Partial Revokes”). To indicate which global schema privileges have been revoked for particular schemas,SHOW GRANTS output includesREVOKE statements:

mysql> SET PERSIST partial_revokes = ON;mysql> CREATE USER u1;mysql> GRANT SELECT, INSERT, DELETE ON *.* TO u1;mysql> REVOKE SELECT, INSERT ON mysql.* FROM u1;mysql> REVOKE DELETE ON world.* FROM u1;mysql> SHOW GRANTS FOR u1;+--------------------------------------------------+| Grants for u1@%                                  |+--------------------------------------------------+| GRANT SELECT, INSERT, DELETE ON *.* TO `u1`@`%`  || REVOKE SELECT, INSERT ON `mysql`.* FROM `u1`@`%` || REVOKE DELETE ON `world`.* FROM `u1`@`%`         |+--------------------------------------------------+

SHOW GRANTS does not display privileges that are available to the named account but are granted to a different account. For example, if an anonymous account exists, the named account might be able to use its privileges, butSHOW GRANTS does not display them.

SHOW GRANTS displays mandatory roles named in themandatory_roles system variable value as follows:

This behavior is for the benefit of applications that use the output ofSHOW GRANTS FORuser to determine which privileges are granted explicitly to the named user. Were that output to include mandatory roles, it would be difficult to distinguish roles granted explicitly to the user from mandatory roles.

For the current user, applications can determine privileges with or without mandatory roles by usingSHOW GRANTS orSHOW GRANTS FOR CURRENT_USER, respectively.