Assign SELECT-only access for all tables/views (excluding randomization lists and those containint erik) for specified user to specified database

db_grant_select_access.sh

#!/bin/bash
################################################################################
# Grants privileges to specified user on specified database.  
#
# Grants/privileges currently defined in: generate_edc_select_grants.sql
# which provide select-only access to all tables in specified database, 
# excluding those containing 'rando' or 'erik' in their name.
#
# Usage:  db_grant_select_access.sh <user_to_grant_to> <database_to_grant_on>
#
################################################################################
set -e  # on error, exit early

# Set vars from passed args
user=$1
db=$2


# Validate command usage
usage_message="usage: db_grant_select_access.sh <user_to_grant_to> <database_to_grant_on>\n"
if [[ -z "${user}" ]]
then
  echo -e ${usage_message}
  echo "Error: Missing required argument: <user_to_grant_to>.  Exiting..."
  exit 1
elif [[ -z "${db}" ]]
then
  echo -e ${usage_message}
  echo "Error: Missing required argument: <database_to_grant_on>.  Exiting..."
  exit 1
fi


echo "Checking MySQL user '${user}' exists..."
user_exists="$(mysql \
  --silent --silent \
  --execute="SELECT EXISTS(SELECT 1 FROM mysql.user WHERE user = '${user}')" \
)"
if [[ "${user_exists}" -eq 1 ]]
then
  echo "Found MySQL user: ${user}"
else
  echo "Error: Command failed (see above) or MySQL user '${user}' does not exist.  Exiting..."
  exit 1
fi


echo "Checking MySQL database '${db}' exists..."
db_search_result="$(mysqlshow \
  "${db}" \
  | grep --invert-match Wildcard \
  | grep --only-matching --word-regexp ${db} \
)"
echo "$db_search_result"
if [[ "${db_search_result}" == "${db}" ]]
then
  echo "Found MySQL database: ${db_search_result}"
else
  echo "Error: Command failed (see above) or MySQL database '${db}' does not exist.  Exiting..."
  exit 1
fi


echo "Generating grant statements for '${user}' on '${db}'..."
grant_statements="$(mysql \
  --silent \
  --execute="SET @db_to_grant_on='${db}';SET @user_to_grant_to='${user}';SOURCE generate_edc_select_grants.sql;" \
)"


echo "Granting rights on '${db}' to '${user}'..."
mysql --execute="${grant_statements}"

echo "Displaying grants for '${user}'..."
mysql --execute="SHOW GRANTS FOR '${user}'@'localhost';" | sort

generate_edc_select_grants.sql

-- Generate SELECT-only GRANT statements to @user_to_grant_to
-- on ALL tables in @db_to_grant_on, except those with 'rando' or 'erik' in their name
SELECT CONCAT("GRANT SELECT ON ", table_schema, ".", table_name, " TO '", @user_to_grant_to,  "'@'localhost';")
FROM information_schema.TABLES
WHERE table_schema = @db_to_grant_on
    AND table_name NOT LIKE '%rando%'
    AND table_name NOT LIKE '%erik%'