How the CURRENT_ROLE() function works in Mariadb?

The MariaDB CURRENT_ROLE() function is used to return the active roles for the current user session. This function is particularly useful for managing access control and permissions within the database.

Syntax

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

CURRENT_ROLE()

This function does not take any arguments and returns a comma-separated list of active roles for the current user session.

Examples

Retrieve Current Active Roles

To display the current active roles for the user session, you would use the CURRENT_ROLE() function like this:

SELECT CURRENT_ROLE();

The output will be the active roles:

'role1,role2'

Check for a Specific Role

If you want to check if a specific role is active in the current session, you can do so using the CURRENT_ROLE() function:

SELECT FIND_IN_SET('admin', CURRENT_ROLE()) > 0 AS Is_Admin;

The output will indicate whether the ‘admin’ role is active:

Using `CURRENT_ROLE()` in a Stored Procedure

You can use the CURRENT_ROLE() function within a stored procedure to execute specific commands based on the current role:

DELIMITER //
CREATE PROCEDURE ShowDataBasedOnRole()
BEGIN
  IF FIND_IN_SET('admin', CURRENT_ROLE()) THEN
    SELECT * FROM sensitive_data;
  ELSE
    SELECT * FROM general_data;
  END IF;
END //
DELIMITER ;

Conditional Role-Based Query

To conditionally query data based on the current role, you can use CURRENT_ROLE() in a WHERE clause:

SELECT * FROM documents WHERE FIND_IN_SET('editor', CURRENT_ROLE());

Role-Based Dynamic SQL

For dynamic SQL execution based on roles, you can use CURRENT_ROLE() to construct the SQL statement:

SET @roleCheck = FIND_IN_SET('manager', CURRENT_ROLE()) > 0;
SET @query = IF(@roleCheck, 'SELECT * FROM management_view', 'SELECT * FROM employee_view');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

MariaDB SET ROLE is used to activate a specific role within a session.
MariaDB DEFAULT_ROLE function is used to return the default roles for a user.
MariaDB ROLE_GRANTS function lists the privileges granted to a role.

Conclusion

The CURRENT_ROLE() function in MariaDB is a powerful tool for role-based access control within the database. It allows for dynamic and flexible permission management, enabling database administrators to ensure that users have the appropriate level of access to the database resources. By understanding and utilizing this function, you can enhance the security and efficiency of your database operations.