0

i have radius billing system with mysql as DB. I want to know which user 'password' was changed in last 5 minutes So that I can fetch such user mobile number from the DB and send SMS Alert about the password change event.

Here is the table structure

DB name = radius Table name = rm_users (where all users information is stored) Sample:

mysql> select * from rm_users;
+----------+----------------------------------+---------+------------+---------+-----------+---------------+-----------+----------+---------+-------+--------+---------+------+-----+---------+-------+---------+------------------+------------------+-----+------------+---------------------+-------------+-------+------------+-------------+----------+-----------+----------+-----------+------------+---------+---------+-----------+-----------+-------+-------+-------+-------+----------+-------------+------------+----------+---------+-------------+---------------+--------------+------------+---------------+---------+--------------+------------+----------+---------+---------------------+
| username | password                         | groupid | enableuser | uplimit | downlimit | comblimit     | firstname | lastname | company | phone | mobile | address | city | zip | country | state | comment | gpslat           | gpslong          | mac | usemacauth | expiration          | uptimelimit | srvid | staticipcm | staticipcpe | ipmodecm | ipmodecpe | poolidcm | poolidcpe | createdon  | acctype | credits | cardfails | createdby | owner | taxid | email | maccm | custattr | warningsent | verifycode | verified | selfreg | verifyfails | verifysentnum | verifymobile | contractid | contractvalid | actcode | pswactsmsnum | alertemail | alertsms | lang    | lastlogoff          |
+----------+----------------------------------+---------+------------+---------+-----------+---------------+-----------+----------+---------+-------+--------+---------+------+-----+---------+-------+---------+------------------+------------------+-----+------------+---------------------+-------------+-------+------------+-------------+----------+-----------+----------+-----------+------------+---------+---------+-----------+-----------+-------+-------+-------+-------+----------+-------------+------------+----------+---------+-------------+---------------+--------------+------------+---------------+---------+--------------+------------+----------+---------+---------------------+

| zaib     | b59c67bf196a4758191e42f76670ceba |       1 |          1 |       0 |         0 | 1111111111111 |           |          |         |       |        |         |      |     |         |       |         | 0.00000000000000 | 0.00000000000000 |     |          0 | 2016-06-04 00:00:00 |           0 |    33 |            |             |        0 |         0 |        0 |         0 | 2016-05-04 |       0 |    0.00 |         0 | admin     | admin |       |       |       |          |           0 |            |        0 |       0 |           0 |             0 |              |            | 0000-00-00    |         |            0 |          1 |        1 | English | 2016-05-04 09:10:26 |
+----------+----------------------------------+---------+------------+---------+-----------+---------------+-----------+----------+---------+-------+--------+---------+------+-----+---------+-------+---------+------------------+------------------+-----+------------+---------------------+-------------+-------+------------+-------------+----------+-----------+----------+-----------+------------+---------+---------+-----------+-----------+-------+-------+-------+-------+----------+-------------+------------+----------+---------+-------------+---------------+--------------+------------+---------------+---------+--------------+------------+----------+---------+---------------------+
2 rows in set (0.00 sec)`
0

I think you should be doing something like this.

  1. Maintain username,password in separate table like 'password_change_history'
  2. Compare the separate table password with rm_users password for each user

    2.1 if the password is not same - changed - perform the following task.

      2.1.1 - send sms
      2.1.2 - update password_change_history with latest password for that user.
    

You can even add date_time column for last password change, counter for how many times changed...extra , so the new table will act as extra meta information table.

You also need to check if any new user is added or any user is deleted.

4
  • unfortunately its a proprietary php system which uses freeradius/mysql as backend, so I dont have any access to modify its php pages where the functions are performed , its encrypted. I fetch many other information via mysql already, but its just password thing not working. I tried to use following query. select password from rm_users WHERE ModifiedTime >= NOW() - INTERVAL 10 MINUTE; but it shows every user whoes account have been updated like even if change address or mobile number, it appears with above command, whereas I only want users whose password have been changed. – Syed Jahanzaib May 11 '16 at 2:59
  • Hi, I am unable to location ModifiedTime field in the table you mentioned in question – Suyash Jain May 11 '16 at 5:07
  • I added ModifiedTime later, but this updates even when i just add or update any column like user first name, mobile number etc, I want to see only password change datetime. – Syed Jahanzaib May 11 '16 at 5:43
  • kindly update the question with latest table definition. – Suyash Jain May 11 '16 at 5:56
0

Sorry I couldn't post updates due to some busy schedule. I found the solution on same day. I made TRIGGER in mysql and then export the result in another table. that did the trick plus it will keep the logs of password change history as well for user.

Details are documented as below, maybe it will be helpful for someone looking for same solution.

https://aacable.wordpress.com/2016/05/11/sending-password-change-alert-to-users-via-smsemail-through-kannel-sms-gateway-in-radius-manager/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.