Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query
optimization, and usability that you won’t want to miss.
SingleStore Managed Service does not support this command.
Returns the details (username and hostname) of the user executing the query.
CURRENT_USER()
USER()
String
SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@% |
+----------------+
The below query returns the current user name and host name for the MySQL 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 |
+----------------+----+------------------+--------------+----------+--------+