Setting up Row Level Access Control (RLAC)

Set up row-level access control on a table and grant permissions on rows to users.

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.

Note: 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.

Note: By default row level access control is disabled. To enable RLAC set to true, see Enabling DSE Unified Authentication. Fetching RLAC permissions can be resource intensive and impact performance; therefore RLAC has a separate cache. Tune the RLAC cache settings in to manage performance impact.

cassandra.yaml

The location of the cassandra.yaml file depends on the type of installation:

Package installations	/etc/dse/cassandra/cassandra.yaml
Tarball installations	`installation_location`/resources/cassandra/conf/cassandra.yaml

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

The following example uses the and cycling roles.

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

All list of all available values displays:

 cyclist_name
----------------------
         Marianne VOS
           Alex FRAME
    Steven KRUIKSWIJK
          Vera ADRIAN
     Michael MATTHEWS
 Anna VAN DER BREGGEN
            Joe WALLS
      Paolo TIRALONGO

(8 rows)

Assign RLAC:
1. Allow role dantest1 to access expenses entered only by Paolo TIRALONGO:
```
GRANT SELECT 
ON 'Paolo TIRALONGO' ROWS IN cycling.cyclist_expenses 
TO dantest1;
```
2. 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 | granted | restricted | grantable
----------+----------+------------------------------------------------------------+------------+---------+------------+-----------
 dantest1 | dantest1 | 'Paolo TIRALONGO' rows IN <table cycling.cyclist_expenses> |     SELECT |    True |      False |     False

(1 rows)

Check jane permissions:

LIST ALL PERMISSIONS OF jane;

In this example these are the permissions only for Dan:


 role | username | resource                                               | permission | granted | restricted | grantable
------+----------+--------------------------------------------------------+------------+---------+------------+-----------
 jane |     jane | 'Vera ADRIAN' rows IN <table cycling.cyclist_expenses> |     SELECT |    True |      False |     False

(1 rows)

When Dan logs in and runs queries, only rows he has permission to access display in the results set:

Run a query:

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)