Row Level Entitlements - BETA

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

When Data Entitlements is turned on, 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 more granular entitlement policies per table and user group, providing more flexible entitlement functionality. Row level entitlement policies allow groups of users to either see all rows, a specific set of rows, or no rows in the database tables.

Initially, when row level entitlements are turned on for a database, the group of users are unable to query any rows across any table in the database. Adding a row policy filters the data to only the rows you wish a group of users to access.

Limitations

qsql bypasses 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 honour 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:

Deployment wide enablement of data entitlements. This determines if any entitlements are enforced in kdb Insights Enterprise.
Deployment wide enablement of row level entitlements. This determines if row level entitlement enforcement of queries is available in kdb Insights Enterprise.
Enforce row level policies on a specific database. This ensures all queries to that database enforce any row level entitlement policies defined for each database where the row level policies are enabled.

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.
Add row policies to specific database tables. This allows users in the associated groups to see certain rows in certain tables.

1. Enable Data Entitlements

To enable row level entitlements, first you must enable data entitlements. Follow the steps in the data entitlements section.

Info

At this point, the deployment is configured as follows:

Data entitlements are enabled

This enforces query entitlements on all databases.

Note

Only the database owner and users with the insights.entitlements.admin role can query the data in the databases.

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

If not already created, you need to add a group entitlement for the database and add the groups of users you wish to entitle.

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:

{
  "id": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
  "groups": [
    "id": "ZZZZZZZZ-ZZZZ-ZZZZ-ZZZZ-ZZZZZZZZ", 
    "access": "RWX", 
    "policyMapping": {}
  ],  
  "internalId": "YYYYYYYY-YYYY-YYYY-YYYY-YYYYYYYY",
  "policiesEnabled": false,
  "policyTypes": {}
}

Info

At this point, the deployment is configured as follows:

Data entitlements are enabled in the deployment.
Row level entitlements are enabled in the deployment but are not being enforced on the database as no row level policies have not been enabled.
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 insights.entitlements.admin role can query the data in all of the databases.

3. Enable Row Level Entitlements

By default, kdb Insights Enterprise does not enable the row level entitlement feature. To enable row level entitlements within your deployment, you need to update the values file to include the row level entitlements option:

Update your deployment values:

Merging install values

The values below are a partial extract of your deployment values. These must be merged with your existing values file.

For more information, refer to Applying configuration changes.
```
global:
  entitlements:
    enabled: true
    level: 
      row: true
```
Run the upgrade command with $INSIGHTS_VERSION set to your target application version.
```
kxi install upgrade --filepath values.yaml --version $INSIGHTS_VERSION
```

Info

At this point, the deployment is configured as follows:

Data entitlements are enabled.
Row level entitlements are enabled in the deployment but not enforced on the database as row policies have not been enabled or defined for it.

Note

Only the database owner and users with the insights.entitlements.admin role can query the data in the databases.

4. Enforce row policies on a database

To limit users only quer 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:

ID=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
kxi entitlement policies-enable $ID assembly
kxi entitlement policy-mapping enable $ID assembly --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:

{
  "id": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
  "groups": [
    {
      "id": "ZZZZZZZZ-ZZZZ-ZZZZ-ZZZZ-ZZZZZZZZ",
      "access": "RWX",
      "policyMapping": {}
    }
  ],
  "internalId": "YYYYYYYY-YYYY-YYYY-YYYY-YYYYYYYY",
  "policiesEnabled": true,
  "policyTypes": {
    "row": true
    }
}

Warning

Once the row policy type has been enabled for this database, the users in groups to access the database are not able to see any rows in any tables of the database until policies are added.

Info

At this point, the deployment is configured as follows:

Data entitlements are enabled in the deployment.
Row level 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 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 insights.entitlements.admin role can query the data in the databases.

5. 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 an _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:

ID=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
kxi entitlement policy-mapping add $ID assembly --group groupid1 --policy-type row --asset myTable --policies _allRows

Note

The UUID of a group needs to be used for the group argument not the group name. This can be identified by calling kxi group list command.

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 UUID of ZZZZZZZZ-ZZZZ-ZZZZ-ZZZZ-ZZZZZZZZ:

kxi entitlement policy-mapping get $ID assembly

{
  "id": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
  "groups": [
    "id": "ZZZZZZZZ-ZZZZ-ZZZZ-ZZZZ-ZZZZZZZZ", 
    "access": "RWX", 
    "policyMapping": {
      "myTable": 
      {
        "row": ["_allRows"]
      }
    }    
  ],   
  "internalId": "YYYYYYYY-YYYY-YYYY-YYYY-YYYYYYYY",
  "policiesEnabled": true,
  "policyTypes":{
      "row": true
  }
}

Info

At this point, the following is true:

Data entitlements are enabled in the deployment.
Row level 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 enforced on the database.
_allRow row policy has been defined on the database for certain tables.

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 insights.entitlements.admin 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:

Add a policy to the package that contains the database table
Enforce the policy on a specific table for a group of users

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:

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

bash kxi pm pull mypackage
Add the da.q file to the package manifest.yaml file.
```
entrypoints:
  data-access: da.q
```
Add the policy to the da.q file with the convention // @policy.name.

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.
Ensure the policies are loaded into the processes that are part of the package:

Make sure the KXI_PACKAGES environment flag is included the deployment configuration and points to your package. This flag ensures the code is loaded into processes where entrypoints are defined when the package is deployed.

If this is not already part of the package, add it as follows:
```
kxi package add --to packagename deployment-config
echo "env: [{name: KXI_PACKAGES, value: packagename}]" >> packagename/deployment_config/deployment_config.yaml
```
Teardown the current version of the package:

bash kxi pm teardown
Push the updated package to the environment and redeploy:

bash kxi pm push --force mypackage kxi pm deploy mypackage

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:

ID=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
kxi entitlement policy-mapping add $ID assembly --group groupid1  --policy-type row --asset myTable --policies policy1,policy2

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

ID=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
kxi entitlement policy-mapping add $ID assembly --group groupid1 --policy-type row  --asset myTable --policies pricegreaterthan1,symFDLP

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

kxi entitlement policy-mapping get $ID assembly

{
  "id": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
  "groups": [
    "id": "ZZZZZZZZ-ZZZZ-ZZZZ-ZZZZ-ZZZZZZZZ", 
    "access": "RWX", 
    "policyMapping": {
      "myTable": 
      {
        "row": [
          "pol1pricegreaterthan1",
          "symFDLP"
        ]          
      }
    }
  ], 
  "internalId": "YYYYYYYY-YYYY-YYYY-YYYY-YYYYYYYY",
  "policiesEnabled": true, 
  "policyTypes": 
  {
      "row": true
  }
}

Info

At this point, the following is true:

Data entitlements are enabled in the deployment.
Row level 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 enforced on the database.
A row policy has been applied to a table on the database.

This enforces query entitlements on all databases.

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 insights.entitlements.admin role can query the data in the databases.

Replace row policies

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

ID=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
kxi entitlement policy-mapping update $ID assembly --group groupid1 --policy-type row --asset myTable --policies pricegreaterthan1

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

kxi entitlement policy-mapping get $ID assembly

{
  "id": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",    
  "groups": [
    "id": "groupid", 
    "access": "RWX", 
    "policyMapping": {
      "myTable": 
      {
        "row": ["pol1pricegreaterthan1"]          
      }
    }
  ], 
  "internalId": "YYYYYYYY-YYYY-YYYY-YYYY-YYYYYYYY",
  "policiesEnabled": true, 
  "policyTypes": 
  {
    "row": true
  }
}

6. Remove row policies

To remove all policies from a table:

ID=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
kxi entitlement policy-mapping delete $ID assembly --group groupid1 --asset myTable

Below is an example of running the policy-mapping get subcommand showing that myTable no longer has any policies:

kxi entitlement policy-mapping get $ID assembly

{
  "id": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX", 
  "groups": [
    {
      "id": "ZZZZZZZZ-ZZZZ-ZZZZ-ZZZZ-ZZZZZZZZ", 
      "access": "RWX", 
      "policyMapping": {}
    }
  ], 
  "internalId": "YYYYYYYY-YYYY-YYYY-YYYY-YYYYYYYY", 
  "policiesEnabled": true, 
  "policyTypes": 
  {
    "row": true
  }
}

Info

At this point, the following is true:

Data entitlements are enabled in the deployment.
Row level 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 enforced on the database.
No row policies are associated with any tables.

Note

Only the database owner and users with the insights.entitlements.admin role can query myTable.

7. Disable row level entitlements

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

ID=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
kxi entitlement policy-mapping disable $ID assembly --policy-types row
kxi entitlement policies-disable $ID assembly

Below is an example of the returned value from the policy-mapping get subcommand showing that the policyType row has been turned off and all policy types have been disabled:

{
  "id": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
    "groups": [
      "access": "RWX", 
      "policyMapping": {}
    ], 
  "internalId": "YYYYYYYY-YYYY-YYYY-YYYY-YYYYYYYY",
  "policiesEnabled": false,
  "policyTypes": {}
}

Info

At this point, the following is true:

Data entitlements are enabled in the deployment.
Row level 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 disabled.

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.
Database owner and users with the insights.entitlements.admin role can query all the data.