Determining the Current MySQL User

9.16.1 Problem

What is the name of the client user and from what host was the connection made?

9.16.2 Solution

Use the USER( ) function.

9.16.3 Discussion

SELECT USER( ) returns a string in the form user@host, indicating the name of the current user and the host from which the user connected.[5] To select just the name or host parts, use these queries:

[5] Prior to MySQL 3.22.1, the value of USER( ) does not include the @host part.

SELECT SUBSTRING_INDEX(USER( ),'@',1);
SELECT SUBSTRING_INDEX(USER( ),'@',-1);

You can use this information in various ways. For example, to have a Perl application greet the user, you could do something like this:

my ($user, $host) = $dbh->selectrow_array (q{
 SELECT SUBSTRING_INDEX(USER( ),'@',1),
 SUBSTRING_INDEX(USER( ),'@',-1)
 });
print "Hello, $user! Good to see you.
";
print "I see you're connecting from $host.
" unless $host eq "";

Alternatively, you could simply retrieve the entire USER( ) value and break it apart by using a pattern-match operation:

my ($user, $host) = ($dbh->selectrow_array (
 "SELECT USER( )") =~ /([^@]+)@?(.*)/);

Or by splitting it:

my ($user, $host) = split (/@/, $dbh->selectrow_array ("SELECT USER( )"));

Another application for USER( ) values is to maintain a log of who's using an application. A simple log table might look like this (the values 16 and 60 reflect the lengths of the user and host columns in the MySQL grant tables):

CREATE TABLE app_log
(
 t TIMESTAMP,
 user CHAR(16),
 host CHAR(60)
);

To insert new records into the app_log table, use the following statement. The TIMESTAMP column gets set automatically to the current date and time; there's no need to specify a value for it.

INSERT INTO app_log
 SET user = SUBSTRING_INDEX(USER( ),'@',1),
 host = SUBSTRING_INDEX(USER( ),'@',-1);

The table stores the user and host values separately because it's more efficient to run summary queries against those values when you don't have to break them apart. For example, if you check periodically how many distinct hosts you're getting connections from, it's better to split the USER( ) value once when you create the record than to split the value each time you issue a SELECT to generate the summary. Also, you can index the host column if you store host values separately, which you can't do if you store combined user@host values.