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.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net