Solved

The MySQL audit function

Posted on 2013-01-17
18
406 Views
Last Modified: 2013-08-13
Dear all,

Right now we don't have the MYSQL audit plug-in, but it seems that we can do something in the table level so that whenever a user insert a record, we can automatically insert the timestamp to a colume.

Anyway in MySQL or PHP coding we can do it?

DBA100.
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
  • 11
  • 3
  • 3
  • +1
18 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 38790681
Add a column with the default value of 'NOW'.
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 250 total points
ID: 38791059
try


ALTER TABLE level
  ADD COLUMN new_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38791932
"ALTER TABLE level
  ADD COLUMN new_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP "

the new_date is the name of this new column, and it has the type of TIMESTAMP and usually the application not add vaule of this column as whenever a new value input to this new row, then the current time when the row added will add to this colume with that time?
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 39

Expert Comment

by:Pratima Pharande
ID: 38792054
yes you are right .

this column withh insert current time by default , no need to add it from application
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38792090
pratima_mcs,

but one thing, right now we have another problem related to this.

right now, usually that the DB can only log down DB users and what they has done. But what if we only have application users but not DB users, how can we log down, who they are also?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38792110
we have a lot of user and if we can do it in PHP coding level instead of MysQL user level, then it will be perfect.

PHP use only one account to connect to MySQL.

and if we have to create them all user account in MySQL, very time consuming.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38792212
all users must be loogged and saved in DB right ?

and this line will not depend on application , whenever data inserted new_date column ge tpdated with current time

ALTER TABLE level
  ADD COLUMN new_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38792384
"all users must be loogged and saved in DB right ?"

 yes.


can we do in  this way that whatever change to each cell of a table, (update/insert/delete) can be record ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38792419
we need a good audit trial for MySQL, any suggestion ?
0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 38793257
The SQL column type of TIMESTAMP will be updated whenever any column in the row is modified.  This applies to INSERT and UPDATE queries.  Obviously DELETE queries will remove the entire row, so the TIMESTAMP column will be gone, too.

If an UPDATE query WHERE clause matches the row, but no data is actually changed during the UPDATE query, then the TIMESTAMP column will not be changed.

This may or may not be enough for your needs, so consider whether you need greater logging.  You might create a log table with the client ID, a TIMESTAMP and the attempted query.  This would let you create a timeline of events and view it through many different filters.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38797756
Ray_Paseur,

Any paid tools for that ? or we just create one more table to record EVERYTHING ? for example, we need to know which cell the user updated also ?

If one comprehenive tools can solve that why don't we try it? we will upgrade the whole thing to MySQL 5.5 Enterprise.


"The SQL column type of TIMESTAMP will be updated whenever any column in the row is modified.  This applies to INSERT and UPDATE queries.  Obviously DELETE queries will remove the entire row, so the TIMESTAMP column will be gone, too.

If an UPDATE query WHERE clause matches the row, but no data is actually changed during the UPDATE query, then the TIMESTAMP column will not be changed."

how can we mark down that operation for that row/cell is update/insert ?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 38798330
If I am understanding your needs correctly, you want to keep a running log of all changes to the data base.  This implies that you agree that your programmers are trustworthy.  If you are not sure of that, you will need to set up some additional security -- safeguards that prevent programmers from being able to deploy scripts without approval.

If your programmers are trustworthy, then you simply need an abstraction layer over the query functions.  Example: Instead of using mysql_query() to run a query, you would write own_mysql_query().  The own_mysql_query() function would first record the text of the query string and the instant environmental variables in some kind of a log file, perhaps a table of your data base or perhaps an external file, and then it would run the query.

After the query has been run, you might also record the number of affected rows, etc.  It's not rocket science, but it may add a lot of overhead to the query process.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38799790
"If I am understanding your needs correctly, you want to keep a running log of all changes to the data base.  This implies that you agree that your programmers are trustworthy.  If you are not sure of that, you will need to set up some additional security -- safeguards that prevent programmers from being able to deploy scripts without approval."

no matter they are trused or not, we need to make sure that a logging method here to log down all change, that's it. Audit trial provide this.

"If your programmers are trustworthy, then you simply need an abstraction layer over the query functions.  Example: Instead of using mysql_query() to run a query, you would write own_mysql_query().  The own_mysql_query() function would first record the text of the query string and the instant environmental variables in some kind of a log file, perhaps a table of your data base or perhaps an external file, and then it would run the query."

They use the PHP library to do this and it is hard to make them all use that as the exist command is well known. how to stop them from doing this ?

Any paid software for MySQL or PHP for this ? we need to do it in MySQL level as the data write to DB, any thing can suggest of this concept is wrong?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 38801533
Maybe you can use this.  It sounds like it will log everything.
http://dev.mysql.com/doc/refman/5.1/en/query-log.html
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38804036
no, we need audit only tools, this is not the one we want.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39372646
how about a we do it in table trigger level so that when a user update/insert/delete somehting , we write the respective action to antoher table on only for Audit log? this table will have one more time stamp column to record the action.

The result of the MySQL audit plug-in is not good. the infomratoin is much less thant general log.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39404288
Oralce 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.

Can MySQL has any audit feature do this on this to show what that the value 123456 is updated by Joe and the colume he/she udpate is the "salary" one ?

or we need trigger for all insert/update/delete/select on a table anyway and write to a table created for this kind of information?

what I need is data level, log down what vaule has been changed.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39404290
or is there any third party tools can do this for us without building trigger for each table?

if the application has 3000 > tables, then we have to turn on trigger for each of them, this make the audit hightly unmanageable.
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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.
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.

623 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