SECURITY_LISTS_INTERSECT

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)

SQL

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

SECURITY_LISTS_INTERSECT

On this page

Syntax

Examples

Was this article helpful?

On this page

Was this article helpful?