MySQL daily operation and maintenance Account Authority control

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

Business is basically divided into read accounts and write accounts two accounts, so can be organized into fixed stored procedures, so that the database automatically generate corresponding library account, random password. As well as unified Read permissions, write permissions. (There are no excessive restrictions on host.) It is only assigned to the general 192.168.%. Interested students can add a parameter in the stored procedure, the host control)

Delimiter//set session Sql_log_bin=off;drop PROCEDURE IF EXISTS ' usercrt '//create definer= ' root ' @ ' localhost ' PROCEDURE ' USERCRT ' (dbname varchar, type int,username varchar ()) COMMENT ' Create user call USERCRT (library name, 1/0, ') 1 write 0 read. The last parameter specifies the user name manually, without specifying that the user name defaults to the library name _w/r ' label:begin DECLARE chars_str varchar (+) 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 ($);D eclare notice_msg varchar, set notice_msg= ' account ';        While I < n does 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 to 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 (' Library name is wrong and cannot be a system library, enter: ', 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 "Read-write type incorrect 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!= ' thenset @crtsql = "CREATE table IF not EXISTS tmp_pwd (col varchar (1 00)) "; 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; SELECTCONCAT (' database name ', pri_dbname,notice_msg, pri_username, ' password ', return_str); END//delimiter;

MySQL daily operation and maintenance Account Authority control