MySQL's SHOW GRANTS shows the permissions of the current user.

Is there a way to log in as root and show the permissions of all users?


10 Answers 10


Nothing built-in. You have two options though:

  • Use common_schema's sql_show_grants view. For example, you can query:

    SELECT sql_grants FROM common_schema.sql_show_grants;

    Or you can query for particular users, for example:

    SELECT sql_grants FROM common_schema.sql_show_grants WHERE user='app';

    To install common_schema, follow the instructions here.

    Disclaimer: I am author of this tool.

  • Use Percona Toolkit's pt-show-grants, for example:

    pt-show-grants --host localhost --user root --ask-pass

In both cases you can ask for the GRANT command or the REVOKE (opposite) command.

The first case requires that you install a schema, the latter requires that you install PERL scripts + dependencies.

  • 19
    Could you please describe in more detail how to use common_schema's sql_show_grants view? I get an error ERROR 1146 (42S02): Table 'common_schema.sql_show_grants' doesn't exist Oct 24, 2013 at 19:52
  • 3
    @MartinVegter , have you installed common_schema? Download here and install following these instructions. Oct 25, 2013 at 5:04
  • 2
    @ThorSummoner, it is unfortunate that the MySQL's default value for group_concat_max_len is 1024. It is one of the first defaults to change, and I recommend that you set it to a high value such as 1,000,000 or above. See further in my blog Dec 5, 2015 at 12:28
  • 3
    Sorry, but there's no such thing as a 'common_schema'. It doesn't exist.
    – SineSwiper
    Sep 22, 2017 at 21:00
  • 2
    link sql_show_grants broken
    – Cyzanfar
    Jan 6, 2018 at 2:13
select * from information_schema.user_privileges;


As mentioned by Shlomi Noach:

It does not list database-specific, table-specific, column-specific, routine-specific privileges. Therefore, the grant GRANT SELECT ON mydb.* TO myuser@localhost does not show in information_schema.user_privileges. The common_schema solution presented above aggregates the data from user_privileges and other tables to give you the full picture.

  • 5
    Sorry, it should not be the accepted answer. information_schema.user_privileges only lists user-level privileges, such as SUPER, RELOAD etc. It also lists all-round DML grants like SELECT. It does not list database-specific, table-sepcific, column-specific, routine-specific privileges. There fore, the grant GRANT SELECT ON mydb.* TO myuser@localhost does not show on information_schema.user_privileges. The common_schema solution presented above aggregates data from user_privileges and other tables to give you the full picture. Jul 9, 2015 at 11:22
  • 3
    The common_schema does not exist on the official MySQL table set, so I disagree with taking away the checkmark here. Not everybody has access to install custom databases like that.
    – SineSwiper
    Oct 20, 2020 at 15:33
  • 1
    Best short answer and should be the accepted one. Dec 10, 2021 at 12:24
  • Now I wish this table was in first normal form ... May 18 at 10:46

This Linux shell fragment loops over all MySQL users and does a SHOW GRANTS for each:

mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user" | sort | \
while read u
 do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'

Works best if you can connect to MySQL without a password.

Output is formatted so it can be run in a MySQL shell. Caution: Output also contains the MySQL root user permissions and password! Remove those lines if you don't want the MySQL root user changed.

  • 6
    You may want to add some details about what this does or how it answers the question. Just displaying a bunch of code doesn't help anyone understand why your solution works.
    – Hannah Vernon
    Sep 9, 2013 at 23:13
  • Where can I give password? Feb 1, 2017 at 15:05
  • To provide a password one may use a Option File or the --password flag of the mysql command.
    – mleu
    Feb 2, 2017 at 13:08
  • Would it not be possible to give one root password and run the query to get all users grants? Feb 3, 2017 at 11:33
  • 3
    You can stream the requests in order to make only one connection, and use a mode 400 root-owned credentials file. My version: mysql --defaults-file=/auth/root-mysql.cnf --batch --skip-column-names --execute "SELECT User, Host from mysql.user" | while read user host; do echo "SHOW GRANTS FOR '${user}'@'${host}';"; done | mysql --defaults-file=/auth/root-mysql.cnf --batch | sed 's/^Grants for/-- Grants for/'
    – BaseZen
    May 16, 2018 at 19:05

One liner (change -uroot to -u$USER_NAME for use with other user) in a Unix bash (because of the backticks):

mysql -uroot -p -sNe"`mysql -uroot -p -se"SELECT CONCAT('SHOW GRANTS FOR \'',user,'\'@\'',host,'\';') FROM mysql.user;"`"

or without backticks and with password inline (space in front of command excludes it from Bash history in Ubuntu):

 mysql -uroot -p"$PASSWORD" -sNe"$(mysql -uroot -p"$PASSWORD" -se"SELECT CONCAT('SHOW GRANTS FOR \'',user,'\'@\'',host,'\';') FROM mysql.user;")"

In Windows:

mysql -uroot -p -se"SELECT CONCAT('SHOW GRANTS FOR \'',user,'\'@\'',host,'\';') FROM mysql.user;" > grants.sql
mysql -uroot -p < grants.sql
del grants.sql

If you can run the following SELECT statements without error:

/* User-Specific Grants     */   SELECT * FROM mysql.user;
/* Database-Specific Grants */   SELECT * FROM mysql.db;
/* Table-Specific Grants    */   SELECT * FROM mysql.tables_priv;
/* Column-Specific Grants   */   SELECT * FROM mysql.columns_priv;

then feel free to use the following code (below), written in .sql syntax.

I designed this query in an attempt to re-build GRANT statements for all existing permissions (for frequent upkeep during database migration). There are a few issues to be handeld, such as user-password-linking, but because we frequently update passwords, that was not in the scope of this project.

/* Get All Grants/Permissions for MySQL Instance */

/* [Database.Table.Column]-Specific Grants */
    CONCAT("`",gcl.Db,"`") AS 'Database(s) Affected',
    CONCAT("`",gcl.Table_name,"`") AS 'Table(s) Affected',
    gcl.User AS 'User-Account(s) Affected',
    IF(gcl.Host='%','ALL',gcl.Host) AS 'Remote-IP(s) Affected',
    CONCAT("GRANT ",UPPER(gcl.Column_priv)," (",GROUP_CONCAT(gcl.Column_name),") ",
                 "ON `",gcl.Db,"`.`",gcl.Table_name,"` ",
                 "TO '",gcl.User,"'@'",gcl.Host,"';") AS 'GRANT Statement (Reconstructed)'
FROM mysql.columns_priv gcl
GROUP BY CONCAT(gcl.Db,gcl.Table_name,gcl.User,gcl.Host)
/* SELECT * FROM mysql.columns_priv */


/* [Database.Table]-Specific Grants */
    CONCAT("`",gtb.Db,"`") AS 'Database(s) Affected',
    CONCAT("`",gtb.Table_name,"`") AS 'Table(s) Affected',
    gtb.User AS 'User-Account(s) Affected',
    IF(gtb.Host='%','ALL',gtb.Host) AS 'Remote-IP(s) Affected',
        "GRANT ",UPPER(gtb.Table_priv)," ",
        "ON `",gtb.Db,"`.`",gtb.Table_name,"` ",
        "TO '",gtb.User,"'@'",gtb.Host,"';"
    ) AS 'GRANT Statement (Reconstructed)'
FROM mysql.tables_priv gtb
WHERE gtb.Table_priv!=''
/* SELECT * FROM mysql.tables_priv */


/* Database-Specific Grants */
    CONCAT("`",gdb.Db,"`") AS 'Database(s) Affected',
    "ALL" AS 'Table(s) Affected',
    gdb.User AS 'User-Account(s) Affected',
    IF(gdb.Host='%','ALL',gdb.Host) AS 'Remote-IP(s) Affected',
        'GRANT ',
            IF(gdb.Create_tmp_table_priv='Y','CREATE TEMPORARY TABLES',NULL),
            IF(gdb.Lock_tables_priv='Y','LOCK TABLES',NULL),
            IF(gdb.Create_view_priv='Y','CREATE VIEW',NULL),
            IF(gdb.Show_view_priv='Y','SHOW VIEW',NULL),
            IF(gdb.Create_routine_priv='Y','CREATE ROUTINE',NULL),
            IF(gdb.Alter_routine_priv='Y','ALTER ROUTINE',NULL),
        " ON `",gdb.Db,"`.* TO '",gdb.User,"'@'",gdb.Host,"';"
    ) AS 'GRANT Statement (Reconstructed)'
FROM mysql.db gdb
WHERE gdb.Db != ''
/* SELECT * FROM mysql.db */


/* User-Specific Grants */
    "ALL" AS 'Database(s) Affected',
    "ALL" AS 'Table(s) Affected',
    gus.User AS 'User-Account(s) Affected',
    IF(gus.Host='%','ALL',gus.Host) AS 'Remote-IP(s) Affected',
        "GRANT ",
                "ALL PRIVILEGES",
                    IF(gus.Show_db_priv='Y','SHOW DATABASES',NULL),
                    IF(gus.Create_tmp_table_priv='Y','CREATE TEMPORARY TABLES',NULL),
                    IF(gus.Lock_tables_priv='Y','LOCK TABLES',NULL),
                    IF(gus.Repl_slave_priv='Y','REPLICATION SLAVE',NULL),
                    IF(gus.Repl_client_priv='Y','REPLICATION CLIENT',NULL),
                    IF(gus.Create_view_priv='Y','CREATE VIEW',NULL),
                    IF(gus.Show_view_priv='Y','SHOW VIEW',NULL),
                    IF(gus.Create_routine_priv='Y','CREATE ROUTINE',NULL),
                    IF(gus.Alter_routine_priv='Y','ALTER ROUTINE',NULL),
                    IF(gus.Create_user_priv='Y','CREATE USER',NULL),
                    IF(gus.Create_tablespace_priv='Y','CREATE TABLESPACE',NULL)
        " ON *.* TO '",gus.User,"'@'",gus.Host,"' REQUIRE ",
        CASE gus.ssl_type
            WHEN 'ANY' THEN
                "SSL "
            WHEN 'X509' THEN
                "X509 "
                CONCAT_WS("AND ",
                    IF((LENGTH(gus.ssl_cipher)>0),CONCAT("CIPHER '",CONVERT(gus.ssl_cipher USING utf8),"' "),NULL),
                    IF((LENGTH(gus.x509_issuer)>0),CONCAT("ISSUER '",CONVERT(gus.ssl_cipher USING utf8),"' "),NULL),
                    IF((LENGTH(gus.x509_subject)>0),CONCAT("SUBJECT '",CONVERT(gus.ssl_cipher USING utf8),"' "),NULL)
            ELSE "NONE "
        "WITH ",
        IF(gus.Grant_priv='Y',"GRANT OPTION ",""),
        "MAX_QUERIES_PER_HOUR ",gus.max_questions," ",
        "MAX_CONNECTIONS_PER_HOUR ",gus.max_connections," ",
        "MAX_UPDATES_PER_HOUR ",gus.max_updates," ",
        "MAX_USER_CONNECTIONS ",gus.max_user_connections,
    ) AS 'GRANT Statement (Reconstructed)'
FROM mysql.user gus
WHERE gus.Password != ''
/* SELECT * FROM mysql.user gus */

/* TODO: */
/* SELECT * FROM mysql.host ghs */
/* SELECT * FROM mysql.procs_priv gpr */

Happy to answer / verify any questions or concerns

  • I know this is not kosher, but... your script is amazing! Now, all I have to do is to automate it. I'll warm up my bash
    – hanzo2001
    Sep 26, 2018 at 16:34

select * from mysql.user;

Can give you User list and Privileges assigned to each of them, requires access to mysql.user table though and root user has it.

  • 4
    This only gives you the "top level" (server level) privileges. Privileges set on specific schemas are in mysql.db. Privileges on specific tables are in mysql.tables_priv, and so on. So it's not so simple. Aug 28, 2012 at 12:40
  • For rainbow table shenanigans, throw your password hashes from select * from mysql.user into crackstation.net and see the unhashed output.
    – Pacerier
    Jul 7, 2015 at 8:30

This will give you a better view...

mysql> select Host, Db, User, Insert_priv, Update_priv, Delete_priv, Create_tmp_table_priv, Alter_priv from mysql.db limit 1;
| Host | Db   | User | Insert_priv | Update_priv | Delete_priv | Create_tmp_table_priv | Alter_priv |
| %    | test |      | Y           | Y           | Y           | Y                     | Y          |
1 row in set (0.00 sec)
  • This is the best answer as of now. Thank you!
    – Player1
    Jun 12, 2020 at 14:08

As mentioned in this answer, you can run the following set of commands to list the database-specific, table-specific, column-specific and routine-specific privileges of all users. Note that you need to run this from the shell, not the MySQL command prompt.

mysql -u root --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -u root --skip-column-names -A

The advantage of this approach is that you don't need to install additional software.


The command SHOW GRANTS [FOR user] may show any user you want. See here for more detail.


If you are administering databases often, you will likely want to keep tight privileges. You can use a stored procedure to quickly run a check. This example works in mariadb might need a tweak to work with the standard mysql version.

Using the answer from Mansur Ali with a little tweak reordering the columns and adding in some ordering to better organise the output.

Using a root login:

USE mysql;

CREATE PROCEDURE ShowPrivs(start, end)
    SELECT Db, User, Host, Insert_priv, Update_priv, Delete_priv, Create_tmp_table_priv, Alter_priv FROM mysql.db order by Db, Host, User ASC;


You could change the procedure to check mysql.user table instead.

Usage, using a root login:

USE mysql;
CALL ShowPrivs();

I used mysql workbench on Ubuntu to run the create procedure part of this answer.

As an aside and a little off the topic here but, you could also have a procedure to show unknown hosts or users. An example for unknown hosts:

USE mysql;

CREATE PROCEDURE `ShowUnknownHosts`(IN Hosts_String VARCHAR(200))
    SELECT user,host FROM user
    WHERE FIND_IN_SET(host, Hosts_String) = 0;


Usage note: Supply a string of hosts separated by commas so only one set of '' is used:

CALL ShowUnknownHosts('knownhost1,knownhost2');

You could also make the column variable by including another parameter in the procedure and call it with ShowUnknownHosts(user,'user1,user2'); for example.

Not the answer you're looking for? Browse other questions tagged or ask your own question.