| 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
|