Setting row-level permissions
Row-level access control (RLAC) provides authorization to data within a table by matching a filter applied to a text-based partition key. RLAC provides more granular security for tables so that only authorized users are able to view or modify subsets of the data.
If the role has a higher level of permission, such as to the keyspace or the table, the higher level of permission allows the user access to all rows in the table. |
Configuring RLAC is a multi-step process:
-
Select partition key: Apply to any text column of the primary key; requires MODIFY permission on the table.
-
Grant access using a filter: Different filters can be granted to any number of roles.
By default row level access control is disabled.
To enable RLAC set |
Procedure
-
Select a column on the table that you want to configure permissions. Set a UTF-8 partition key column. Only one filtering column per table is allowed:
RESTRICT ROWS ON [<keyspace_name>.]<table_name> USING <primary_key_text_column>;
For example:
CREATE TABLE test.rlac(pk1 text, pk2 text, ck1 text, ck2 text, PRIMARY KEY ((pk1, pk2), ck1, ck2));
RESTRICT ROWS ON test.rlac using ck1;
Existing filters (if any) now filter on this column. The DESCRIBE TABLE command shows the row restriction.
DESCRIBE TABLE <table_name>;
-
Assign RLAC to the roles:
GRANT <permission> ON '<filtering_string>' ROWS IN [<keyspace_name>.]<table_name> TO <role_name>;
Where the <filtering_string> is the case-sensitive text string to exactly match.
The permission is applied to the role immediately, even for active sessions. Use the LIST command to display the settings:
LIST ALL PERMISSIONS OF <role_name>;
Example
-
Select the
cyclist_name
column as the filtering column:RESTRICT ROWS ON cycling.cyclist_expenses USING cyclist_name ;
Show the changes to the table:
DESC cycling.cyclist_expenses;
The restrict statement appears at the end:
CREATE TABLE cycling.cyclist_expenses ( cyclist_name text, expense_id int, amount float, balance float static, description text, paid boolean, PRIMARY KEY (cyclist_name, expense_id) ) WITH CLUSTERING ORDER BY (expense_id ASC) AND bloom_filter_fp_chance = 0.01 AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'} AND comment = '' AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'} AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'} AND crc_check_chance = 1.0 AND dclocal_read_repair_chance = 0.1 AND default_time_to_live = 0 AND gc_grace_seconds = 864000 AND max_index_interval = 2048 AND memtable_flush_period_in_ms = 0 AND min_index_interval = 128 AND read_repair_chance = 0.0 AND speculative_retry = '99PERCENTILE'; RESTRICT ROWS ON cycling.cyclist_expenses USING cyclist_name;
-
Get a list of the primary keys to use in the filter:
SELECT DISTINCT cyclist_name FROM cycling.cyclist_expenses ;
cyclist_name ---------------------- Marianne VOS Alex FRAME Steven KRUIKSWIJK Vera ADRIAN Michael MATTHEWS Anna VAN DER BREGGEN Paolo TIRALONGO (7 rows)
-
Assign RLAC:
-
Allow role dantest1 to access expenses entered only by Paolo TIRALONGO:
GRANT SELECT ON 'Paolo TIRALONGO' ROWS IN cycling.cyclist_expenses TO dantest1;
-
Allow role jane to access only Vera ADRIAN:
GRANT SELECT ON 'Vera ADRIAN' ROWS IN cycling.cyclist_expenses TO jane;
-
-
Verify permissions:
-
Check dantest1 permissions:
LIST ALL PERMISSIONS OF dantest1;
In this example these are the permissions only for Dan:
role | username | resource | permission ----------+----------+------------------------------------------------------------+------------ dantest1 | dantest1 | 'Paolo TIRALONGO' rows IN <table cycling.cyclist_expenses> | SELECT
-
Check jane permissions:
LIST ALL PERMISSIONS OF jane;
In this example these are the permissions only for Dan:
role | username | resource | permission ----------+----------+------------------------------------------------------------+------------ dantest1 | dantest1 | 'Vera ADRIAN' rows IN <table cycling.cyclist_expenses> | SELECT
-
When Dan logs in and runs queries, only rows he has permission to access display in the results set:
-
Log in as dantest1:
cqlsh -p password -u dantest1
-
Run a query:
dantest1@cqlsh> SELECT * FROM cycling.cyclist_expenses ;
Only the records that exactly match the filter are displayed:
cyclist_name | expense_id | balance | amount | description | paid -----------------+------------+---------+--------+-------------+------ Paolo TIRALONGO | 11 | null | 10 | dinner | null Paolo TIRALONGO | 12 | null | 10 | dinner | null Paolo TIRALONGO | 24 | null | 10 | lunch | null Paolo TIRALONGO | 25 | null | 11 | dinner | null Paolo TIRALONGO | 26 | null | 12 | lunch | null Paolo TIRALONGO | 27 | null | 13 | lunch | null Paolo TIRALONGO | 28 | null | 14 | lunch | null Paolo TIRALONGO | 29 | null | 15 | dinner | null Paolo TIRALONGO | 30 | null | 16 | lunch | null Paolo TIRALONGO | 31 | null | 17 | dinner | null Paolo TIRALONGO | 32 | null | 18 | breakfast | null (11 rows)