Subversion Repositories ALCASAR

Rev

Rev 3277 | 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 
{
3285 rexy 41 
	del_date=`date +%F`
3274 rexy 42 
	SQL_USER=""
3285 rexy 43 
	SQL_USER=`/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --execute  "SELECT username FROM userinfo WHERE username NOT IN (SELECT DISTINCT username FROM radacct WHERE acctstarttime >= NOW() - INTERVAL 1 YEAR) ;"`
3274 rexy 44 
	for u in $SQL_USER
45 
	do
46 
		/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';"
47 
		if [ $? = 0 ]
48 
		then
49 
			echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
50 
		else
51 
			echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
52 
		fi
53 
 	 done
54 
}
55 
 
56 
expire_users ()	# Remove users 7 days after their expiry date
57 
{
3269 rexy 58 
	del_date=`date +%F`
59 
	SQL_USER=""
60 
	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';"`
61 
	for u in $SQL_USER
62 
	do
3274 rexy 63 
		/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 64 
		if [ $? = 0 ]
65 
		then
66 
			echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
67 
		else
68 
			echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
69 
		fi
70 
 	 done
71 
}
72 
 
3274 rexy 73 
expire_groups () # remove users of group whom expiration date has passed to 7 days
3269 rexy 74 
{
75 
	del_date=`date +%F`
76 
	SQL_GROUP=""
77 
	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';"`
78 
	for g in $SQL_GROUP
79 
	do
80 
		SQL_USERGROUP=""
81 
		SQL_USERGROUP=`/usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --execute  "SELECT username FROM radusergroup WHERE groupname = '$g';"`
82 
		for u in $SQL_USERGROUP
83 
		do
84 
		 	/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';"
85 
			if [ $? = 0 ]
86 
			then
87 
				echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
88 
			else
89 
				echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
90 
			fi
91 
	 	 done
92 
		 /usr/bin/mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS --execute "DELETE FROM radgroupreply WHERE groupname = '$g'; DELETE FROM radgroupcheck WHERE groupname = '$g';"
93 
		if [ $? = 0 ]
94 
		then
95 
			echo "Group $g was deleted $del_date" >> /var/log/mysqld/delete_group.log
96 
		else
97 
			echo "Delete Group $g : Error $del_date" >> /var/log/mysqld/delete_group.log
98 
		fi
99 
 	 done
100 
 }
101 
 
3274 rexy 102 
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 103 
nb_args=$#
104 
args=$1
105 
if [ $nb_args -eq 0 ]
106 
then
107 
	nb_args=1
108 
	args="-h"
109 
fi
110 
case $args in
111 
	-\? | -h* | --h*)
112 
		echo "$usage"
113 
		exit 0
114 
		;;
115 
	-d | --dump | -dump)
116 
		[ -d $rep_tr ] || mkdir -p $rep_tr
3274 rexy 117 
		if [ -e  $tmp_file ];
118 
			then rm -f  $tmp_file
3269 rexy 119 
		fi
120 
		check
3274 rexy 121 
		echo "Export the database in file : $tmp_file.gz"
122 
		mariadb-dump -u $DB_USER -p$DB_PASS --opt -BcQC  $DB_RADIUS > $rep_tr/$tmp_file
123 
		gzip -f $rep_tr/$tmp_file
3269 rexy 124 
		echo "End of export $( date "+%Hh %Mmn" )"
125 
		;;
126 
	-c | --check | -check)
127 
		check
128 
		;;
129 
	-i | --import | -import)
130 
		if [ $nb_args -ne 2 ]
131 
			then
132 
				echo "Enter a SQL file name ('.sql' or '.sql.gz')"
133 
			exit 0
134 
		else
135 
			case $2 in
136 
			*.sql.gz )
137 
				gunzip -f < $2 | mariadb -u $DB_USER -p$DB_PASS
3274 rexy 138 
				stop_acct
3269 rexy 139 
				;;
140 
			*.sql )
141 
				mariadb -u $DB_USER -p$DB_PASS < $2
3274 rexy 142 
				stop_acct
3269 rexy 143 
				;;
144 
			esac
145 
			migrationsPath="$DIR_BIN/alcasar-db-migrations"
146 
			"$migrationsPath/alcasar-migration_db-3.2.0.sh"
147 
			"$migrationsPath/alcasar-migration_db-3.3.0.sh"
148 
			"$migrationsPath/alcasar-migration_db-3.3.1.sh"
149 
			"$migrationsPath/alcasar-migration_db-3.7.0.sh"
150 
		fi
151 
		;;
152 
	-r | --raz | -raz)
3274 rexy 153 
		mariadb-dump -u $DB_USER -p$DB_PASS --opt -BcQC  $DB_RADIUS > $rep_tr/$tmp_file
154 
		gzip -f $rep_tr/$tmp_file
3269 rexy 155 
		mariadb -u$DB_USER -p$DB_PASS $DB_RADIUS < /etc/raddb/empty-radiusd-db.sql
156 
		;;
157 
	-a | --acct_stop | -acct_stop)
158 
		stop_acct
159 
		;;
160 
	-e | --expire_user)
3274 rexy 161 
		expire_users
162 
		expire_groups
3269 rexy 163 
		;;
3274 rexy 164 
	-o | --old_user)
165 
		remove_old_users
166 
		;;
3269 rexy 167 
	*)
168 
		echo "Unknown argument :$1";
169 
		echo "$usage"
170 
		exit 1
171 
		;;
172 
esac