List:General Discussion« Previous MessageNext Message »
From:Sid Lane Date:August 29 2008 1:16pm
Subject:login logger
View as plain text  

I have been playing with different ways to improve diagnostic and audit data
in MySQL that don't necessarily involve source mods (ex. general log to fifo
w/background parser/logger).  I've come up w/the following way of logging
connections to give similar data to the listener.log file in Oracle would
but wanted to run it by the collective to see if there's anything obviously
stupid I'm missing:

CREATE TABLE `connection_log` (
  `connected_at` datetime default NULL,
  `host` varchar(64) default NULL,
  `user` varchar(64) default NULL,
  `fn_user` varchar(255) default NULL,
  `authenticated_as` varchar(255) default NULL,
  `id` int(10) unsigned default NULL

set global init_connect='insert into gumsdata.connection_log (connected_at,
host, user, fn_user, authenticated_as, id) values (utc_timestamp(),
substr(user(), instr(user(), char(64))+1, length(user())-instr(user(),
char(64))), substr(user(), 1, instr(user(), char(64))-1), user(),
current_user(), connection_id())';

it seems to work great on the qc DB but (again) I thought I should give it a
peer-review sanity test before I begin rolling it into prod next week.  the
only obvious problems/limitations I see are that super users don't execute
init_connect and that insert priv has to be granted to the log table every
time a new user is added.  the 1st issue doesn't concern me too much since
MySQL root is only allowed from localhost and we use a secured, key logging
shell surrogate/proxy thing (powerbroker) - my main goal is to hunt
down/catalog one-off programs which occasionally use the DB(s) but don't
maintain persistent connections (ex. cron scripts).  the 2nd issue is a
little trickier - obviously basic process discipline (i.e. grant the table
when you create a user) would work but if we as an organization had that I
wouldn't have to be writing this thing in the 1st place.  I thought about
putting trigger(s) on the mysql (re: security) tables to create/update the
corresponding tables_priv row which would work but kind of offends me on

anyone have any feedback on this (preferrably constructive) before I start
rolling this out live next week?


login loggerSid Lane29 Aug