hey, 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 principle. anyone have any feedback on this (preferrably constructive) before I start rolling this out live next week? thanks!
