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

45

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 at 3:01

RAnders00

1073 bronze badges

answered Aug 28 '12 at 11:11

Shlomi Noach

6,48719 silver badges22 bronze badges

11

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
2

@MartinVegter , have you installed common_schema? Download here and install following these instructions. – Shlomi Noach Oct 25 '13 at 5:04
1

@ShlomiNoach, When you say that there's "nothing built-in"... Are there any errors with information_schema.user_privileges? – Pacerier Jul 7 '15 at 8:14
2

Sorry, but there's no such thing as a 'common_schema'. It doesn't exist. – Brendan Byrd 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

82

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

9296 silver badges5 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

add a comment |

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

11

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

1311 silver badge4 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. – Max 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 comment

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,70410 silver badges21 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 |

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

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)

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

1114 bronze badges

add a comment |

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.

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

Your Answer

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

Your Answer

Sign up or log in

Post as a guest

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

Linked

Related

Hot Network Questions