Audit Trail in PHP

[9911782]

Hi

I would like you to help me here, now sure if this should go under PHP Help or MySQL Help...

 

I need help regarding Audit Trail. Im expected to do An audit trail that must be kept of access to the system as well as of all create and update transactions to the database.  This implies the details of the relevant person(s) who transacted as well as time and date of transactions.  This does not apply to the load process – only to the user intervention in the system.

 

Can somebody run me through the steps I should take to do this for my application.

 

Thanks

D

 

[john-formby]

I am making the following assumptions:

 

1.  All users must be logged in to make alterations

2.  The updates are done through forms

3.  User ID is stored in a session

 

 

Based on this, here is how I would do it:

 

1. New db table to store transactions with following fields:

    - transID (unique ID number)

    - userID (obtained from session)

    - transtype (determined by the form modified)

    - transdatetime (timestamp)

 

2.  When a user modifies a form, e.g. their profile, we need to have a hidden field for the transaction type, e.g. <input type="hidden" name="transtype" value="profile" />.

 

3.  When posting the change, an Insert query (in addition to the query that updates) is done to the transaction table which assigns a unique transID grabs the userID from session, and the transtype from POST and timestamps NOW()

 

e.g.

$userID = $_SESSION['userID'];
$transtype = $_POST['transtype'];
$sql = mysql_query("INSERT INTO tbltrans (transID, userID, transtype, transdatetime) VALUES ('', '$userID', '$transtype', NOW());

 

This is just a very brief overview.  If you need more details let me know and I will be happy to expand.

 

[9911782]

Thank you John. I will try and once im stacked will let you know.

 

I appreciate your help on this BIG TIME!

 

thanks

D

[9911782]

Just need logic for doing this.Im desparate really. When do I register these trasanction to DB. Can you please expand for me please. 

I am making the following assumptions:

 

1.  All users must be logged in to make alterations

2.  The updates are done through forms

3.  User ID is stored in a session

 

 

Based on this, here is how I would do it:

 

1. New db table to store transactions with following fields:

    - transID (unique ID number)

    - userID (obtained from session)

    - transtype (determined by the form modified)

    - transdatetime (timestamp)

 

2.  When a user modifies a form, e.g. their profile, we need to have a hidden field for the transaction type, e.g. <input type="hidden" name="transtype" value="profile" />.

 

3.  When posting the change, an Insert query (in addition to the query that updates) is done to the transaction table which assigns a unique transID grabs the userID from session, and the transtype from POST and timestamps NOW()

 

e.g.

$userID = $_SESSION['userID'];
$transtype = $_POST['transtype'];
$sql = mysql_query("INSERT INTO tbltrans (transID, userID, transtype, transdatetime) VALUES ('', '$userID', '$transtype', NOW());

 

This is just a very brief overview.  If you need more details let me know and I will be happy to expand.

 

[john-formby]

Hi, no problem.  Here is an example for you.

 

MySQL table structure:

CREATE TABLE `tbluser` (
  `uID` int(11) NOT NULL auto_increment,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  KEY `uID` (`uID`)
) ENGINE=MyISAM;

-- 
-- Dumping data for table `tbluser`
-- 

INSERT INTO `tbluser` VALUES (1, 'John', 'Formby');


CREATE TABLE `tblaudit` (
  `auditID` int(11) NOT NULL auto_increment,
  `uID` int(11) NOT NULL,
  `editor` varchar(30) NOT NULL,
  `formname` varchar(30) NOT NULL,
  `whenpost` timestamp NOT NULL default '0000-00-00 00:00:00',
  KEY `auditID` (`auditID`)
) ENGINE=MyISAM;

 

 

edituser.php

<?php
$dbHost = "localhost"; 
$dbUser = "blah"; 
$dbPass = "blah"; 
$dbname = "testdb"; 
$db = mysql_connect($dbHost,$dbUser,$dbPass); 
mysql_select_db($dbname,$db);                

$uID = 1;
$editor = 'John'; // The person who is editing the record.  This would be grabbed from the session

if(isset($_POST['submit'])) { 

foreach($_POST as $key=>$value) { 
	$$key = $value;
}

// update the record
$sql2 = "UPDATE tbluser SET firstname = '$firstname', lastname = '$lastname' WHERE uID = '$uID'"; 
$result2 = mysql_query($sql2);

// create the audit trail
$sql3 = mysql_query("INSERT INTO tblaudit (auditID, uID, editor, formname, whenpost) VALUES ('', '$uID', '$editor', '$formname', NOW())") or die(mysql_error());

echo 'User details updated';
}

echo '
<html>
<head>
<title>Edit User</title>
</head>
<body>';

$sql = mysql_query("SELECT * FROM tbluser WHERE uID = '$uID'");
$row = mysql_fetch_array($sql);

echo '<form name="edituser" action="edituser.php" method="post">
<input type="hidden" name="uID" value="'.$row['uID'].'" />
<input type="hidden" name="formname" value="Edit User" />
<input type="hidden" name="editor" value="'.$editor.'" />
<input type="text" name="firstname" value="'.$row['firstname'].'" />
<input type="text" name="lastname" value="'.$row['lastname'].'" />
<input type="submit" name="submit" value="submit" />
</form>

</body>
</html>';

?>

 

I have not tested because I am at work at the moment.  If you get any errors let me know.

[oniel182]

how about to get the ip address of the users? I mean to get the ip address of the user who is loged in into the system?

Edited January 16, 2013 by oniel182

[cpd]

If you want to log the IP address consider checking for a proxy as well and logging them together.

 

Look up on google the most efficient method for getting both.

Edited January 16, 2013 by cpd

[KevinM1]

Please don't bump up topics that are nearly 5 years old.