Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL data auditing from PHP side

Posted on 2013-11-11
7
Medium Priority
?
405 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 111

Accepted Solution

by:
Ray Paseur earned 1000 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 111

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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 111

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 111

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

Independent Software Vendors: 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!

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
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…
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.

704 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