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 | +----------------+----+------------------+--------------+----------+--------+