MySQL 日常運維業務賬號許可權的控制

vansky發表於2018-05-06
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 ;

 

相關文章