这篇文章主要介绍了Mysql数据库创建账号授权、回收权限、删除用户、查询用户权限等功能，需要的朋友可以参考下。

脚本功能：文章源自小柒网-https://www.yangxingzhen.cn/7250.html

1、授权用户权限文章源自小柒网-https://www.yangxingzhen.cn/7250.html

2、回收用户权限文章源自小柒网-https://www.yangxingzhen.cn/7250.html

3、查询用户权限文章源自小柒网-https://www.yangxingzhen.cn/7250.html

4、删除用户及权限文章源自小柒网-https://www.yangxingzhen.cn/7250.html

5、支持批量IP文章源自小柒网-https://www.yangxingzhen.cn/7250.html

6、输入{Q/q}退出脚本文章源自小柒网-https://www.yangxingzhen.cn/7250.html

[root@Ansible scripts]# vim auto_authorization_mysql_db.sh文章源自小柒网-https://www.yangxingzhen.cn/7250.html

脚本内容如下：文章源自小柒网-https://www.yangxingzhen.cn/7250.html

#!/bin/bash
#Date：2020-7-15 16:28:10
#Author Blog:
#	https://www.yangxingzhen.cn
#	https://www.i7ti.cn
#Author WeChat：
#	微信公众号：小柒博客
#Author mirrors site:
#	https://mirrors.yangxingzhen.com
#About the Author
#	BY：YangXingZhen
#	Mail：xingzhen.yang@yangxingzhen.com
#	QQ：675583110
#执行脚本之前请执行命令：grant all on *.* to admin@'%' identified by 'CRDEP7X2zHUJCG^eZu2T*Wny' WITH GRANT OPTION;
#						 flush privileges;

source /etc/rc.d/init.d/functions


User="admin"
Passwd="CRDEP7X2zHUJCG\^eZu2T\*Wny"


function query (){
Code=""
while true
do
read -p "$(echo -e "\\033\[32m请输入需要连接的Mysql主机IP\[多个IP以空格分隔\]：\\033\[0m")" IPADDR
if \[ -z "${IPADDR}" \];then
echo -e "\\033\[31m输入错误,主机IP不能为空...\\033\[0m"
elif \[ "${IPADDR}" = "q" -o "${IPADDR}" = "Q" \];then
Code="break"
else
while true
do
read -p "$(echo -e "\\033\[32m请输入需要查询的用户名：\\033\[0m")" USER
if \[ -z "${USER}" \];then
echo -e "\\033\[31m输入错误,用户名不能为空...\\033\[0m"
elif \[ "${USER}" = "q" -o "${USER}" = "Q" \];then
Code="break"
else
while true
do
read -p "$(echo -e "\\033\[32m请输入需要查询的授权连接地址：\\033\[0m")" Connection
if \[ -z "${Connection}" \];then
echo -e "\\033\[31m输入错误,授权连接地址不能为空...\\033\[0m"
elif \[ "${Connection}" = "q" -o "${Connection}" = "Q" \];then
Code="break"
else
for i in ${IPADDR}
do
echo -e "\\033\[32mIPADDR：${i}\\033\[0m"
mysql -h ${i} -u${User} -p"${Passwd}" -e "show grants for ${USER}@'"${Connection}"';"
done
return 1
fi
${Code}
done
fi
${Code}
done
fi
${Code}	
done	
}


function revoke (){
Code=""
while true
do
read -p "$(echo -e "\\033\[32m请输入需要连接的Mysql主机IP\[多个IP以空格分隔\]：\\033\[0m")" IPADDR
if \[ -z "${IPADDR}" \];then
echo -e "\\033\[31m输入错误,主机IP不能为空...\\033\[0m"
elif \[ "${IPADDR}" = "q" -o "${IPADDR}" = "Q" \];then
Code="break"
else
while true
do
read -p "$(echo -e "\\033\[32m请输入需要撤销权限的数据库名：\\033\[0m")" DB_NAME
if \[ -z "${DB_NAME}" \];then
echo -e "\\033\[31m输入错误,权限列表不能为空...\\033\[0m"
elif \[ "${DB_NAME}" = "q" -o "${DB_NAME}" = "Q" \];then
Code="break"
else
while true
do
read -p "$(echo -e "\\033\[32m请输入需要撤销的权限列表\[以逗号分开\]：\\033\[0m")" List
if \[ -z "${List}" \];then
echo -e "\\033\[31m输入错误,权限列表不能为空...\\033\[0m"
elif \[ "${List}" = "q" -o "${List}" = "Q" \];then
Code="break"
else
while true
do
read -p "$(echo -e "\\033\[32m请输入需要撤销权限的用户名：\\033\[0m")" USER
if \[ -z "${USER}" \];then
echo -e "\\033\[31m输入错误,用户名不能为空...\\033\[0m"
elif \[ "${USER}" = "q" -o "${USER}" = "Q" \];then
Code="break"
else
while true
do
read -p "$(echo -e "\\033\[32m请输入需要撤销权限的连接地址：\\033\[0m")" Connection
if \[ -z "${Connection}" \];then
echo -e "\\033\[31m输入错误,授权连接地址不能为空...\\033\[0m"
elif \[ "${Connection}" = "q" -o "${Connection}" = "Q" \];then
Code="break"
else
for i in ${IPADDR}
do
mysql -h ${i} -u${User} -p"${Passwd}" -e "revoke ${List} on ${DB_NAME}.\* from ${USER}@'"${Connection}"';"
if \[ $(mysql -h ${i} -u${User} -p"${Passwd}" -e "show grants for ${USER}@'"${Connection}"';" \|grep -wc "${List}") -eq 0 \];then
action "This IP: ${i} Removed User ${USER} Permission Success..." /bin/true
else
action "This IP: ${i} Removed User ${USER} Permission Failed..." /bin/false
fi
done
return 1
fi
${Code}
done
fi
${Code}
done
fi
${Code}
done
fi
${Code}
done
fi
${Code}
done
}


function update (){
Code=""
while true
do
read -p "$(echo -e "\\033\[32m请输入需要授权的Mysql主机IP\[多个IP以空格分隔\]：\\033\[0m")" IPADDR
if \[ -z "${IPADDR}" \];then
echo -e "\\033\[31m输入错误,主机IP不能为空...\\033\[0m"
elif \[ "${IPADDR}" = "q" -o "${IPADDR}" = "Q" \];then
Code="break"
else
while true
do
read -p "$(echo -e "\\033\[32m请输入需要授权的数据库名：\\033\[0m")" DB_NAME
if \[ -z "${DB_NAME}" \];then
echo -e "\\033\[31m输入错误,数据库名不能为空...\\033\[0m"
elif \[ "${DB_NAME}" = "q" -o "${DB_NAME}" = "Q" \];then
Code="break"
else
while true
do
read -p "$(echo -e "\\033\[32m请输入需要授权的权限列表\[以逗号分开\]：\\033\[0m")" Permissions
if \[ -z ${Permissions} \];then
echo -e "\\033\[31m输入错误,权限列表不能为空...\\033\[0m"
elif \[ "${Permissions}" = "q" -o "${Permissions}" = "Q" \];then
Code="break"
else
while true
do
read -p "$(echo -e "\\033\[32m请输入需要授权的远程登录地址：\\033\[0m")" Login
if \[ -z "${Login}" \];then
echo -e "\\033\[31m输入错误,远程登录地址不能为空...\\033\[0m"
elif \[ "${Login}" = "q" -o "${Login}" = "Q" \];then
Code="break"
else
while true
do
read -p "$(echo -e "\\033\[32m请输入需要授权的用户名：\\033\[0m")" USER
if \[ -z "${USER}" \];then
echo -e "\\033\[31m输入错误,用户名不能为空...\\033\[0m"
elif \[ "${USER}" = "q" -o "${USER}" = "Q" \];then
Code="break"
else
while true
do
read -p "$(echo -e "\\033\[32m请输入需要授权的用户名密码：\\033\[0m")" PASSWD
if \[ -z "${PASSWD}" \];then
echo -e "\\033\[31m输入错误,用户名不能为空...\\033\[0m"
elif \[ "${PASSWD}" = "q" -o "${PASSWD}" = "Q" \];then
Code="break"
else
for i in ${IPADDR}
do
mysql -h ${i} -u${User} -p"${Passwd}" -e "grant ${Permissions} on ${DB_NAME}.\* to ${USER}@'"${Login}"' identified by '"${PASSWD}"';"
mysql -h ${i} -u${User} -p"${Passwd}" -e "flush privileges;"
if \[ $(mysql -h ${i} -u${User} -p"${Passwd}" -e "show grants for ${USER}@'"${Login}"';" \|grep -wc "${USER}") -ne 0 \];then
action "This IP: ${i} Authorized User ${USER} Success..." /bin/true
else
action "This IP: ${i} Authorized User ${USER} Failed..." /bin/false
fi
done
return 1
fi
${Code}
done
fi
${Code}
done	
fi
${Code}
done
fi
${Code}
done
fi
${Code}
done
fi
${Code}	
done
}


function delete (){
Code=""
while true
do
read -p "$(echo -e "\\033\[32m请输入需要连接的Mysql主机IP\[多个IP以空格分隔\]：\\033\[0m")" IPADDR
if \[ -z "${IPADDR}" \];then
echo -e "\\033\[31m输入错误,主机IP不能为空...\\033\[0m"
elif \[ "${IPADDR}" = "q" -o "${IPADDR}" = "Q" \];then
Code="break"
else
while true
do
read -p "$(echo -e "\\033\[32m请输入需要删除的用户名：\\033\[0m")" USER
if \[ -z "${USER}" \];then
echo -e "\\033\[31m输入错误,用户名不能为空...\\033\[0m"
elif \[ "${USER}" = "q" -o "${USER}" = "Q" \];then
Code="break"
else
while true
do
read -p "$(echo -e "\\033\[32m请输入需要删除的授权连接地址：\\033\[0m")" Connection
if \[ -z "${Connection}" \];then
echo -e "\\033\[31m输入错误,授权连接地址不能为空...\\033\[0m"
elif \[ "${Connection}" = "q" -o "${Connection}" = "Q" \];then
Code="break"
else
for i in ${IPADDR}
do
mysql -h ${i} -u${User} -p"${Passwd}" -e "delete from mysql.user where user='"${USER}"' and host='"${Connection}"';"
if \[ $(mysql -h ${i} -u${User} -p"${Passwd}" -e "select user,host from mysql.user;" \|grep -wc "${USER}") -eq 0 \];then
action "This IP: ${i} Deleted User ${USER} Success..." /bin/true
else
action "This IP: ${i} Deleted User ${USER} Failed..." /bin/false
fi
done
return 1
fi
${Code}
done
fi
${Code}
done
fi
${Code}
done	
}


function list (){
while true
do
read -p "$(echo -e "\\033\[32m请输入需要获取列表的Mysql主机IP\[多个IP以空格分隔\]：\\033\[0m")" IPADDR
if \[ -z "${IPADDR}" \];then
echo -e "\\033\[31m输入错误,主机IP不能为空...\\033\[0m"
elif \[ "${IPADDR}" = "q" -o "${IPADDR}" = "Q" \];then
Code="break"
else
for i in ${IPADDR}
do
echo -e "\\033\[32mIPADDR：${i}\\033\[0m"
mysql -h ${i} -u${User} -p"${Passwd}" -e "select user,host from mysql.user;"
done
fi
break
done	
}


function Main (){
stty erase '\^H'
Code=""
while true
do
read -p "$(echo -e "\\033\[32m请输入需要执行的参数：\\033\[0m")" Value
case "${Value}" in
select)
query
;;
revoke)
revoke
;;
update)
update
;;
delete)
delete
;;
list)
list
;;
"q" \| "Q")
exit 1
;;
\*)
echo -e "\\033\[32m参数名称：\\033\[0m{select\|revoke\|update\|delete\|list}"
;;
esac
${Code}
done
}


Main

脚本执行方式：文章源自小柒网-https://www.yangxingzhen.cn/7250.html

[root@Ansible scripts]# sh auto_authorization_mysql_db.sh