---

Row-Level Security (RLS) Configuration

Row-level security in SingleStore DB is achieved by creating a view on a table with a special roles column.

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.

Creating a new table with row-level security

To create a new table with a roles column use an appropriate version of the following command:

CREATE TABLE <table>(ACCESS_ROLES VARBINARY(<SIZE>) DEFAULT ",", …);

It is important that the default value for ACCESS_ROLES be a comma: "," for row-level security to work correctly.

The <SIZE> of the ACCESS_ROLES column should be set to match the expected number of roles. Note that the size can always be changed using:

ALTER TABLE <table> MODIFY COLUMN ACCESS_ROLES VARBINARY(<NEWSIZE>);

Updating an existing table with row-level security

To update an existing table to work with row-level security, use the following command:

ALTER TABLE <table> ADD COLUMN ACCESS_ROLES VARBINARY(<SIZE>) DEFAULT ",";

Once again it is important to set the default value of the ACCESS_ROLES column to a comma (",") for row-level security control to function correctly.

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.

Revoking row access from a role

To revoke access from a role, R, on rows whose ID column in a table is 5:

UPDATE <table> SET ACCESS_ROLES=REPLACE(ACCESS_ROLES, ',R,', ',') WHERE ID=5;

It is important for the role name being revoked be surrounded by commas in the REPLACE function.

Updating and inserting rows in a row-level security table

When updating rows in a row-level security table, it is important to not update the roles access column.

When inserting into a row-level security table, it is important that new values in the roles access column have a default value of a comma. If the table is configured correctly, a comma will be the default value of the roles access column. The following query inserts values into a table with 4 rows (ACCESS_ROLES, A, B, C), the first of which is the roles access column:

INSERT INTO <table> (A, B, C) VALUES(…);

Creating Views on row-level security Tables

To restrict access on a row-level security table whose role access column is called ACCESS_ROLES, the following view must be created on the table:

CREATE VIEW <view_name> AS SELECT COLUMNS FROM <table> WHERE SECURITY_LISTS_INTERSECT(CURRENT_SECURITY_ROLES(), ACCESS_ROLES);