Solved

The MySQL audit function

Posted on 2013-01-17
18
389 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 109

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 109

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 109

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dynamic varibles 5 32
php extract($_REQUEST) 5 50
PHP curl issue VERBOSE output 18 42
MySQL Warning Statements when you have a LIMIT clause. 6 29
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The viewer will learn how to dynamically set the form action using jQuery.
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…

773 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