一个MySQL自动收回用户权限的小脚本

原创

懂点啥OL 2021-07-12 09:26:28 ©著作权

文章标签 MySQL 文章分类 MySQL 数据库

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

刚上班的时候感觉天天给开发同事授权和撤权太麻烦了，有时候会忘了收回权限，所以想写个脚本实现自动收回权限的功能，省的每次还要想着收回权限。

功能：

+---------------+
+[1]  查看用户  +
+[2]  创建用户  +
+[3]  查看权限  +
+[4]  授权用户  +
+[5]  撤权用户  +
+[6]  修改密码  +
+[7]  锁定用户  +
+[8]  解锁用户  +
+[9]  删除用户  +
+   任意键退出  +
+---------------+

思路：

执行正确给出友好反馈
执行失败直接系统报错
- 方便排查问题
- 错误友好显示编程太浪费时间

使用方法：

# 配置文件指定账号密码(其他路径也可以，需要在脚本中指定)
vim /etc/my.cnf
user=root
password=***
host=***
sockte=****

# 调用

sh ~/user_actions.sh

脚本：

vim ~/user_actions.sh
echo "
全部功能：
+---------------+
+[1]  查看用户  +
+[2]  创建用户  +
+[3]  查看权限  +
+[4]  授权用户  +
+[5]  撤权用户  +
+[6]  修改密码  +
+[7]  锁定用户  +
+[8]  解锁用户  +
+[9]  删除用户  +
+   任意键退出  +
+---------------+
"
read  -t 600   -p "请输入序号: " -e  Sum
case ${Sum} in
    
    1)
Variable='Select_User'
    ;;
    2)
Variable='Create_User'
    ;;
    3)
Variable='Show_Grants'
    ;;
    4)
Variable='Grant_User'
    ;;
    5)
Variable='Revoke_User'
    ;;
    6)
Variable='Update_Pass'
    ;;
    7)
Variable='Lock_User'
    ;;
    8)
Variable='Unlock_User'
    ;;
    9)
Variable='Delete_User'
    ;;
    *)
    exit
    ;;
esac
# 查看用户
function Select_User(){
/usr/bin/echo "select user as 用户名,host as 主机域,account_locked as 是否锁定 from mysql.user;" |HOME=/etc/my.cnf  mysql|column -t
}
# 创建用户
function Create_User(){
read  -t 600   -p "请输入用户名: " -e  Username
if [[ ! ${Username} =~ ^[a-zA-Z\_]+$ ]];then
    echo "请输入英文字母"
else
    read  -t 600   -p "请输入密码: " -e    Passwd
    if [[ ! ${Passwd} =~ ^[a-zA-Z0-9\.\!\@\#\$]+$ ]];then
        echo "请输入英文或数字"
    else
        read  -t 600   -p "请输入主机域: " -e    Host
        /usr/bin/echo "create user '${Username}'@'${Host}' identified by '${Passwd}';" |HOME=/etc/my.cnf  mysql
        if [[ $? -eq 0 ]];then
            /usr/bin/echo "flush  privileges;" |HOME=/etc/my.cnf  mysql
            echo "创建 [${Username}@${Host}] 用户成功"
        fi
    fi
fi
}
# 查看权限
function Show_Grants(){
read  -t 600   -p "请输入用户名: " -e  Username
if [[ ! ${Username} =~ ^[a-zA-Z\_]+$ ]];then
    echo "请输入英文字母"
else
    read  -t 600   -p "请输入主机域: " -e    Host
    echo  "
详细权限:
"
/usr/bin/echo "show grants for '${Username}'@'${Host}';" |HOME=/etc/my.cnf mysql|sed  '/GRANT/i===============================================================================>'
fi
}
# 授权用户
function Grant_User(){
read  -t 600   -p "请输入用户名: " -e  Username
if [[ ! ${Username} =~ ^[a-zA-Z\_]+$ ]];then
    echo "请输入英文字母"
else
    read  -t 600   -p "请输入主机域: " -e    Host
        /usr/bin/echo "select user,host from mysql.user where user='${Username}' and host='${Host}'; " |HOME=/etc/my.cnf  mysql|grep -w "${Username}"|grep -w "${Host}"
        if [[ ! $? -eq 0 ]];then
                echo "用户不存在"
                exit
        fi
        read  -t 600   -p "请输入授权的权限并用 [,] 分隔,如需授权字段请在权限名前添加字段名 例: select(user,host) : " -e Grant
    read  -t 600   -p "请输入授权的库名: " -e Database
        read  -t 600   -p "请输入授权的表名: " -e Table
        /usr/bin/echo "select TABLE_SCHEMA,TABLE_NAME from information_schema.tables where TABLE_SCHEMA='${Database}' and TABLE_NAME='${Table}';" |HOME=/etc/my.cnf  mysql|grep  -w "${Database}"|grep -w "${Table}" >/dev/null
        if [[ ! $? -eq 0 ]];then
                echo "库或者表不存在"
                exit
        fi
        /usr/bin/echo  "grant  ${Grant} on "${Database}"."${Table}" to '${Username}'@'${Host}';"|HOME=/etc/my.cnf  mysql
        if [[ $? -eq 0 ]];then
                /usr/bin/echo  "flush  privileges;"|HOME=/etc/my.cnf  mysql
                echo " [${Username}@${Host}用户 ${Database}库 ${Table}表 ${Grant}权限] 授权成功"
        else
           exit
        fi
        read  -t 600   -p "是否定时回收权限 [yes/no]: " -e Revoke
    if [[ ${Revoke} == y || ${Revoke} == Y || ${Revoke} == yes || ${Revoke} == YES ]];then
                read  -t 600   -p "请指定回收时间为多少小时: " -e Revoke_time
                        if [[ ! ${Revoke_time} =~ ^[0-9]+$ ]];then
                                echo "请输入小时数"
                        fi
                read  -t 600   -p "是否确定 【${Revoke_time}】 小时后回收 【${Grant}】权限 [yes/no]: " -e Revoke_yes
                        if [[ ${Revoke_yes} == y || ${Revoke_yes} == Y || ${Revoke_yes} == yes || ${Revoke_yes} == YES ]];then
mkdir -p  ~/cron.d/
Time=$(date +%Y_%m_%d_%k_%M_%S)
Script_time=$(echo "60 * 60 * ${Revoke_time}"|bc |awk -F '.' '{print $1}')
cat >~/cron.d/${Username}_${Host}_${Time}.sh<<EOF
#!/bin/bash
echo "
${Time} 【revoke ${Grant}  on "${Database}"."${Table}" from '${Username}'@'${Host}';】权限等待回收" >>~/cron.d/cron_time.log
sleep ${Script_time}
/usr/bin/echo "revoke  ${Grant}  on "${Database}"."${Table}" from '${Username}'@'${Host}';"|HOME=/etc/my.cnf  mysql >>~/cron.d/cron_time.log
if [[ ! $? -eq 0 ]];then
echo "
${Time} 【revoke ${Grant}  on "${Database}"."${Table}" from '${Username}'@'${Host}';】权限回收失败" >>~/cron.d/cron_time.log
else
echo "
${Time} 【revoke ${Grant}  on "${Database}"."${Table}" from '${Username}'@'${Host}';】权限回收成功" >>~/cron.d/cron_time.log
fi
rm -f ~/cron.d/${Username}_${Host}_${Time}.sh
/usr/bin/echo  "flush  privileges;"|HOME=/etc/my.cnf  mysql
EOF
nohup /usr/bin/sh  ~/cron.d/${Username}_${Host}_${Time}.sh >/dev/null 2>&1 & 
        else
                exit
        fi
        fi
fi
}
# 撤权用户
function Revoke_User(){
read  -t 600   -p "请输入用户名: " -e  Username
if [[ ! ${Username} =~ ^[a-zA-Z\_]+$ ]];then
    echo "请输入英文字母"
else
    read  -t 600   -p "请输入主机域: " -e    Host
        /usr/bin/echo "select user,host from mysql.user where user='${Username}' and host='${Host}'; " |HOME=/etc/my.cnf  mysql|grep -w "${Username}"|grep -w "${Host}" >/dev/null
        if [[ ! $? -eq 0 ]];then
                echo "用户不存在"
                exit
        fi
        read  -t 600   -p "请输入收回的权限并用 [,] 分隔,如需收回字段请在权限名前添加字段名 例: select(user,host) : " -e Grant
    read  -t 600   -p "请输入回收的库名: " -e Database
        read  -t 600   -p "请输入回收的表名: " -e Table
        /usr/bin/echo "select TABLE_SCHEMA,TABLE_NAME from information_schema.tables where TABLE_SCHEMA='${Database}' and TABLE_NAME='${Table}';" |HOME=/etc/my.cnf  mysql|grep  -w "${Database}"|grep -w "${Table}" >/dev/null
        if [[ ! $? -eq 0 ]];then
                echo "库或者表不存在"
                exit
        fi
                read  -t 600   -p "是否设置定时回收权限 [yes/no]: " -e Cron_time
                if [[ ${Cron_time} == y || ${Cron_time} == Y || ${Cron_time} == yes || ${Cron_time} == YES ]];then
                read  -t 600   -p "请指定回收时间为多少小时: " -e Revoke_time
            if [[ ! ${Revoke_time} =~ ^[0-9]+$ ]];then
                echo "请输入小时数"
                                exit
            fi
mkdir -p  ~/cron.d/
Time=$(date +%Y_%m_%d_%k_%M_%S)
Script_time=$(echo "60 * 60 * ${Revoke_time}"|bc |awk -F '.' '{print $1}')
cat >~/cron.d/${Username}_${Host}_${Time}.sh<<EOF
#!/bin/bash
echo "
${Time} 【revoke ${Grant}  on "${Database}"."${Table}" from '${Username}'@'${Host}';】权限等待回收" >>~/cron.d/cron_time.log
sleep ${Script_time}
/usr/bin/echo "revoke  ${Grant}  on "${Database}"."${Table}" from '${Username}'@'${Host}';"|HOME=/etc/my.cnf  mysql >>~/cron.d/cron_time.log
if [[ ! $? -eq 0 ]];then
echo "
${Time} 【revoke ${Grant}  on "${Database}"."${Table}" from '${Username}'@'${Host}';】权限回收失败" >>~/cron.d/cron_time.log
else
echo "
${Time} 【revoke ${Grant}  on "${Database}"."${Table}" from '${Username}'@'${Host}';】权限回收成功" >>~/cron.d/cron_time.log
fi
rm -f ~/cron.d/${Username}_${Host}_${Time}.sh
/usr/bin/echo  "flush  privileges;"|HOME=/etc/my.cnf  mysql
EOF
nohup /usr/bin/sh  ~/cron.d/${Username}_${Host}_${Time}.sh >/dev/null 2>&1 &
echo "系统将于【${Revoke_time}】 小时后回收 【${Grant}】权限"   
else
        /usr/bin/echo  "revoke  ${Grant} on "${Database}"."${Table}" from '${Username}'@'${Host}';"|HOME=/etc/my.cnf  mysql
        if [[ $? -eq 0 ]];then
                echo " [${Username}@${Host} 用户 ${Database}库 ${Table}表 ${Grant}权限] 回收成功"
        fi
    fi
fi
}
# 修改密码
function Update_Pass(){
read  -t 600   -p "请输入用户名: " -e  Username
if [[ ! ${Username} =~ ^[a-zA-Z\_]+$ ]];then
    echo "请输入英文字母"
else
    read  -t 600   -p "请输入新密码: " -e    Passwd
    if [[ ! ${Passwd} =~ ^[a-zA-Z0-9\.\!\@\#\$\%]+$ ]];then
        echo "请输入英文或数字"
    else
        read  -t 600   -p "请输入主机域: " -e    Host
        read  -t 600   -p "是否修改 [${Username}@${Host}] 用户密码 [yes/no]: " -e Update
        if [[ ${Update} == y || ${Update} == Y ||  ${Update} == yes || ${Update} == YES ]];then
            /usr/bin/echo "alter user '${Username}'@'${Host}' identified by '${Passwd}';" |HOME=/etc/my.cnf  mysql
            if [[ $? -eq 0 ]];then
                /usr/bin/echo "flush  privileges;" |HOME=/etc/my.cnf  mysql
                echo "修改 [${Username}@${Host}] 用户密码成功"
            fi
        else
            echo  "取消修改"
            exit
        fi
    fi
fi
}
# 锁定用户
function Lock_User(){
read  -t 300   -p "请输入用户名: " -e  Username
if [[ ! ${Username} =~ ^[a-zA-Z\_]+$ ]];then
    echo "请输入英文字母"
else
    read  -t 300   -p "请输入主机域: " -e    Host
    read  -t 300   -p "是否锁定 [${Username}@${Host}] 用户 [yes/no]: " -e Lock
    if [[ ${Lock} == y || ${Lock} == Y ||  ${Lock} == yes || ${Lock} == YES ]];then
                /usr/bin/echo "alter user '${Username}'@'${Host}' ACCOUNT LOCK;" |HOME=/etc/my.cnf  mysql
                if [[ $? -eq 0 ]];then
                        echo "[${Username}@${Host}] 用户已锁定"
                fi
        else
                echo "取消锁定"
                exit
        fi
fi
}
# 解锁用户
function Unlock_User(){
read  -t 300   -p "请输入用户名: " -e  Username
if [[ ! ${Username} =~ ^[a-zA-Z\_]+$ ]];then
    echo "请输入英文字母"
else
    read  -t 300   -p "请输入主机域: " -e    Host
    read  -t 300   -p "是否解锁 [${Username}@${Host}] 用户 [yes/no]: " -e Lock
    if [[ ${Lock} == y || ${Lock} == Y ||  ${Lock} == yes || ${Lock} == YES ]];then
                /usr/bin/echo "alter user '${Username}'@'${Host}' ACCOUNT UNLOCK;" |HOME=/etc/my.cnf  mysql
                if [[ $? -eq 0 ]];then
                        echo "[${Username}@${Host}] 用户已解锁"
                fi
        else
                echo "取消解锁"
                exit
        fi
fi
}
# 删除用户
function Delete_User(){
read  -t 600   -p "请输入用户名: " -e  Username
if [[ ! ${Username} =~ ^[a-zA-Z\_]+$ ]];then
    echo "请输入英文字母"
else
    read  -t 600   -p "请输入主机域: " -e  Host
    read  -t 600   -p "是否删除 [${Username}@${Host}] 用户 [yes/no]: " -e Remove
    if [[ ${Remove} == y || ${Remove} == Y || ${Remove} == yes || ${Remove} == YES ]];then
    /usr/bin/echo "drop user '${Username}'@'${Host}';" |HOME=/etc/my.cnf  mysql
        if [[ $? -eq 0 ]];then
                /usr/bin/echo "flush  privileges;" |HOME=/etc/my.cnf  mysql
                echo "删除 [${Username}@${Host}] 用户成功"
        fi
    else
        echo  "取消删除"
        exit
    fi
fi
}
${Variable}