Control of account authority of MySQL daily operation and maintenance business

In the daily operation and maintenance of MySQL database, it is necessary to control the permissions of business sub accounts uniformly.

Basically, there are two types of accounts: read account and write account, so they can be sorted into fixed stored procedures, so that the database can automatically generate the corresponding database account and random password. And unified read permission and write permission. (there are not too many restrictions on host here. Only 192.168%. Interested students can add a parameter to the stored procedure to control the 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 'Create user call usercrt(Library name,1/0,'') 1 Write the 0 reading. The last parameter specifies the user name manually,If not specified, the user name defaults to the library name_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='  Account number  ';
    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('The library name is wrong and cannot be a system library,Please input:',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='  Read account  ';
    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='  Write account  ';
    ELSE
    select "Incorrect read / write type 1 write 0 read";
    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('Database name ',pri_dbname,notice_msg, pri_username,'   Password  ',return_str);

END //
delimiter ;

Tags: MySQL Database Stored Procedure Session

Posted on Thu, 19 Mar 2020 12:19:12 -0400 by Grayda