SingleStore Managed Service

Setting up a table for row-level security

For a table to be used with row-level security, it must have a VARBINARY column where row entry in the column contains a comma separated list of roles which have access to that row. There are special formatting constraints for the roles columns which are discussed below.

Consider the following table containing 4 rows:

ACCESS_ROLES

DATA_1

DATA_2

DATA_3

,ROLE_A,ROLE_B,

xxxxxx

xxxxxx

xxxxxx

,ROLE_A,ROLE_C,

xxxxxx

xxxxxx

xxxxxx

,ROLE_D

xxxxxx

xxxxxx

xxxxxx

,

xxxxxx

xxxxxx

xxxxxx

For a given role, the ACCESS_ROLES field will be used to specify which roles have access to that row. The DATA_1 through DATA_3 columns are data stored in a table.

It is important that each role name in ACCESS_ROLES be surrounded by a preceding and trailing comma.