How the SYSTEM_USER() function works in Mariadb?

The SYSTEM_USER() function in MariaDB is used to retrieve the username and hostname of the current client connected to the server. This function is particularly useful for auditing purposes, logging user activities, and implementing access control mechanisms based on the user’s identity.

Syntax

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

SYSTEM_USER()

The SYSTEM_USER() function does not accept any parameters. It returns a STRING value representing the combination of the username and hostname of the current client connected to the server, in the format 'username'@'hostname'.

Examples

Example 1: Retrieving the current user’s identity

This example demonstrates how to use the SYSTEM_USER() function to retrieve the username and hostname of the current client connected to the server.

SELECT SYSTEM_USER();

The following is the output:

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

In this example, the function returns the string 'root'@'localhost', indicating that the current client is connected with the username 'root' from the hostname 'localhost'.

Example 2: Using SYSTEM_USER() in a query

This example shows how to use the SYSTEM_USER() function in a query to filter data based on the current user’s identity.

DROP TABLE IF EXISTS logs;
CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event VARCHAR(100),
    created_by VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO logs (event, created_by) VALUES
    ('User logged in', '[email protected]'),
    ('File uploaded', '[email protected]'),
    ('Database backup', SYSTEM_USER());

SELECT * FROM logs WHERE created_by = SYSTEM_USER();

The following is the output:

+----+-----------------+----------------+---------------------+
| id | event           | created_by     | created_at          |
+----+-----------------+----------------+---------------------+
|  3 | Database backup | root@localhost | 2024-03-09 15:55:21 |
+----+-----------------+----------------+---------------------+

In this example, the SYSTEM_USER() function is used in the INSERT statement to record the current user’s identity in the created_by column. The subsequent SELECT statement retrieves only the rows where the created_by value matches the current user’s identity returned by SYSTEM_USER().

Example 3: Using SYSTEM_USER() in a VIEW

This example demonstrates how to use the SYSTEM_USER() function in a VIEW to restrict data access based on the current user’s identity.

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer VARCHAR(100),
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

INSERT INTO orders (customer, order_date, total_amount) VALUES
    ('John Doe', '2023-03-01', 100.50),
    ('Jane Smith', '2023-03-05', 75.25),
    ('Bob Johnson', '2023-03-09', 120.75);

CREATE VIEW customer_orders AS
SELECT id, customer, order_date, total_amount
FROM orders
WHERE customer = SUBSTRING_INDEX(SYSTEM_USER(), '@', 1);

SELECT * FROM customer_orders;

The following is the output:

+----+----------+------------+--------------+
| id | customer | order_date | total_amount |
+----+----------+------------+--------------+
|  1 | John Doe | 2023-03-01 |       100.50 |
+----+----------+------------+--------------+

In this example, the SYSTEM_USER() function is used in the WHERE clause of the customer_orders VIEW to filter the rows based on the username part of the current user’s identity. The SUBSTRING_INDEX() function is used to extract the username from the SYSTEM_USER() result. When querying the customer_orders VIEW, only the rows where the customer value matches the current user’s username are displayed.

Example 4: Using SYSTEM_USER() in a trigger

This example demonstrates how to use the SYSTEM_USER() function in a trigger to automatically record the user who modifies data in a table.

DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    last_updated_by VARCHAR(100),
    last_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER trg_customers_update
BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
    SET NEW.last_updated_by = SYSTEM_USER();
END$$
DELIMITER ;

INSERT INTO customers (name, email) VALUES
    ('John Doe', '[email protected]'),
    ('Jane Smith', '[email protected]');

UPDATE customers SET email = '[email protected]' WHERE id = 1;

SELECT * FROM customers;

The following is the output:

+----+------------+------------------+-----------------+---------------------+
| id | name       | email            | last_updated_by | last_updated_at     |
+----+------------+------------------+-----------------+---------------------+
|  1 | John Doe   | [email protected] | root@localhost  | 2024-03-09 15:57:40 |
|  2 | Jane Smith | [email protected] | NULL            | 2024-03-09 15:57:40 |
+----+------------+------------------+-----------------+---------------------+

In this example, a BEFORE UPDATE trigger is created on the customers table. The trigger uses the SYSTEM_USER() function to record the current user’s identity in the last_updated_by column whenever a row is updated. When the UPDATE statement is executed, the trigger populates the last_updated_by column with the value returned by SYSTEM_USER().

Example 5: Using SYSTEM_USER() in a stored procedure

This example shows how to use the SYSTEM_USER() function in a stored procedure to restrict access based on the current user’s identity.

DROP PROCEDURE IF EXISTS get_customer_orders;

DELIMITER $$
CREATE PROCEDURE get_customer_orders(IN customer_name VARCHAR(100))
BEGIN
    IF customer_name = SUBSTRING_INDEX(SYSTEM_USER(), '@', 1) THEN
        SELECT id, order_date, total_amount
        FROM orders
        WHERE customer = customer_name;
    ELSE
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Access denied. You can only view your own orders.';
    END IF;
END$$
DELIMITER ;

CALL get_customer_orders('John Doe');

The following is the output:

ERROR 1644 (45000): Access denied. You can only view your own orders.

In this example, a stored procedure get_customer_orders is created. The procedure checks if the provided customer_name parameter matches the username part of the current user’s identity returned by SYSTEM_USER(). If the condition is met, the procedure retrieves and displays the order details for the specified customer. Otherwise, it raises an error denying access.

Related Functions

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

• The USER() function returns the username of the current client connected to the server.
• The CURRENT_USER() function returns the username and hostname of the current client connected to the server, similar to SYSTEM_USER().
• The SESSION_USER() function returns the username and hostname of the current client connected to the server, taking into account any proxying or mapping of usernames.
• The DATABASE() function returns the name of the currently selected database.

Conclusion

The SYSTEM_USER() function in MariaDB is a valuable tool for retrieving the username and hostname of the current client connected to the server. It plays a crucial role in auditing, logging user activities, and implementing access control mechanisms based on the user’s identity. By understanding the usage and applications of this function, along with related functions like USER() and CURRENT_USER(), developers can enhance the security and auditing capabilities of their MariaDB applications.