mysql: Show GRANTs for all users

Question

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?

score 129 · Answer 1 · edited Jan 26 '17 at 10:37

select * from information_schema.user_privileges;

EDIT:

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.

score 51 · Accepted Answer · edited Sep 03 '19 at 03:01

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.

score 21 · Answer 3 · edited Feb 17 '16 at 07:16

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/$/;/'
done

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.

Cavallo · Answer 4 · 2023-08-02T01:11:54.677

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 /
SELECT
  gcl.User AS 'User-Account(s) Affected',
  IF(gcl.Host='%',"ALL",gcl.Host) AS 'Remote-IP(s) Affected',
  CONCAT("&quot;,gcl.Db,&quot;") AS 'Database(s) Affected',
  CONCAT("&quot;,gcl.Table_name,&quot;") 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 &quot;,gcl.Db,&quot;.&quot;,gcl.Table_name,&quot; ",
         "TO '",gcl.User,"'@'",gcl.Host,"';") AS 'GRANT Statement (Reconstructed)',
  CONCAT_WS(", ","SERVER","DATABASE","TABLE","COLUMN") AS 'Scope',
  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 */
UNION
/* Table-Specific Grants /
SELECT
  gtb.User AS 'User-Account(s) Affected',
  IF(gtb.Host='%',"ALL",gtb.Host) AS 'Remote-IP(s) Affected',
  CONCAT("&quot;,gtb.Db,&quot;") AS 'Database(s) Affected',
  CONCAT("&quot;,gtb.Table_name,&quot;") AS 'Table(s) Affected',
  "ALL" AS 'Column(s) Affected',
  CONCAT(
    "GRANT ",UPPER(gtb.Table_priv)," ",
    "ON &quot;,gtb.Db,&quot;.&quot;,gtb.Table_name,&quot; ",
    "TO '",gtb.User,"'@'",gtb.Host,"';"
  ) AS 'GRANT Statement (Reconstructed)',
  CONCAT_WS(", ","SERVER","DATABASE","TABLE") AS 'Scope',
  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 */
UNION
/* Database-Specific Grants /
SELECT
  gdb.User AS 'User-Account(s) Affected',
  IF(gdb.Host='%',"ALL",gdb.Host) AS 'Remote-IP(s) Affected',
  CONCAT("&quot;,gdb.Db,&quot;") AS 'Database(s) Affected',
  "ALL" AS 'Table(s) Affected',
  "ALL" AS 'Column(s) Affected',
  CONCAT(
    'GRANT ',
    CONCAT_WS(',',
      IF(gdb.Select_priv='Y','SELECT',NULL),
      IF(gdb.Insert_priv='Y','INSERT',NULL),
      IF(gdb.Update_priv='Y','UPDATE',NULL),
      IF(gdb.Delete_priv='Y','DELETE',NULL),
      IF(gdb.Create_priv='Y','CREATE',NULL),
      IF(gdb.Drop_priv='Y','DROP',NULL),
      IF(gdb.Grant_priv='Y','GRANT',NULL),
      IF(gdb.References_priv='Y','REFERENCES',NULL),
      IF(gdb.Index_priv='Y','INDEX',NULL),
      IF(gdb.Alter_priv='Y','ALTER',NULL),
      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),
      IF(gdb.Execute_priv='Y','EXECUTE',NULL),
      IF(gdb.Event_priv='Y','EVENT',NULL),
      IF(gdb.Trigger_priv='Y','TRIGGER',NULL)
    ),
    " ON &quot;,gdb.Db,&quot;. TO '",gdb.User,"'@'",gdb.Host,"';"
  ) AS 'GRANT Statement (Reconstructed)',
  CONCAT_WS(", ","SERVER","DATABASE") AS 'Scope',
  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 */
UNION
/* User-Specific Grants /
SELECT
  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',
  CONCAT(
    "GRANT ",
    IF((usr.Select_priv='N')&(usr.Insert_priv='N')&(usr.Update_priv='N')&(usr.Delete_priv='N')&(usr.Create_priv='N')&(usr.Drop_priv='N')&(usr.Reload_priv='N')&(usr.Shutdown_priv='N')&(usr.Process_priv='N')&(usr.File_priv='N')&(usr.References_priv='N')&(usr.Index_priv='N')&(usr.Alter_priv='N')&(usr.Show_db_priv='N')&(usr.Super_priv='N')&(usr.Create_tmp_table_priv='N')&(usr.Lock_tables_priv='N')&(usr.Execute_priv='N')&(usr.Repl_slave_priv='N')&(usr.Repl_client_priv='N')&(usr.Create_view_priv='N')&(usr.Show_view_priv='N')&(usr.Create_routine_priv='N')&(usr.Alter_routine_priv='N')&(usr.Create_user_priv='N')&(usr.Event_priv='N')&(usr.Trigger_priv='N')&(usr.Create_tablespace_priv='N')&(usr.Grant_priv='N'),
      "USAGE",
      IF((usr.Select_priv='Y')&(usr.Insert_priv='Y')&(usr.Update_priv='Y')&(usr.Delete_priv='Y')&(usr.Create_priv='Y')&(usr.Drop_priv='Y')&(usr.Reload_priv='Y')&(usr.Shutdown_priv='Y')&(usr.Process_priv='Y')&(usr.File_priv='Y')&(usr.References_priv='Y')&(usr.Index_priv='Y')&(usr.Alter_priv='Y')&(usr.Show_db_priv='Y')&(usr.Super_priv='Y')&(usr.Create_tmp_table_priv='Y')&(usr.Lock_tables_priv='Y')&(usr.Execute_priv='Y')&(usr.Repl_slave_priv='Y')&(usr.Repl_client_priv='Y')&(usr.Create_view_priv='Y')&(usr.Show_view_priv='Y')&(usr.Create_routine_priv='Y')&(usr.Alter_routine_priv='Y')&(usr.Create_user_priv='Y')&(usr.Event_priv='Y')&(usr.Trigger_priv='Y')&(usr.Create_tablespace_priv='Y')&(usr.Grant_priv='Y'),
        "ALL PRIVILEGES",
        CONCAT_WS(',',
          IF(usr.Select_priv='Y','SELECT',NULL),
          IF(usr.Insert_priv='Y','INSERT',NULL),
          IF(usr.Update_priv='Y','UPDATE',NULL),
          IF(usr.Delete_priv='Y','DELETE',NULL),
          IF(usr.Create_priv='Y','CREATE',NULL),
          IF(usr.Drop_priv='Y','DROP',NULL),
          IF(usr.Reload_priv='Y','RELOAD',NULL),
          IF(usr.Shutdown_priv='Y','SHUTDOWN',NULL),
          IF(usr.Process_priv='Y','PROCESS',NULL),
          IF(usr.File_priv='Y','FILE',NULL),
          IF(usr.References_priv='Y','REFERENCES',NULL),
          IF(usr.Index_priv='Y','INDEX',NULL),
          IF(usr.Alter_priv='Y','ALTER',NULL),
          IF(usr.Show_db_priv='Y','SHOW DATABASES',NULL),
          IF(usr.Super_priv='Y','SUPER',NULL),
          IF(usr.Create_tmp_table_priv='Y','CREATE TEMPORARY TABLES',NULL),
          IF(usr.Lock_tables_priv='Y','LOCK TABLES',NULL),
          IF(usr.Execute_priv='Y','EXECUTE',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.Event_priv='Y','EVENT',NULL),
          IF(usr.Trigger_priv='Y','TRIGGER',NULL),
          IF(usr.Create_tablespace_priv='Y','CREATE TABLESPACE',NULL)
        )
      )
    ),
    " ON .* TO '",usr.User,"'@'",usr.Host,"' REQUIRE ",
    CASE usr.ssl_type
      WHEN 'ANY' THEN
        "SSL "
      WHEN 'X509' THEN
        "X509 "
      WHEN 'SPECIFIED' THEN
        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 "
    END,
    "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

inemanja · Answer 5 · 2017-02-16T14:35:47.310

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

score 9 · Answer 6 · edited Dec 18 '18 at 17:04

9

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.

edited Dec 18 '18 at 17:04

spuder

253
1
2
9

answered Aug 28 '12 at 11:29

Mahesh Patil

3,078
2
17
23

score 6 · Answer 7 · edited Mar 20 '17 at 05:54

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)

score 2 · Answer 8 · answered Feb 28 '18 at 22:21

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.

score 1 · Answer 9 · answered May 11 '18 at 16:33

1

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

answered May 11 '18 at 16:33

Eugen Konkov

137
8

CodingInTheUK · Answer 10 · 2019-10-05T14:26:26.110

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;
DELIMITER //

CREATE PROCEDURE ShowPrivs(start, end)
BEGIN
    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;
END;
//

DELIMITER ;

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;

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

DELIMITER ;

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.

mysql: Show GRANTs for all users

10 Answers10

Edit (01-Aug-2023)

MySQL Grant Rebuilder _{(Reverse Engineer Grants)}

Linked

mysql: Show GRANTs for all users

10 Answers10

Edit (01-Aug-2023)

MySQL Grant Rebuilder (Reverse Engineer Grants)

Linked

MySQL Grant Rebuilder _{(Reverse Engineer Grants)}