mysql: Show GRANTs for all users

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

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

    +1 for developing this tool! Any chance that MySQL will adopt your code?

    Many thanks! I'm pushing this project at this time (new release scheduled in a week or so), in an attempt to make it popular. I do not know if any will adopt it.

    I am going to stop working on my answer. It looks like you are light-years ahead on this one. +1. I hope it gains more traction in the MySQL Universe.

    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`

    @MartinVegter , have you installed common_schema? Download here and install following these instructions.

    @ShlomiNoach, When you say that there's "nothing built-in"... Are there any errors with `information_schema.user_privileges`?

    @Pacerier please see my comment on the `user_privileges` answer

    Uhh, It looks like this `common_schema` select will truncate data at 1024 characters :(

    @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

    @ThorSummoner that's your `group_concat_max_len` setting limitation. `SET GLOBAL group_concat_max_len=1000000` to solve. Also of course in config file.

    ERROR 1146 (42S02): Table 'common_schema.sql_show_grants' doesn't exist

    The tool doesn't install properly on MySQL 5.7: it throws an error around line 197

    Sorry, but there's no such thing as a 'common_schema'. It doesn't exist.

    link sql_show_grants broken

    @BrendanByrd: It is a 3rd party tool, that used to be hosted on Google Code... (I'm not sure what the official replacement repo is)

  • 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 [email protected] 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.

    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 [email protected]` 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.

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

    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.

    Where can I give password?

    To provide a password one may use a Option File or the --password flag of the mysql command.

    Would it not be possible to give one root password and run the query to get all users grants?

    Youd could ask for the password and store it in a variable with something like bash's `read`, then use `--password=$STORED_PW`. But this is considered insecure.

    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/'`

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

    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.

    For rainbow table shenanigans, throw your password hashes from `select * from mysql.user` into https://crackstation.net and see the unhashed output.

  • 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

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

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM