SHOW GRANTS
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
On this page
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_
- user for which to show privileges.name' 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
andhost_
need not be quoted if they are legal as unquoted identifiers.name Quotes must be used if a host_
string contains wildcard characters (such asname %
), or a user string contains special characters (such as space or-
) or is a restricted keyword. -
'role_
- role for which to show privilegesname'
Remarks
-
This command can be run on any SingleStore node.
For more information, see Node Requirements for SingleStore Commands. -
The
SHOW GRANTS
output depends on the value of the engine variableprivilege_
.transfer_ mode If the value is left at the default value grant_
, then the output is one row and can include theoption WITH GRANT OPTION
privilege.If the value is per_
, then the output can be two rows.privilege The first row will display the non-transferable privileges. The second row will display the transferable privileges. Note: The user must have
SYSTEM_
privilege to be able to change the value ofVARIABLES_ ADMIN privilege_
.transfer_ mode See GRANT page for more information. -
Refer to 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
privilege 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
privilege 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.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' |
+---------------------------------------------------------------------------------+
Last modified: March 7, 2025