Skip to content

Row-Level Entitlements

This page describes how row-level entitlements work and how to configure them.

When Data Entitlements are enforced, row-level entitlement policies can be used to ensure users are only able to access rows in the database that they are entitled to. For example, only rows in a particular table where the sym value is FDLP.

Row-level entitlements are enabled per database and provide entitlement policies per table and user group; that is, they provide flexible and granular entitlement functionality. Row-level entitlement policies allow groups of users to see all rows, a specific set of rows, or no rows in the database tables.

When row-level entitlements are first enabled for a database, user groups who already have data entitlements will not be able to query any rows from any table until a row-level policy is configured. This policy must explicitly define which rows each user group is allowed to access.

Limitations

  • qsql bypasses row-level entitlements. You must disable qsql to enforce row-level entitlements. Refer to the disable qsql instructions. Alternatively, manage access to qsql using the Role in Keycloak so only trusted users can access all data through qsql.
  • UDAs must be developed following the best practice guide to honor row-level entitlements. Refer to details on how to develop a secure UDA.
  • Entitlements are applied on the query path only. Streaming data from a stream processor or over a web socket does not currently apply entitlements. Read Streaming to Views.

Entitlement workflow

Row-level entitlements are configured in the following stages:

  1. Global enforcement of data entitlements. This determines if any entitlements are enforced in kdb Insights Enterprise.

  2. Enabling of row-level policies on a specific database. This ensures that once row-level policies are enabled, all queries to that database are subject to the applicable row-level entitlement policies defined for it.

    Warning

    If data entitlements were used previously on a database and you choose to enable row-level entitlements, users in a group that already had a data entitlement and could query the data can no longer access any rows in any tables in the database. The add row policies step should be followed to add row policies to each table associated with the group to allow those users to access specific rows of data.

  3. Add row policies to specific database tables. This allows users in the associated groups to see certain rows in certain tables.

1. Enforce data entitlements

To enable row-level entitlements, first you must ensure data entitlements are enforced.

Follow the steps in the Data entitlements section to ensure data entitlements are enforced.

Info

At this point, the deployment is configured as follows:

  • Data entitlements are enforced

This means that query entitlements are enforced on all databases.

Note

Only the database owner and users with the Administrator role can query the data in the databases.

2. Allow users to query all the data in a database

Once data entitlements are configured, if not already created, you need to add a group entitlement for the database and add the groups of users you want to entitle. Refer to Data Entitlements Quickstart.

Below is an example of the returned value from the policy-mapping get subcommand showing that a group is associated with the entitlement but no policies are enabled:

╭──────────────────────────────────┬──────────┬─────────┬─────────-─┬──────────────────┬──────────┬──────────────────┬───────────────────╮
│ entity                           │ entity   │ owner   │ groups    │ users            │ policies │ policy types     │ policies          │
│                                  │ type     │         │           │                  │ enabled  │                  │                   │
├──────────────────────────────────┼──────────┼─────────┼────────-──┼──────────────────┼──────────┼──────────────────┼───────────────────┤
│ packagename                      │ database │ owner   │ groupname │ name     access  │ False    │ name    enabled  │ table    policy   │
│ -------------------------------- │          │         │           │ ------   ------- │          │ ------  -------- │ ------   -------- │
│ XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXX │          │         │           │ owner    ARWX    │          │                  │                   │
╰──────────────────────────────────┴──────────┴─────────┴───────-───┴──────────────────┴──────────┴──────────────────┴───────────────────╯

Info

At this point, the deployment is configured as follows:

  • Data entitlements are enforced in the deployment.
  • Data entitlements have been defined for the database to ensure only certain groups of users can query it.

Note

  • Users in groups entitled to query this database are able to query all the data.

  • The database owner and users with the Administrator role can query the data in all of the databases.

3. Enable row policies on a database

To limit users to only query the rows in specific tables they are entitled to, row policies must be enabled and then specific policies need to be added to ensure that users can only access the rows that they have been given polices for.

To enable row policies for a specific database:

PACKAGENAME=packagename
kxi entitlement policies-enable $PACKAGENAME database
kxi entitlement policy-mapping enable $PACKAGENAME database --policy-types row

Below is an example of the returned value from the policy-mapping get subcommand showing that the policyType row has been turned on:

╭──────────────────────────────────┬──────────┬─────────┬─────────-─┬──────────────────┬──────────┬──────────────────┬───────────────────╮
│ entity                           │ entity   │ owner   │ groups    │ users            │ policies │ policy types     │ policies          │
│                                  │ type     │         │           │                  │ enabled  │                  │                   │
├──────────────────────────────────┼──────────┼─────────┼─────────-─┼──────────────────┼──────────┼──────────────────┼───────────────────┤
│ packagename                      │ database │ owner   │ groupname │ name     access  │ True     │ name    enabled  │ table    policy   │
│ -------------------------------- │          │         │           │ ------   ------- │          │ ------  -------- │ ------   -------- │
│ XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXX │          │         │           │ owner    ARWX    │          │ row     True     │                   │
╰──────────────────────────────────┴──────────┴─────────┴────────-──┴──────────────────┴──────────┴──────────────────┴───────────────────╯

Warning

Once the row policy type has been enabled for this database, the users in groups previously entitled to access the database are not able to see any rows in any tables of the database until policies are added. However, when querying the table they can see the column details.

Info

At this point, the deployment is configured as follows:

  • Data entitlements are enforced in the deployment.
  • Entitlements have been defined for the database to ensure only certain groups of users can query it.
  • Row-level entitlements are being enforced on the database but no row policies have been defined for it.

Note

  • Users in groups entitled to query this database CANNOT access any data in any table because row policies have not yet been defined. However, when querying the table they can see the column details.
  • The database owner and users with the Administrator role can query the data in the databases.

4. Add row policies

Once the row policy has been enabled on a database, a group must include a row policy for each table they are entitled to query. Row policies allow users to see all or a subset of rows on specific tables, based on the policy associated with each table in the database for the group they are associated with. Follow the instructions below for the level of access you wish to apply.

Access all rows

If you wish users to have access to all rows for certain tables in the database you can add the built-in _allRows policy that tells kdb Insights Enterprise to allow the users in the group to query any rows in a specific table.

Adding an _allRows row policy to a specific table allows groups of users to access all rows in that table:

PACKAGENAME=packagename
kxi entitlement policy-mapping add $PACKAGENAME database --group groupname --policy-type row --asset myTable --policies _allRows

Below is an example of running the policy-mapping get subcommand showing the _allRows policy for myTable is associated with the users in the group with the name group1:

kxi entitlement policy-mapping get $PACKAGENAME database

╭──────────────────────────────────┬──────────┬─────────┬──────────┬───────────────────┬──────────┬──────────────────┬───────────────────╮
│ entity                           │ entity   │ owner   │ groups   │ users             │ policies │ policy types     │ policies          │
│                                  │ type     │         │          │                   │ enabled  │                  │                   │
├──────────────────────────────────┼──────────┼─────────┼──────────┼───────────────────┼──────────┼──────────────────┼───────────────────┤
│ packagename                      │ database │ owner   │ group1   │ name     access   │ True     │ name    enabled  │ table    policy   │
│ -------------------------------- │          │         │          │ ------   -------- │          │ ------  -------- │ ------   -------- │
│ XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXX │          │         │          │ owner    ARWX     │          │ row     True     │ myTable  _allRows │
╰──────────────────────────────────┴──────────┴─────────┴──────────┴───────────────────┴──────────┴──────────────────┴───────────────────╯

Info

At this point, the following is true:

  • Data entitlements are enforced in the deployment.
  • Data entitlements have been defined for the database to ensure only certain groups of users can query it.
  • Row-level entitlements are enforced on the database.
  • _allRow row policy has been associated with certain tables in the database for a specific group.

Note

  • Users in groups entitled to query this database can only query data in tables with a row policy defined.
  • The database owner and users with the Administrator role can query all the data in the databases.

Add an _allRows row policy for each table you wish the users in the group be able to query all rows.

Access a subset of rows

If users should only be able to query a subset of the rows in certain tables, this requires you to define a row policy to filter only those rows a group of users are entitled to. For example, users might only be allowed to query rows where the sym column is set to "FDLP".

To do this, you must:

  1. Add a policy to the package that filters data in a specific table.
  2. Enforce the policy on a specific table for a group of users.

Note

If a table has multiple policies associated with it, then a user will be able to query any row that satifisies at least one of the policies i.e. the union of all the policies.

Define a row policy

To define a row policy in a package, create a q file that contains the policy and then add that q file as an entrypoint to the data-access component.

The following steps show how you define policies in a da.q file, which is part of your package:

  1. If you have not done so already, pull the package from the environment. 

    kxi pm pull packagename

  2. Add a text file to the package folder and name it da.q. This file will include the policies you will apply to the tables.

  3. Add a reference to the da.q file to the package manifest.yaml file.

    entrypoints:
  data-access: da.q

  4. Add the policy to the da.q file with the convention // @policy.name.

    Note

    Refer to the guides on the like keyword and regular expressions for guidance on creating policies.

    Policy examples:

    • Limiting users to records where the price1 column is greater than 1

      // @policy.name("pricegreaterthan1")
pricegreaterthan1:{[price]price>1}

    • Limiting users to records were a sym column is "FDLP"

      // @policy.name("symFDLP")
symFDLP:{[sym]sym="FDLP"}

    • Limiting users to a value in the sym column that starts with "ab"

      // @policy.name("symab")
symab:{[sym]sym like"ab*"}

      Note

      These are equivalent ways of defining the same filter based on your preference:

      symab:{[sym]sym like"ab*"}    // As a function
symab:"sym like\"ab*\""       // As a string
symab:enlist(like;`sym;"ab*") // As a functional where clause

    Note

    Using the convention // @policy.name ensures kdb Insights Enterprise can check that the policy exists when you push or deploy a package. If an enabled policy is not present in the package, the KXI CLI warns you on package push or deploy, as no users in the group associated with the missing policy can access rows in the table.

    Warning

    The row policy needs to only reference columns that are part of the table this policy is associated with.

  5. Teardown the current version of the package:

    kxi pm teardown packagename

  6. Push the updated package to the environment and redeploy:

    kxi pm push --force packagename 
kxi pm deploy packagename

Apply a row policy

To apply a row policy to a specific database table and group of users, use kxi entitlement policy-mapping add as follows:

PACKAGENAME=packagename
kxi entitlement policy-mapping add $PACKAGENAME database --group groupname  --policy-type row --asset myTable --policies policy1,policy2

The example below shows how to add the pricegreaterthan1 and symFDLP policies to myTable for group group1:

PACKAGENAME=packagename
kxi entitlement policy-mapping add $PACKAGENAME database --group group1 --policy-type row  --asset myTable --policies pricegreaterthan1,symFDLP

Calling the policy-mapping get subcommand now shows the two policies have been added for myTable to group1:

kxi entitlement policy-mapping get $PACKAGENAME database

╭──────────────────────────────────┬──────────┬─────────┬────────┬──────────────────┬──────────┬──────────────────┬────────────────────────────────────────╮
│ entity                           │ entity   │ owner   │ groups │ users            │ policies │ policy types     │ policies                               │
│                                  │ type     │         │        │                  │ enabled  │                  │                                        │
├──────────────────────────────────┼──────────┼─────────┼────────┼──────────────────┼──────────┼──────────────────┼────────────────────────────────────────┤
│ packagename                      │ database │ owner   │ group1 │ name     access  │ True     │ name    enabled  │ table    policy                        │
│ -------------------------------- │          │         │        │ ------   ------- │          │ ------  -------- │ ------   ----------------------------- │
│ XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXX │          │         │        │ owner    ARWX    │          │ row     True     │ myTable  pol1pricegreaterthan1,symFDLP │
╰──────────────────────────────────┴──────────┴─────────┴────────┴──────────────────┴──────────┴──────────────────┴────────────────────────────────────────╯

Info

At this point, the following is true:

  • Data entitlements are enabled in the deployment.
  • Entitlements have been defined for the database to ensure only certain groups of users can query it.
  • Row-level entitlements are enabled on the database.
  • A row policy has been applied to a table on the database.

Note

  • Users in groups associated with a policy can only query a subset of the data from certain tables.
  • The database owner and users with the Administrator role can query the data in the databases.

Replace row policies

You can replace the policies with a new list of policies by calling kxi entitlement policy-mapping update as follows:

PACKAGENAME=packagename
kxi entitlement policy-mapping update $PACKAGENAME database --group groupname --policy-type row --asset myTable --policies pricegreaterthan1

The example below shows how to update the policies for myTable and group group1 to remove symFDLP:

PACKAGENAME=packagename
kxi entitlement policy-mapping update $PACKAGENAME database --group group1 --policy-type row  --asset myTable --policies pricegreaterthan1

Calling the policy-mapping get subcommand now shows that only the pricegreaterthan1 policy is present for myTable and group1:

╭──────────────────────────────────┬──────────┬─────────┬────────┬─────────────────┬──────────┬──────────────────┬────────────────────────────────╮
│ entity                           │ entity   │ owner   │ groups │ users           │ policies │ policy types     │ policies                       │
│                                  │ type     │         │        │                 │ enabled  │                  │                                │
├──────────────────────────────────┼──────────┼─────────┼────────┼─────────────────┼──────────┼──────────────────┼────────────────────────────────┤
│ insights-demo                    │ database │ owner   │ group1 │ name     access │ True     │ name    enabled  │ table    policy                │
│ -------------------------------- │          │         │        │ ------   ------ │          │ ------  -------- │ ------   --------------------  │
│ XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXX │          │         │        │ owner    ARWX   │          │ row     True     │ myTable  pol1pricegreaterthan1 │
╰──────────────────────────────────┴──────────┴─────────┴────────┴─────────────────┴──────────┴──────────────────┴────────────────────────────────╯

5. Remove row policies

To remove all policies from a table:

PACKAGENAME=packagename
kxi entitlement policy-mapping delete $PACKAGENAME database --group groupname --asset myTable

The example below shows how to remove all policies for myTable and group group1:

PACKAGENAME=packagename
kxi entitlement policy-mapping delete $PACKAGENAME database --group group1 --asset myTable

Calling the policy-mapping get subcommand shows that myTable no longer has any policies:

╭──────────────────────────────────┬──────────┬─────────┬────────┬───────────────────┬──────────┬──────────────────┬──────────────────────╮
│ entity                           │ entity   │ owner   │ groups │ users             │ policies │ policy types     │ policies             │
│                                  │ type     │         │        │                   │ enabled  │                  │                      │
├──────────────────────────────────┼──────────┼─────────┼────────┼───────────────────┼──────────┼──────────────────┼──────────────────────┤
│ package                          │ database │ owner   │ group1 │ name     access   │ True     │ name    enabled  │ table    policy      │
│ -------------------------------- │          │         │        │ ------   -------- │          │ ------  -------- │ ------   ----------  │
│ XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXX │          │         │        │ user1    RWX      │          │ row     True     │                      │
╰──────────────────────────────────┴──────────┴─────────┴────────┴───────────────────┴──────────┴──────────────────┴──────────────────────╯

Info

At this point, the following is true:

  • Data entitlements are enabled in the deployment.
  • Data entitlements have been defined for the database to ensure only certain groups of users can query it.
  • Row-level entitlements are enforced on the database.
  • No row policies are associated with any tables.

Note

Only the database owner and users with the Administrator role can query myTable.

6. Disable qsql

The qsql API bypasses row-level database entitlements and enables access to data in any database regardless of any row-level entitlements that have been configured. Therefore, if you are using entitlements, you should ensure qsql is disabled on all query resources:

  • Exploratory query path: Query environments allow adhoc SQL and qSQL queries. These are disabled by default on all new installs. Refer to Query environment configuration to see how to disable them as part of an upgrade.
  • Main query path: By default, qsql is disabled on the main query resources.

7. Disable row-level entitlements

To disable row-level entitlements for a specific database run the following:

PACKAGENAME=packagename
kxi entitlement policy-mapping disable $PACKAGENAME database --policy-types row
kxi entitlement policies-disable $PACKAGENAME database

Below is an example of the returned value from kxi entitlement list showing that the policyType row has been turned off and all policy types have been disabled:

╭──────────────────────────────────┬──────────┬─────────┬─────────-─┬───────────────────┬──────────┬─────────────────┬──────────────────╮
│ entity                           │ entity   │ owner   │ groups    │ users             │ policies │ policy types    │ policies         │
│                                  │ type     │         │           │                   │ enabled  │                 │                  │
├──────────────────────────────────┼──────────┼─────────┼──────────-┼───────────────────┼──────────┼─────────────────┼──────────────────┤
│ package                          │ database │ owner   │ groupname │ name     access   │ False    │ name    enabled │ table    policy  │
│ -------------------------------- │          │         │           │ ------   -------- │          │ ------  ------- │ ------   ------  │
│ XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXX │          │         │           │ user1    RWX      │          │                 │                  │
╰──────────────────────────────────┴──────────┴─────────┴──────-────┴───────────────────┴──────────┴─────────────────┴──────────────────╯

Info

At this point, the following is true:

  • Data entitlements are enabled in the deployment.
  • Data entitlements have been defined for the database to ensure only certain groups of users can query it.
  • Row-level entitlements are disabled for the database.

Note

  • Access for entitled groups falls back to database entitlements, where all rows are visible to users in entitled groups and no tables or rows are visible to the non-entitled groups.
  • Only the database owner and users with the Administrator role can query all the data.