这篇文章主要介绍了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
# 查询用户列表
若文章图片、下载链接等信息出错,请在评论区留言反馈,博主将第一时间更新!如本文“对您有用”,欢迎随意打赏,谢谢!
![weinxin](https://www.yangxingzhen.com/images/weixin.jpg)
![weinxin](https://www.yangxingzhen.com/images/weixingongzhonghao.png)
评论