Solved

MySQL data auditing from PHP side

Posted on 2013-11-11
7
396 Views
Last Modified: 2013-11-20
Dear all,

Oracle has some kind of Fine-grained auditing (FGA), introduced in Oracle9i, allowed recording of these row-level changes, so we know what data has been write to a data cell.

for example,
update SCOTT.EMP set salary = 12000 where empno = 123456;
How do you track this activity in the database?

Usually Audit trial lets you know that Joe updated the table EMP owner by SCOTT, but it does not show that he updated the salary column for the table for employee number 123456. It does not show the value of the salary column before the change, either¿ to capture such detailed changes.

In MySQL, we need trigger for all AFTER insert/update and BEFORE delete on a table and write to another table created for this kind of information, then create the respective trigger for that (AFTER insert/update and BEFORE delete)

We have code here that:
1)      Create a separate audit table with same structure as source table, with table name as DBAudit_<source table>. e.g. :

CREATE TABLE DBAudit_AB  LIKE AB;

Open in new window


2)      Add fields on DBAudit_AB:

ALTER TABLE DBAudit_AB 
ADD DBAuditID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,
ADD TriggerAction VARCHAR( 10 ) NULL AFTER DBAuditID,
ADD Action CHAR( 10 ) NULL AFTER TriggerAction ,
ADD ActionDate DATETIME NULL AFTER Action ,
ADD ActionBy VARCHAR( 50 ) NULL AFTER ActionDate ;

Open in new window


3) Create trigger:

3)	set delimiter to ||

CREATE TRIGGER AB_INSERT_TR AFTER INSERT ON AB
FOR EACH ROW
BEGIN 
INSERT INTO DBAudit_AB SET 
TriggerAction='AFTER',
Action='INSERT',
ActionDate=now(),
ActionBy=USER(),
<field 1>=NEW. <field 1>,
<field 2>=NEW. <field 2>,
<field 3>=NEW. <field 3>;
.
.
.
.
END
||

Open in new window


If the application has 3000 > tables, then we have to turn on trigger for each of them, this make the data audit highly unmanageable.

A method is need for the situation when table is removed/added, it should be about to change the under layer DBAudit table automatically,

If Table A changed fields (add/remove), anyway to know it quickly and execute the whole change in PHP code?

There can be 2 x situtations:
1) Table A has new field added.
2) Table A has field removed.


We want to build a method here that do data level audit automatically, image one table has changed fielded, how can the respective DBAudit_<table> know there are changed of fields and then add/delete the respective new field on the DBAudit_<table> to start data level audit again.?

Anyway in PHP to change the same change to table AB so that A and AB has the same number of field again?
0
Comment
Question by:marrowyung
  • 4
  • 3
7 Comments
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39642123
This sounds like an interesting project, but it's also trying to make MySQL do things that Oracle can do.  And that is like trying to make a car do things that a boat can do.  The outcome is likely to be suboptimal.  If you truly require this functionality you should convert the data base to Oracle and use their functionality.

If you're open to alternatives, you might consider a data base abstraction class that first logs all queries, then executes them.  With some programmatic analysis of the logs, you should be able to follow the trail of changes.  Of course, a lot of this depends on how much you trust your developers to use the abstraction layer.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39643531
"If you truly require this functionality you should convert the data base to Oracle and use their functionality."

we will implement the same thing in Oracle once we are done on MySQL.

Oracel's DDL trigger can do this too !  has to do by some thing else.

" you might consider a data base abstraction class that first logs all queries, then executes them.  With some programmatic analysis of the logs, you should be able to follow the trail of changes.  Of course, a lot of this depends on how much you trust your developers to use the abstraction layer. "

sorry, can't do much on this ! you mean we can't do it from PHP side?
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39644806
A database abstraction layer would be written in PHP.  Instead of calling the database functions directly you would call the functions in the abstraction layer.  These functions would perform the logging you want, then call the database functions.
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 1

Author Comment

by:marrowyung
ID: 39646793
any example ?  I don't know anything in PHP and I have to give them example to read.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39647808
An example?  No, this would be something you would hire a professional developer to write for you.  A coded example would be a work product, not a concept, and for that you would want hands-on professional assistance.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39662729
@marrowyung: It is a plain fact that MySQL can't do what you want without a lot of programming.  Either you do not understand that or you do not understand the EE grading guidelines.  Or both.  Please explain why you gave the worst possible grade anyone can give at EE.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39663036
I don't see any clear definition/example of abstraction layer in PHP, there fore I don't see this answer provide much help.

you can tell the administrator to rise up that rank.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP installation issues 11 59
Move wordpress site 3 38
Why do people dis php? 5 48
Sending an image to a php webservice via cURL from command line 7 29
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question