Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

CURRENT_USER

Syntax

CURRENT_USER()
SESSION_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.

SQL

CREATE USER 'admin'@'192.168.88.65';

Query OK, 0 rows affected (0.003 sec)

Shell

memsql -u admin -h 192.168.88.65

SQL

SELECT USER(), CURRENT_USER();

+--------+---------------------+
| USER() | CURRENT_USER()      |
+--------+---------------------+
| admin@ | admin@192.168.88.65 |
+--------+---------------------+
1 row in set (0.042 sec)

SESSION_USER() is similar to CURRENT_USER(), but if called from inside a stored procedure, it returns the original user instead of the user the stored procedure is running as.

Examples

SQL

SELECT CURRENT_USER();

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

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

SQL

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:

SQL

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);

SQL

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

SQL

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

SQL

-- as oliver:
SELECT CURRENT_USER(), * FROM employee_view;

+----------------+----+------------------+--------------+----------+--------+
|CURRENT_USER()  | id | fullname         | account_name | role     | salary |
+----------------+----+------------------+--------------+----------+--------+
| oliver@%       |  3 | Oliver Heavyside | oliver@%     | employee |   3000 |
+----------------+----+------------------+--------------+----------+--------+