Determine the current user in MySQL 5.0 (Part 1: Comparison of built-in functions)

I posted an ignorant comment last night to the MySQL bugs database when I mixed up the different functions used to determine the current user. I didn't realize there was a new function CURRENT_USER() added to MySQL 5.0 but thought it was yet another alias for SESSION_USER(). As a compensation for my mistake I've written this comparison of the two.

Both functions and their aliases actually return the name and host of the current user in the same format, but still they're not at all the same:

SESSION_USER(), USER(), SYSTEM_USER()
Return the credentials (an UTF8 string of the format user@host) that were used for login with the current connection. All three variants are synonym.
CURRENT_USER(), CURRENT_USER
Return the name of the grant (as stored in the mysql.user table) that's currently used to determine access rights (as an UTF8 string in the format of user@host, as for the above functions). The parentheses are facultative here.

So SESSION_USER() and its aliases will return a constant value throughout a whole session, whereas the returned value from CURRENT_USER() can change depending on the context it is executed in.

In a stored procedure for example that has been defined with an SQL SECURITY of DEFINER, the function CURRENT_USER() will show the definer, as its user rights apply during execution. Do the following as your root user to check it out:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
       ON test.* TO testuser@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE test_user_functions()
       SQL SECURITY DEFINER
       SELECT SESSION_USER(), CURRENT_USER();
Query OK, 0 rows affected (0.01 sec)

mysql> call test_user_functions();
+----------------+----------------+
| SESSION_USER() | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)

Now login again as the above created testuser and do the same procedure call again.

mysql> call test_user_functions();
+--------------------+----------------+
| SESSION_USER()     | CURRENT_USER() |
+--------------------+----------------+
| testuser@localhost | root@localhost |
+--------------------+----------------+
1 row in set (0.00 sec)

The session user (the credentials used to login) changed, but the current user (the grant that's active while executing the SELECT inside the stored routine) is still the definer of the procedure.

But even when called directly outside from a stored routine the two functions don't necessarily return the same result. As root user again create yet another user, this time with access rights from any host.

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
       ON test.* TO testuser2@'%';
Query OK, 0 rows affected (0.00 sec)

Now login with this testuser2 account from your localhost and execute the two functions directly inside a SELECT statement:

mysql> SELECT SESSION_USER(), CURRENT_USER();
+---------------------+----------------+
| SESSION_USER()      | CURRENT_USER() |
+---------------------+----------------+
| testuser2@localhost | testuser2@%    |
+---------------------+----------------+
1 row in set (0.01 sec)

SESSION_USER() returns exactly the credentials used for login, including the hostname (which would be different if you logged in from a remote host), but CURRENT_USER() reflects always the underlying grant that determines the access rights of the user, which is testuser2 from any host (%).

In a second part of this blog post I'll show you how to use these functions for auditing in a TRIGGER and what will change regarding this in release 5.0.17 due to the fix of Bug #5861 that actually confused me last night.