How the CURRENT_USER() function works in Mariadb?

The CURRENT_USER() function is a built-in function in Mariadb that returns the user name and host name of the current user account. This function is often used to check the privileges or roles of the current user in a database.

Syntax

The syntax of the CURRENT_USER() function is as follows:

CURRENT_USER()

The CURRENT_USER() function does not take any arguments. It returns a string value in the format of 'user_name'@'host_name'.

Examples

Example 1: Basic usage of CURRENT_USER()

The following example shows how to use the CURRENT_USER() function to get the user name and host name of the current user account.

SELECT CURRENT_USER();

The output is:

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

This means that the current user is root and the host name is localhost, which means any host.

Example 2: Using CURRENT_USER() with IF()

The following example shows how to use the CURRENT_USER() function with the IF() function to perform different actions based on the current user account.

SELECT IF(CURRENT_USER() = 'root@localhost',
   'You are the administrator',
   'You are not the administrator') AS result;

The output is:

+---------------------------+
| result                    |
+---------------------------+
| You are the administrator |
+---------------------------+

This means that the current user is root@localhost, so the first argument of the IF() function is executed.

Example 3: Using CURRENT_USER() with GRANT

The following example shows how to use the CURRENT_USER() function with the GRANT statement to grant privileges to the current user account.

GRANT SELECT, INSERT, UPDATE ON test.* TO CURRENT_USER();

This statement grants the SELECT, INSERT, and UPDATE privileges on the test database to the current user account.

Related Functions

There are some other functions in Mariadb that are related to the CURRENT_USER() function. Here are some of them:

• USER(): This function returns the user name and host name of the current client connection. It is similar to the CURRENT_USER() function, but it does not reflect the changes made by the SET ROLE or SET SESSION AUTHORIZATION statements.
• SESSION_USER(): This function returns the user name of the current session. It is equivalent to the SUBSTRING_INDEX(CURRENT_USER(), '@', 1) expression, which extracts the user name part from the CURRENT_USER() function.
• SYSTEM_USER(): This function returns the user name of the operating system user that runs the Mariadb server process. It is useful to check the system-level permissions of the Mariadb server.

For example, the following query shows the difference between the CURRENT_USER() function, the USER() function, and the SYSTEM_USER() function.

SELECT CURRENT_USER(), USER(), SYSTEM_USER();

The output is:

+----------------+----------------+--------------+
| CURRENT_USER() | USER()         | SYSTEM_USER()|
+----------------+----------------+--------------+
| 'alice'@'%'    | 'bob'@'127.0.0.1' | 'root'       |
+----------------+----------------+--------------+

As you can see, the CURRENT_USER() function returns the user name and host name of the current user account, the USER() function returns the user name and host name of the current client connection, and the SYSTEM_USER() function returns the user name of the operating system user that runs the Mariadb server process.

Conclusion

The CURRENT_USER() function is a useful function in Mariadb that returns the user name and host name of the current user account. It can be used with other functions or statements to check the privileges or roles of the current user in a database. There are some other functions in Mariadb that are related to the CURRENT_USER() function, such as USER(), SESSION_USER(), and SYSTEM_USER(). These functions can be used to get different aspects of the user information in different contexts.