MySQL stored procedure: Batch authorization for users, mysql Stored Procedure

MySQL stored procedure: Batch authorization for users, mysql Stored Procedure

To write these scripts, you need to write slow query logs into the database for ease of viewing.

1. because of the default mysql. the slow_log table uses the csv Data Engine and does not support data indexing. Therefore, you need to change it to the MyISAM engine and index the query_time field to Optimize search efficiency.

2. authorization is required for all users so that you can call the pub_getSlowQuery (limit) stored procedure to obtain slow query record data for one day.

3. naming Conventions for stored procedures: a private stored procedure starts with priv _ and does not require user authorization. All members are authorized to run the stored procedure starting with pub, it cannot be modified or deleted.

-- Modify the structure of the slow query log table and add indexes to optimize the search speed. drop procedure if exists 'mysql '. 'Priv _ setSlowLogEngine '; DELIMITER $ create procedure 'mysql '. 'Priv _ setSlowLogEngine '() comment' modify the slow query settings 'in in/** disable slow query records */set global slow_query_log = 0; /** modify the storage mode */set global log_output = 'table';/** record the log execution time */set global long_query_time = 3; /** Modify TABLE engine */alter table 'mysql '. 'slow _ log' ENGINE = MYISAM;/** add Index */alter table 'mysql '. 'slow _ Log' Add index 'query _ time' ('query _ Time');/** enable slow query records */set global slow_query_log = 1; END $ DELIMITER; -- Get the slow query sentence list drop procedure if exists 'mysql '. 'pub _ getslowquery'; DELIMITER $ create procedure 'mysql '. 'pub _ getslowquery' (IN top INT) comment' obtains the slow query record 'in IN/*** at a time IN the early morning of yesterday. * The service needs to be executed every morning, so it is to take all the slow query logs from the early morning of yesterday to the current time */DECLARE yesterday DATETIME; SELECT CONCAT_WS ('', DATE_SUB (CURDATE (), INTERVAL 1 DAY), '00: 00: 00') INTO yesterday; SET @ SQL = CONCAT ("SELECT * FROM 'mysql '. 'slow _ log' WHERE 'query _ time'> 0 order by 'query _ time' desc limit 0 ", top ); /** use preprocessing to execute SQL sentences */PREPARE m FROM @ SQL; EXECUTE m; DEALLOCATE PREPARE m; END $ DELIMITER; -- authorize the operation to DROP PROCEDURE IF EXISTS 'mysql '. 'Priv _ grantToProcedure '; DELIMITER $ create procedure 'mysql '. 'Priv _ grantToProcedure '(IN procedureName VARCHAR (30) comment' grant 'beg In declare not_found_data int default 0; DECLARE userName VARCHAR (20) DEFAULT ''; DECLARE hostName VARCHAR (20) DEFAULT ''; /*** read the user list into the CURSOR */DECLARE users cursor for select 'user', 'host' FROM mysql. user WHERE 'user '! = 'Csc86'; declare continue handler for not found set not_found_data = 1; OPEN users; WHILE not_found_data = 0 do fetch users INTO userName, hostName; SET @ SQL = CONCAT ('Grant Execute ON PROCEDURE 'mysql '. '', procedureName, ''to'', userName, ''@'', hostName ,'''); /** use preprocessing to execute SQL sentences */PREPARE m FROM @ SQL; EXECUTE m; DEALLOCATE PREPARE m; END WHILE; CLOSE users; END $ DELIMITER; -- Grant the drop procedure if exists 'mysql' permission to all users for the stored PROCEDURE starting with pub _ In the mysql database '. 'Priv _ setprivileges'; DELIMITER $ create procedure 'mysql '. 'Priv _ setprivileges' () comment' sets the permission to call the Stored Procedure 'in in/*** cursor */DECLARE not_found_data int default 0; /*** stored procedure name */DECLARE proc_name VARCHAR (30) DEFAULT ''; /*** read all public Stored procedures */DECLARE procedures cursor for select 'name' FROM 'mysql '. 'proc' WHERE 'db' = 'mysql' AND 'type' = 'processed' AND 'name' REGEXP '^ pub _';/*** when it reaches the end of the cursor, SET not_found_data to 1 */declare continue handler for not found set not_found_data = 1;/*** OPEN the cursor and enter the loop */-- priv_grantToProcedure OPEN procedures; truncate table mysql. 'procs _ priv'; WHILE not_found_data = 0 do fetch procedures INTO proc_name; CALL priv_grantToProcedure (proc_name); end while;/** CLOSE the cursor */CLOSE procedures; /** refresh permission */flush privileges; END $ DELIMITER;

If MYSQL grants the stored procedure change permission to another user?

Alter routine -- change Stored Procedure Permissions

Create routine -- CREATE Stored Procedure permission

EXECUTE -- EXECUTE stored procedure Permissions
 
Mysql batch Update (available Stored Procedure)

I don't know how you click = click + 2, + 3, + 4 ?? Is all data the same?