Account Creation and Deletion Date
From Qmailtoaster
Background
My Helpdesk was audited by external auditor regarding date of creation and deletion of email accounts. I can't find it the exact date by looking /home/vpopmail folders. So I ask the community and Bharath Chari kind enough to provide the tweaks.
Notes
- Use this tips with qmailtoaster's version >= vpopmail-5.4.17 with mysql backend (not LDAP).
- Use only with MySQL version 5.0 or newer which has trigger feature.
- This trigger only work with vpopmail with --disable-many-domains (each domain has it's own table).
- In the future, perhaps Bharath will make a script available via QTP to do this automatically, so you no longer need to follow the steps below.
- These steps are based on email sent by Bharath Chari to QMailToaster's list.
- MAKE BACKUP of vpopmail database before you do these steps.
Steps
Assumption: Domain to be monitored is example.com and the domain table is example_com.
1) Log in to mysql shell as root
mysql -uroot -p vpopmail
2) Alter the example_com table (replace example_com with your domain table)
ALTER TABLE `example_com` DROP PRIMARY KEY; ALTER TABLE `example_com` ADD UNIQUE (`pw_name`); ALTER TABLE `example_com` ADD `uid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;
3) Create log table - replace example_com with your domain table name
CREATE TABLE `userlog_example_com` ( `uid` int(11) NOT NULL, `pw_name` varchar(255) NOT NULL, `creation_date` datetime NOT NULL default '0000-00-00 00:00:00', `deletion_date` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`uid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
4) Import existing records into the userlog_example_com table. While this won't help with creation date, it will allow you to track when an ID was deleted:
mysql>insert into userlog_example_com (uid,pw_name) select uid,pw_name from example_com;
5) Create the INSERT Trigger (again replace example_com as appropriate)
mysql> delimiter $$
mysql> create trigger example_com_insert_trigger
-> AFTER insert on example_com
-> FOR EACH ROW
-> BEGIN
-> insert into userlog_example_com values(new.uid,new.pw_name,NOW(),'0000-00-00 00:00:00');
-> END$$ Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
6) Create the DELETE Trigger (again replace example_com as appropriate)
mysql> delimiter $$ mysql> CREATE TRIGGER example_com_delete_trigger -> AFTER delete on example_com -> FOR EACH ROW -> BEGIN
-> update userlog_example_com set deletion_date=NOW() where uid=old.uid;
-> END$$ Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql>quit
7) Test and test again:
Create a test user eg : xxxxx using qmailadmin
Delete the user xxxxx
Create the test user xxxxx again
Delete the user xxxxx
Your table userlog_example_com should contain two records both with xxxxx as the pw_name but with different uid numbers and creation/deletion dates.