Mysql数据库授权脚本

小辉博客
小辉博客
小辉博客
358
文章
3
评论
2020-07-2115:43:58 评论 1.5K 6188字阅读20分37秒

这篇文章主要介绍了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.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

脚本执行方式:

[root@Ansible scripts]# sh auto_authorization_mysql_db.sh

# 查询用户列表

Mysql数据库授权脚本# 授权

Mysql数据库授权脚本# 撤销权限

Mysql数据库授权脚本# 查询权限

Mysql数据库授权脚本# 删除用户及权限

Mysql数据库授权脚本

继续阅读
若文章图片、下载链接等信息出错,请在评论区留言反馈,博主将第一时间更新!如果喜欢本站,请打赏支持本站,谢谢!
  • 我的微信
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
Shell 最后更新:2020-7-27
小辉博客
  • 本文由 发表于 2020-07-2115:43:58
  • 除非特殊声明,本站文章均为原创,转载请务必保留本文链接
Docker 安装 MySQL Docker

Docker 安装 MySQL

Docker 安装 MySQL MySQL 是世界上最受欢迎的开源数据库。凭借其可靠性、易用性和性能,MySQL已成为Web应用程序的数据库优先选择。 1、查看可用的MySQL版本 ...
MySQL 连接 Mysql

MySQL 连接

MySQL 连接 使用mysql二进制方式连接 您可以使用MySQL二进制方式进入到mysql命令提示符下来连接MySQL数据库。 实例 以下是从命令行中连接mysql服务...
MySQL PHP 语法 Mysql

MySQL PHP 语法

MySQL PHP 语法 MySQL 可应用于多种语言,包括 PERL, C, C++, JAVA 和 PHP,在这些语言中,MySQL 在 PHP 的 web 开发中是应用最广泛。 在本...
匿名

发表评论

匿名网友 填写信息

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