9.16 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. 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
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.
|
