# Using the Data Security API

Automating row-level security using Sisense REST API

# Abstract

This article will guide you through the process of automating your row-level security management using the Sisense REST API.

# When To Use Data Security API

Often, managing data security rules can be done adequately and efficiently through the Sisense Admin page manually. However, with an increased scale or complexity of a Sisense deployment, this task can become time consuming and prone to human error, justifying the investment in automation. Such cases include:

When there are too many users or groups to manage
When users or groups are added and removed frequently
When users are added automatically and should have immediate access to dashboards and data
When users' permissions need to change frequently

This is especially true when several of the above factors are combined.

# How Row-level Security Works

Data Security in Sisense is defined as a list of rules associated to a specific, single Elasticube. These rules are stored in the Sisense Application Database and are evoked whenever a query is run on the associated Elasticube, narrowing down the query's result-set to only the allowed data, before the results are sent to the client.

In essence, Data Security applies additional background constraints that are generated on the Sisense Server (and not passed via the HTTP request) to a query's WHERE clause so that any and all associated data is filtered, based on user context only and with no consideration of where the query is sent from, resulting in rules that cannot be overridden.

A data security rule is comprised of three distinct entities:

A Sisense User or Group (aka "the party")
A column (field/dimension) along with the Elasticube and Table it belongs to
One or more values (members) of the column to which the party is allowed access

For each Elasticube, once a user has any security rules applied to them, Sisense will limit query results to data associated with the specified values in the rule across all linked tables in the schema.

More Information

For further information, please see the Row Level Security documentation (opens new window)

# Designing Your Data Security Strategy

There are several decisions that must be made while designing a Data Security approach and automation.

Covered below are the decisions related to the technical aspect of the task, and not the data or business aspect (such as which dimension should data security apply to?).

# Rule Application Scope

Should rules be applied to individual users or groups?

Deciding whether to apply rules to individual users or groups depends mostly on how diverse the settings are for each user. For example:

A small company/department with few employees, using Sisense internally, might need to give access to different areas of their Elasticube to each user. As there are few rules to set, and they diverge significantly, it would be acceptable to assign rules to individual users.
A SaaS company using Sisense as an OEM with multi-tenant data in each Elasticube, using Data Security to segregate tenants, would likely have multiple users per tenant and thus would be better off applying rules to groups representing the tenants.

# Default Access

Should users/groups see all or none of the data by default?

In most cases, it is preferable to set the default rule to "forbid all", so that a user that isn't assigned any rules or groups with rules will not be able to see any data. While this approach has the disadvantage of users being unable to see data should their rule assignment go wrong, it is the safer approach that avoids exposure of data in the very same case. When dealing with sensitive data, such as PII and PHI, always choose this approach.

Sometimes, when dealing with non-sensitive data and when limitations need to apply only to a small portion of users, it would be beneficial to set the default to "allow all", meaning that any user can see the entire data set unless a limitation was explicitly applied to them or one of the groups they belong to. This approach can be useful when, for example, most of a company's employees should have access to the same data, except for a handful of contractors or external users.

# Automation Timing

When should Data Security automation scripts run?

Depending on the Data Security scope chosen, timing the application of Data Security rules changes significantly:

When applied to individual users, data security rules should normally apply as soon as a User entity is created. This can be done by running Data Security automation as part of user provisioning, which is normally automated as well and often triggered by a user being added to a database, an API call, or a manual trigger of some sort.
In some cases, when using a "forbid all" default strategy and when it is acceptable for users to not have access to data immediately after creation, this process could be relegated to a scheduled batch job. This is rarely recommended or required.
When applied to groups, data security should be applied when the group is created, and based on the method of group creation. The timing is less crucial in the case of groups that are created empty and are not assigned to users immediately, in which case Data Security needs to be defined only before the first users are assigned to the group.
For Data Security assigned to groups, the critical component is shifted from the Data Security automation to the Group assignment automation - users must be assigned to groups at the right time to ensure they have access to their data.
In both cases, changes to Data Security must come into consideration. Should a user's or group's data access permission change, such as when an employee changes position in the company or a tenant buys out another tenant, automation must re-run in some form to reflect these changes in the Data Security rules. For this reason it is recommended to ensure Data Security automation scripts are either idempotent or aware of current vs. desired state.

# Using The Data Security API

Datamodel Types

While most of this tutorial applies to all types of Datamodels, please note that the endpoints and payloads differ slightly for extract type Datamodels ("Elasticubes") and live type Datamodels.

# Authentication

See parent article - Using the REST API

# For Extract Datamodels

# Endpoints

All of these endpoints are in the v0.9 REST API version.

Method	Path	Purpose
`GET`	`/api/elasticubes/{server}/{elasticube}/datasecurity`	Get all rules for a cube
`GET`	`/api/elasticubes/{server}/{elasticube}/datasecurity/{table}/{column}`	Get all rules for a dimension of a cube
`GET`	`/api/elasticubes/{server}/{elasticube}/{user}/datasecurity`	Get all rules for a cube & user
`POST`	`/api/elasticubes/datasecurity`	Create rules (bulk - multiple cubes, users and values)
`POST`	`/api/elasticubes/{server}/{elasticube}/datasecurity`	Create rules for a cube (bulk - multiple users/values)
`PUT`	`/api/elasticubes/datasecurity/{id}`	Update a rule by ID
`DELETE`	`/api/elasticubes/{server}/{elasticube}/datasecurity`	Delete all rules for a cube's dimension
`DELETE`	`/api/elasticubes/datasecurity/{id}`	Delete a specific rule

# Rule Schema

This is a generic description of a "rule" object as it is retrieved and stored by the Data Security API. While the exact properties may change depending on the endpoint and action performed, understanding which attributes and entities are part of Data Security rules will help you use these APIs more efficiently.

Object structure:

{
	"_id": "string",
    "table": "string",
    "column": "string",
    "datatype": "string",
    "members": [
      "string"
    ],
    "allMembers": false,
    "exclusionary": false,
    "shares": [
      {
        "party": "string",
        "type": "string"
      }
    ]
}

Main object fields:

Name	Type	Required	Description
`_id`	`string`	Sometimes¹	Unique identifier
`server`	`string`	Sometimes²	Address of the server hosting the Elasticube
`elasticube`	`string`	Sometimes²	Elasticube name
`table`	`string`	Yes	Table name
`column`	`string`	Yes	Column (dimension) name
`datatype`	`string`	Yes	Column data type (either `text` or `numeric`)⁴
`members`	`string[]`	Yes³	List of values the parties are allowed to access
`allMembers`	`boolean`	Yes³	Should rule apply to all of a column's possible values
`exclusionary`	`boolean`	No	When set to `true`, the rule is "reversed" & the values will be hidden from the users. Defaults to `false`
`shares`	`object[]`	Yes	List of parties (Users & Groups) to whom the rule applies (See Details)
`shares.*.party`	`string`	No⁵	UUID/OID of the User or Group entity
`shares.*.type`	`string`	Yes	Party type (either `user`, `group`, or `default`)

Notes:

¹ This field will be returned with any GET request; It will be automatically assigned by POST requests and should not be specified; It is required for PUT and some DELETE requests.
² Some API endpoints don't require the server and elasticube properties to be specified in the payload, as they are present in the API path.
³ The properties members and allMembers are mutually exclusive - only one of them is required. When allMembers is specified, members will be ignored. Note that the field allMembers is required, and when not in use the value needs to be null and not false.
⁴Date dimensions are not supported for Data Security rules
⁵ The party attribute is not required when type=default and is required when type=user or type=group.
^All All string type fields are case sensitive
^{Elasticube Sets} When applying Data Security to an elasticube set, use the set's name as the elasticube attribute and the term set as the server attribute. This applies both to properties of the payload and to parts of the API URL path or query parameters.

# For Live Datamodels

# Endpoints

All of these endpoints are in the v1.0 REST API version.

Method	Path	Purpose
`GET`	`/api/v1/elasticubes/live/{title}/datasecurity`	Returns the data security rules set up for a live Datamodel
`POST`	`/api/v1/elasticubes/live/{title}/datasecurity`	Creates data security rules for a live Datamodel
`DELETE`	`/api/v1/elasticubes/live/datasecurity/{dataSecurityId}`	Removes a data security rule by ID
`DELETE`	`/api/v1/elasticubes/live/{title}/datasecurity/{table}/{column}`	Removes the data security rules for a column of a live Datamodel

# Rule Schema

Object structure:

{
    "_id": "string",
    "fullname": "string",
    "table": "string",
    "column": "string",
    "datatype": "string",
    "members": [
      "string"
    ],
    "allMembers": false,
    "live": true,
    "shares": [
      {
        "partyId": "string",
        "type": "string"
      }
    ]
}

Main object fields:

Name	Type	Required	Description
`_id`	`string`	Sometimes¹	Unique identifier
`fullname`	`string`	Yes	Datamodel name in the format: `live:my-datamodel` (always has a `live:` prefix)
`table`	`string`	Yes	Table name
`column`	`string`	Yes	Column (dimension) name
`datatype`	`string`	Yes	Column data type (either `text` or `numeric`)⁴
`members`	`string[]`	Yes³	List of values the parties are allowed to access
`allMembers`	`boolean`	Yes³	Should rule apply to all of a column's possible values
`live`	`boolean`	Yes	Must always be `true`.
`shares`	`object[]`	Yes	List of parties (Users & Groups) to whom the rule applies (See Details)
`shares.*.partyId`	`string`	No⁵	UUID/OID of the User or Group entity
`shares.*.type`	`string`	Yes	Party type (either `user`, `group`, or `default`)

Notes:

# Different Types of Rules

With optional attributes, a Data Security rule can take on several forms. Below are the common types of rules used:

# Default Rule

A default rule applies to all users for whom a specific user/group rule does not exist. Most commonly, it will be a "forbid all" rule, meaning that any user who does not have an explicit rule associated with them or their group, will be blocked from seeing any data linked to the dimension in question.

This is achieved by creating a single shares object with type: "default" (defines this rule as a default rule to apply to all non-explicit parties) and setting allMembers: false so that the rule blocks access to all values of the dimension.

In some (rare) cases, the Data Security strategy is to allow full access to all users except those with explicitly set limitations, aka an "allow all" rule. This is also done via the default rule, by setting allMembers to true. Note however that this is a less secure approach, as any user that has not been assigned an explicit rule or a group with an explicit rule will be able to see all available data. This approach can be valid when access needs to be limited only to a handful of users (for example, contractors and temps with a specific scope of work) and when the data in question is of low sensitivity (for example, non PII or PHI data).

For more information on which access strategy to choose, please see Designing Your Data Security Strategy.

# User/Group rule

Normally, a user or group rule will be specific and explicit - it is by definition associated with an explicit & finite list of users/groups and it will have an explicit & finite list of values the associated parties are allowed to see.

This is achieved by not specifying the allMembers attribute (setting its value to null), and instead providing an array of 1 or more specific values in the members attribute, as well as specifying 1 or more parties of type user or group in the shares attribute.

Note that a single rule can support both multiple allowed values and multiple associated parties.

# Implementing Data Security Automation

There are a few more decisions to make before writing the scripts to automate Data Security.

# Which Language to Automate With?

Generally, script languages that aren't compiled, such as JavaScript (Node.js), Python and PowerShell are preferable for automation.
Choose a language you and your colleagues are comfortable with, so that your various automation scripts can co-exist, share components (DRY), and be maintained by a wide group of engineers.

# Authentication Approach

To run Data Security automation you will need to authenticate, receive, and use an API Token as an Administrator level user. Only administrators can set Data Security rules.
You can create a dedicated Administrator user for automation purposes, or use the credentials of a real administrator.
You will need to choose whether to authenticate only once (and store the Token for all subsequent use), authenticate every time the script runs, or authenticate before every API call. With a dedicated API user, pre-authenticating and using the Token is a safer, better choice. Re-authenticating provides a way of handling possible password changes and other scripts re-generating the Token, but also slows down the process and adds complexity.
Whether using credentials or a token, it is recommended not to store these sensitive values as a hard-coded part of the script. Use a more secure method to store and access these values when the script runs, such as AWS EC2 Parameter Store.
It is recommended to check for authentication (ensure the Token is valid) before running the scripts, exiting the script cleanly if authentication fails. This can be done by executing a GET request to the /auth/isauth endpoint with the authentication header.

# Bulk operations

Applying rules can be done in bulk or individually.
Applying individual rules can result in many API calls, adding significant overhead to the process and load to the host system. However, by keeping each "transaction" small, it is easier to handle changes and concurrent API calls.
Applying the rules in bulk saves on API call overhead resulting in faster performance, however a single bulk call may not be sufficient; mind the maximum size of an HTTP request, as well as the time a call of that size can take and the risk of it failing. It might be beneficial to break large operations into multiple bulk calls.

# Code Example

The example below is written in Windows PowerShell syntax, and makes the following assumptions:

The datamodel to which rules are applied is an extract type model (aka "Elasticube")
Using a static API token that is stored in AWS Parameter Store, that is retrieved using the AWS CLI
Data security rules are applied to user groups only (not individual users)
Taking group names as the input (requires an additional step to convert group name to ID, but is more user-friendly)
Adding all data security rules at once
Supports both separate Elasticubes and Elasticube Sets
One value associated per group

This code can easily be customized to your specific requirements, and is intended only as a demo of the process described in this article.

######################################################
# Data Security API Example                          #
# -------------------------                          #
# Script demonstrating how the Sisense Datasecurity  #
#  REST API can be invoked to create a full set of   #
#  Group-based rules in a "White-list" configuration #
######################################################

<### Inputs ###>

# Get the API token from AWS SSM Parameter Store
$apiToken = (aws ssm get-parameter --region eu-west-1 --name sisense-api-token --query Parameter.Value --output text);

# Properties of the Sisense web server
$protocol = "https";
$url = "example.com";
$port = "443";

# A collection of Elasticubes to apply data security to, and which rules should be applied
$datasources = @(
    @{
        "name" = "Sample ECommerce_set";
        "server" = "Set";
        "fields" = @(
            @{
                "column" = "Brand";
                "table" = "Brand"
                "type" = "text";
                "rules" = @(
                    @{
                        "group" = "Customer1";
                        "value" = "Addimax WorldWide "
                    }
                )
            }
        )
    }
);

<### Internals ###>

# Generic HTTP headers that apply to all API calls
$headers = @{
    "Authorization" = "Bearer $apiToken";
    "accept" = "application/json";
    "Content-Type" = "application/json;charset=UTF-8";
};

# Generic function to generate a valid API path for requests
function Generate-URI {
    param($APIPath);
    "$protocol`://$url`:$port/api$APIPath";
}

# Handler for fatal errors - exits the script
function Be-Sad {
    param($Message);
    Write-Host ":( Error! $Message";
    exit 1;
}

# Handler for non-fatal errors that can be skipped
function Be-Apathetic {
    param($Message);
    Write-Host ":| $Message";
}

# Handler for success messages
function Be-Happy {
    param($Message);
    Write-Host ":) $Message";
}

<### Script Flow Starts Here ###>

# Ensure authentication (check if API token is valid)

$RestError = $null;
$response = $null;
Try {
    $response = Invoke-WebRequest -Uri (Generate-URI -APIPath "/auth/isauth") -Method "GET" -Headers $headers;
    if(!($response.Content | ConvertFrom-Json).isauthenticated) {
        Be-Sad -Message "Invalid token";
    }
    else {
        Be-Happy -Message "Authentication valid.";
    }
} Catch {
    $RestError = $_;
    Be-Sad -Message $RestError;
}

# Create an empty array of rules to set
$body = @();

# Start batch process - iterate over Elasticubes and the Fields of each Elasticube
foreach ($cube in $datasources) {
    foreach ($field in $cube.fields) {

        # Define whitelist setting for current field (set default to no access)
        $body += @{
            "server" = $cube.server;
            "elasticube" = $cube.name;
            "allMembers" = $false;
            "table" = $field.table;
            "column" = $field.column;
            "datatype" = $field.type;
            "shares" = @(@{
                "type" = "default";
            });
            "members" = @();
        };

        # Create rules for each group
        foreach ($rule in $field.rules) {

            # Get group ID from provided group name
            $groupId = $null;
            $RestError = $null;
            $response = $null;
            Try {
                $response = Invoke-WebRequest -Uri (Generate-URI -APIPath "/v1/groups?fields=_id&amp;name=$($rule.group)") -Method "GET" -Headers $headers;
                $groupId = ($response.Content | ConvertFrom-Json)[0]._id;
                if ($groupId -eq $null) {
                    Be-Sad -Message "Can&apos;t find groupID for group $($rule.group)";
                }
                Be-Happy -Message "Got group ID for group $($rule.group)";
            } Catch {
                $RestError = $_;
                Be-Sad -Message $RestError;
            }

            # Define rule and add to collection
            $body += @{
                "server" = $cube.server;
                "elasticube" = $cube.name;
                "allMembers" = $null;
                "table" = $field.table;
                "column" = $field.column;
                "datatype" = $field.type;
                "shares" = @(@{
                    "type" = "group";
                    "party" = $groupId;
                });
                "members" = @($rule.value);
            };
        }
    }
}

# When all rules have been defined, Apply all of them via one API call
$RestError = $null;
$response = $null;
Try {
    $response = Invoke-WebRequest -Uri (Generate-URI -APIPath "/elasticubes/datasecurity") -Method "POST" -Headers $headers -Body ($body | ConvertTo-Json -Depth 50);
    Be-Happy -Message "Applied Data Security rules!";
} Catch {
    $RestError = $_;
    Be-Sad -Message $RestError;
}

# Using the Data Security API

# Abstract

# When To Use Data Security API

# How Row-level Security Works

# Designing Your Data Security Strategy

# Rule Application Scope

# Default Access

# Automation Timing

# Using The Data Security API

# Authentication

# For Extract Datamodels

# Endpoints

# Rule Schema

# For Live Datamodels

# Endpoints

# Rule Schema

# Different Types of Rules

# Default Rule

# User/Group rule

# Implementing Data Security Automation

# Which Language to Automate With?

# Authentication Approach

# Bulk operations

# Code Example

SISENSE

SUPPORT

RESOURCES