Sign inTry Free

SECURITY_LISTS_INTERSECT

On this page

SECURITY_LISTS_INTERSECT() is a function that returns a list of a assigned roles for a user.

It can also be used to confirm if a user is assigned to roles within a table. SECURITY_LISTS_INTERSECT() requires 2 parameters. Both parameters can be strings or one parameter can use the CURRENT_SECURITY_ROLES() function. SECURITY_LISTS_INTERSECT() can be used in a SELECT or a WHERE clause.

Syntax

SELECT role_column, SECURITY_LISTS_INTERSECT(CURRENT_SECURITY_ROLES() or "role_name", role_column) AS intersect_column FROM table_name;

SELECT role_column FROM table_name WHERE SECURITY_LISTS_INTERSECT(CURRENT_SECURITY_ROLES() or "role_name", role_column) = 1;

Output

Column

List of assigned roles for a user.

Examples

SELECT role_column, SECURITY_LISTS_INTERSECT(CURRENT_SECURITY_ROLES() or "role_name", role_column) 
  AS intersect FROM table_name;
+---------------+-----------+
| role_column   | intersect |
+----------- ---+-----------+
| role_name_0   |      1    |
| role_name_1   |      1    |
| role_name_2   |      0    |
+---------------+-----------+
3 rows in set (0.131 sec)
SELECT role_column FROM table_name WHERE SECURITY_LISTS_INTERSECT(CURRENT_SECURITY_ROLES() 
  or "role_name", role_column) = 1;
+-------------+
| role_column |
+-------------+
| role_name_0 |
| role_name_1 | 
+-------------+
2 rows in set (0.003 sec)

Related Topics

Last modified: August 18, 2023

Was this article helpful?

On this page

Was this article helpful?