Menu

MySQL CURRENT_ROLE() Function

Updated on

The MySQL CURRENT_ROLE() function returns a string representing the currently active roles for the current session, with multiple roles separated by commas.

CURRENT_ROLE() Syntax

Here is the syntax of the MySQL CURRENT_ROLE() function:

CURRENT_ROLE()

Parameters

The MySQL CURRENT_ROLE() function does not require any parameters.

Return value

The CURRENT_ROLE() function returns a UTF8 string containing the currently active role for the current session.

If the current session user does not have any role, this function will returns a string: NONE.

CURRENT_ROLE() Examples

The following example shows how to use the current user information using the CURRENT_ROLE() function.

As an administrator, create a local user. MySQL 8.0.18 and later can generate a random password and return it once:

CREATE USER 'testuser'@'localhost' IDENTIFIED BY RANDOM PASSWORD;

For MySQL releases earlier than 8.0.18, create the account with a unique password supplied securely instead; those releases do not support IDENTIFIED BY RANDOM PASSWORD.

Save the generated password securely. Log in to the MySQL server with the user you just created; the client prompts for the password:

mysql --user=testuser --password

Use the following statement to view the current role:

SELECT CURRENT_ROLE();

+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+

Here, the function returns NONE, it means that there are no roles in the current session.

In a separate administrator session, create two roles, grant them to the local testuser account, and make both roles active by default:

CREATE ROLE test_role1, test_role2;
GRANT 'test_role1', 'test_role2' TO 'testuser'@'localhost';
SET DEFAULT ROLE ALL TO 'testuser'@'localhost';

Reconnect as testuser and view its active roles:

SELECT CURRENT_ROLE();

+-----------------------------------+
| CURRENT_ROLE()                    |
+-----------------------------------+
| `test_role1`@`%`,`test_role2`@`%` |
+-----------------------------------+

Here, the result we expect is returned. The two roles are separated with a comma.

Let’s modify the roles of the following current session user to be test_role1:

SET ROLE 'test_role1';

To view roles for the current session user:

SELECT CURRENT_ROLE();

+------------------+
| CURRENT_ROLE()   |
+------------------+
| `test_role1`@`%` |
+------------------+

Only one character is returned here. Because we used the statement SET ROLE 'test_role1'; to set the role of the current session to test_role1.