Syed Jahanzaib Personal Blog to Share Knowledge !

May 11, 2016

Sending ‘Password Change’ Alert to users via SMS/Email through KANNEL SMS GATEWAY in Radius Manager

Filed under: Radius Manager — Tags: , , , — Syed Jahanzaib / Pinochio~:) @ 2:47 PM

1234

Screenshot_2016-05-11-14-44-07


Following is a quick dirty method on how you can generate SMS / EMAIL alert when admin changes any user password [as requested by an OP]. and I think its a good idea so that users must be informed about there account password security.

In this guide I have used Radius Manager 4.1.5 along with KANNEL on same machine. Serial Modem is being used along with local mobile SIM for sending SMS.

You need to perform few steps. Proceed with caution, as alerting mysql DB incorrectly can result in partial or total database wipe out or can led to corruption of DB. Make sure you take full DB backup before proceeding. Better to test it on Virtual lab.

you need to make two .sql file

1- triggers.sql
[It will make a new trigger that will be executed when rm_users table will be modified. It will match new password field with the old.field and add then log the changed with username and other details in below table.

2-rm_userpasschangehistory.sql
[It will create new DB which will store password change datetime, username, first last name and mobile]


1- TRIGGERS.SQL

Ok lets first make triggers.sql file, open text editor and paste the data.

mkdir /temp
nano /temp/triggers.sql

Paste the following data in this file.

-- MySQL dump 10.13 Distrib 5.5.46, for debian-linux-gnu (i686)
-- Host: localhost Database: radius
-- ------------------------------------------------------
-- Server version 5.5.46-0ubuntu0.12.04.2-log

DELIMITER ;;

FOR EACH ROW BEGIN
IF NEW.password <> OLD.password THEN
INSERT INTO rm_userpasschangehistory (datetime, username, firstname, lastname, mobile) VALUES (NOW(), new.username, new.firstname, new.lastname, new.mobile);
END IF;
END */;;
DELIMITER ;

-- Dumping routines for database 'radius'
--

Save and exit.


2- rm_userpasschangehistory

Now let’s make rm_userpasschangehistory.sql , open text editor and paste the data.

mkdir /temp
nano /temp/rm_userpasschangehistory.sql

Paste the following data in this file.

-- Table structure for table rm_userpasschangehistory`
--

DROP TABLE IF EXISTS rm_userpasschangehistory`;
CREATE TABLE `rm_userpasschangehistory` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`datetime` datetime NOT NULL,
`username` varchar(64) NOT NULL,
`firstname` varchar(64) NOT NULL,
`lastname` varchar(64) NOT NULL,
`mobile` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
ALTER TABLE `rm_users` ADD `ModifiedTime` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
--
-- Dumping data for table rm_userpasschangehistory

Save and exit.


IMPORTING .sql files intro RADIUS DB.

Now we can import above created .sql files into radius DB. Use below commands

mysql -uroot -pSQLPASS radius < triggers.sql

mysql -uroot -pSQLPASS radius < rm_userpasschangehistory.sql

TEST DRIVER ….

Ok now try to change any user password from radius admin panel. Once updated, check the new table cahnges by following command (I used time interval to fetch accounts changed in last 5 minutes, you can modify it as per your requirements

.. and you may see result as below …


mysql -uroot -pSQLPASS --skip-column-names -e "use radius; select * from rm_userpasschangehistory WHERE datetime >= NOW() - INTERVAL 5 MINUTE;"
+---+---------------------+------+------+-----------+-------------+
| 5 | 2016-05-11 13:46:55 | zaib | syed | jahanzaib | 03333021909 |
+---+---------------------+------+------+-----------+-------------+

~ ALHAMDOLILLAH ~


SCRIPT to fetch data via SCHEDULED CRON  job to send SMS/EMAIL.

You can use following script in cron scheduler.


#!/bin/sh
# passchange.sh
# Bash script which will run after every 5 minutes and will fetch info from mysqltable
# and will send SMS/Email alert for password change event.
# Created by SYED JAHANZAIB
# aacable@hotmail.com
# https://aacable.wordpress.com

SLQPASS="MYSQL_ROOT_PASSWORD"
TMPUSRINFO=/tmp/userpass.txt
mysql -uroot -p$SQLPASS --skip-column-names -e "use radius; select * from rm_userpasschangehistory WHERE datetime >= NOW() - INTERVAL 5 MINUTE;" >> $TMPUSRINFO

# KANNEL DETAILS
KHOST="127.0.0.1:13013"
KID="kannel"
KPASS="KANNEL_PASSWORD"

# Apply Count Loop Formula while deleting first line which have junk text
num=0
cat $TMPUSRINFO | while read users
do
num=$[$num+1]
username=`echo $users | awk '{print $4}'`
firstname=`echo $users | awk '{print $5}'`
lastname=`echo $users | awk '{print $6}'`
mobile=`echo $users | awk '{print $7}'`
date=`echo $users | awk '{print $2,$3}'`
# Print Info on screen
echo "Dear $firstname $lastname,
Password for your internet account ID=$username been successfully changed on $date.
Regard's

XYZ ISP SERVICES (PVT) LTD"

# Store Info for sending SMS in /tmp folder where we will call kannel to send customized SMS
echo "Dear $firstname $lastname,
Password for your internet account ID=$username been successfully changed on $date.

Regard's
XYZ ISP SERVICES (PVT) LTD" > /tmp/$username.passchange.sms

curl "http://$KHOST/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$mobile" -G --data-urlencode text@/tmp/$username.passchange.sms
# If you send lot of SMS via local mobile SIM, then make sure you give enough delay so that your SIM may not get blocked by BULK SMS monitor by TELCOM authority like PTA.
#sleep 15

done

# once done, we should delete the .sms files to clear the garbage

rm -fr /tmp/*.sms

sms done


CRON CODE for 5 minute schedule.

crontab -e

# Run renewal check script Script after every 5 minutes
*/5 * * * * /temp/passchange.sh

Possibilities are endless…..

Regard’s
Syed Jahanzaib

Advertisements

1 Comment »

  1. Your know What You are Awasome Sir.. Respect 🙂

    Like

    Comment by bluebtc — May 12, 2016 @ 2:49 AM


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: