User Parameters Use Case - Enforcing Data Isolation / Row-Level Security Rules from Source Database

[TriAnthony]

User Parameters Use Case - Enforcing Data Isolation / Row-Level Security Rules from Source Database

This article explains two of the example use cases of User Parameters laid out in User Parameters - What They Are and What They're For (Example Use Cases).

The most common use case of User Parameters is to create a single dynamic data model that supports multiple database connections. A dynamic model has varying connection information that is resolved during runtime based on the currently logged-in user. These are two common scenarios that can be supported by a dynamic live model:

1. Data are stored in multiple isolated, structurally identical databases and need to be federated in a single model (parameterized database location or name)
2. Row-level security rules are already defined in the source database and need to be enforced to Sisense queries (parameterized username and password)

Further details on each scenario and its implementation are provided below.

1. Data are stored in multiple isolated, structurally identical databases and need to be federated in a single model

Certain organizations partition their data into separate isolated databases (e.g. one database per customer or region), typically due to laws, industry regulations, or company policies. For example, an insurance company serving the US and Australian markets may store the American customers' data in a Snowflake server located in Ohio and the Australian customers' data in a Snowflake server located in Sydney.

We can serve both users in the US and Australia with a single data model without comprising security by using parameters to assign a connection string, database location, or database name to users and groups. For more information on how to create parameters, refer to this documentation: Personalization Parameters for Live Data Sources. Once the parameters are created and assigned to the appropriate groups and users, you can update your live model's connection settings and replace the hard-coded values with the parameters. Depending on the database vendor (Snowflake, Redshift, etc) and what is being parameterized, you can use the Location, Database, or Connection String fields. To select a parameter, click the dropdown menu to the right of the input field, then select the parameter name. The parameter name will then be populated in the input field in two pairs of curly brackets.

Note that you can parameterize the whole or parts of a string. For example, if you opt to use the connection string field, and the databases are in the same server (they just have different warehouse/database names), you can specify the parameter as part of the string, as shown in the example below.

jdbc://yoursnowflakeserver/?warehouse={{WAREHOUSE_NAME}}&db={{DATABASE_NAME}}

If the two databases are stored in different servers, with different warehouse and database names, you can parameterize the whole string, as shown in the example below.

The value of {{CONNECTION_STRING}} parameter is the entire connection string assigned to the groups/users. For example, this could be the connection string assigned to the American users:

jdbc://yourUSAsnowflakeserver/?warehouse=USA_WH&db=USA_DB

and this could be the connection string assigned to the Australian users:

jdbc://yourAUSsnowflakeserver/?warehouse=AUS_WH&db=AUS_DB

After updating the connection settings, republish the model and revisit your dashboard.

The screenshots below show the same dashboard connected to the same data model, utilizing user parameters. When users open the dashboard, Sisense determines which database to be queried based on the value of the user parameter assigned to the logged-in user or the group they are a member of. Therefore, in this example, the entire dashboard displays only data from the region the user belongs to. This is most apparent in the State and City filters, as well as the Scatter Map widget.

For USA users:

For Australian users:

Note that if further row-level restrictions are required within each database/region, data security rules still need to be established in the data model.

2. Row-level security rules are already defined in the source database and need to be enforced to Sisense queries

In this scenario, each Sisense user or group has an associated account in the database server where row-level access rules have already been defined. By parameterizing the user and password used in the live model's connection settings, you don't have to recreate the security rules in the Sisense live model. In other words, when users open the dashboard, Sisense determines which database username and password will be used to establish the connection, based on the value of the user parameter assigned to the logged-in user or the group they are a member of. All associated row access policies from the source database are then enforced automatically to Sisense queries.

The first step to implementing this use case is creating the username and password parameters, and assigning the appropriate values to the users and groups. After you create and assign these parameters, update your live model's connection settings and populate the User Name and Password input fields with the associated parameters, as shown in the example below. Save the changes and republish the model.

Note that when you create the parameter for passwords, it's important to mark it as a hidden value for security. Selecting Yes for this option prevents assigned values from being displayed in plain text. Once the values are saved, they cannot be viewed anymore. It's also not possible to change the Hide Values option to No once the parameter has been created.