Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

The MySQL audit function

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
marrowyung
Asked:
marrowyung
  • 11
  • 3
  • 3
  • +1
2 Solutions
 
Dave BaldwinFixer of ProblemsCommented:
Add a column with the default value of 'NOW'.
0
 
Pratima PharandeCommented:
try


ALTER TABLE level
  ADD COLUMN new_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
0
 
marrowyungAuthor Commented:
"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
Industry Leaders: 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!

 
Pratima PharandeCommented:
yes you are right .

this column withh insert current time by default , no need to add it from application
0
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
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
 
Pratima PharandeCommented:
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
 
marrowyungAuthor Commented:
"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
 
marrowyungAuthor Commented:
we need a good audit trial for MySQL, any suggestion ?
0
 
Ray PaseurCommented:
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
 
marrowyungAuthor Commented:
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
 
Ray PaseurCommented:
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
 
marrowyungAuthor Commented:
"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
 
Ray PaseurCommented:
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
 
marrowyungAuthor Commented:
no, we need audit only tools, this is not the one we want.
0
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 11
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now