Row-level security in MemSQL is achieved by creating a view on a table with a special roles column.
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:
For a given role, the
ACCESS_ROLES field will be used to specify which roles have access to that row. The
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.
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.
<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>);
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 the default value of the
ACCESS_ROLES column to a comma (
“,”) for row-level security control to function correctly
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.
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.
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(…);
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_ROLES(), ACCESS_ROLES);