Solved

MySQL data auditing from PHP side

Posted on 2013-11-11
7
395 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Paypal 502 Error 3 74
get domain with php 7 20
Make custom query_posts look show the excerpt only 9 26
Why my select dropdown does not work? 8 26
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

920 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

18 Experts available now in Live!

Get 1:1 Help Now