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 allow_row_level_security to true in dse.yaml, 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.

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

  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 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;
  2. 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)
  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
      ----------+----------+------------------------------------------------------------+------------
       dantest1 | dantest1 | 'Paolo TIRALONGO' rows IN <table cycling.cyclist_expenses> |     SELECT
    2. 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:

  1. Log in as dantest1:

    cqlsh -p password -u dantest1
  2. 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)

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com