#!/bin/sh
# Auth: ET
# Date: 2024-03-25

Host="localhost"
Port=3306
User="root"
Passwd="xxxxxxx"

usage() {
  echo 'mysql_create_user_auto.sh  <database_name>  <user_name>  read/readWrite/all/revoke'
  echo 'mysql_create_user_auto.sh  dropUser  <user_name>'
}

which mysql > /dev/null 2>&1
if [ $? -ne 0 ];then
  echo "Please Install mysql client."
  echo "Such as : \$ yum install http://devops.drugeyes.vip:8000/tools/mysql/5.7.44/mysql-community-client-5.7.44-1.el7.x86_64.rpm "
  exit
fi


if [ $# -ne 3 ];then
  if [ $1 = "dropUser" ];then
    if [ $# -ne 2 ];then
      usage
      exit
    else
      sql="drop user '$2'@'%';"
      mysql -h $Host -P $Port  -u $User -p$Passwd  -e "${sql}" 2>/dev/null && echo "drop user $2 Success !" || echo "drop user $2 Failed or user not exist !"
      exit
    fi
  else
    usage;
    exit
  fi
fi

newDatabase=$1
newUser=$2
newPasswd=$(openssl rand -base64 14)
newPri=$3

findUser() {
   userName=$1
   sql="select user from mysql.user;"
   mysql -h $Host -P $Port -u $User -p$Passwd  -e "${sql}" 2>/dev/null| grep "^$userName$"
}

findDatabase() {
   dbName=$1
   sql="SHOW DATABASES LIKE '${dbName}';"
   mysql -h $Host -P $Port -u $User -p$Passwd  -e "${sql}" 2>/dev/null| grep "^${dbName}$"| wc -l
}

# check database is exit or not
if [ $(findDatabase ${newDatabase}) -eq 1  ];then
   echo "find Database ${newDatabase}"
else
   echo "${newDatabase} is not exist! exit!"
   exit
fi

# 如果找到了该用户,那就直接授权
if [ $(findUser $newUser|wc -l) -ne 0 ];then

  if [ $newPri = 'read' ];then
    sql="grant SELECT on \`$newDatabase\`.* to '$newUser'@'%' with grant option;FLUSH PRIVILEGES;"
    mysql -h $Host -P $Port -u $User -p$Passwd  -e "${sql}"
    if [ $? -eq 0 ];then
       echo "user: $newUser is exist , grant SELECT privileges to $newUser on $newDatabase Success!"
    else
       echo "user: $newUser is exist , grant SELECT privileges to $newUser on $newDatabase Failed !"
    fi
  elif [ $newPri = 'readWrite' ];then
    sql="grant SELECT,INSERT,DELETE,UPDATE,DROP on \`$newDatabase\`.* to '$newUser'@'%' with grant option;FLUSH PRIVILEGES;"
    mysql -h $Host -P $Port -u $User -p$Passwd  -e "${sql}"
    if [ $? -eq 0 ];then
       echo "user: $newUser is exist , grant SELECT,INSERT,DELETE,UPDATE,DROP privileges to $newUser on $newDatabase Success!"
    else
       echo "user: $newUser is exist , grant SELECT,INSERT,DELETE,UPDATE,DROP privileges to $newUser on $newDatabase Failed !"
    fi

  elif [ $newPri = 'all' ];then
    sql="grant all on \`$newDatabase\`.* to '$newUser'@'%' with grant option;FLUSH PRIVILEGES;"
    mysql -h $Host -P $Port -u $User -p$Passwd  -e "${sql}"
    if [ $? -eq 0 ];then
       echo "user: $newUser is exist , grant all privileges to $newUser on $newDatabase Success!"
    else
       echo "user: $newUser is exist , grant all privileges to $newUser on $newDatabase Failed !"
    fi
  elif [ $newPri = 'revoke' ];then
    sql="revoke all on \`$newDatabase\`.* from '$newUser'@'%';"
    mysql -h $Host -P $Port -u $User -p$Passwd  -e "${sql}"
    if [ $? -eq 0 ];then
       echo "user: $newUser is exist , revoke all privileges to $newUser on $newDatabase Success!"
    else
       echo "user: $newUser is exist , revoke all privileges to $newUser on $newDatabase Failed!"
    fi

  else
    usage;
    exit
  fi

# 如果没找到该用户，就创建该用户并授权
else 

  if [ $newPri = 'read' ];then
    sql="create user '$newUser'@'%' identified by '$newPasswd'; grant SELECT on \`$newDatabase\`.* to '$newUser'@'%' with grant option;FLUSH PRIVILEGES;"
    mysql -h $Host -P $Port -u $User -p$Passwd  -e "${sql}"
    if [ $? -eq 0 ];then
       echo "Create user success , password: $newPasswd  ,  grant SELECT privileges to $newUser on $newDatabase Success!"
    else 
       echo "Create user failed: $newUser."
    fi
  elif [ $newPri = 'readWrite' ];then
    sql="create user '$newUser'@'%' identified by '$newPasswd'; grant SELECT,INSERT,DELETE,UPDATE,DROP on \`$newDatabase\`.* to '$newUser'@'%' with grant option;FLUSH PRIVILEGES;"
    mysql -h $Host -P $Port -u $User -p$Passwd  -e "${sql}"
    if [ $? -eq 0 ];then
       echo "Create user success , password: $newPasswd  ,  grant SELECT,INSERT,DELETE,UPDATE,DROP privileges to $newUser on $newDatabase Success!"
    else 
       echo "Create user failed: $newUser."
    fi
  elif [ $newPri = 'all' ];then
    sql="create user '$newUser'@'%' identified by '$newPasswd'; grant all on \`$newDatabase\`.* to '$newUser'@'%' with grant option;FLUSH PRIVILEGES;"
    mysql -h $Host -P $Port -u $User -p$Passwd  -e "${sql}"
    if [ $? -eq 0 ];then
       echo "Create user success , password: $newPasswd  ,  grant all privileges to $newUser on $newDatabase Success!"
    else 
       echo "Create user failed: $newUser."
    fi
  else
    usage;
    exit
  fi

fi
