mysql 批量授权_MySQL存储过程：批量为用户授权

编写出这些脚本的需求是把慢查日志写入数据库中，方便查看。

1. 由于默认的mysql.slow_log表使用的是csv数据引擎，不支持对数据进行索引，所以需要将其修改为MyISAM引擎，并对query_time字段进行索引以优化查寻效率。

2. 需要对所有的用户进行授权，让大家要可通过调用 pub_getSlowQuery( limit ) 存储过程获取一天的慢查记录数据。

3. 存储过程命名约定：priv_ 起头的为私有存储过程，不需要对用户授权，以pub_起头的存储过程对所有的会员进行授权，只允许运行，不可修改和删除。

-- 修改慢查日志表结构，添加索引优化查寻速度

DROP PROCEDURE IF EXISTS `mysql`.`priv_setSlowLogEngine`;

DELIMITER $$

CREATE PROCEDURE `mysql`.`priv_setSlowLogEngine`() COMMENT '修改慢查设置'

BEGIN

/** 关闭慢查记录 */

SET GLOBAL slow_query_log=0;

/** 修改存储方式 */

SET GLOBAL log_output='TABLE';

/** 记录日志的执行时间 */

SET GLOBAL long_query_time=3;

/** 修改表引擎 */

ALTER TABLE `mysql`.`slow_log` ENGINE=MYISAM;

/** 添加索引 */

ALTER TABLE `mysql`.`slow_log` ADD INDEX `query_time`(`query_time`);

/** 开启慢查记录 */

SET GLOBAL slow_query_log=1;

END$$

DELIMITER ;

-- 获取慢查寻句子列表

DROP PROCEDURE IF EXISTS `mysql`.`pub_getSlowQuery`;

DELIMITER $$

CREATE PROCEDURE `mysql`.`pub_getSlowQuery`(IN top INT) COMMENT '获取慢查记录'

BEGIN

/**

* 昨天凌晨一点的时间

* 业务需求是每天凌晨时间执行，所以是取昨天凌晨到当前时间的所有慢查日志 */

DECLARE yesterday DATETIME;

SELECT CONCAT_WS(' ', DATE_SUB(CURDATE(),INTERVAL 1 DAY), '00:00:00') INTO yesterday;

SET @sql=CONCAT("SELECT * FROM `mysql`.`slow_log` WHERE `query_time`>0 ORDER BY `query_time` DESC LIMIT 0",top);

/** 使用预处理执行SQL句子 */

PREPARE m FROM @sql;

EXECUTE m;

DEALLOCATE PREPARE m;

END$$

DELIMITER ;

-- 授权操作

DROP PROCEDURE IF EXISTS `mysql`.`priv_grantToProcedure`;

DELIMITER $$

CREATE PROCEDURE `mysql`.`priv_grantToProcedure`( IN procedureName VARCHAR(30) ) COMMENT '对存储过程授权'

BEGIN

DECLARE not_found_data INT DEFAULT 0;

DECLARE userName VARCHAR(20) DEFAULT '';

DECLARE hostName VARCHAR(20) DEFAULT '';

/**

* 将用户列表读入游标 */

DECLARE users CURSOR FOR SELECT `user`,`host` FROM mysql.user WHERE `user`!='csc86';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found_data=1;

OPEN users;

WHILE not_found_data=0 DO

FETCH users INTO userName,hostName;

SET @sql=CONCAT('GRANT Execute ON PROCEDURE `mysql`.`',procedureName,'` TO `',userName,'`@`',hostName,'`');

/** 使用预处理执行SQL句子 */

PREPARE m FROM @sql;

EXECUTE m;

DEALLOCATE PREPARE m;

END WHILE;

CLOSE users;

END$$

DELIMITER ;

-- 将mysql库中以pub_开头的存储过程对所有用户授权

DROP PROCEDURE IF EXISTS `mysql`.`priv_setPrivileges`;

DELIMITER $$

CREATE PROCEDURE `mysql`.`priv_setPrivileges`() COMMENT '设置调用存储过程权限'

BEGIN

/**

* 游标 */

DECLARE not_found_data INT DEFAULT 0;

/**

* 存储过程名称 */

DECLARE proc_name VARCHAR(30) DEFAULT '';

/**

* 读取所有公开的存储过程 */

DECLARE procedures CURSOR FOR SELECT `name` FROM `mysql`.`proc` WHERE `db`='mysql' AND `type`='PROCEDURE' AND `name` REGEXP '^pub_';

/**

* 到达游标尾部时，设置not_found_data为1 */

DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found_data = 1;

/**

* 打开游标进入循环 */

-- priv_grantToProcedure

OPEN procedures;

TRUNCATE TABLE mysql.`procs_priv`;

WHILE not_found_data=0 DO

FETCH procedures INTO proc_name;

CALL priv_grantToProcedure( proc_name );

END WHILE;

/** 关闭游标 */

CLOSE procedures;

/** 刷新权限 */

FLUSH PRIVILEGES;

END$$

DELIMITER ;