Audit Log
Posted: 2014-11-07 13:26
--
-- SQL Table structure for `auditor` table
--
CREATE TABLE IF NOT EXISTS `auditor` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`ipaddr` varchar(25) DEFAULT NULL,
`time_stmp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`change_type` varchar(10) DEFAULT NULL,
`table_name` varchar(40) DEFAULT NULL,
`fieldName` varchar(40) DEFAULT NULL,
`OldValue` text,
`NewValue` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;
Functions to Add to: incCommon.php
#########################################################
###### AUDIT LOG FUNCTIONS #######
#########################################################
####### GET TABLE DATA BEFORE ANY CHANGES ########
function table_before_change($TableName, $currentID, $tableID) {
$_SESSION['before_change_sql'] ="";
$fet=sql("SELECT * FROM $TableName WHERE $tableID = $currentID");
$row = mysql_fetch_row($fet);
// STORE THEM IN A SESSION VARIABLE FOR LATER COMPARISON
$row = str_replace("\n", "<br/>", $row);
$row = str_replace("'", "\'", $row);
$_SESSION ['before_change_sql'] = $row;
return;
}
### GET CHANGES AND ADD TO AUDIT LOG ###
function table_after_change($db_name, $TableName, $username, $userIP, $currentID, $tableID, $type){
// GET ALL COLUMN NAMES IN THE TABLE
$colnames = array(); // Create Empty Array
$colquery =sql("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='$db_name' AND `TABLE_NAME`='$TableName'");
$i = 0;
while ($row = mysql_fetch_array($colquery)) {
foreach($row as $key => $val) {
$colnames[$i] = $val; }
$i++; }
// GET ARRAY OF VALUES AFTER CHANGE
$_SESSION ['after_change_sql'] ="";
$fet=sql("SELECT * FROM $TableName WHERE $tableID = $currentID");
$row = mysql_fetch_row($fet);
$row = str_replace("\n", "<br/>", $row);
$row = str_replace("'", "\'", $row);
$_SESSION ['after_change_sql'] = $row;
// ARRAY OF ORIGINAL VALUES // ARRAY OF UPDATED VALUES
$array1 = $_SESSION ['before_change_sql']; $array2 = $_SESSION ['after_change_sql'];
####### COMPARE BEFORE AND AFTER VALUES AND ADD CHANGED DATA ENTRIES TO AUDIT LOG #######
$del="-DELETED-";
$i = 0;
for ($i = 0; $i <= count($array1); ++$i) {
if ($array1[$i] != $array2[$i])
{// Adds the INITIAL Values and CHANGED values to the auditlog database table
switch (true) {
// START Examples of how to exclude specific fields from being added to the audit Log
case ($colnames[$i] == "editDate"):
break;
case ($colnames[$i] == "editedBy"):
break;
case ($colnames[$i] == "id_cnote"):
break;
case ($colnames[$i] == "fk_res"):
break;
// END of exclusion examples
case ($type == "DELETION"):
sql("INSERT INTO auditor (username, ipaddr, time_stmp, change_type, table_name, fieldName, OldValue, NewValue) VALUES ('$username','$userIP',NOW(),'$type','$TableName','$colnames[$i]','$array1[$i]','$del')", $eo);
break;
default:
sql("INSERT INTO auditor (username, ipaddr, time_stmp, change_type, table_name, fieldName, OldValue, NewValue) VALUES ('$username','$userIP',NOW(),'$type','$TableName','$colnames[$i]','$array1[$i]','$array2[$i]')", $eo);
break;
}
}
}
unset($_SESSION['before_change_sql']);
unset($_SESSION['after_change_sql']);
return;
}
#########################################################
###### END OF AUDIT LOG FUNCTIONS #######
#########################################################
Lines to Add to config.php -:
--------------------------
// IF SESSION VARIABLE NOT SET OR BLANK, SET SESSION VARIABLE 'dbase' with Database Name
if (!(isset($_SESSION ['dbase']) && $_SESSION ['dbase'] != '')) {$_SESSION ['dbase'] = $dbDatabase;};
LINES TO ADD TO ** EACH ** TABLES HOOK THAT YOU WISH TO AUDIT
------------------------------------------------------------
Note: DO NOT add 'table_before_change' function to the tablename_before_insert hook as the $selectedID has not been created yet!
tablename_init Hook:
//////////////GET AND SET TABLE NAME TO SESSSION VARIABLE//////////////////
$_SESSION ['tablenam'] = $options->TableName;
//////////////GET AND SET TABLE ID FIELD NAME TO SESSSION VARIABLE//////////////////
$_SESSION ['tableID'] = $options->PrimaryKey;
$tableID = $_SESSION ['tableID'];
tablename_after_insert hook:
// CHANGED (UPDATED TABLE VALUES) ADDED TO AUDIT LOG
table_after_change($_SESSION ['dbase'], $_SESSION['tablenam'], $memberInfo['username'], $memberInfo['IP'], $data['selectedID'], $_SESSION['tableID'], "INSERTION");
tablename_before_update hook:
// INITIAL TABLE VALUES
table_before_change($_SESSION['tablenam'], $data['selectedID'], $_SESSION['tableID']);
tablename_after_update hook:
// CHANGED (UPDATED TABLE VALUES) ADDED TO AUDIT LOG
table_after_change($_SESSION ['dbase'], $_SESSION['tablenam'], $memberInfo['username'], $memberInfo['IP'], $data['selectedID'], $_SESSION['tableID'], "UPDATE");
tablename_before_delete hook:
// INITIAL TABLE VALUES
table_before_change($_SESSION['tablenam'], $selectedID, $_SESSION['tableID']);
tablename_after_delete hook:
// CHANGED (UPDATED TABLE VALUES) ADDED TO AUDIT LOG
table_after_change($_SESSION ['dbase'], $_SESSION['tablenam'], $memberInfo['username'], $memberInfo['IP'], $selectedID, $_SESSION['tableID'], "DELETION");
-- SQL Table structure for `auditor` table
--
CREATE TABLE IF NOT EXISTS `auditor` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`ipaddr` varchar(25) DEFAULT NULL,
`time_stmp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`change_type` varchar(10) DEFAULT NULL,
`table_name` varchar(40) DEFAULT NULL,
`fieldName` varchar(40) DEFAULT NULL,
`OldValue` text,
`NewValue` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;
Functions to Add to: incCommon.php
#########################################################
###### AUDIT LOG FUNCTIONS #######
#########################################################
####### GET TABLE DATA BEFORE ANY CHANGES ########
function table_before_change($TableName, $currentID, $tableID) {
$_SESSION['before_change_sql'] ="";
$fet=sql("SELECT * FROM $TableName WHERE $tableID = $currentID");
$row = mysql_fetch_row($fet);
// STORE THEM IN A SESSION VARIABLE FOR LATER COMPARISON
$row = str_replace("\n", "<br/>", $row);
$row = str_replace("'", "\'", $row);
$_SESSION ['before_change_sql'] = $row;
return;
}
### GET CHANGES AND ADD TO AUDIT LOG ###
function table_after_change($db_name, $TableName, $username, $userIP, $currentID, $tableID, $type){
// GET ALL COLUMN NAMES IN THE TABLE
$colnames = array(); // Create Empty Array
$colquery =sql("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='$db_name' AND `TABLE_NAME`='$TableName'");
$i = 0;
while ($row = mysql_fetch_array($colquery)) {
foreach($row as $key => $val) {
$colnames[$i] = $val; }
$i++; }
// GET ARRAY OF VALUES AFTER CHANGE
$_SESSION ['after_change_sql'] ="";
$fet=sql("SELECT * FROM $TableName WHERE $tableID = $currentID");
$row = mysql_fetch_row($fet);
$row = str_replace("\n", "<br/>", $row);
$row = str_replace("'", "\'", $row);
$_SESSION ['after_change_sql'] = $row;
// ARRAY OF ORIGINAL VALUES // ARRAY OF UPDATED VALUES
$array1 = $_SESSION ['before_change_sql']; $array2 = $_SESSION ['after_change_sql'];
####### COMPARE BEFORE AND AFTER VALUES AND ADD CHANGED DATA ENTRIES TO AUDIT LOG #######
$del="-DELETED-";
$i = 0;
for ($i = 0; $i <= count($array1); ++$i) {
if ($array1[$i] != $array2[$i])
{// Adds the INITIAL Values and CHANGED values to the auditlog database table
switch (true) {
// START Examples of how to exclude specific fields from being added to the audit Log
case ($colnames[$i] == "editDate"):
break;
case ($colnames[$i] == "editedBy"):
break;
case ($colnames[$i] == "id_cnote"):
break;
case ($colnames[$i] == "fk_res"):
break;
// END of exclusion examples
case ($type == "DELETION"):
sql("INSERT INTO auditor (username, ipaddr, time_stmp, change_type, table_name, fieldName, OldValue, NewValue) VALUES ('$username','$userIP',NOW(),'$type','$TableName','$colnames[$i]','$array1[$i]','$del')", $eo);
break;
default:
sql("INSERT INTO auditor (username, ipaddr, time_stmp, change_type, table_name, fieldName, OldValue, NewValue) VALUES ('$username','$userIP',NOW(),'$type','$TableName','$colnames[$i]','$array1[$i]','$array2[$i]')", $eo);
break;
}
}
}
unset($_SESSION['before_change_sql']);
unset($_SESSION['after_change_sql']);
return;
}
#########################################################
###### END OF AUDIT LOG FUNCTIONS #######
#########################################################
Lines to Add to config.php -:
--------------------------
// IF SESSION VARIABLE NOT SET OR BLANK, SET SESSION VARIABLE 'dbase' with Database Name
if (!(isset($_SESSION ['dbase']) && $_SESSION ['dbase'] != '')) {$_SESSION ['dbase'] = $dbDatabase;};
LINES TO ADD TO ** EACH ** TABLES HOOK THAT YOU WISH TO AUDIT
------------------------------------------------------------
Note: DO NOT add 'table_before_change' function to the tablename_before_insert hook as the $selectedID has not been created yet!
tablename_init Hook:
//////////////GET AND SET TABLE NAME TO SESSSION VARIABLE//////////////////
$_SESSION ['tablenam'] = $options->TableName;
//////////////GET AND SET TABLE ID FIELD NAME TO SESSSION VARIABLE//////////////////
$_SESSION ['tableID'] = $options->PrimaryKey;
$tableID = $_SESSION ['tableID'];
tablename_after_insert hook:
// CHANGED (UPDATED TABLE VALUES) ADDED TO AUDIT LOG
table_after_change($_SESSION ['dbase'], $_SESSION['tablenam'], $memberInfo['username'], $memberInfo['IP'], $data['selectedID'], $_SESSION['tableID'], "INSERTION");
tablename_before_update hook:
// INITIAL TABLE VALUES
table_before_change($_SESSION['tablenam'], $data['selectedID'], $_SESSION['tableID']);
tablename_after_update hook:
// CHANGED (UPDATED TABLE VALUES) ADDED TO AUDIT LOG
table_after_change($_SESSION ['dbase'], $_SESSION['tablenam'], $memberInfo['username'], $memberInfo['IP'], $data['selectedID'], $_SESSION['tableID'], "UPDATE");
tablename_before_delete hook:
// INITIAL TABLE VALUES
table_before_change($_SESSION['tablenam'], $selectedID, $_SESSION['tableID']);
tablename_after_delete hook:
// CHANGED (UPDATED TABLE VALUES) ADDED TO AUDIT LOG
table_after_change($_SESSION ['dbase'], $_SESSION['tablenam'], $memberInfo['username'], $memberInfo['IP'], $selectedID, $_SESSION['tableID'], "DELETION");