How the USER() function works in Mariadb?

The USER() function in MariaDB is used to retrieve the username and hostname of the user that is currently connected to the MariaDB server. It provides information about the authenticated user account making the connection.

Syntax

The syntax for the MariaDB USER() function is as follows:

USER()

The USER() function does not take any arguments. It returns a string containing the username and hostname of the currently connected user, in the format 'username'@'hostname'.

Examples

Example 1: Retrieving the Current User Information

This example demonstrates how to use the USER() function to get the username and hostname of the current user.

SELECT USER();

Output:

+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+

In this example, the USER() function returned ‘root@localhost’, indicating that the current user is ‘root’ connected from the local machine.

Example 2: Using USER() in a Query

This example shows how to use the USER() function within a query to filter or display data based on the current user.

DROP TABLE IF EXISTS example;
CREATE TABLE example (id INT, data VARCHAR(100), created_by VARCHAR(100));
INSERT INTO example VALUES (1, 'Record 1', '[email protected]'), (2, 'Record 2', '[email protected]'), (3, 'Record 3', USER());

SELECT * FROM example WHERE created_by = USER();

Output:

+------+----------+----------------+
| id   | data     | created_by     |
+------+----------+----------------+
|    3 | Record 3 | root@localhost |
+------+----------+----------------+

In this example, the USER() function is used to insert the current user’s information into the created_by column of the example table. The query then selects records where the created_by value matches the current user.

Example 3: Using USER() in a Trigger

This example demonstrates how to use the USER() function within a trigger to automatically record the user who modified a record.

DROP TABLE IF EXISTS example;
CREATE TABLE example (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(100), modified_by VARCHAR(100), modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

DELIMITER $$
CREATE TRIGGER example_update_trigger
BEFORE UPDATE ON example
FOR EACH ROW
BEGIN
    SET NEW.modified_by = USER();
END$$
DELIMITER ;

INSERT INTO example (data) VALUES ('Record 1');
UPDATE example SET data = 'Updated Record 1';

SELECT * FROM example;

Output:

+----+------------------+----------------+---------------------+
| id | data             | modified_by    | modified_at         |
+----+------------------+----------------+---------------------+
|  1 | Updated Record 1 | root@localhost | 2024-03-10 15:48:22 |
+----+------------------+----------------+---------------------+

In this example, a trigger example_update_trigger is created on the example table. Before any update operation, the trigger sets the modified_by column to the current user’s information using the USER() function. When a record is updated, the trigger automatically records the user who made the modification.

Related Functions

The following are a few functions related to the MariaDB USER() function:

• MariaDB CURRENT_USER() function returns the username and hostname of the user who is currently connected to the server.
• MariaDB SESSION_USER() function returns the username and hostname of the user for the current connection thread.
• MariaDB SYSTEM_USER() function returns the username and hostname of the user that the server authenticated to connect.

Conclusion

The USER() function in MariaDB is a simple yet powerful tool for retrieving information about the currently connected user. By understanding the syntax and usage examples, you can easily incorporate this function into your SQL queries, stored procedures, triggers, and other database operations. Whether you need to log user activity, filter data based on user permissions, or simply display the current user’s information, the USER() function provides a convenient way to access this information within your MariaDB environment.