A Little Noise

Show all grants

Written by

snoyes

in

A stored procedure to show all grants in the database.

USE mysql;

DELIMITER //
CREATE PROCEDURE showAllGrants() BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE theUser CHAR(16);
    DECLARE theHost CHAR(60);
    DECLARE cur1 CURSOR FOR SELECT user, host FROM mysql.user;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    OPEN cur1;

    REPEAT
        FETCH cur1 INTO theUser, theHost;
        IF NOT done THEN
            SET @sql := CONCAT('SHOW GRANTS FOR ', QUOTE(theUser), '@', QUOTE(theHost));
            PREPARE grantStatement FROM @sql;
            EXECUTE grantStatement;
            DROP PREPARE grantStatement;
        END IF;
    UNTIL done END REPEAT;

    CLOSE cur1;
END//
DELIMITER ;

CALL showAllGrants();

Comments

3 responses to “Show all grants”

  1. Shlomi Noach Avatar
    Shlomi Noach

    Allow me to suggest using the view sql_show_grants, part of the common_schema instead.
    I believe it is better than the above code, for the following reasons:
    1. The above code results with multiple results set
    2. It is a stored procedure, so you can’t do SELECTs on it
    3. In particular, you can’t do WHERE, e.g. WHERE user = ‘particular_user’ etc. Unless you code it into the proc, but then there’s so many other issues which must be coded…

    sql_show_grants is a view which lists down using a SELECT query the GRANT statements for all users. You can filter based on specific user.
    Better, it’s sql_grants companion can also list down the REVOKE statement for each user. And you get even more details per entry; the doc is very detailed.

    There is a lot more code in common_schema that answers for some of the posts you’ve posted today; I urge you to take a look!

    Regards,
    Shlomi

  2. Roland Bouman Avatar
    Roland Bouman

    Shlomi,

    I think Scott is just salvaging the snippets from MySQL forge.

  3. Shlomi Noach Avatar
    Shlomi Noach

    Ah, I see. Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More posts