CURRENT_USER

Returns the details (username and hostname) of the user executing the query.

Syntax

CURRENT_USER()
USER()

Return Type

String

Remarks

  • USER() returns the hostname (if found) of the host specified to the client when connecting, or an empty string if that cannot be resolved.

  • CURRENT_USER() returns the authenticated host, which is taken from the matching user.

    CREATE USER 'admin'@'192.168.88.65';
    Query OK, 0 rows affected (0.003 sec)
    memsql -u admin -h 192.168.88.65
    SELECT USER(), CURRENT_USER();
    +--------+---------------------+
    | USER() | CURRENT_USER()      |
    +--------+---------------------+
    | admin@ | admin@192.168.88.65 |
    +--------+---------------------+
    1 row in set (0.042 sec)

Examples

SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@%         |
+----------------+

The below query returns the current user name and host name for the connection.

SELECT USER();
+------------------------------+
| USER()                       |
+------------------------------+
| root@localhost               |
+------------------------------+
1 row in set (1.22 sec)

This feature can be combined with views to enable row-level permissions. In this example, persons with the employee role can only see themselves, while managers can see all records:

CREATE TABLE employees (
-> id int primary key,
-> fullname varchar(64),
-> account_name varchar(64),
-> role enum('employee', 'manager'),
-> salary int
-> );
CREATE TABLE permissions (
-> account_name varchar(64),
-> emp_id int,
-> primary key (account_name, emp_id)
-> );
CREATE VIEW employee_view as
-> select e.* from employees e, permissions p
-> where e.id = p.emp_id
-> and p.account_name = current_user();
INSERT INTO employees values
-> (1, 'SYSTEM', 'root@%', 'manager', 0),
-> (2, 'Kevin Kelvin', 'kelvin@%', 'manager', 5000),
-> (3, 'Oliver Heavyside', 'oliver@%', 'employee', 3000);
INSERT INTO permissions values
-> ('root@%', 1), ('root@%', 2), ('root@%', 3),
-> ('kelvin@%', 2), ('kelvin@%', 3),
-> ('oliver@%', 3);
-- as root:
SELECT CURRENT_USER(), * FROM employee_view;
+----------------+----+------------------+--------------+----------+--------+
| CURRENT_USER() | id | fullname         | account_name | role     | salary |
+----------------+----+------------------+--------------+----------+--------+
| root@%         |  1 | SYSTEM           | root@%       | manager  |      0 |
| root@%         |  2 | Kevin Kelvin     | kevin@%      | manager  |   5000 |
| root@%         |  3 | Oliver Heavyside | oliver@%     | employee |   3000 |
+----------------+----+------------------+--------------+----------+--------+
-- as kelvin:
SELECT CURRENT_USER(), * FROM employee_view;
+----------------+----+------------------+--------------+----------+--------+
| CURRENT_USER() | id | fullname         | account_name | role     | salary |
+----------------+----+------------------+--------------+----------+--------+
| kelvin@%       |  2 | Kevin Kelvin     | kevin@%      | manager  |   5000 |
| kelvin@%       |  3 | Oliver Heavyside | oliver@%     | employee |   3000 |
+----------------+----+------------------+--------------+----------+--------+
-- as oliver:
SELECT CURRENT_USER(), * FROM employee_view;
+----------------+----+------------------+--------------+----------+--------+
|CURRENT_USER()  | id | fullname         | account_name | role     | salary |
+----------------+----+------------------+--------------+----------+--------+
| oliver@%       |  3 | Oliver Heavyside | oliver@%     | employee |   3000 |
+----------------+----+------------------+--------------+----------+--------+

Last modified: August 18, 2023

Was this article helpful?