Easiest Application-Level MySQL Auditing

Direct play this episode at:
 
Download all podcasts at:
 
Subscribe to the podcast at:
 
Google Summer of Code Home Page:
 
MySQL Summer of Code Ideas:
 
MySQL Summer of Code Accepted Projects:
 
 
Feedback:

email
podcast@technocation.org

call the comment line at +1 617-674-2369

use Odeo to leave a voice mail through your computer:
http://odeo.com/sendmeamessage/Sheeri

Or use the Technocation forums:
http://technocation.org/forum

Direct play this episode at:
 
Download all podcasts at:
 
Subscribe to the podcast at:
 
Google Summer of Code Home Page:
 
MySQL Summer of Code Ideas:
 
MySQL Summer of Code Accepted Projects:
 
 
Feedback:

email
podcast@technocation.org

call the comment line at +1 617-674-2369

use Odeo to leave a voice mail through your computer:
http://odeo.com/sendmeamessage/Sheeri

Or use the Technocation forums:
http://technocation.org/forum

In the spirit of humor, healing here are 2 ways I know I am a MySQL geek. These are actually things I do….

You Know You’re a MySQL Geek When….
1. You cannot type the word “myself” without typing “mysql” first, troche deleting 2 characters and finishing out the word.
2. You type “show processlist” at the commandline when you really mean “ps -ef”

Anyone have anything they can add to the list?

Direct play this episode at:
 
Download all podcasts at:
 
Subscribe to the podcast at:
 
Google Summer of Code Home Page:
 
MySQL Summer of Code Ideas:
 
MySQL Summer of Code Accepted Projects:
 
 
Feedback:

email
podcast@technocation.org

call the comment line at +1 617-674-2369

use Odeo to leave a voice mail through your computer:
http://odeo.com/sendmeamessage/Sheeri

Or use the Technocation forums:
http://technocation.org/forum

In the spirit of humor, healing here are 2 ways I know I am a MySQL geek. These are actually things I do….

You Know You’re a MySQL Geek When….
1. You cannot type the word “myself” without typing “mysql” first, troche deleting 2 characters and finishing out the word.
2. You type “show processlist” at the commandline when you really mean “ps -ef”

Anyone have anything they can add to the list?
In this episode, meningitis we go through how a B-tree works. The next episode will use what we learn in this episode to explain why MySQL indexes work the way they do.

Direct play this episode at:
http://technocation.org/content/oursql-episode-13%3A-nitty-gritty-indexes-0

Download all podcasts at:
http://technocation.org/podcasts/oursql/

Subscribe to the podcast at:
http://feeds.feedburner.com/oursql

Register for the MySQL Conference now!:
http://www.mysqlconf.com

Quiz to receive a free certification voucher from Proven Scaling:
http://www.provenscaling.com/freecert

MySQL Full Reference Cards:
http://www.visibone.com/sql

About B-Trees:
http://www.semaphorecorp.com/btp/algo.html

http://perl.plover.com/BTree/article.txt

Feedback:

Email podcast@technocation.org

call the comment line at +1 617-674-2369

use Odeo to leave a voice mail through your computer:
http://odeo.com/sendmeamessage/Sheeri

Or use the Technocation forums:
http://technocation.org/forum

Direct play this episode at:
 
Download all podcasts at:
 
Subscribe to the podcast at:
 
Google Summer of Code Home Page:
 
MySQL Summer of Code Ideas:
 
MySQL Summer of Code Accepted Projects:
 
 
Feedback:

email
podcast@technocation.org

call the comment line at +1 617-674-2369

use Odeo to leave a voice mail through your computer:
http://odeo.com/sendmeamessage/Sheeri

Or use the Technocation forums:
http://technocation.org/forum

In the spirit of humor, healing here are 2 ways I know I am a MySQL geek. These are actually things I do….

You Know You’re a MySQL Geek When….
1. You cannot type the word “myself” without typing “mysql” first, troche deleting 2 characters and finishing out the word.
2. You type “show processlist” at the commandline when you really mean “ps -ef”

Anyone have anything they can add to the list?
In this episode, meningitis we go through how a B-tree works. The next episode will use what we learn in this episode to explain why MySQL indexes work the way they do.

Direct play this episode at:
http://technocation.org/content/oursql-episode-13%3A-nitty-gritty-indexes-0

Download all podcasts at:
http://technocation.org/podcasts/oursql/

Subscribe to the podcast at:
http://feeds.feedburner.com/oursql

Register for the MySQL Conference now!:
http://www.mysqlconf.com

Quiz to receive a free certification voucher from Proven Scaling:
http://www.provenscaling.com/freecert

MySQL Full Reference Cards:
http://www.visibone.com/sql

About B-Trees:
http://www.semaphorecorp.com/btp/algo.html

http://perl.plover.com/BTree/article.txt

Feedback:

Email podcast@technocation.org

call the comment line at +1 617-674-2369

use Odeo to leave a voice mail through your computer:
http://odeo.com/sendmeamessage/Sheeri

Or use the Technocation forums:
http://technocation.org/forum

This article shows the easiest way to audit commands to a MySQL database, refractionist assuming all content happens from an application. Now, viagra this will use a lot of storage, dosage and doubles the query load for each query, but it’s useful for when you know you want to capture the information of someone using the application.

The basic premise is simple. Logon to your nearest MySQL server and type the following:

SELECT CURRENT_USER(), USER();

Chances are the values are different. More on this later.

First, create a table:

CREATE TABLE `action` (
`user` varchar(77) NOT NULL default '',
`asuser` varchar(77) NOT NULL default '',
`db` varchar(64) NOT NULL default '',
`query` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Why varchar(77)? Because the mysql.user table puts a maximum of 16 characters for the username, and 60 characters for the hostname. And then there’s the 1 character “@”. Similarly, database names are limited to varchar(64).

The “asuser” column is the grant record that the user is acting as. For instance, a connection with the username “sheeri” from the host “www.sheeri.com” has a user value of “sheeri@www.sheeri.com” but may have an asuser value of “sheeri@’%.sheeri.com'” — whatever the GRANT statement that applies to my current user is. This is the difference between CURRENT_USER() and USER().

Then, create the function — here’s a PHP example:
function my_mysql_query ($query, $dblink) {
$action="INSERT INTO action (user,asuser,db,query) VALUES (CURRENT_USER(), USER(), DATABASE(), $query)";
mysql_query($action, $dblink);
mysql_query($query, $dblink);
}

Of course, we could also add in application specific information. For a web-based application where there is an overall password instead of a different password for each customer or user, this does not help. However in that case, a session username and client IP can be easily gotten from environment variables and used instead of the MySQL-specific “user@host”.

To use it, simply use my_mysql_query in place of mysql_query.

Note that this is the quick-and-dirty way to do it.

Comments are closed.