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.

  • 22
    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.

  • 7
    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
  • 2
    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, 2022 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?
    – drmaa
    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?
    – drmaa
    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 below MySQL Grant Rebuilder script to expedite getting user permissions from a MySQL server.

  • 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.

Edit (01-Aug-2023)

  • Updated the MySQL Grant Rebuilder script (below) to the latest version I ended up with before taking a position at a different company (which didn't use MySQL at any capacity).
  • Adding a link to the script on GitHub: MySQL - Reverse Engineer MySQL Grants.sql

MySQL Grant Rebuilder (Reverse Engineer Grants)

SET @SPECIFIC_USER := ''; /* If blank, show all user grants, otherwise show only target user's grants */

/* Column-Specific Grants */
  gcl.User AS 'User-Account(s) Affected',
  IF(gcl.Host='%',"ALL",gcl.Host) AS 'Remote-IP(s) Affected',
  CONCAT("`",gcl.Db,"`") AS 'Database(s) Affected',
  CONCAT("`",gcl.Table_name,"`") AS 'Table(s) Affected',
  CONCAT(UPPER(gcl.Column_priv)," (",GROUP_CONCAT(gcl.Column_name ORDER BY UPPER(gcl.Column_name) SEPARATOR ", "),") ") AS 'Column(s) Affected',
  CONCAT("GRANT ",UPPER(gcl.Column_priv)," (",
          GROUP_CONCAT(gcl.Column_name ORDER BY UPPER(gcl.Column_name)),") ",
         "ON `",gcl.Db,"`.`",gcl.Table_name,"` ",
         "TO '",gcl.User,"'@'",gcl.Host,"';") AS 'GRANT Statement (Reconstructed)',
  NOW() AS 'Timestamp'
FROM `mysql`.columns_priv gcl
WHERE true
AND (gcl.User=@SPECIFIC_USER OR 'show_all'=(IF(@SPECIFIC_USER<>'','single_user','show_all')))
GROUP BY CONCAT(gcl.Host,gcl.Db,gcl.User,gcl.Table_name,gcl.Column_priv)
/* SELECT * FROM `mysql`.columns_priv */


/* Table-Specific Grants */
  gtb.User AS 'User-Account(s) Affected',
  IF(gtb.Host='%',"ALL",gtb.Host) AS 'Remote-IP(s) Affected',
  CONCAT("`",gtb.Db,"`") AS 'Database(s) Affected',
  CONCAT("`",gtb.Table_name,"`") AS 'Table(s) Affected',
  "ALL" AS 'Column(s) Affected',
    "GRANT ",UPPER(gtb.Table_priv)," ",
    "ON `",gtb.Db,"`.`",gtb.Table_name,"` ",
    "TO '",gtb.User,"'@'",gtb.Host,"';"
  ) AS 'GRANT Statement (Reconstructed)',
  NOW() AS 'Timestamp'
FROM `mysql`.tables_priv gtb
WHERE gtb.Table_priv!=''
AND (gtb.User=@SPECIFIC_USER OR 'show_all'=(IF(@SPECIFIC_USER<>'','single_user','show_all')))
/* SELECT * FROM `mysql`.tables_priv */


/* Database-Specific Grants */
  gdb.User AS 'User-Account(s) Affected',
  IF(gdb.Host='%',"ALL",gdb.Host) AS 'Remote-IP(s) Affected',
  CONCAT("`",gdb.Db,"`") AS 'Database(s) Affected',
  "ALL" AS 'Table(s) Affected',
  "ALL" AS 'Column(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)',
  NOW() AS 'Timestamp'
FROM `mysql`.db gdb
WHERE gdb.Db != ''
AND (gdb.User=@SPECIFIC_USER OR 'show_all'=(IF(@SPECIFIC_USER<>'','single_user','show_all')))
/* SELECT * FROM `mysql`.db */


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

/* SELECT * FROM `mysql`.user usr */

  /* To-Do (1): Show User-Permissions
  /* SELECT * FROM information_schema.user_privileges privs */

  /* To-Do (2): Function Specific Grants ? */
  /* SELECT * FROM `mysql`.procs_priv gpr */

  /* To-Do (3): Procedure/Routine-Specific Grants*/
  /* SELECT * FROM `mysql`.procs_priv gpr */

  /* To-Do (4): ??? Host-Specific Grants ??? */
  /* SELECT * FROM `mysql`.host ghs */

Happy to answer / verify any questions or concerns

  • 1
    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
  • 1
    it is very amazing
    – Alex
    Feb 1, 2023 at 21:36
  • @hanzo2001 I hope this proved useful in automation!
    – Cavallo
    Aug 2, 2023 at 1:12
  • 1
    @Alex Thanks! Tons of time went into it, lol. I have a handful of other MySQL scripts that I've generalized - They can be seen over on GitHub. Maybe you'll get some use out of them, probably not but who knows!
    – Cavallo
    Aug 2, 2023 at 1:14

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.