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?

RAnders00 · Accepted Answer · 2019-09-03 03:01:15Z

47

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.

edited Sep 3 '19 at 3:01

RAnders00

1073 bronze badges

answered Aug 28 '12 at 11:11

Shlomi Noach

6,93721 silver badges22 bronze badges

13

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 – Martin Vegter Oct 24 '13 at 19:52
3

@MartinVegter , have you installed common_schema? Download here and install following these instructions. – Shlomi Noach Oct 25 '13 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 – Shlomi Noach Dec 5 '15 at 12:28
2

Sorry, but there's no such thing as a 'common_schema'. It doesn't exist. – SineSwiper Sep 22 '17 at 21:00
2

link sql_show_grants broken – Cyzanfar Jan 6 '18 at 2:13

| Show 11 more comments

smottt · Accepted Answer · 2017-01-26 10:37:33Z

101

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.

edited Jan 26 '17 at 10:37

smottt

1093 bronze badges

answered Apr 6 '14 at 16:59

rumburak

1,1212 gold badges7 silver badges6 bronze badges

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. – Shlomi Noach Jul 9 '15 at 11:22
1

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 '20 at 15:33

Add a comment |

Community · Accepted Answer · 2016-02-17 07:16:44Z

14

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.

edited Feb 17 '16 at 7:16

Community♦

1

answered Sep 9 '13 at 15:44

mleu

2612 silver badges4 bronze badges

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 '13 at 23:13
Where can I give password? – Mian Asbat Ahmad Feb 1 '17 at 15:05
To provide a password one may use a Option File or the --password flag of the mysql command. – mleu Feb 2 '17 at 13:08
Would it not be possible to give one root password and run the query to get all users grants? – Mian Asbat Ahmad Feb 3 '17 at 11:33
2

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 '18 at 19:05

| Show 1 more comments

inemanja · Accepted Answer · 2017-02-16 14:35:47Z

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

spuder · Accepted Answer · 2018-12-18 17:04:04Z

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

2531 gold badge2 silver badges9 bronze badges

answered Aug 28 '12 at 11:29

Mahesh Patil

2,84013 silver badges22 bronze badges

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. – Shlomi Noach Aug 28 '12 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 '15 at 8:30

Add a comment |

Cavallo · Accepted Answer · 2017-11-14 12:36:48Z

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 */
SELECT
    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 */

UNION

/* [Database.Table]-Specific Grants */
SELECT
    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',
    CONCAT(
        "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 */

UNION

/* Database-Specific Grants */
SELECT
    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',
    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 `",gdb.Db,"`.* TO '",gdb.User,"'@'",gdb.Host,"';"
    ) AS 'GRANT Statement (Reconstructed)'
FROM mysql.db gdb
WHERE gdb.Db != ''
/* SELECT * FROM mysql.db */

UNION

/* User-Specific Grants */
SELECT
    "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',
    CONCAT(
        "GRANT ",
        IF((gus.Select_priv='N')&(gus.Insert_priv='N')&(gus.Update_priv='N')&(gus.Delete_priv='N')&(gus.Create_priv='N')&(gus.Drop_priv='N')&(gus.Reload_priv='N')&(gus.Shutdown_priv='N')&(gus.Process_priv='N')&(gus.File_priv='N')&(gus.References_priv='N')&(gus.Index_priv='N')&(gus.Alter_priv='N')&(gus.Show_db_priv='N')&(gus.Super_priv='N')&(gus.Create_tmp_table_priv='N')&(gus.Lock_tables_priv='N')&(gus.Execute_priv='N')&(gus.Repl_slave_priv='N')&(gus.Repl_client_priv='N')&(gus.Create_view_priv='N')&(gus.Show_view_priv='N')&(gus.Create_routine_priv='N')&(gus.Alter_routine_priv='N')&(gus.Create_user_priv='N')&(gus.Event_priv='N')&(gus.Trigger_priv='N')&(gus.Create_tablespace_priv='N')&(gus.Grant_priv='N'),
            "USAGE",
            IF((gus.Select_priv='Y')&(gus.Insert_priv='Y')&(gus.Update_priv='Y')&(gus.Delete_priv='Y')&(gus.Create_priv='Y')&(gus.Drop_priv='Y')&(gus.Reload_priv='Y')&(gus.Shutdown_priv='Y')&(gus.Process_priv='Y')&(gus.File_priv='Y')&(gus.References_priv='Y')&(gus.Index_priv='Y')&(gus.Alter_priv='Y')&(gus.Show_db_priv='Y')&(gus.Super_priv='Y')&(gus.Create_tmp_table_priv='Y')&(gus.Lock_tables_priv='Y')&(gus.Execute_priv='Y')&(gus.Repl_slave_priv='Y')&(gus.Repl_client_priv='Y')&(gus.Create_view_priv='Y')&(gus.Show_view_priv='Y')&(gus.Create_routine_priv='Y')&(gus.Alter_routine_priv='Y')&(gus.Create_user_priv='Y')&(gus.Event_priv='Y')&(gus.Trigger_priv='Y')&(gus.Create_tablespace_priv='Y')&(gus.Grant_priv='Y'),
                "ALL PRIVILEGES",
                CONCAT_WS(',',
                    IF(gus.Select_priv='Y','SELECT',NULL),
                    IF(gus.Insert_priv='Y','INSERT',NULL),
                    IF(gus.Update_priv='Y','UPDATE',NULL),
                    IF(gus.Delete_priv='Y','DELETE',NULL),
                    IF(gus.Create_priv='Y','CREATE',NULL),
                    IF(gus.Drop_priv='Y','DROP',NULL),
                    IF(gus.Reload_priv='Y','RELOAD',NULL),
                    IF(gus.Shutdown_priv='Y','SHUTDOWN',NULL),
                    IF(gus.Process_priv='Y','PROCESS',NULL),
                    IF(gus.File_priv='Y','FILE',NULL),
                    IF(gus.References_priv='Y','REFERENCES',NULL),
                    IF(gus.Index_priv='Y','INDEX',NULL),
                    IF(gus.Alter_priv='Y','ALTER',NULL),
                    IF(gus.Show_db_priv='Y','SHOW DATABASES',NULL),
                    IF(gus.Super_priv='Y','SUPER',NULL),
                    IF(gus.Create_tmp_table_priv='Y','CREATE TEMPORARY TABLES',NULL),
                    IF(gus.Lock_tables_priv='Y','LOCK TABLES',NULL),
                    IF(gus.Execute_priv='Y','EXECUTE',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.Event_priv='Y','EVENT',NULL),
                    IF(gus.Trigger_priv='Y','TRIGGER',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 "
            WHEN 'SPECIFIED' THEN
                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 "
        END,
        "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 '18 at 16:34

JSapkota · Accepted Answer · 2017-03-20 05:54:36Z

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 '20 at 14:08 — Player1, Jun 12 '20 at 14:08

billyw · Accepted Answer · 2018-02-28 22:21:52Z

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.

Eugen Konkov · Accepted Answer · 2018-05-11 16:33:31Z

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

1135 bronze badges

Add a comment |

Chris · Accepted Answer · 2019-10-05 14:26:26Z

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.

Stack Exchange Network

current community

your communities

more stack exchange communities

mysql: Show GRANTs for all users

10 Answers 10

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

Linked

Hot Network Questions

mysql: Show GRANTs for all users

10 Answers 10

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

Linked

Related

Hot Network Questions