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();
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
Comment by Shlomi Noach — October 12, 2011 @ 2:49 pm
Shlomi,
I think Scott is just salvaging the snippets from MySQL forge.
Comment by Roland Bouman — October 13, 2011 @ 4:14 pm
Ah, I see. Thanks
Comment by Shlomi Noach — October 18, 2011 @ 1:52 pm