Subversion Repositories ALCASAR

Rev

Rev 3274 | Details | Compare with Previous | Last modification | View Log

Rev Author Line No. Line
3269 rexy 1 
#!/bin/bash
2 
 
3 
# alcasar-mariadb.sh
3274 rexy 4 
# by Franck BOUIJOUX, Pascal LEVANT, Richard REY & Laurent ROUX
3269 rexy 5 
# This script is distributed under the Gnu General Public License (GPL)
6 
 
3274 rexy 7 
# Actions sur la base MariaDB 'radius'
8 
#	- Gestion (sauvegarde / import / RAZ)
9 
#	- Fermeture des sessions de comptabilité ouvertes
10 
#	- Suppression des utilisateurs 7 jours après leur date d'expiration
11 
#	- Suppression des utilisateurs ne s'étant pas connectés depuis plus un an
12 
# Action on MariaDB 'radius' database
13 
#	- Management (save / import / RAZ)
14 
#	- Close the accounting open sessions
15 
#	- Remove users 7 days after their expiry date
16 
#	- Remove users who haven't logged in for more than a year
3269 rexy 17 
 
18 
rep_tr="/var/Save/base"
19 
DIR_BIN="/usr/local/bin"
20 
PASSWD_FILE="/root/ALCASAR-passwords.txt"
21 
DB_RADIUS="radius"
22 
DB_USER=$(grep '^db_user=' $PASSWD_FILE | cut -d'=' -f 2-)
23 
DB_PASS=$(grep '^db_password=' $PASSWD_FILE | cut -d'=' -f 2-)
3274 rexy 24 
new="$(date +%G%m%d-%Hh%M)"		# date & hour of files
25 
tmp_file="alcasar-users-database-$new.sql"
3269 rexy 26 
 
3274 rexy 27 
stop_acct () # Close the accounting open sessions
3269 rexy 28 
{
29 
	date_now=`date "+%F %X"`
30 
	echo "UPDATE radacct SET acctstoptime = '$date_now', acctterminatecause = 'Admin-Reset' WHERE acctstoptime IS NULL" | mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS
31 
}
3274 rexy 32 
 
3269 rexy 33 
check ()
34 
{
35 
	echo "check (and repair if needed) the database :"
36 
	mariadb-check --databases $DB_RADIUS -u $DB_USER -p$DB_PASS --auto-repair
37 
}
38 
 
3277 rexy 39 
remove_old_users ()	# Remove users who haven't logged in for more than a year (TO BE VERIFY BEFORE ACTIVATE)
3269 rexy 40 
{
3274 rexy 41 
	SQL_USER=""
42 
	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;"`
43 
	for u in $SQL_USER
44 
	do
45 
		/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';"
46 
		if [ $? = 0 ]
47 
		then
48 
			echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
49 
		else
50 
			echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
51 
		fi
52 
 	 done
53 
}
54 
 
55 
expire_users ()	# Remove users 7 days after their expiry date
56 
{
3269 rexy 57 
	del_date=`date +%F`
58 
	SQL_USER=""
59 
	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';"`
60 
	for u in $SQL_USER
61 
	do
3274 rexy 62 
		/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';"
3269 rexy 63 
		if [ $? = 0 ]
64 
		then
65 
			echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
66 
		else
67 
			echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
68 
		fi
69 
 	 done
70 
}
71 
 
3274 rexy 72 
expire_groups () # remove users of group whom expiration date has passed to 7 days
3269 rexy 73 
{
74 
	del_date=`date +%F`
75 
	SQL_GROUP=""
76 
	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';"`
77 
	for g in $SQL_GROUP
78 
	do
79 
		SQL_USERGROUP=""
80 
		SQL_USERGROUP=`/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --execute  "SELECT username FROM radusergroup WHERE groupname = '$g';"`
81 
		for u in $SQL_USERGROUP
82 
		do
83 
		 	/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';"
84 
			if [ $? = 0 ]
85 
			then
86 
				echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
87 
			else
88 
				echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
89 
			fi
90 
	 	 done
91 
		 /usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS --execute "DELETE FROM radgroupreply WHERE groupname = '$g'; DELETE FROM radgroupcheck WHERE groupname = '$g';"
92 
		if [ $? = 0 ]
93 
		then
94 
			echo "Group $g was deleted $del_date" >> /var/log/mysqld/delete_group.log
95 
		else
96 
			echo "Delete Group $g : Error $del_date" >> /var/log/mysqld/delete_group.log
97 
		fi
98 
 	 done
99 
 }
100 
 
3274 rexy 101 
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 }"
3269 rexy 102 
nb_args=$#
103 
args=$1
104 
if [ $nb_args -eq 0 ]
105 
then
106 
	nb_args=1
107 
	args="-h"
108 
fi
109 
case $args in
110 
	-\? | -h* | --h*)
111 
		echo "$usage"
112 
		exit 0
113 
		;;
114 
	-d | --dump | -dump)
115 
		[ -d $rep_tr ] || mkdir -p $rep_tr
3274 rexy 116 
		if [ -e  $tmp_file ];
117 
			then rm -f  $tmp_file
3269 rexy 118 
		fi
119 
		check
3274 rexy 120 
		echo "Export the database in file : $tmp_file.gz"
121 
		mariadb-dump -u $DB_USER -p$DB_PASS --opt -BcQC  $DB_RADIUS > $rep_tr/$tmp_file
122 
		gzip -f $rep_tr/$tmp_file
3269 rexy 123 
		echo "End of export $( date "+%Hh %Mmn" )"
124 
		;;
125 
	-c | --check | -check)
126 
		check
127 
		;;
128 
	-i | --import | -import)
129 
		if [ $nb_args -ne 2 ]
130 
			then
131 
				echo "Enter a SQL file name ('.sql' or '.sql.gz')"
132 
			exit 0
133 
		else
134 
			case $2 in
135 
			*.sql.gz )
136 
				gunzip -f < $2 | mariadb -u $DB_USER -p$DB_PASS
3274 rexy 137 
				stop_acct
3269 rexy 138 
				;;
139 
			*.sql )
140 
				mariadb -u $DB_USER -p$DB_PASS < $2
3274 rexy 141 
				stop_acct
3269 rexy 142 
				;;
143 
			esac
144 
			migrationsPath="$DIR_BIN/alcasar-db-migrations"
145 
			"$migrationsPath/alcasar-migration_db-3.2.0.sh"
146 
			"$migrationsPath/alcasar-migration_db-3.3.0.sh"
147 
			"$migrationsPath/alcasar-migration_db-3.3.1.sh"
148 
			"$migrationsPath/alcasar-migration_db-3.7.0.sh"
149 
		fi
150 
		;;
151 
	-r | --raz | -raz)
3274 rexy 152 
		mariadb-dump -u $DB_USER -p$DB_PASS --opt -BcQC  $DB_RADIUS > $rep_tr/$tmp_file
153 
		gzip -f $rep_tr/$tmp_file
3269 rexy 154 
		mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS < /etc/raddb/empty-radiusd-db.sql
155 
		;;
156 
	-a | --acct_stop | -acct_stop)
157 
		stop_acct
158 
		;;
159 
	-e | --expire_user)
3274 rexy 160 
		expire_users
161 
		expire_groups
3269 rexy 162 
		;;
3274 rexy 163 
	-o | --old_user)
164 
		remove_old_users
165 
		;;
3269 rexy 166 
	*)
167 
		echo "Unknown argument :$1";
168 
		echo "$usage"
169 
		exit 1
170 
		;;
171 
esac