Row-Level Security (RLS) Configuration

Row-Level Security (RLS) allows only those users who have the required permissions to access data by rows in a database. For example, RLS can be used to restrict each salesman to access only those rows in a table that are relevant to their sales details.

RLS restricts users' access to data at the database level instead of the application level. The database applies this access control to the table whenever a query runs, irrespective of which application needs the data.

Row-level security in SingleStore is achieved by creating a view on a table with a special roles column. RLS works for read-only, you cannot restrict write access on a table using this configuration.

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 a 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:

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.

In addition to applying RLS using an access-control list field, you can also restrict access based on the data in the rows of a table. For example, grant access based on an owner id or restrict access to only rows for a specific year, 2022.

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 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);

Limitations

RLS restricts the results of a query returned for each user based on their permissions but does not guarantee security against side-channel attacks. For example, a malicious user with access to an RLS view may connive another user with a different set of permissions on a table to extract information.

A carefully designed query can trigger exceptions on non-permitted data, timing dependence, EXPLAIN command options, etc. For example, using a query with  "1/(total_value - 200000)" on a Tenders table may result in a “divide by zero” error, thereby indicating there is at least one tender with this value.

There are mitigations that can be introduced with RLS, for example, turning on audit logging to check for suspicious queries.