Solved

The MySQL audit function

Posted on 2013-01-17
18
377 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
  • 11
  • 3
  • 3
  • +1
18 Comments
 
LVL 82

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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 108

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 108

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 108

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
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.
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…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

760 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

21 Experts available now in Live!

Get 1:1 Help Now