对于mysql数据库的操作,有时为了安全及分级管理的需求,需要对不同的用户授予不同的权限,并且对于授权的用户,不仅要给予其对数据库的增产改查等操作权限,还要使其有执行数据库的函数和存储过程的权限,具体的操作如下:
一般有几种类别的授权操作:
【对网站连接用户授权】
1、新建授权用户,主要其中涉及的的权限
mysql> GRANTUSAGE,SELECT,INSERT,UPDATE,DELETE,SHOW VIEW,CREATE TEMPORARY TABLES,EXECUTE ON dbname.* TO 'dbuser'@'192.168.2.%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
2、还要使以上账号具有调用系统中函数和存储过程的权限
mysql> update mysql.proc set DEFINER='dbuser@192.168.2.%' WHERE db='dbname' AND `type`= 'FUNCTION';
mysql> update mysql.proc set DEFINER='dbuser@192.168.2.%' WHERE db='dbname' AND `type`= 'PROCEDURE';
mysql> FLUSH PRIVILEGES;
【数据库DBA用户授权】
mysql> GRANT ALL PRIVILEGES ON *.* TO 'dbauser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
【供开发人员查询用账号】
mysql> GRANT USAGE,SELECT,SHOW VIEW,CREATE TEMPORARY TABLES,EXECUTE ON dbname.* TO 'dbuser'@'%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
【mysql双机热备用户授权】
mysql> GRANT ALL PRIVILEGES ON *.* TO 'Rep_user'@'192.168.2.%' IDENTIFIED BY 'password' WITH GRANT OPTION; ----双机热备必须内网
mysql> FLUSH PRIVILEGES;
转载于:https://blog.51cto.com/davidlinux/1650344