Setting up Row Level Access Control (RLAC)

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.

When 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 allow_row_level_security to true. See Enabling DSE Unified Authentication.

Fetching RLAC permissions can be resource intensive and can impact performance; therefore RLAC has a separate cache. Tune the RLAC cache settings in cassandra.yaml to manage performance impact.

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 for which 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 cyclist_expenses 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:

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 | granted | restricted | grantable
----------+----------+------------------------------------------------------------+------------+---------+------------+-----------
 dantest1 | dantest1 | 'Paolo TIRALONGO' rows IN <table cycling.cyclist_expenses> |     SELECT |    True |      False |     False

(1 rows)

Check Jane’s permissions:

LIST ALL PERMISSIONS OF jane;

In this example these are the permissions only for Jane:

 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 are returned 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)

If you see different results, then the roles and results might be cached.

Setting up Row Level Access Control (RLAC)

Procedure

Example

Was this helpful?

Give Feedback