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.

Higher level permissions (on keyspaces or tables) granted to a role override RLAC. The user still has access to all rows in the table due to the higher level permissions.
RLAC isn’t enforced for DSE Graph.

GRANT statements using an ON filter ROWS IN clause don’t produce errors, and LIST ALL PERMISSIONS returns the defined restriction. However, the permissions aren’t enforced.

Granting access on any rows in a table grants access to data in all graph keyspaces.

RLAC workflow

Configuring RLAC is a multi-step process:

Enable RLAC on the node.

By default, row level access control is disabled. To enable RLAC, set allow_row_level_security to true in dse.yaml. For more information, see Enable DSE Unified Authentication.
Create a partition key filter for RLAC.

The filter can be defined on any text column in the primary key. You must have MODIFY permission on the table to do this.
GRANT permissions on roles using the filter.

Different filters can be granted to any number of roles.
Tune the RLAC cache settings in cassandra.yaml if needed.

RLAC has a separate cache because it can be resource intensive to fetch RLAC permissions.

Restrict rows

Tables can have only one restriction. Running multiple RESTRICT commands on the same table replaces any existing restriction. Only the last RESTRICT command is retained.

To check a table’s existing restrictions, use DESCRIBE TABLE.

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.
Grant permissions with RLAC to roles, specifying the exact case-sensitive text string to match:
```
GRANT <permission> ON
'<filtering_string>' ROWS IN [<keyspace_name>.]<table_name>
TO <role_name>;
```
The filter string must match the filter string used with the RESTRICT command.

The permission is applied to the role immediately, even for active sessions.
Use the LIST command to verify the restriction on the role:
```
LIST ALL PERMISSIONS OF <role_name>;
```

Unrestrict rows

The partition key to filter on using GRANT can be unselected from the table:

UNRESTRICT ROWS ON [<keyspace_name>.]<table_name>
USING <partition_key>;

Unrestricting the column does not grant access to all columns within the table; it invalidates existing filters only. Users that are granted access with a filter are unable to access any rows within the table. To grant permissions to all rows, grant permission on the table to the role.

You can also unrestrict rows using REVOKE. Row permissions are stored based on the filtering string, so you must use the exact filtering string that you want to remove.

REVOKE <permission> on '<filtering_data>' ROWS IN <keyspace_name>.<table_name>;

Example

Create a table to use for this example:

CREATE TABLE IF NOT EXISTS cycling.cyclist_expenses (
  cyclist_name text,
  balance float STATIC,
  expense_id int,
  amount float,
  description text,
  paid boolean,
  PRIMARY KEY (cyclist_name, expense_id)
);

Insert some rows:

INSERT INTO cycling.cyclist_expenses (cyclist_name,expense_id,amount,description) VALUES ('Alex FRAME',5,40,'lunch');
INSERT INTO cycling.cyclist_expenses (cyclist_name,expense_id,amount,description) VALUES ('Alex FRAME',6,40,'dinner');
INSERT INTO cycling.cyclist_expenses (cyclist_name,expense_id,amount,description) VALUES ('Paolo TIRALONGO',11,10,'dinner');
INSERT INTO cycling.cyclist_expenses (cyclist_name,expense_id,amount,description) VALUES ('Paolo TIRALONGO',12,10,'dinner');
INSERT INTO cycling.cyclist_expenses (cyclist_name,expense_id,amount,description) VALUES ('Anna VAN DER BREGGEN',13,50,'dinner');
INSERT INTO cycling.cyclist_expenses (cyclist_name,expense_id,amount,description) VALUES ('Anna VAN DER BREGGEN',14,25,'lunch');
INSERT INTO cycling.cyclist_expenses (cyclist_name,expense_id,amount,description) VALUES ('Anna VAN DER BREGGEN',15,35,'dinner');
INSERT INTO cycling.cyclist_expenses (cyclist_name,expense_id,amount,description) VALUES ('Michael MATTHEWS',40,25,'lunch');
INSERT INTO cycling.cyclist_expenses (cyclist_name,expense_id,amount,description) VALUES ('Michael MATTHEWS',41,25,'lunch');
INSERT INTO cycling.cyclist_expenses (cyclist_name,expense_id,amount,description) VALUES ('Michael MATTHEWS',42,25,'lunch');

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)

RLAC workflow

Restrict rows

Unrestrict rows

Example

Was this helpful?

Give Feedback