Revision [839]

Last edited on 2007-11-14 23:25:10 by PhilDaintree
Additions:
<Phil> .... et voila - Tim coded this up and I put in tupence - committed to CVS 13 November 2007 - nice!!</Phil>


Revision [741]

Edited on 2007-11-08 09:11:41 by JeffWang
Additions:
in weberp_production version, source ConnectDB_mysql.inc, they used a function AuditTrail($sql, $user, $page) and a table `sqlaudit`.
Hope this is helpful.
Yes, the problem is we need a config parameter to control the increase of table `sqlaudit`
Deletions:
Follow codes come from weberp_production version. Hope they are helpful.
ConnectDB_mysql.inc
DB_query()
//CMM - added audit trail
$action = strtoupper(substr($SQL, 0, 6));

if($_SESSION['AuditTrail']==_('All Access')
|| ($_SESSION['AuditTrail']==_('Changes Only')
&& ($action=='INSERT'
|| $action=='UPDATE'
|| $action=='DELETE') ) ) {

AuditTrail(addslashes($SQL) , $_SESSION['UserID'], $_SERVER['PHP_SELF']);

}
function AuditTrail($sql, $user, $page){
/*
* Tracks a sql action by logging the sql code to the sqlaudit table
*/

global $AuditTrail_dbuser;
global $AuditTrail_dbpw;
global $host;

//this user has rights only to the sqlaudit table
$auditConn = mysql_connect($host, $AuditTrail_dbuser, $AuditTrail_dbpw);

if(!$auditConn || !mysql_select_db($_SESSION['DatabaseName'],$auditConn)){
echo '<BR>' . _('Could not open audit trail connection!');
}else{

$AuditSql = "INSERT INTO sqlaudit
(sqlaction,
user,
date_time,
page)
VALUES('"
. addslashes($sql)
. "','"
. $user
. "','"
. date("m/d/Y H:i:s")
. "','"
. $page
. "')";
mysql_query($AuditSql,$auditConn);
mysql_close($auditConn);
}
}
Yes, the problem is we need a config parameter to control the increase os table `sqlaudit`


Revision [740]

Edited on 2007-11-08 09:03:08 by JeffWang
Additions:
<JeffWang>
Follow codes come from weberp_production version. Hope they are helpful.
ConnectDB_mysql.inc
DB_query()
//CMM - added audit trail
$action = strtoupper(substr($SQL, 0, 6));

if($_SESSION['AuditTrail']==_('All Access')
|| ($_SESSION['AuditTrail']==_('Changes Only')
&& ($action=='INSERT'
|| $action=='UPDATE'
|| $action=='DELETE') ) ) {

AuditTrail(addslashes($SQL) , $_SESSION['UserID'], $_SERVER['PHP_SELF']);

}
function AuditTrail($sql, $user, $page){
/*
* Tracks a sql action by logging the sql code to the sqlaudit table
*/

global $AuditTrail_dbuser;
global $AuditTrail_dbpw;
global $host;

//this user has rights only to the sqlaudit table
$auditConn = mysql_connect($host, $AuditTrail_dbuser, $AuditTrail_dbpw);

if(!$auditConn || !mysql_select_db($_SESSION['DatabaseName'],$auditConn)){
echo '<BR>' . _('Could not open audit trail connection!');
}else{

$AuditSql = "INSERT INTO sqlaudit
(sqlaction,
user,
date_time,
page)
VALUES('"
. addslashes($sql)
. "','"
. $user
. "','"
. date("m/d/Y H:i:s")
. "','"
. $page
. "')";
mysql_query($AuditSql,$auditConn);
mysql_close($auditConn);
}
}
Yes, the problem is we need a config parameter to control the increase os table `sqlaudit`
</JeffWang>


Revision [700]

Edited on 2007-11-06 13:41:09 by PhilDaintree
Additions:
Thinking about it some more in fact the UPDATE/INSERT/DELETE sql is probably the most succinct way of describing what is happening - rather than long winded narrative that we would otherwise have to change every call to DB_query - it is the association of the login name and the sql we are missing from the sql log. Of course the mysql binary log does filter out select queries already - but we don't have the name next to the sql with that so maybe pragmatically this is the best solution. It will be clever script to parse these into english though...
Deletions:
Thinking about it some more in fact the UPDATE/INSERT/DELETE sql is probably the most succinct way of describing what is happening - rather than long winded narrative that we would otherwise have to change every call to DB_query .


Revision [699]

Edited on 2007-11-06 13:32:02 by PhilDaintree
Additions:
<Phil>
Yes that would work ... maybe with the addition of the calling script as an additional field?
Thinking about it some more in fact the UPDATE/INSERT/DELETE sql is probably the most succinct way of describing what is happening - rather than long winded narrative that we would otherwise have to change every call to DB_query .
May be useful to build in also a new configuration option for the number of months of audit log history to keep otherwise this could end up one "big momma" of a table!
</Phil>


Revision [698]

Edited on 2007-11-06 06:05:53 by TimSchofield
Additions:
<Tim>
<Tim>
What I had in mind was different than the sql log, I had in mind a table with the following fields :
transactionid (auto incremented)
userid
datetime
sqlstring
Within the DB_query function we would then filter out all queries other than INSERT/UPDATE/DELETE queries, and just update the above table with these queries.
Then for the reporting side a script could be written that parsed the sqlstring (there are php classes released under gpl that will parse sql statements, though I haven't investigated them) and output to a table with the following headings:
|Transaction id | User | Date |Table Name |Field Name |New Value| Type of Change|
Or something along these lines.
This would keep the changes needed to just a few scripts.
Deletions:
<Tim?>


Revision [695]

Edited on 2007-11-05 14:05:55 by PhilDaintree
Additions:
<Tim?>
</Tim>
<Phil>Yes - unless we add another parameter to DB_query and do "a little" rework on existing scripts - or what about making a stab at what is being done based on the calling script? - we still need transaction numbers etc though. I am not sure that we want to duplicate the SQL log which is accessible already - provided it is enabled in my.cnf. This is quite a job to effect but probably a significant over-sight in the original system development ... whoops:-(</Phil>


Revision [694]

Edited on 2007-11-05 01:42:44 by TimSchofield
Additions:
~ The problem here is that none of the messages currently sent to DB_query are very informative regarding what the transaction was doing. That information is contained in $SQL, but I think this could cause security issues? If the sql could be stored in a secure way, then a script could be written to parse this string, and display it in a readable form as an audit trail.
Deletions:
~ The problem here is that none of the messages currently sent to DB_query are very informative regarding what the transaction was doing. That information is contained in $SQL, but I think this could cause security issues?


Revision [693]

Edited on 2007-11-04 02:25:25 by TimSchofield
Additions:
~ The problem here is that none of the messages currently sent to DB_query are very informative regarding what the transaction was doing. That information is contained in $SQL, but I think this could cause security issues?
Deletions:
The problem here is that none of the messages currently sent to DB_query are very informative regarding what the transaction was doing. That information is contained in $SQL, but I think this could cause security issues?


Revision [692]

Edited on 2007-11-04 02:24:48 by TimSchofield
Additions:
The problem here is that none of the messages currently sent to DB_query are very informative regarding what the transaction was doing. That information is contained in $SQL, but I think this could cause security issues?


Revision [287]

Edited on 2007-10-05 11:57:48 by PhilDaintree

No Differences

Revision [286]

Edited on 2007-10-05 11:57:38 by PhilDaintree
Additions:
~ 1. Traceability of GRNs paid is not as good as it could be. To identify when an item received was paid for it would be great to be able to click on a stock movement in the movements inquiry and find the GRN it was received on and the supplier invoice it was paid for on. Currently the only loop back to the GRN is through the narrative on the GL transactions created if the GL integration to stock is not used then we don't even have this! I think what is required is to store the GRNs against supplier invoices in a new table - credit notes would need to alter these or perhaps store another negative entry.
~ 2. A history of the costs could be maintained together with the reasons for cost changes with user names effecting the cost changes.
~ 3. An update and insert transaction log - with the username who effected the updates inserts. A new table would be required. This could be done through modifications to the DB_query script and the text of the messages sent to the DB_query function used as log entries. Or maybe the prnMsg function....
Deletions:
* Traceability of GRNs paid is not as good as it could be. To identify when an item received was paid for it would be great to be able to click on a stock movement in the movements inquiry and find the GRN it was received on and the supplier invoice it was paid for on. Currently the only loop back to the GRN is through the narrative on the GL transactions created if the GL integration to stock is not used then we don't even have this! I think what is required is to store the GRNs against supplier invoices in a new table - credit notes would need to alter these or perhaps store another negative entry.
* A history of the costs could be maintained together with the reasons for cost changes with user names effecting the cost changes.
* An update and insert transaction log - with the username who effected the updates inserts. A new table would be required. This could be done through modifications to the DB_query script and the text of the messages sent to the DB_query function used as log entries. Or maybe the prnMsg function....


Revision [269]

The oldest known version of this page was created on 2007-08-20 20:17:32 by PhilDaintree
Valid XHTML :: Valid CSS: :: Powered by WikkaWikiGet webERP Accounting & Business Management at SourceForge.net. Fast, secure and Free Open Source software downloads