Solved

MySQL data auditing from PHP side

Posted on 2013-11-11
7
392 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 108

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 108

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 108

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 108

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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.

706 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now