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
Solved

The MySQL audit function

Posted on 2013-01-17
18
392 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
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…

829 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