#!/bin/bash

###
# MySQL Remote Access Control Script
# This script generates lists of allowed remote IP addresses for incoming and outgoing MySQL connections,
# taking into account both IPv4 and IPv6 addresses. It then updates the firewall rules using CSF.
#
# Usage: This script is meant to be scheduled to run periodically, e.g., using cron.
#
# Source: https://gist.github.com/scysys/21994b8ec478c799fbdbbbd1ac5fb58c
###

# Generate list for incoming MySQL connections
echo "Generating list for incoming MySQL connections..."
mysql mysql -e "SELECT Host,User FROM user WHERE Host != 'localhost' GROUP BY Host;" | \
# Format and print incoming connection rules
awk 'NR>1 {print "tcp:in:d=3306:s=" $1 "\t# " $2'} | \
# Exclude wildcard entries
grep -v "%" | \
# Filter valid IPv4 and IPv6 addresses
grep -E "([0-9]{1,3}\.){3}[0-9]{1,3}|([0-9a-fA-F:]+)" | \
# Exclude local IP addresses
grep -v -E "(127\.0\.0\.1|::1)" | \
# Exclude hostnames, save to file
grep -v -E "([a-zA-Z0-9.-]+\.[a-zA-Z]{2,4})" > "/var/www/html/mysql/allow_remote_mysql_in.txt"

# Generate list for outgoing MySQL connections
echo "Generating list for outgoing MySQL connections..."
mysql mysql -e "SELECT Host,User FROM user WHERE Host != 'localhost' GROUP BY Host;" | \
# Format and print outgoing connection rules
awk 'NR>1 {print "tcp:out:d=3306:s=" $1 "\t# " $2'} | \
# Exclude wildcard entries
grep -v "%" | \
# Filter valid IPv4 and IPv6 addresses
grep -E "([0-9]{1,3}\.){3}[0-9]{1,3}|([0-9a-fA-F:]+)" | \
# Exclude local IP addresses
grep -v -E "(127\.0\.0\.1|::1)" | \
# Exclude hostnames, save to file
grep -v -E "([a-zA-Z0-9.-]+\.[a-zA-Z]{2,4})" > "/var/www/html/mysql/allow_remote_mysql_out.txt"

# Update firewall rules using CSF
echo "Updating firewall rules using CSF..."
/usr/sbin/csf -ra >/dev/null 2>&1  # Run CSF to reload rules

echo "Script Execution completed."