SingleStore Managed Service

Granting row access to a role

To give a new role access to a column, append the role name followed by a comma to the column of a table containing role access information. If the role access called column is called ACCESS_ROLES, then the following query gives the role ROLE access to all rows whose ID column is 5.

UPDATE <table> SET ACCESS_ROLES=CONCAT(ACCESS_ROLES, "ROLE,") WHERE ID=5;

To give access to multiple roles R1, R2, R3 to rows whose ID column is 5:

UPDATE <table> SET ACCESS_ROLES=CONCAT(ACCESS_ROLES, "R1,R2,R3,") WHERE ID=5;

It is important that each new role added is suffixed with a comma.