Description:
I would like to be able to fire a trigger routine when a user logs into to an account on the database.
Such triggers could have BEFORE and AFTER types. For example, a BEFORE LOGIN trigger could check a user's incoming USER() value and adjust session parameters based on the incoming host address (like different session timeouts, query limits, etc). This is more compact and transparent than having several different accounts the force a user to use different logins and passwords for different locations.
An AFTER LOGIN trigger could do thinks like log a user's login in a table, and do similar housekeeping automatically instead of from an application layer.
Another thing that could be triggered after a login is ON FIRST COMMAND. This variant checks for certain operations to be be performed immediately afer a user account is logged in. For example, if an application accesses the server using a certain account, this routine could check to see that certain signature behaviors occur, adding a layer of security in the form of application behavior checking.
For example, the first thing an application does after establishing a connectinon is insert a row into a log table, containing they USER() and CONNECTION_ID() values along with a timestamp. The trigger can find the last log entry using LAST_INSERT_ID() to check for appropriate values and take further action. An inappropriate user of this account would have to know to perform the appropriate INSERT operation in order to validate this login and continue with the session.
Even an interactive (non-application) user could be rewquiredto issue a specific command to caalidate their use of the login account. This command could even be an invalid one; the ON FIRST COMMAND could check for something like:
HELLO, IT'S ME!;
or the user to enter their full user name, causing an error but also passing a value that can be checked against their entry in the server's mysql.user_info table.
The interactive user could be required to call a particular stored procedure as the first command, or the session will be terminated. A unauthorized visitor would not know that this procedure exists, or what it is named,, because they cannot look for it before calling it.
Login triggers could add considerable functionality and even improve server security with a flexible mechanism.
How to repeat:
This is a feature request, there is no existing syntax, please see description ofr examples.
