Rev 3274 | Blame | Compare with Previous | Last modification | View Log
#!/bin/bash
# alcasar-mariadb.sh
# by Franck BOUIJOUX, Pascal LEVANT, Richard REY & Laurent ROUX
# This script is distributed under the Gnu General Public License (GPL)
# Actions sur la base MariaDB 'radius'
# - Gestion (sauvegarde / import / RAZ)
# - Fermeture des sessions de comptabilité ouvertes
# - Suppression des utilisateurs 7 jours après leur date d'expiration
# - Suppression des utilisateurs ne s'étant pas connectés depuis plus un an
# Action on MariaDB 'radius' database
# - Management (save / import / RAZ)
# - Close the accounting open sessions
# - Remove users 7 days after their expiry date
# - Remove users who haven't logged in for more than a year
rep_tr="/var/Save/base"
DIR_BIN="/usr/local/bin"
PASSWD_FILE="/root/ALCASAR-passwords.txt"
DB_RADIUS="radius"
DB_USER=$(grep '^db_user=' $PASSWD_FILE | cut -d'=' -f 2-)
DB_PASS=$(grep '^db_password=' $PASSWD_FILE | cut -d'=' -f 2-)
new="$(date +%G%m%d-%Hh%M)" # date & hour of files
tmp_file="alcasar-users-database-$new.sql"
stop_acct () # Close the accounting open sessions
{
date_now=`date "+%F %X"`
echo "UPDATE radacct SET acctstoptime = '$date_now', acctterminatecause = 'Admin-Reset' WHERE acctstoptime IS NULL" | mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS
}
check ()
{
echo "check (and repair if needed) the database :"
mariadb-check --databases $DB_RADIUS -u $DB_USER -p$DB_PASS --auto-repair
}
remove_old_users () # Remove users who haven't logged in for more than a year (TO BE VERIFY BEFORE ACTIVATE)
{
SQL_USER=""
SQL_USER=`/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --execute "SELECT username FROM (SELECT DISTINCT username FROM radacct WHERE acctstarttime >= NOW() - INTERVAL 1 YEAR) AS actifs;"`
for u in $SQL_USER
do
/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS --execute "DELETE FROM radusergroup WHERE username = '$u'; DELETE FROM radreply WHERE username = '$u'; DELETE FROM userinfo WHERE UserName = '$u'; DELETE FROM radcheck WHERE username = '$u';"
if [ $? = 0 ]
then
echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
else
echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
fi
done
}
expire_users () # Remove users 7 days after their expiry date
{
del_date=`date +%F`
SQL_USER=""
SQL_USER=`/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --execute "SELECT username FROM radcheck WHERE ( DATE_SUB(CURDATE(),INTERVAL 7 DAY) > STR_TO_DATE(value,'%d %M %Y')) AND attribute='Expiration';"`
for u in $SQL_USER
do
/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS --execute "DELETE FROM radusergroup WHERE username = '$u'; DELETE FROM radreply WHERE username = '$u'; DELETE FROM userinfo WHERE UserName = '$u'; DELETE FROM radcheck WHERE username = '$u';"
if [ $? = 0 ]
then
echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
else
echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
fi
done
}
expire_groups () # remove users of group whom expiration date has passed to 7 days
{
del_date=`date +%F`
SQL_GROUP=""
SQL_GROUP=`/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --execute "SELECT groupname FROM radgroupcheck WHERE ( DATE_SUB(CURDATE(),INTERVAL 7 DAY) > STR_TO_DATE(value,'%d %M %Y')) AND attribute='Expiration';"`
for g in $SQL_GROUP
do
SQL_USERGROUP=""
SQL_USERGROUP=`/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --execute "SELECT username FROM radusergroup WHERE groupname = '$g';"`
for u in $SQL_USERGROUP
do
/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS --execute "DELETE FROM radusergroup WHERE username = '$u'; DELETE FROM radreply WHERE username = '$u'; DELETE FROM userinfo WHERE UserName = '$u'; DELETE FROM radcheck WHERE username = '$u';"
if [ $? = 0 ]
then
echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
else
echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
fi
done
/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS --execute "DELETE FROM radgroupreply WHERE groupname = '$g'; DELETE FROM radgroupcheck WHERE groupname = '$g';"
if [ $? = 0 ]
then
echo "Group $g was deleted $del_date" >> /var/log/mysqld/delete_group.log
else
echo "Delete Group $g : Error $del_date" >> /var/log/mysqld/delete_group.log
fi
done
}
usage="Usage: alcasar-mariadb.sh { -d or --dump } | { -c or --check } | { -i or --import } | { -r or --raz } | { -a or --acct_stop } | { -e or --expire_user } | { -o or --old_users }"
nb_args=$#
args=$1
if [ $nb_args -eq 0 ]
then
nb_args=1
args="-h"
fi
case $args in
-\? | -h* | --h*)
echo "$usage"
exit 0
;;
-d | --dump | -dump)
[ -d $rep_tr ] || mkdir -p $rep_tr
if [ -e $tmp_file ];
then rm -f $tmp_file
fi
check
echo "Export the database in file : $tmp_file.gz"
mariadb-dump -u $DB_USER -p$DB_PASS --opt -BcQC $DB_RADIUS > $rep_tr/$tmp_file
gzip -f $rep_tr/$tmp_file
echo "End of export $( date "+%Hh %Mmn" )"
;;
-c | --check | -check)
check
;;
-i | --import | -import)
if [ $nb_args -ne 2 ]
then
echo "Enter a SQL file name ('.sql' or '.sql.gz')"
exit 0
else
case $2 in
*.sql.gz )
gunzip -f < $2 | mariadb -u $DB_USER -p$DB_PASS
stop_acct
;;
*.sql )
mariadb -u $DB_USER -p$DB_PASS < $2
stop_acct
;;
esac
migrationsPath="$DIR_BIN/alcasar-db-migrations"
"$migrationsPath/alcasar-migration_db-3.2.0.sh"
"$migrationsPath/alcasar-migration_db-3.3.0.sh"
"$migrationsPath/alcasar-migration_db-3.3.1.sh"
"$migrationsPath/alcasar-migration_db-3.7.0.sh"
fi
;;
-r | --raz | -raz)
mariadb-dump -u $DB_USER -p$DB_PASS --opt -BcQC $DB_RADIUS > $rep_tr/$tmp_file
gzip -f $rep_tr/$tmp_file
mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS < /etc/raddb/empty-radiusd-db.sql
;;
-a | --acct_stop | -acct_stop)
stop_acct
;;
-e | --expire_user)
expire_users
expire_groups
;;
-o | --old_user)
remove_old_users
;;
*)
echo "Unknown argument :$1";
echo "$usage"
exit 1
;;
esac