MySQL业务账号需要哪些权限_MySQL 日常运维业务账号权限的控制

傷城~ 2022-11-05 03:19 234阅读 0赞

在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