[database] create role and grant access to tables following a text file

role.sh

#!/bin/bash


############################################################
# Usage                                                    #
############################################################
usage()
{
   # Display Help
   echo ""
   echo "Create a role and grant access to tables following a text file."
   echo
   echo "Syntax: command [-h|u|p|r|e]"
   echo "options:"
   echo "h     Host"
   echo "u     Username"
   echo "p     Password"
   echo "r     Repository (sample)"
   echo "e     Environment (dev prod) !optional"
   echo
}


############################################################
############################################################
# Main program                                             #
############################################################
############################################################

############################################################
# Set variables                                            #
############################################################
repositories=("sample")
host=
user=
password=
repository=
environment="dev"

############################################################
# Check option exists                                      #
############################################################
if [ $# -eq 0 ]; then
  usage
  exit
fi

############################################################
# Process the input options. Add options as needed.        #
############################################################
# Get the options
while getopts ":h:u:p:r:e:" option; do
  case $option in
  	h) # host
    	host=${OPTARG}
    	;;
    u) # user
			user=${OPTARG}
			;;
		p) # password
			password=${OPTARG}
			;;
		r) # repository, must be in $repositories
			repository=${OPTARG}
			;;
		e) # environment (optional)
			environment=${OPTARG}
			;;
    \?) # Invalid option
      echo "Error: Invalid option"
      usage
      exit;;
  esac
done

# Validation
if [ -z "$host" ] \
	|| [ -z "$user" ] \
	|| [ -z "$password" ] \
	|| [[ ! " ${repositories[*]} " =~ " ${repository} " ]]
then
	echo "Error: Invalid input"
  usage
  exit
fi

echo "Running command with options:"
echo "host: $host"
echo "user: $user"
echo "password: $password"
echo "repository: $repository"
echo "environment: $environment"

############################################################
# Source file. Execute.        												 		 #
############################################################
# create role if not exists
mysql -h $host -u $user -p$password -N -se "CREATE ROLE IF NOT EXISTS 'role_${repository}'@'localhost'"

# get tables
filename="$repository.txt"
tables=()
while read line; do
	tables+=($line)
done < "$( dirname -- "$0" )/$filename"

# get current DB tables
mapfile db_tables_records  < <( mysql -h $host -u $user -p$password -N -se "SHOW GRANTS FOR 'role_${repository}'@'localhost'" )
db_tables=()

for table in "${db_tables_records[@]}"; do
  if [[ "$table" == *"USAGE"* ]]; then
    continue
  fi
  db_tables+=($(echo $table | sed -e "s/GRANT .* ON//g" -e "s/TO .*//g" -e "s/[[:blank:]]//g" -e "s/\`//g"))
done

# analyze which table should be removed
to_be_removed=()
for table in "${db_tables[@]}"; do
  if [[ ! " ${tables[*]} " =~ " ${table} " ]]; then
    to_be_removed+=("$table")
  fi
done

# analyze which table should be added
to_be_added=()
for table in "${tables[@]}"; do
  if [[ ! " ${db_tables[*]} " =~ " ${table} " ]]; then
    to_be_added+=("$table")
  fi
done

# print all variables for debug
echo ""
echo "Variables that we're working on"
echo "TABLES: ${tables[@]}"
echo "DB_TABLES: ${db_tables[@]}"
echo "TO_BE_REMOVED: ${to_be_removed[@]}"
echo "TO_BE_ADDED: ${to_be_added[@]}"

# the query that grant/revoke tables on a role
sql_queries=""

# revoke tables from role
for table in "${to_be_removed[@]}"; do
  if [[ $environment == "prod" ]]; then
    # only revoke select on prod
    sql_queries="${sql_queries}REVOKE SELECT ON ${table} FROM 'role_${repository}'@'localhost';
    "
  else
    # revoke all
    sql_queries="${sql_queries}REVOKE ALL ON ${table} FROM 'role_${repository}'@'localhost';
    "
  fi
done

# grant privileges on tables to role
for table in "${to_be_added[@]}"; do
  if [[ $environment == "prod" ]]; then
    # only grant select on prod
    sql_queries="${sql_queries}GRANT SELECT ON ${table} TO 'role_${repository}'@'localhost';
    "
  else
    # grant all
    sql_queries="${sql_queries}GRANT ALL ON ${table} TO 'role_${repository}'@'localhost';
    "
  fi
done

echo "SQL queries: $sql_queries"
mysql -h $host -u $user -p$password -N -se "$sql_queries"

############################################################
# Validate privileges on tables                            #
############################################################
# get latest DB tables
mapfile db_tables_records  < <( mysql -h $host -u $user -p$password -N -se "SHOW GRANTS FOR 'role_${repository}'@'localhost'" )
db_tables=()

for table in "${db_tables_records[@]}"; do
  if [[ "$table" == *"USAGE"* ]]; then
    continue
  fi
  db_tables+=($(echo $table | sed -e "s/GRANT .* ON//g" -e "s/TO .*//g" -e "s/[[:blank:]]//g" -e "s/\`//g"))
done

# validate tables
echo "Validating tables..."
for table in "${tables[@]}"; do
  if [[ ! " ${db_tables[*]} " =~ " ${table} " ]]; then
    echo "Error: ${table} not in DB" 1>&2
    exit -1
  fi
done

echo "Job Successful !!!"

sample.txt

sp.users
sp.products
sp.orders