126

I know that I can set user's privileges in the following simple way:

grant all on [database name].[table name] to [user name]@[host name];

But how can I see existing privileges?

I need to see data similar to those which are used in grant. In other words I want to know that a given user has a given access to a given table of a given database from a given host.

How can I get it?

3 Answers 3

136

The command SHOW GRANTS [FOR user] is what you're looking for. See the SHOW GRANTS Statement section for more detail.

81

Here is the MySQL Documentation for SHOW GRANTS:

SHOW GRANTS [FOR user]

This statement lists the GRANT statement or statements that must be issued to duplicate the privileges that are granted to a MySQL user account. The account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. If you specify only the user name part of the account name, a host name part of '%' is used. For additional information about specifying account names, see Section 12.5.1.3, “GRANT Syntax”.

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

To list the privileges granted to the account that 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();

As of MySQL 5.0.24, if SHOW GRANTS FOR CURRENT_USER (or any of the equivalent syntaxes) is used in DEFINER context, such as within a stored procedure that is defined with SQL SECURITY DEFINER), the grants displayed are those of the definer and not the invoker.

SHOW GRANTS displays only the privileges granted explicitly to the named account. Other privileges might be available to the account, but they are not displayed. For example, if an anonymous account exists, the named account might be able to use its privileges, but SHOW GRANTS will not display them.

SHOW GRANTS requires the SELECT privilege for the mysql database.

1
  • SHOW GRANTS requires the SELECT privilege for the mysql system database, except to display privileges and roles for the current user. Feb 12, 2019 at 22:16
1

show grants works fine but if you need to know if user has special access from a specific IP/domain, a better command is:

select * from information_schema.user_privileges where grantee like '%username%';

Sometimes, you can mess around creating users like username@localhost and the command above can help on discovering these pitfalls.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .