Friday, October 14, 2011

Mysql: show all grants

MYSQL PROCEDURE:
------------------------

-- Mysql Procedure to show all grants

CREATE PROCEDURE `showAllGrants`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE mUser CHAR(16);
DECLARE hostName 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 mUser, hostName;
IF NOT done THEN
SET @sql := CONCAT('SHOW GRANTS FOR ', QUOTE(mUser), '@', QUOTE(hostName));
PREPARE grntStmt FROM @sql;
EXECUTE grntStmt;
DROP PREPARE grntStmt;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END

USAGE:
--------
CALL showAllGrants();