MySQL Bugs: #41743: Functions to get information about the original thread

Bug #41743	Functions to get information about the original thread
Submitted:	25 Dec 2008 11:39	Modified:	4 Oct 2010 19:04
Reporter:	Serdar S. Kacar	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	5.1, 6.X	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	CONNECTION_ID, current_user

Description:
Some of the original thead information (user, thread_id) is not accessible when thread changes identity.

They should be already kept in well defined/established structures. Please expose them by functions and/or session variables :

ORIGINAL_USER()
CURRENT_USER() value changes in a thread  
- when SQL SECURITY DEFINER is used in stored procedures, triggers and views,
- if it is an event,
- if it is Slave SQL.

ORIGINAL_CONNECTION_ID()
Most of the time, it is the same as CONNECTION_ID().
But Slave SQL thread change(s) it ? (Confirmation required)

ORIGINAL_CONNECTION_TYPE()
"User"  : Standard user connection
"Generic System" : E.g. for the process that executes "init_file" sql
"Event" : Ones spawned by event scheduler
"SlaveSQL": Slave SQL thread

It should be easy to implement since all of the above functions are primitive : read thread variables.
For the same reason, there should be no code stability risk, since it is almost impossible to introduce bugs while implementing those kind of primitive functions.
Malicious use risk is none as fas as I can think of.

Benefits are :
- being able to keep log about the callers where identity could have been changed temporarily (read stored rouines)
- design safe&understandable custom execution paths on master / slave (it is still possible but requires tricks/"hard to read" and assumptions/"could be broken in any future release"
- .. add your own ones ..

How to repeat:
N/A

One testcase where being able to find out a base thread_id would be just to be able to differentiate if you are in the SQL Thread or not on a slave.  

When auditing you could then have separate logic branches within a stored procedure or trigger for if it is run within the SQL thread and "normal" connections.

So a IS_SQLTHREAD() function or a SERVER_THREAD_ID to always give the servers thread_id value - the CONNECTION_ID() on the slave in the SQL Thread gives the thread_id from the master...