Tracking user activity in a database

Posted by admin at December 25, 2016

To ensure data integrity and to improve security in a database system, there is a need to track what the users are doing in the system as regards to manipulation of sensitive information stored in the database. This action mandates that meta-data be collected to help document the history of edits to stored data. This meta-data can be very important in situations such as erroneous and malicious data entry, deletion or unauthorised page access.

1. Using a database log table

A central table is created for recording user activity: user id, user action, affected tables, notes and datetime stamp. The table is updated ever time a user visits a page, creates or uploads or deletes a record in the database.

2. User ID and Timestamp columns in every table

This method typically employs 4 columns:



-- who created the record
    `created_by_user` int(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'User who created the record',
    
-- the date record was created
    `date_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The date the record was created'

-- who updated the record
    `updated_by_user` int(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'User who updated the record',

-- the date record was updated
    `date_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The date the record was updated'


So whenever a user creates a new record in the table, the system records the users ID into the `created_by_user` column, as well as the date and time of entry into the `date_created` column. Likewise, whenever a user updates a record in the table, the system records the users ID into the `updated_by_user` column, as well as the date and time of entry into the `date_updated` column. The downside of this method when compared to the first, that the full history of record updates is not available as the newest update log replaces older ones.

3. Notifications

Simply sending an email notification to a group of system administrators, whenever a user performs a target action or accesses a page, will not only preserve a duplicated log of events, but will more importantly prompt key personnel of activities immediately they happen. This is most timely method of user activity tracking and is suitable for highly data-sensitive, highly data-volatile application systems where close supervision of end-user activities is required.

4. Tracking with JavaScript and LocalStorage

This can be used to track page views by a user and the number of refreshes. A javascript funnction that records the user credentials or user ids of visitors who access a group of records or links in a system. This function should be triggered by the windows.onload eventListener and data is temporarily saved to the LocalStorage. At the end of a session, this information in the localStorage can be gathered and saved in the database.

   0 likes

Suggested Read