MySQL 日常运维业务账号权限的控制

在MySQL数据库日常运维中，对业务子账号的权限的统一控制十分必要。

业务上基本分为读账号和写账号两种账号，所以可以整理为固定的存储过程，让数据库自动生成对应的库的账号，随机密码。以及统一的读权限，写权限。（这里没有对 host进行过多的限制。只赋给通用的192.168.% 。有兴趣的同学可以在存储过程加个参数，对host 控制）

delimiter //

set session sql_log_bin=OFF;

drop PROCEDURE IF EXISTS `usercrt` //

CREATE  DEFINER=`root`@`localhost` PROCEDURE  `usercrt`(dbname varchar(64),type int,username varchar(16))

    COMMENT '创建用户 call usercrt(库名,1/0,'') 1写 0读 。最后一个参数为手动指定用户名,没有指定则用户名默认为 库名_w/r'

label:BEGIN

    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

    DECLARE return_str varchar(255) DEFAULT '';

    DECLARE n int DEFAULT 12;

    DECLARE i INT DEFAULT 0;

        DECLARE pri_dbgrant  VARCHAR(500);

        DECLARE pri_namepre  VARCHAR(500);

        DECLARE pri_dbname  VARCHAR(500);

        DECLARE check_user  VARCHAR(500);

        DECLARE grantsql  VARCHAR(200);

        DECLARE pri_username  VARCHAR(500);

        DECLARE pri_grant  VARCHAR(500);

				DECLARE notice_msg  VARCHAR(500);

				set notice_msg='  账号  ';

    WHILE i < n DO

        SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));

        SET i = i +1;

    END WHILE;

IF dbname = '*' THEN

    SET pri_dbgrant="*.*";

    SET pri_namepre="alldb";

    ELSE

    select SCHEMA_NAME INTO pri_dbname FROM information_schema.SCHEMATA where SCHEMA_NAME=dbname and SCHEMA_NAME NOT IN ("information_schema","performance_schema","mysql","sys");

    IF pri_dbname IS NOT NULL AND pri_dbname !=''  THEN

    SET  pri_namepre=substring(pri_dbname,1,14);

    SET  pri_dbgrant=concat(pri_dbname,'.*');

    ELSE

    select concat('库名错误且不能为系统库,请输入:',group_concat(SCHEMA_NAME))  FROM information_schema.SCHEMATA where SCHEMA_NAME NOT IN ("information_schema","performance_schema","mysql","sys");

    leave label;

    END IF ;

END IF;

  IF TYPE = 0 THEN

    SET pri_username=CONCAT(pri_namepre,'_r');

    set pri_grant="GRANT select on ";

		set notice_msg='  读账号  ';

    ELSEIF  TYPE = 1 THEN

    SET pri_username=CONCAT(pri_namepre,'_w');

    set pri_grant="GRANT Show view,select,insert,update,delete on ";

		set notice_msg='  写账号  ';

    ELSE

    select "读写类型不正确 1 写 0 读";

    leave label;

    END IF;

   IF username IS NOT NULL AND username !='' THEN

   SET  pri_username =username;

   END IF;

    select User INTO check_user from mysql.user where user=pri_username AND Host='192.168.%' ;

    IF check_user IS NOT NULL AND check_user !='' THEN

    SET return_str='';

    set grantsql=concat(pri_grant,pri_dbgrant,' to ',pri_username,'@"192.168.%"');

    ELSE

    set grantsql=concat(pri_grant,pri_dbgrant,' to ',pri_username,'@"192.168.%"  identified by ',"'",return_str,"'");

    END IF ;

SELECT grantsql;

SET @gsql=grantsql;

PREPARE STMT FROM @gsql;

EXECUTE STMT;

DEALLOCATE PREPARE STMT;

IF  return_str!='' THEN

set @crtsql="create table IF NOT EXISTS  tmp_pwd(col varchar(100))";

PREPARE STMT2 FROM @crtsql;

EXECUTE STMT2;

DEALLOCATE PREPARE  STMT2;

set @intsql=concat("insert into tmp_pwd(col) values('",return_str,"')");

PREPARE STMT3 FROM @intsql;

EXECUTE STMT3;

DEALLOCATE PREPARE  STMT3;

END IF;

set @showsql=concat(' show grants for ',pri_username,'@"192.168.%"');

    PREPARE STMT4 FROM @showsql;

    EXECUTE STMT4;

    DEALLOCATE PREPARE STMT4;

SELECT CONCAT('数据库名 ',pri_dbname,notice_msg, pri_username,'   密码  ',return_str);

END //

delimiter ;