Subversion Repositories ALCASAR

Rev

Rev 2829 | Show entire file | Regard whitespace | Details | Blame | Last modification | View Log

Rev 2829 Rev 3061
Line 1... Line 1...
1
#!/bin/bash
1
#!/bin/bash
2
# $Id: alcasar-mysql.sh 2829 2020-06-01 21:56:41Z rexy $
2
# $Id: alcasar-mysql.sh 3061 2022-08-16 11:12:08Z rexy $
3
 
3
 
4
# alcasar-mysql.sh
4
# alcasar-mysql.sh
5
# by Franck BOUIJOUX, Pascal LEVANT and Richard REY
5
# by Franck BOUIJOUX, Pascal LEVANT and Richard REY
6
# This script is distributed under the Gnu General Public License (GPL)
6
# This script is distributed under the Gnu General Public License (GPL)
7
 
7
 
Line 31... Line 31...
31
 
31
 
32
expire_user ()	# remove users whom expiration date has passed to 7 days
32
expire_user ()	# remove users whom expiration date has passed to 7 days
33
{
33
{
34
	del_date=`date +%F`
34
	del_date=`date +%F`
35
	MYSQL_USER=""
35
	MYSQL_USER=""
36
	MYSQL_USER=`/usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --exec  "SELECT username FROM radcheck WHERE ( DATE_SUB(CURDATE(),INTERVAL 7 DAY) > STR_TO_DATE(value,'%d %M %Y')) AND attribute='Expiration';"`
36
	MYSQL_USER=`/usr/bin/mysql -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';"`
37
	for u in $MYSQL_USER
37
	for u in $MYSQL_USER
38
	do
38
	do
39
		 /usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS --exec "DELETE FROM radusergroup WHERE username = '$u'; DELETE FROM radreply WHERE username = '$u'; DELETE FROM userinfo WHERE UserName = '$u'; DELETE FROM radcheck WHERE username = '$u';"
39
		 /usr/bin/mysql -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';"
40
		if [ $? = 0 ]
40
		if [ $? = 0 ]
41
		then
41
		then
42
			echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
42
			echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
43
		else
43
		else
44
			echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
44
			echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
Line 48... Line 48...
48
 
48
 
49
expire_group () # remove users of group whom expiration date has passed to 7 days
49
expire_group () # remove users of group whom expiration date has passed to 7 days
50
{
50
{
51
	del_date=`date +%F`
51
	del_date=`date +%F`
52
	MYSQL_GROUP=""
52
	MYSQL_GROUP=""
53
	MYSQL_GROUP=`/usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --exec  "SELECT groupname FROM radgroupcheck WHERE ( DATE_SUB(CURDATE(),INTERVAL 7 DAY) > STR_TO_DATE(value,'%d %M %Y')) AND attribute='Expiration';"`
53
	MYSQL_GROUP=`/usr/bin/mysql -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';"`
54
	for g in $MYSQL_GROUP
54
	for g in $MYSQL_GROUP
55
	do
55
	do
56
		MYSQL_USERGROUP=""
56
		MYSQL_USERGROUP=""
57
		MYSQL_USERGROUP=`/usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --exec  "SELECT username FROM radusergroup WHERE groupname = '$g';"`
57
		MYSQL_USERGROUP=`/usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS -ss --execute  "SELECT username FROM radusergroup WHERE groupname = '$g';"`
58
		for u in $MYSQL_USERGROUP
58
		for u in $MYSQL_USERGROUP
59
		do
59
		do
60
		 	/usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS --exec "DELETE FROM radusergroup WHERE username = '$u'; DELETE FROM radreply WHERE username = '$u'; DELETE FROM userinfo WHERE UserName = '$u'; DELETE FROM radcheck WHERE username = '$u';"
60
		 	/usr/bin/mysql -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';"
61
			if [ $? = 0 ]
61
			if [ $? = 0 ]
62
			then
62
			then
63
				echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
63
				echo "User $u was deleted $del_date" >> /var/log/mysqld/delete_user.log
64
			else
64
			else
65
				echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
65
				echo "Delete User $u : Error $del_date" >> /var/log/mysqld/delete_user.log
66
			fi
66
			fi
67
	 	 done
67
	 	 done
68
		 /usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS --exec "DELETE FROM radgroupreply WHERE groupname = '$g'; DELETE FROM radgroupcheck WHERE groupname = '$g';"
68
		 /usr/bin/mysql -u$DB_USER -p$DB_PASS $DB_RADIUS --execute "DELETE FROM radgroupreply WHERE groupname = '$g'; DELETE FROM radgroupcheck WHERE groupname = '$g';"
69
		if [ $? = 0 ]
69
		if [ $? = 0 ]
70
		then
70
		then
71
			echo "Group $g was deleted $del_date" >> /var/log/mysqld/delete_group.log
71
			echo "Group $g was deleted $del_date" >> /var/log/mysqld/delete_group.log
72
		else
72
		else
73
			echo "Delete Group $g : Error $del_date" >> /var/log/mysqld/delete_group.log
73
			echo "Delete Group $g : Error $del_date" >> /var/log/mysqld/delete_group.log