Hi, Oli! On Dec 11, Oli Sennhauser wrote:
Hi all,
As suggested by Sergei we should discuss this here.
Copying the login trigger stuff to a place in the code where I thought it makes sense I have created a logout trigger. This gives the capability to track a session from its beginning to its end and collects all the data from each session. With this capability we can start thinking about auditing of MySQL users like it is needed/wanted from big companies. http://www.fromdual.com/mysql-logon-and-logoff-trigger-for-auditing
I am NOT a developer and I was just lucky that it worked out how I did it. So I am not of much help for an other approach as Sergei hinted in his comment.
I didn't thought so much about the implementation, but about the interface, how it look from the user point of view. You, yourself, used the word "logon and logoff triggers" - perhaps, these *should* indeed be defined as triggers? There are different "events" that a user can attach SQL code on - isn't it time to consolidate the interface to them, and to use a unified syntax for that? Like CREATE TRIGGER foo BEFORE INSERT ON test1 FOR EACH ROW CALL sp1(); CREATE TRIGGER bar ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO UPDATE myschema.mytable SET mycol = mycol + 1; CREATE TRIGGER xyz ON LOGON INSERT INTO logons VALUES (USER(), NOW()); etc. In this form, this is purely a syntax extension - different set of keywords, providing exactly the same functionality as before. Old syntax should continue to work, of course. Regards, Sergei P.S. I'd also like to see changes in functionality, coming after changes in the syntax. E.g. an I_S table that shows all triggers. Or MySQL remembering and preserving LOGON/LOGOFF triggers over restart. But I don't mind moving forward one step at time.