WebSVN – ALCASAR – Diff – //scripts/alcasar-mariadb.sh

 #!/bin/bash
 # alcasar-mariadb.sh
-# by Franck BOUIJOUX, Pascal LEVANT and Richard REY
+# 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)
-# Gestion (sauvegarde / import / RAZ) de la base MariaDB 'radius'. Fermeture des sessions de comptabilité ouvertes
+#	- Fermeture des sessions de comptabilité ouvertes
+#	- Suppression des utilisateurs 7 jours après leur date d'expiration
-# Management of MariaDB 'radius' database (save / import / RAZ). Close the accounting open sessions
+#	- 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
+new="$(date +%G%m%d-%Hh%M)"		# date & hour of files
-fichier="alcasar-users-database-$new.sql"
+tmp_file="alcasar-users-database-$new.sql"
-stop_acct ()
+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
+{
+	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_user ()	# remove users whom expiration date has passed to 7 days
+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';"
+		/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_group () # remove users of group whom expiration date has passed to 7 days
+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
 			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 ]"
+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
 		echo "$usage"
 		exit 0
 		;;
 	-d | --dump | -dump)
 		[ -d $rep_tr ] || mkdir -p $rep_tr
-		if [ -e  $fichier ];
+		if [ -e  $tmp_file ];
-			then rm -f  $fichier
+			then rm -f  $tmp_file
 		fi
 		check
-		echo "Export the database in file : $fichier.gz"
+		echo "Export the database in file : $tmp_file.gz"
-		mariadb-dump -u $DB_USER -p$DB_PASS --opt -BcQC  $DB_RADIUS > $rep_tr/$fichier
+		mariadb-dump -u $DB_USER -p$DB_PASS --opt -BcQC  $DB_RADIUS > $rep_tr/$tmp_file
-		gzip -f $rep_tr/$fichier
+		gzip -f $rep_tr/$tmp_file
 		echo "End of export $( date "+%Hh %Mmn" )"
 		;;
 	-c | --check | -check)
 		check
 		;;
 			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/$fichier
+		mariadb-dump -u $DB_USER -p$DB_PASS --opt -BcQC  $DB_RADIUS > $rep_tr/$tmp_file
-		gzip -f $rep_tr/$fichier
+		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_user
+		expire_users
-		expire_group
+		expire_groups
+		;;
+	-o | --old_user)
+		remove_old_users
 		;;
 	*)
 		echo "Unknown argument :$1";
 		echo "$usage"
 		exit 1

Subversion Repositories ALCASAR

(root)//scripts/alcasar-mariadb.sh – Rev 3269 → 3274