How the SESSION_USER() function works in Mariadb?

The SESSION_USER() function in MariaDB is used to retrieve the current user’s username and hostname associated with the current session. This function is particularly useful when you need to identify the user who is executing a query or performing certain actions in the database.

Syntax

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

SESSION_USER()

The SESSION_USER() function does not take any arguments. It simply returns the current user’s username and hostname as a string in the format 'user_name@host_name'.

Examples

Example 1: Get the current user’s username and hostname

This example demonstrates how to retrieve the current user’s username and hostname using the SESSION_USER() function.

SELECT SESSION_USER();

The output for this statement is:

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

In this case, the current user is root, and the session is established from the local host (localhost).

Example 2: Use SESSION_USER() in a WHERE clause

You can use the SESSION_USER() function in a WHERE clause to filter results based on the current user.

DROP TABLE IF EXISTS user_logs;
CREATE TABLE user_logs (
    log_id INT PRIMARY KEY,
    log_message VARCHAR(100),
    created_by VARCHAR(50)
);

INSERT INTO user_logs (log_id, log_message, created_by) VALUES
    (1, 'User logged in', '[email protected]'),
    (2, 'File uploaded', SESSION_USER()),
    (3, 'Report generated', '[email protected]');

SELECT log_message, created_by
FROM user_logs
WHERE created_by = SESSION_USER();

The output for this statement is:

+---------------+----------------+
| log_message   | created_by     |
+---------------+----------------+
| File uploaded | root@localhost |
+---------------+----------------+

In this example, the SESSION_USER() function is used in the WHERE clause to filter the rows where the created_by column matches the current user’s username and hostname.

Example 3: Use SESSION_USER() in a trigger

The SESSION_USER() function can be used in triggers to capture the current user’s information when certain actions occur.

DROP TABLE IF EXISTS user_activities;
CREATE TABLE user_activities (
    activity_id INT PRIMARY KEY AUTO_INCREMENT,
    activity_name VARCHAR(50),
    performed_by VARCHAR(50),
    activity_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER log_user_activity
AFTER INSERT ON user_logs
FOR EACH ROW
BEGIN
    INSERT INTO user_activities (activity_name, performed_by)
    VALUES (CONCAT('New log entry: ', NEW.log_message), SESSION_USER());
END//
DELIMITER ;

INSERT INTO user_logs (log_id, log_message, created_by) VALUES
    (4, 'Error occurred', '[email protected]');

SELECT * FROM user_activities;

The output for this statement is:

+-------------+-------------------------------+----------------+---------------------+
| activity_id | activity_name                 | performed_by   | activity_time       |
+-------------+-------------------------------+----------------+---------------------+
|           1 | New log entry: Error occurred | root@localhost | 2024-03-08 21:24:24 |
+-------------+-------------------------------+----------------+---------------------+

In this example, a trigger is created to log user activities in the user_activities table whenever a new row is inserted into the user_logs table. The SESSION_USER() function is used to capture the current user’s information in the performed_by column.

Example 4: Use SESSION_USER() in a view

You can use the SESSION_USER() function in a view to provide a user-specific perspective of the data.

DROP VIEW IF EXISTS user_specific_logs;

CREATE VIEW user_specific_logs AS
SELECT log_id, log_message, created_by
FROM user_logs
WHERE created_by = SESSION_USER();

SELECT * FROM user_specific_logs;

The output for this statement is:

+--------+---------------+----------------+
| log_id | log_message   | created_by     |
+--------+---------------+----------------+
|      2 | File uploaded | root@localhost |
+--------+---------------+----------------+

In this example, a view user_specific_logs is created to display only the log entries created by the current user. The SESSION_USER() function is used in the WHERE clause of the view to filter the rows based on the current user’s username and hostname.

Example 5: Use SESSION_USER() in a stored procedure

The SESSION_USER() function can also be used within stored procedures to capture the current user’s information.

DELIMITER //
CREATE PROCEDURE log_user_action(
    IN action_name VARCHAR(50)
)
BEGIN
    INSERT INTO user_activities (activity_name, performed_by)
    VALUES (action_name, SESSION_USER());
END//
DELIMITER ;

CALL log_user_action('User logged out');

SELECT * FROM user_activities
ORDER BY activity_id DESC
LIMIT 1;

The output for this statement is:

+-------------+-----------------+----------------+---------------------+
| activity_id | activity_name   | performed_by   | activity_time       |
+-------------+-----------------+----------------+---------------------+
|           2 | User logged out | root@localhost | 2024-03-08 21:25:20 |
+-------------+-----------------+----------------+---------------------+

In this example, a stored procedure log_user_action is created to log user activities in the user_activities table. The SESSION_USER() function is used within the procedure to capture the current user’s information in the performed_by column.

Related Functions

Here are a few functions related to the MariaDB SESSION_USER() function:

• MariaDB USER() function is used to retrieve the current user’s username and hostname in the format 'user_name@host_name'.
• MariaDB CURRENT_USER() function is used to retrieve the current user’s username and hostname in the format 'user_name@host_name'.
• MariaDB SYSTEM_USER() function is used to retrieve the current user’s username and hostname in the format 'user_name@host_name'.

Conclusion

The SESSION_USER() function in MariaDB is a handy tool for retrieving the current user’s username and hostname associated with the current session. By understanding the usage of this function, you can implement various security measures, logging mechanisms, and user-specific data filtering in your MariaDB database. Whether you need to capture user information in triggers, views, stored procedures, or general queries, the SESSION_USER() function provides a straightforward way to accomplish this task.