Mysql数据库授权脚本

原创

小柒博客2023 2022-12-30 15:45:26 ©著作权

文章标签 Code IP User 文章分类 运维

©著作权归作者所有：来自51CTO博客作者小柒博客2023的原创作品，请联系作者获取转载授权，否则将追究法律责任

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

脚本功能：

1、授权用户权限

2、回收用户权限

3、查询用户权限

4、删除用户及权限

5、支持批量IP

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

[root@Ansible scripts]# vim auto_authorization_mysql_db.sh

脚本内容如下：

1. #!/bin/bash
2. #Date：2020-7-15 16:28:10
3. #Author Blog:
4. #  https://www.yangxingzhen.com
5. #  https://www.i7ti.cn
6. #Author WeChat：
7. #  微信公众号：小柒博客
8. #Author mirrors site:
9. #  https://mirrors.yangxingzhen.com
10. #About the Author
11. # BY：YangXingZhen
12. # Mail：xingzhen.yang@yangxingzhen.com
13. # QQ：675583110
14. #执行脚本之前请执行命令：grant all on *.* to admin@'%' identified by 'CRDEP7X2zHUJCG^eZu2T*Wny' WITH GRANT OPTION;
15. #            flush privileges;
16. 
17. source /etc/rc.d/init.d/functions
18. 
19. User="admin"
20. Passwd="CRDEP7X2zHUJCG^eZu2T*Wny"
21. 
22. function query (){
23. Code=""
24. whiletrue
25. do
26.   read -p "$(echo -e "\033[32m请输入需要连接的Mysql主机IP[多个IP以空格分隔]：\033[0m")" IPADDR
27. if[-z ${IPADDR}];then
28.     echo -e "\033[31m输入错误,主机IP不能为空...\033[0m"
29. elif["${IPADDR}"="q"-o "${IPADDR}"="Q"];then
30. Code="break"
31. else
32. whiletrue
33. do
34.       read -p "$(echo -e "\033[32m请输入需要查询的用户名：\033[0m")" USER
35. if[-z ${USER}];then
36.         echo -e "\033[31m输入错误,用户名不能为空...\033[0m"
37. elif["${USER}"="q"-o "${USER}"="Q"];then
38. Code="break"
39. else
40. whiletrue
41. do
42.           read -p "$(echo -e "\033[32m请输入需要查询的授权连接地址：\033[0m")"Connection
43. if[-z ${Connection}];then
44.             echo -e "\033[31m输入错误,授权连接地址不能为空...\033[0m"
45. elif["${Connection}"="q"-o "${Connection}"="Q"];then
46. Code="break"
47. else
48. for i in ${IPADDR}
49. do
50.               mysql -h ${i}-u${User}-p"${Passwd}"-e "show grants for ${USER}@'"${Connection}"';"
51. return1
52. done
53. fi
54.         ${Code}
55. done
56. fi
57.     ${Code}
58. done
59. fi
60. ${Code}
61. done  
62. }
63. 
64. function revoke (){
65. Code=""
66. whiletrue
67. do
68.   read -p "$(echo -e "\033[32m请输入需要连接的Mysql主机IP[多个IP以空格分隔]：\033[0m")" IPADDR
69. if[-z ${IPADDR}];then
70.     echo -e "\033[31m输入错误,主机IP不能为空...\033[0m"
71. elif["${IPADDR}"="q"-o "${IPADDR}"="Q"];then
72. Code="break"
73. else
74. whiletrue
75. do
76.       read -p "$(echo -e "\033[32m请输入需要撤销权限的数据库名：\033[0m")" DB_NAME
77. if[-z ${DB_NAME}];then
78.         echo -e "\033[31m输入错误,权限列表不能为空...\033[0m"
79. elif["${DB_NAME}"="q"-o "${DB_NAME}"="Q"];then
80. Code="break"
81. else
82. whiletrue
83. do
84.           read -p "$(echo -e "\033[32m请输入需要撤销的权限列表[以逗号分开]：\033[0m")"List
85. if[-z ${List}];then
86.             echo -e "\033[31m输入错误,权限列表不能为空...\033[0m"
87. elif["${List}"="q"-o "${List}"="Q"];then
88. Code="break"
89. else
90. whiletrue
91. do
92.               read -p "$(echo -e "\033[32m请输入需要撤销权限的用户名：\033[0m")" USER
93. if[-z ${USER}];then
94.                 echo -e "\033[31m输入错误,用户名不能为空...\033[0m"
95. elif["${USER}"="q"-o "${USER}"="Q"];then
96. Code="break"
97. else
98. whiletrue
99. do
100.                  read -p "$(echo -e "\033[32m请输入需要撤销权限的连接地址：\033[0m")"Connection
101. if[-z ${Connection}];then
102.                    echo -e "\033[31m输入错误,授权连接地址不能为空...\033[0m"
103. elif["${Connection}"="q"-o "${Connection}"="Q"];then
104. Code="break"
105. else
106. for i in ${IPADDR}
107. do
108.                      mysql -h ${i}-u${User}-p"${Passwd}"-e "revoke ${List} on ${DB_NAME}.* from ${USER}@'"${Connection}"';"
109. if[ $(mysql -h ${i}-u${User}-p"${Passwd}"-e "show grants for ${USER}@'"${Connection}"';"|grep -wc "${List}")-eq 0];then
110.                        action "Removed User ${USER} Permission Success..."/bin/true
111. return1
112. else
113.                        action "Removed User ${USER} Permission Failed..."/bin/false
114. return1
115. fi
116. done
117. fi
118.                ${Code}
119. done
120. fi
121.            ${Code}
122. done
123. fi
124.        ${Code}
125. done
126. fi
127.    ${Code}
128. done
129. fi
130. ${Code}
131. done
132. }
133. 
134. function update (){
135. Code=""
136. whiletrue
137. do
138.  read -p "$(echo -e "\033[32m请输入需要授权的Mysql主机IP[多个IP以空格分隔]：\033[0m")" IPADDR
139. if[-z ${IPADDR}];then
140.    echo -e "\033[31m输入错误,主机IP不能为空...\033[0m"
141. elif["${IPADDR}"="q"-o "${IPADDR}"="Q"];then
142. Code="break"
143. else
144. whiletrue
145. do
146.      read -p "$(echo -e "\033[32m请输入需要授权的数据库名：\033[0m")" DB_NAME
147. if[-z ${DB_NAME}];then
148.        echo -e "\033[31m输入错误,数据库名不能为空...\033[0m"
149. elif["${DB_NAME}"="q"-o "${DB_NAME}"="Q"];then
150. Code="break"
151. else
152. whiletrue
153. do
154.          read -p "$(echo -e "\033[32m请输入需要授权的权限列表[以逗号分开]：\033[0m")"Permissions
155. if[-z ${Permissions}];then
156.            echo -e "\033[31m输入错误,权限列表不能为空...\033[0m"
157. elif["${Permissions}"="q"-o "${Permissions}"="Q"];then
158. Code="break"
159. else
160. whiletrue
161. do
162.              read -p "$(echo -e "\033[32m请输入需要授权的远程登录地址：\033[0m")"Login
163. if[-z ${Login}];then
164.                echo -e "\033[31m输入错误,远程登录地址不能为空...\033[0m"
165. elif["${Login}"="q"-o "${Login}"="Q"];then
166. Code="break"
167. else
168. whiletrue
169. do
170.                  read -p "$(echo -e "\033[32m请输入需要授权的用户名：\033[0m")" USER
171. if[-z ${USER}];then
172.                    echo -e "\033[31m输入错误,用户名不能为空...\033[0m"
173. elif["${USER}"="q"-o "${USER}"="Q"];then
174. Code="break"
175. else
176. whiletrue
177. do
178.                      read -p "$(echo -e "\033[32m请输入需要授权的用户名密码：\033[0m")" PASSWD
179. if[-z ${PASSWD}];then
180.                        echo -e "\033[31m输入错误,用户名不能为空...\033[0m"
181. elif["${PASSWD}"="q"-o "${PASSWD}"="Q"];then
182. Code="break"
183. else
184. for i in ${IPADDR}
185. do
186.                          mysql -h ${i}-u${User}-p"${Passwd}"-e "grant ${Permissions} on ${DB_NAME}.* to ${USER}@'"${Login}"' identified by '"${PASSWD}"';"
187.                          mysql -h ${i}-u${User}-p"${Passwd}"-e "flush privileges;"
188. if[ $(mysql -h ${i}-u${User}-p"${Passwd}"-e "show grants for ${USER}@'"${Login}"';"|grep -wc "${USER}")-ne 0];then
189.                            action "This IP: ${i} Authorized User ${USER} Success..."/bin/true
190. return1
191. else
192.                            action "This IP: ${i} Authorized User ${USER} Failed..."/bin/false
193. return1
194. fi
195. done
196. fi
197.                    ${Code}
198. done
199. fi
200.                ${Code}
201. done 
202. fi
203.            ${Code}
204. done
205. fi
206.        ${Code}
207. done
208. fi
209.    ${Code}
210. done
211. fi
212. ${Code}  
213. done
214. }
215. 
216. functiondelete(){
217. Code=""
218. whiletrue
219. do
220.  read -p "$(echo -e "\033[32m请输入需要连接的Mysql主机IP[多个IP以空格分隔]：\033[0m")" IPADDR
221. if[-z ${IPADDR}];then
222.    echo -e "\033[31m输入错误,主机IP不能为空...\033[0m"
223. elif["${IPADDR}"="q"-o "${IPADDR}"="Q"];then
224. Code="break"
225. else
226. whiletrue
227. do
228.      read -p "$(echo -e "\033[32m请输入需要删除的用户名：\033[0m")" USER
229. if[-z ${USER}];then
230.        echo -e "\033[31m输入错误,用户名不能为空...\033[0m"
231. elif["${USER}"="q"-o "${USER}"="Q"];then
232. Code="break"
233. else
234. whiletrue
235. do
236.          read -p "$(echo -e "\033[32m请输入需要删除的授权连接地址：\033[0m")"Connection
237. if[-z ${Connection}];then
238.            echo -e "\033[31m输入错误,授权连接地址不能为空...\033[0m"
239. elif["${Connection}"="q"-o "${Connection}"="Q"];then
240. Code="break"
241. else
242. for i in ${IPADDR}
243. do
244.              mysql -h ${i}-u${User}-p"${Passwd}"-e "delete from mysql.user where user='"${USER}"' and host='"${Connection}"';"
245. if[ $(mysql -h ${i}-u${User}-p"${Passwd}"-e "select user,host from mysql.user;"|grep -wc "${USER}")-eq 0];then
246.                action "Deleted User ${USER} Success..."/bin/true
247. return1
248. else
249.                action "Deleted User ${USER} Failed..."/bin/false
250. return1
251. fi
252. done
253. fi
254.        ${Code}
255. done
256. fi
257.    ${Code}
258. done
259. fi
260. ${Code}
261. done 
262. }
263. 
264. function list (){
265. "
266. whiletrue
267. do
268.  read -p "$(echo -e "\033[32m请输入需要获取列表的Mysql主机IP[多个IP以空格分隔]：\033[0m")" IPADDR
269. if[-z ${IPADDR}];then
270.    echo -e "\033[31m输入错误,主机IP不能为空...\033[0m"
271. elif["${IPADDR}"="q"-o "${IPADDR}"="Q"];then
272. Code="break"
273. else
274. for i in ${IPADDR}
275. do
276.      mysql -h ${i}-u${User}-p"${Passwd}"-e "select user,host from mysql.user;"
277. 
278. done
279. fi
280. break
281. done 
282. }
283. 
284. functionMain(){
285. stty erase '^H'
286. Code=""
287. whiletrue
288. do
289.  read -p "$(echo -e "\033[32m请输入需要执行的参数：\033[0m")"Value
290. case"${Value}"in
291. select)
292.    query
293. ;;
294.    revoke)
295.    revoke
296. ;;
297.    update)
298.    update
299. ;;
300. delete)
301. delete
302. ;;
303.    list)
304.    list
305. ;;
306. "q"|"Q")
307. exit1
308. ;;
309. *)
310.    echo -e "\033[32m参数名称：\033[0m{select|revoke|update|delete|list}"
311. ;;
312. esac
313. ${Code}
314. done
315. }
316. 
317. Main