自己写的一个存储过程!一次性获取mysql所有用户的所有权限列表!
做权限列表统计的时候这个非常爽!
- DELIMITER $$
- USE `mysql`$$
- DROP PROCEDURE IF EXISTS `proc_GetUserPriv`$$
- CREATE DEFINER=`root`@`%` PROCEDURE `proc_GetUserPriv`(
- )
- COMMENT '获取账号权限信息列表'
- BEGIN
- # 获取用户总数,用于循环判断
- SET @counts = (SELECT COUNT(*) FROM `user`);
- SET @i = 1;
- WHILE (@i <= @counts) DO
- -- SELECT CONCAT(`user`,'@',"'",`host`,"'") INTO @sql2 FROM USER LIMIT 2,
- -- set @SQL1 = concat('SELECT',CONCAT(`USER`,"@'",`HOST`,"'"),'into @SQL2 FROM USER LIMIT ',@i,",1"); -- 这句会导致错误
- SET @SQL1 = CONCAT('select concat(','"',"'",'"',',user,','"',"'",'"',',','"@',"'",'",host,"',"'",'")',' into @SQL2 from user limit ',@i,",1");
- PREPARE smtm1 FROM @SQL1 ;
- EXECUTE smtm1;
- DEALLOCATE PREPARE smtm1;
- SET @sql3 = CONCAT("show grants for ",@SQL2);
- PREPARE smtm2 FROM @sql3;
- EXECUTE smtm2;
- DEALLOCATE PREPARE smtm2;
- SET @i = @i + 1;
- END WHILE;
- END$$
- DELIMITER ;
嘿嘿,其实上面这个存储过程还可以用游标来实现!
之所以没用游标,是想上面这种方法在某些方面可以替代游标!