SECURITY_LISTS_INTERSECT
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