Pachmarhi Trip

https://plus.google.com/photos/110386800557471112769/albums/5694004808464973937

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();