Important

New database features are no longer connected to engine versions; features are now enabled independently during scheduled update windows, and “major” and “minor” releases no longer exist in Helios. Please visit the release notes to view the latest features available in your database clusters.

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