Solved

MySQL data auditing from PHP side

Posted on 2013-11-11
7
399 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 110

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 110

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 110

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 110

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

740 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