MySql: Trigger for audit connection connections

Triggers in mysql that there are basic and only at the table. There SqlServer or Oracle as a trigger to allow hunting the connections open and get some information regarding the sessions.
A bad example. It may be the case that in a web environment we have a farm of apache servers and whatever one is going to pot. Begins to open threads in our bulk mysql (for whatever reason) and crashes the server because we do not have to limit the number of simultaneous connections for that user. What bad feeling no? And on top of this happens when we are not in the office we can find that we do not know much of what happened, for example we see the peak in cacti but we have no detail.

To get some more and to audit when and who opens connections, we will:

  1. Create schema a schema (or not).

    schema create audit;
    use audit;

  2. Create within the table to store data.
    CREATE TABLE aud_conexiones (
    id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
    , Thread_id INT UNSIGNED NOT NULL DEFAULT 0
    , User VARCHAR (64) NOT NULL DEFAULT 'unknown'
    , Login_ts TIMESTAMP NULL DEFAULT NULL);
  3. We will create a procedure to insert the session data.
    DELIMITER / /
    CREATE PROCEDURE auditoria.login_trigger ()
    SQL SECURITY DEFINER
    BEGIN
    INSERT INTO auditoria.aud_conexiones (thread_id, user, login_ts)
    VALUES (CONNECTION_ID (), USER (), NOW ());
    END;
    / /
    DELIMITER;
  4. Call the procedure to connect (init_connect global variable) and give a user permission to audit.

    SET GLOBAL init_connect = "CALL auditoria.login_trigger ()";

    So far so good.Now it is testing. We begin with a user: we will leave, we tried to make the connection and see if data is loaded into the table.
    GRANT EXECUTE ON PROCEDURE auditoria.login_trigger TO 'user' @ 'host';
    FLUSH PRIVILEGES;

  5. Auditing around the world. For this all you have to do is traipsing all the users table or in our version of MySQL already exists information_schema can run the following query that we "create" the grants to all users except root:
    select concat ("GRANT EXECUTE ON PROCEDURE TO auditoria.login_trigger '" ,"'@'", user WHERE host mysql.user ,"';") from user <>' root ';
    We will take the result, we copy & paste and run the entire query churro.
    Flush privileges we do it again and voila!

Pegas
The system is a bit shabby but it is useful and easy to implement. What's missing here is setting up a mechanism to clean up the table so that one size does not end up getting upset and another way to automate the grant for each new user (eg with a new procedure that gives us new users high and we then the corresponding grant.) Time to time ...