Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

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?