Skip to content

Instantly share code, notes, and snippets.

@JonathanWillitts
Last active May 25, 2023 10:08
Show Gist options
  • Save JonathanWillitts/4e03ecfcd90fc71bd9968c4733e40676 to your computer and use it in GitHub Desktop.
Save JonathanWillitts/4e03ecfcd90fc71bd9968c4733e40676 to your computer and use it in GitHub Desktop.
Assign SELECT-only access for all tables/views (excluding randomization lists and those containint erik) for specified user to specified database
#!/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 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%'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment