mysql grant 存储过程_mysql存储过程-根据不同的用途将表权限授给不同的用户

DROP PROCEDURE IF EXISTS grant_table;

/**

* 批量将需要初始化数据的表授权给某个用户

**/

DELIMITER $$

CREATE PROCEDURE grant_table()

BEGIN

DECLARE TNAME VARCHAR(256);

DECLARE USER_NAME VARCHAR(64);

DECLARE DB_NAME VARCHAR(32);

DECLARE s int DEFAULT 0;

#把需要初始化数据和表结构的结果集存入到游标变量中

DECLARE table_cursor CURSOR FOR select TABLE_NAME from t_icp_init_data_table;

#只需要导数据库表结构的结果集

DECLARE table_cursor1 CURSOR FOR

select t.TABLE_NAME from information_schema.TABLES t where t.TABLE_SCHEMA=DB_NAME

and not exists(select 1 from t_icp_init_data_table d where t.TABLE_NAME = d.table_name );

DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;

SELECT DATABASE() INTO DB_NAME;

SET USER_NAME ='xw_one';

OPEN table_cursor;

FETCH table_cursor INTO TNAME ;

WHILE s<>1 DO

SET @sql1=CONCAT('grant select on ',TNAME,' to ', USER_NAME);

PREPARE stmt FROM @sql1; -- 预处理需要执行的动态SQL，

EXECUTE stmt;

DEALLOCATE PREPARE stmt; -- 释放掉预处理段

FETCH table_cursor INTO TNAME;

END WHILE;

CLOSE table_cursor;

#重置S的值为0

SET s =0;

SET USER_NAME ='xw_two';

OPEN table_cursor1;

FETCH table_cursor1 INTO TNAME ;

WHILE s<>1 DO

SET @sql1=CONCAT('grant select on ',TNAME,' to ', USER_NAME);

PREPARE stmt FROM @sql1; -- 预处理需要执行的动态SQL，

EXECUTE stmt;

DEALLOCATE PREPARE stmt; -- 释放掉预处理段

FETCH table_cursor1 INTO TNAME;

END WHILE;

CLOSE table_cursor1;

#给某个用户授权执行某个数据库所有存储过程的权限

SET @sql1=CONCAT('grant execute on ',DB_NAME,'.* to ', USER_NAME);

PREPARE stmt FROM @sql1; -- 预处理需要执行的动态SQL，

EXECUTE stmt;

DEALLOCATE PREPARE stmt; -- 释放掉预处理段

FLUSH PRIVILEGES;

END

$$

DELIMITER;

涉及导的其它mysql授权语法

1、创建用户并设置密码

create user '用户名'@'%' identified by '88888888';

2、授予mysql的所有权限

grant all on mysql.* to xw_two;

3、授权某张表常用权限给某个用户

grant select,dorp,create,delete,update on 库名.表名 to xw_two;

3、给某个用户授权执行某个数据库所有存储过程的权限

语法

grant execute on DB_NAME.* to USER_NAME

示例：

grant execute on mysql.* to root