maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxxx>]

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.