mysql5.7数据库权限初始化脚本

基于MySQL5.7数据库权限规划如下：

基于前面数据库部署脚本后，自动做权限规划，参考脚本如下：

#!/bin/bash
##################################################
# copyright by hwb
# DATE:2020-12-02
# 用途：适用于mysql5.7数据库第一次初始化权限配置
##################################################

#初始化部署账号
today=`date +"%Y%m%d-%H%M%S"`
myuser="root"
mypwd="xxx1234"
myport="53306"
mysql_cmd="mysql -u${myuser} -p${mypwd}  -P${myport} --protocol=tcp --silent"
#初始化数据库
db_name="xxxg_pas"
#root账号配置
root_user="root"
#密码特殊字符尽量不用$和单引号'
root_pwd="xx#r1[_oMf08px"
mysql_cmd2="mysql -u${myuser} -p${root_pwd}  -P${myport} --protocol=tcp --silent"

#应用程序账号(限制某IP登录)
program_ip="xxxx.134"
program_user="dfwlg_pas"
program_pwd="xxxx[_oMf08px"

#日常运维账号
ops_user="xxx_prod"
ops_pwd="kUv2or1[5oMf08px"

#只读账号
dev_user="xx_read"
dev_pwd="xxx5oM_08px"

#zabbix账号
zabbix_user="zabbix"
zabbix_pwd="zabbix@1234"


#调用函数库
[ -f /etc/init.d/functions ] && source /etc/init.d/functions
export PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
source /etc/profile

#Require root to run this script.
[ $(id -u) -gt 0 ] && echo "请用root用户执行此脚本！" && exit 1

#重置密码
#skip-grant-tables
#update mysql.user set authentication_string='' where user='root';flush privileges;


mysql5.7_priv(){
  echo ""
  echo -e "\033[33m***************************MySQL 5.7 用户权限初始化配置*****************************\033[0m"
  echo ""
  echo -e "\033[36m Usage: $0  \033[0m"
  echo ""
  $mysql_cmd -e "create database ${db_name} default character set utf8mb4" --connect-expired-password
  
  if [ $? -eq 0 ] ; then
    action "[$today]>>>完成数据库${db_name}创建" /bin/true
	echo ""
  else 
    action "[$today]>>>无法创建数据库${db_name},请检查相关配置！" /bin/false
	echo ""
	exit 1
  fi

  $mysql_cmd -e "set global validate_password_policy=0" --connect-expired-password
  $mysql_cmd -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '${root_pwd}'"  --connect-expired-password 
  $mysql_cmd2 -e "ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '${root_pwd}'"  --connect-expired-password
  $mysql_cmd2 -e "grant all privileges on *.* to root@'%'"  --connect-expired-password  
  $mysql_cmd2 -e "ALTER USER 'root'@'localhost'  PASSWORD EXPIRE NEVER"  --connect-expired-password  
  $mysql_cmd2 -e "ALTER USER 'root'@'%'  PASSWORD EXPIRE NEVER"  --connect-expired-password  
  action "******完成root用户权限初始化配置******" /bin/true
  echo ""
  
  $mysql_cmd2 -e "CREATE USER '${program_user}'@'${program_ip}' IDENTIFIED WITH mysql_native_password BY '${program_pwd}'"  --connect-expired-password 
  $mysql_cmd2 -e "grant all privileges on ${db_name}.* to  '${program_user}'@'${program_ip}'"  --connect-expired-password  
  $mysql_cmd2 -e "grant all privileges on mysql.* to  '${program_user}'@'${program_ip}'"  --connect-expired-password   
  $mysql_cmd2 -e "ALTER USER '${program_user}'@'${program_ip}'  PASSWORD EXPIRE NEVER"  --connect-expired-password  
  action "******完成应用程序用户权限初始化配置******" /bin/true
  echo ""

  $mysql_cmd2 -e "CREATE USER '${ops_user}'@'%' IDENTIFIED WITH mysql_native_password BY '${ops_pwd}'"  --connect-expired-password 
  $mysql_cmd2 -e "grant select,insert, update, delete on ${db_name}.* to '${ops_user}'@'%'"  --connect-expired-password 
  $mysql_cmd2 -e "grant create,alter,drop,references on ${db_name}.* to '${ops_user}'@'%'"  --connect-expired-password 
  $mysql_cmd2 -e "grant create temporary tables on ${db_name}.* to '${ops_user}'@'%'"  --connect-expired-password 
  $mysql_cmd2 -e "grant index on ${db_name}.* to '${ops_user}'@'%'"  --connect-expired-password 
  $mysql_cmd2 -e "grant create view on ${db_name}.* to '${ops_user}'@'%'"  --connect-expired-password 
  $mysql_cmd2 -e "grant show view on ${db_name}.* to '${ops_user}'@'%'"  --connect-expired-password 
  $mysql_cmd2 -e "grant create routine on ${db_name}.* to '${ops_user}'@'%'"  --connect-expired-password 
  $mysql_cmd2 -e "grant alter routine on ${db_name}.* to '${ops_user}'@'%'"  --connect-expired-password 
  $mysql_cmd2 -e "grant execute on ${db_name}.* to '${ops_user}'@'%'"  --connect-expired-password 
  $mysql_cmd2 -e "grant all privileges on mysql.* to  '${ops_user}'@'%'"  --connect-expired-password    
  action "******完成日常运维用户权限初始化配置******" /bin/true
  echo ""
  
  $mysql_cmd2 -e "CREATE USER '${dev_user}'@'%' IDENTIFIED WITH mysql_native_password BY '${dev_pwd}'"  --connect-expired-password 
  $mysql_cmd2 -e "grant select on ${db_name}.* to '${dev_user}'@'%'"  --connect-expired-password 
  $mysql_cmd2 -e "grant show view on ${db_name}.* to '${dev_user}'@'%'"  --connect-expired-password 
  $mysql_cmd2 -e "grant file on *.* to '${dev_user}'@'%'"  --connect-expired-password 
  $mysql_cmd2 -e "grant select on mysql.* to  '${dev_user}'@'%'"  --connect-expired-password    
  action "******完成只读用户权限初始化配置******" /bin/true
  echo ""
  
  $mysql_cmd2 -e "CREATE USER '${zabbix_user}'@'localhost' IDENTIFIED WITH mysql_native_password BY '${zabbix_pwd}'"  --connect-expired-password
  $mysql_cmd2 -e "CREATE USER '${zabbix_user}'@'%' IDENTIFIED WITH mysql_native_password BY '${zabbix_pwd}'"  --connect-expired-password 
  $mysql_cmd2 -e "grant USAGE,PROCESS,SUPER,REPLICATION CLIENT,REPLICATION SLAVE on *.* to '${zabbix_user}'@'%'"  --connect-expired-password 
  $mysql_cmd2 -e "grant USAGE,PROCESS,SUPER,REPLICATION CLIENT,REPLICATION SLAVE on *.* to '${zabbix_user}'@'localhost'"  --connect-expired-password    
  $mysql_cmd2 -e "flush privileges"  --connect-expired-password    
  action "******完成zabbix用户权限初始化配置******" /bin/true
  echo ""
  
  echo -e "\033[33m************************MySQL 5.7 用户权限配置信息如下******************************\033[0m"
  echo -e "\033[36m >> root用户密码：${root_pwd} \033[0m"
  echo -e "\033[36m >> 应用程序用户[${program_user}@'${program_ip}']密码：${program_pwd} \033[0m"
  echo -e "\033[36m >> 日常运维用户[${ops_user}@'%']密码：${ops_pwd} \033[0m"
  echo -e "\033[36m >> 只读用户[${dev_user}@'%']密码：${dev_pwd} \033[0m"
  echo -e "\033[36m >> zabbix监控用户[${zabbix_user}@'%']密码：${zabbix_pwd} \033[0m"
  echo ""
  echo -e "\033[33m**********************完成MySQL 5.7 用户权限初始化配置******************************\033[0m"
  echo ""

}

mysql5.7_priv