Skip to main content

SingleStoreDB v8.1

SHOW GRANTS

Show permissions (privileges) for the specified user (or role).

Syntax

SHOW GRANTS [FOR {user | role}]

user:
  'user'@'host_name'
role:
  ROLE 'role_name'

Arguments

  • 'user'@'host_name' - user for which to show privileges. If a hostname is not specified, @'%' will be appended to the user string. For example, 'demo-user' is equivalent to 'demo-user'@'%'.The '%' wildcard matches any hostname. user and host_name need not be quoted if they are legal as unquoted identifiers. Quotes must be used if a host_name string contains wildcard characters (such as %), or a user string contains special characters (such as space or -) or is a restricted keyword.

  • 'role_name' - role for which to show privileges

Remarks

  • This command can be run on any SingleStoreDB node. For more information, see Node Requirements for SingleStoreDB Commands.

  • The SHOW GRANTS output depends on the value of the engine variable privilege_transfer_mode. If the value is left at the default value grant_option, then the output is one row and can include the WITH GRANT OPTION privilege. If the value is per_privilege, then the output can be two rows. The first row will display the non-transferable privileges. The second row will display the transferable privileges.

    Note: The user must have SYSTEM_VARIABLES_ADMIN privilege to be able to change the value of privilege_transfer_mode. See GRANT page for more information.

  • See the Permission Matrix for the required permission.

Example

The SHOW GRANTS command displays the privileges assigned to the current user.

SHOW GRANTS;
****
+---------------------------------------------------------------------+
| Grants for root@%                                                   |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'                           |
+---------------------------------------------------------------------+

+---------------------------------------------------------------------+
| Grants for user1@%                                                  |
+---------------------------------------------------------------------+
| GRANT SELECT, INSERT ON `mydb`.* TO 'user1'@' [WITH GRANT OPTION]   |
| GRANT TRANSFERABLE SELECT, INSERT ON `mydb`.* TO 'user1'@'%'     |
+---------------------------------------------------------------------+

The SHOW GRANTS FOR command displays the privileges assigned to the specified user.

SHOW GRANTS FOR user1;
****
+---------------------------------------------------------------------+
| Grants for user1@%                                                  |
+---------------------------------------------------------------------+
| GRANT SELECT, INSERT ON `mydb`.* TO 'user1'@' [WITH GRANT OPTION]   |
| GRANT TRANSFERABLE SELECT, INSERT ON `mydb`.* TO 'user1'@'%'     |
+---------------------------------------------------------------------+

Users with SUPER or ALTER USER privileges can see the hashed password using the command SHOW GRANTS FOR <user>.

SHOW GRANTS FOR user1;

****
+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@%                                                                                           |
+--------------------------------------------------------------------------------------------------------------+
| GRANT SHOW METADATA ON *.* TO 'user1'@'%' IDENTIFIED BY PASSWORD '*785FBD495FC22B3620EB1572D2504C03B1278554' |
+--------------------------------------------------------------------------------------------------------------+

Users without SUPER or ALTER USER privileges will not be able to see the hashed password.

SHOW GRANTS FOR user1;

****
+---------------------------------------------------------------------------+
| Grants for user1@%                                                        |
+---------------------------------------------------------------------------+
| GRANT SHOW METADATA ON *.* TO 'user1'@'%' IDENTIFIED BY PASSWORD <secret> |
+---------------------------------------------------------------------------+

The SHOW GRANTS FOR ROLE command displays the grants assigned to the role specified in the query. The following query displays the grants assigned to the role “rw”.

SHOW GRANTS FOR ROLE 'rw';
****
+---------------------------------------------------------------------------------+
| Grants for role rw                                                              |
+---------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ROLE 'rw'                                                 |
| GRANT TRANSFERABLE SELECT, INSERT, UPDATE ON `trades`.`company` TO ROLE 'rw' |
+---------------------------------------------------------------------------------+
In this section: