Row-Level Security (RLS) Configuration
Row-level security in MemSQL 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);