Mysql数据库授权脚本

小柒博客
评论59,4471字数 1639阅读5分27秒阅读模式

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

脚本功能：

1、授权用户权限

2、回收用户权限

3、查询用户权限

4、删除用户及权限

5、支持批量IP

6、输入{Q/q}退出脚本

[root@Ansible scripts]# vim auto_authorization_mysql_db.sh

脚本内容如下：

#!/bin/bash
#Date：2020-7-15 16:28:10
#Author Blog:
#	https://www.yangxingzhen.com
#	https://www.yangxingzhen.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

脚本执行方式：

[root@Ansible scripts]# sh auto_authorization_mysql_db.sh

# 查询用户列表

Mysql数据库授权脚本-图片1# 授权

# 撤销权限

# 查询权限

# 删除用户及权限

Mysql数据库授权脚本-图片2

若文章图片、下载链接等信息出错，请在评论区留言反馈，博主将第一时间更新！如本文“对您有用”，欢迎随意打赏，谢谢！

继续阅读
Wechat
微信扫一扫，加我！
weinxin
微信号已复制
微信公众号
微信扫一扫，关注我！
weinxin
公众号已复制
Shell最后更新：2024-1-24
小柒博客
  • 本文由 小柒博客 发表于 2020年7月17日 15:23:23
  • 声明：本站所有文章，如无特殊说明或标注，本站文章均为原创。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。转载请务必保留本文链接：https://www.yangxingzhen.com/7250.html

加载中...

匿名

发表评论

匿名网友
:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

拖动滑块以完成验证