Determining the Current MySQL User
Problem
What is the name of the client user and from what host was the connection made?
Solution
Use the USER( ) function.
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.[41] To select
just the name or host parts, use these queries:
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.\n";
print "I see you're connecting from $host.\n" 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 ...