Use SET CONTEXT_INFO.
The most common use case for this is to send triggers information about the actor that caused the trigger to fire.
For example:
CREATE PROCEDURE [dbo].[ModifyData]
AS
BEGIN
DECLARE @ObjectID BINARY(4) = CAST(@@PROCID AS BINARY(4));
SET CONTEXT_INFO @ObjectID;
UPDATE [dbo].[Data]
...
END;
GO
CREATE TRIGGER [LogModifications]
ON [dbo].[Data]
FOR UPDATE
AS
BEGIN
DECLARE @ModifiedByObjectID INT = CAST(SUBSTRING(CONTEXT_INFO(), 1, 4) AS INT);
...
END;
GO
Question #1
Is there a way to see the SESSION variables of other active, connected users, from a superuser connection ?
No, there is no way.
Question #2
is there a query or view that would show me which active sessions have variables set differently from the global variables?
No, there is no way.
CAVEAT
From your individual connection you can run this
SELECT A.variable_name VarName,
A.variable_value Global_Value,
B.variable_value Session_Value FROM
information_schema.global_variables A INNER JOIN
information_schema.session_variables B USING (variable_name)
WHERE A.variable_value <> B.variable_value;
to see which session settings differ from the global settings.
For example, here is what I am running
mysql> SHOW VARIABLES LIKE 'version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| version | 5.6.15 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Win64 |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)
mysql>
Note the output of the query
mysql> SELECT A.variable_name VarName,
-> A.variable_value Global_Value,
-> B.variable_value Session_Value FROM
-> information_schema.global_variables A INNER JOIN
-> information_schema.session_variables B USING (variable_name)
-> WHERE A.variable_value <> B.variable_value;
+--------------------------+-------------------+------------------+
| VarName | Global_Value | Session_Value |
+--------------------------+-------------------+------------------+
| CHARACTER_SET_CONNECTION | latin1 | cp850 |
| CHARACTER_SET_RESULTS | latin1 | cp850 |
| CHARACTER_SET_DATABASE | latin1 | utf8 |
| COLLATION_DATABASE | latin1_swedish_ci | utf8_general_ci |
| CHARACTER_SET_CLIENT | latin1 | cp850 |
| COLLATION_CONNECTION | latin1_swedish_ci | cp850_general_ci |
+--------------------------+-------------------+------------------+
6 rows in set (0.08 sec)
If you want to find all the active connections that have this difference, perhaps you could have the application dump the output of that query to a text file along with the connection ID (using the CONNECTION_ID() function) and parse it as you wish.
Best Answer
Yes, this appears to be the case - you don't need any particular
GRANT
in order to modify your own session variables:Then:
Note that of course not all system variables can be modified per session, and some are not even dynamic.