MySQL – Check When Last Password Changed in User DB/Table

MySQL

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

Best Answer

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.

Related Question