Bug #30871 Feature request: login triggers
Submitted: 6 Sep 2007 16:40 Modified: 24 Aug 2010 9:32
Reporter: Dan Kloke (Candidate Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: login, qc, trigger

[6 Sep 2007 16:40] Dan Kloke
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:


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.
[8 Sep 2007 2:41] Valeriy Kravchuk
Thank you for a reasonable feature request.
[14 Sep 2007 19:14] Sergei Golubchik
init_connect server variable is pretty close to a "login trigger".
At least it's a very decent workaround.
[21 Feb 2010 14:50] Roger U
Did this, or anything similar, get implemented?

If not ...well I would like it!
[21 Apr 2010 18:24] Tim Looney
init_connect appears to be applied for all user connections that do not have the super privilege. I would like to execute a different init_connect based on the user id connecting. A logon trigger (Like Oracle) would work perfectly. Any further thoughts on this?
[24 Aug 2010 9:32] Valeriy Kravchuk
This is a duplicate of bug #17632 actually.