sqlmysqldatabasemysqlcommandmysql-command-line-client

Reputation: 1563

Show the current username in MySQL?

I would like to know if there's a way for a mysql query to return the username of the user that issues the query.

Is this possible?

Upvotes: 112

Answers (9)

Super Kai - Kazuya Ito

Reputation: 1

You can show the current user with CURRENT_USER(), CURRENT_USER or USER() as shown below:

SELECT CURRENT_USER();

Or:

SELECT CURRENT_USER;

Or:

SELECT USER();

Be careful, USER doesn't exit so it gets error as shown below:

SELECT USER;

ERROR 1054 (42S22): Unknown column 'USER' in 'field list'

Now, the difference between CURRENT_USER() or CURRENT_USER and USER() is:

CURRENT_USER() or CURRENT_USER returns the current user's name and the host.
USER() returns the current user's name but returns the host from which you connect.

For example, you create the user john with % which means any host as shown below:

CREATE USER 'john'@'%';

Then, you log in with the user john from the server where MySQL is running (localhost) as shown below:

mysql -u john

Then, CURRENT_USER() or CURRENT_USER returns the current user's name and the host as shown below:

mysql> SELECT CURRENT_USER(); 
+----------------+
| CURRENT_USER() |
+----------------+
| john@%         |
+----------------+

Or:

mysql> SELECT CURRENT_USER;
+--------------+
| CURRENT_USER |
+--------------+
| john@%       |
+--------------+

Then, USER() returns the current user's name but returns the host from which you connect.

mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| john@localhost |
+----------------+

Upvotes: 0

OneGhana

Reputation: 117

I needed such a feature and i used CURRENT_USER() but that gave me the Global Admin user which happens to be same for every query even if i changed the query username when connecting to the database. Obviously that is useless in the use case i was looking for. Then I used USER() and it gave me the username of the connection string that initiated the query. if i understood the question, this is what the person wanted in plain language. In my case, i used it in a trigger to track changes made to rows in a transaction table.

Upvotes: 0

scotru

Reputation: 2606

Try the CURRENT_USER() function. This returns the username that MySQL used to authenticate your client connection. It is this username that determines your privileges.

This may be different from the username that was sent to MySQL by the client (for example, MySQL might use an anonymous account to authenticate your client, even though you sent a username). If you want the username the client sent to MySQL when connecting use the USER() function instead.

The value indicates the user name you specified when connecting to the server, and the client host from which you connected. The value can be different from that of CURRENT_USER().

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_current-user

Upvotes: 98

heisenbug47

Reputation: 184

You can find the current user name with CURRENT_USER() function in MySQL.

for Ex: SELECT CURRENT_USER();

But CURRENT_USER() will not always return the logged in user. So in case you want to have the logged in user, then use SESSION_USER() instead.

Upvotes: 3

Ashish Karpe

Reputation: 5824

You can also use : mysql> select user,host from mysql.user;

+---------------+-------------------------------+
| user          | host                          |
+---------------+-------------------------------+
| fkernel       | %                             |
| nagios        | %                             |
| readonly      | %                             |
| replicant     | %                             |
| reporting     | %                             |
| reporting_ro  | %                             |
| nagios        | xx.xx.xx.xx                 |
| haproxy_root  | xx.xx.xx.xx
| root          | 127.0.0.1                     |
| nagios        | localhost                     |
| root          | localhost                     |
+---------------+-------------------------------+

Upvotes: 2

Prashanthhh Kumarr

Reputation: 87

to print the current user who is using the database

select user();

select current_user();

Upvotes: 3

Petr Mensik

Reputation: 27536

Try to run either

SELECT USER();

SELECT CURRENT_USER();

It can sometimes be different, USER() will return by which login you attempted to authenticate and CURRENT_USER() will return how you were actually allowed to authenticate.

Upvotes: 114