88

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

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.

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

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

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

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

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
4

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
2

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)
1

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

0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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