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 cassandra.yaml 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

  1. 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;
  2. 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.
  1. 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;
  2. 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)
  3. 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;
  4. Verify permissions:
    1. 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)
    2. 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:
  1. Log in as dantest1:
    cqlsh -p password -u dantest1
  2. 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)