Skip to content

Row-Level Security

Row-level security is used to control which rows data consumers are allowed to view.

When enforcing a row-level security rule in data stores that use the proxy-based integration, Satori intercepts the query before it is sent to the data store and rewrites it to include the necessary filters. In data stores that use a native integration, Satori pushes row-level security policies directly to the data store, which enforces the filters as part of the query execution process.

Screenshot

For example, consider a customers table with a region column, where the security policy of the organization requires that US team members can only view the rows for the US region, and EU team members can only view the rows for the EU region.

When a US team member sends the following query:

SELECT * FROM customers

The data store executes the following query:

SELECT * FROM customers WHERE region IN ('US')

A row-level security policy is comprised of two parts:

  1. The structure of the filter - Whether to filter by a single column or a complex boolean expression.
  2. The values to use when filtering - While the structure of the filter remains the same for all users, the values may change for each user, or other conditions that you can set.

Step 1: Configuring the Filter Structure

Satori provides two methods to configure the structure of your filter:

  1. The Standard Data Filter Builder - Use the standard data filter builder to create a simple filter with a single input field.
  2. The Advanced Data Filter Builder - Use the advanced data filter builder to create a complex filter comprising of several fields with an AND/OR relationship.

Using the Standard Data Filter Builder

The standard data filter builder enables you to select the data store fields that you want to filter by. Use the standard filter builder to filter by a single field, for example, when filtering a customers table by a region column.

To add a location, select the data store and the field. You can add or remove locations by using the plus and minus buttons:

Screenshot

Using the Advanced Data Filter Builder

Use the advanced filter build to filter by a combination of multiple fields. For example, when filtering a table by both a country and a team column, such as country = 'US' and team = 'Customer Success'.

In this option, you write a YAML snippet that describes the boolean expression of your filter without specifying the actual values, which will be assigned by Satori dynamically based on the user filters.

To access the Advanced Data Filter, select a data store and a location and then click the button on the right side of the User Filter Values input field.

Screenshot

Writing the YAML Snippet

Consider the customers table from the previous example. If you want to filter the table by the customer_tier column for an EU team member who is also a member of the VIP Customer Success team, the filter would be comprised of two fields with a boolean AND relationship. In SQL, it would be represented as: region IN ('US') AND customer_tier IN ('VIP').

The following YAML snippet represents the structure of this filter:

Screenshot

The following two user filter values sections define which values to dynamically use for each user:

Screenshot

You can create multiple hierarchy levels in your snippet by using the and and or keys to represent your filter’s logic. Each field section has a name key which should match the name of the field to filter by and a filterName key which should reference the name of the filter values object that you will define in step 2.

NOTE: YAML uses indentation for hierarchical representation of information. Ensure that your YAML expression is well-formatted.

Filtering by Semi-Structured Fields

You can filter your data not only based on columns, you can also filter it based on semi-structured fields embedded in columns. Satori uses the JSONPath syntax to reference such fields. Add the path key to your field object to filter by a semi-structured field embedded within a column. For example, to filter a table by the country and customer_tier semi-structured fields embedded in the data column, use the following YAML snippet:

  or:
  - field:
      name: data
      path: $.country
    filterName: Countries
  - field:
      name: data
      path: $.customer_tier
    filterName: CS Tier Filter

Step 2: Configuring the User Filter Values

In this step, you need to create a user filter values object for the fields you use to filter by. In each user filter values object you map between the users and the values they should be allowed to view from the data for each field. Use the plus and minus buttons to add or remove mappings.

Selecting Users

You have several options for selecting which users to map values to.

The user is / is not

Matches a specific identity or all other users except a specific identity. Several types of identities are supported:

  1. User - a user that is defined in the User Management page. For example: john.smith@acme.com.
  2. Datastore Username - a user that is defined locally in the data store. For example: JSMITH on a Snowflake account.

The user is a member / is not a member of

Matches if the user is or is not a member of a group. Several types of groups are supported:

  1. Group - refers to a Satori directory group. This option is only available for data stores that use the proxy-based integration.
  2. IdP Group - refers to a group that is managed by the organization’s identity provider and is synchronized to Satori using the SCIM integration or SAML attributes. This option is only available for data stores that use the proxy-based integration.
  3. Databricks Group - refers to a group in a Databricks account

Custom Expression

Use this option to match users using a custom expression in the CEL (Common Expression Language) format. This enables you to create Attribute-Based Access Control (ABAC) row-level security policies, reducing the number of rules you need to manage.

You can either synchronize user attributes from your identity provider using the SCIM integration or manage them directly on Satori in the User Management view.

Satori provides the following built-in functions to create CEL expressions:

userMemberOfGroup - checks if a user is a member of the specified group. For example: userMemberOfGroup('groupA').

userHasAttr - checks if a user has an attribute with the specified name. For example: userHasAttr('attribute_name').

userAttr - returns the value of the user attribute with the specified name, or null if no such attribute exists. For example: userAttr('country') == 'value_of_attribute_name'.

You can also create nested logic rules using parenthesis, boolean OR (||) and boolean AND (&&). For example: userAttr('country') == 'US' && userAttr('team') = 'Customer Success'.

Selecting Values

Once you select the users to filter data for, select which values they should see for the field you are configuring. You can choose from several options:

Screenshot

Allow to View These Textual Values

Specify a list of textual values to filter by. This option translates to the following SQL expression: <field> in ('<value1>', '<value2>', ...). For example: country in ('US', 'CA').

Allow to View These Numeric Values

Specify a list of numeric values to filter by. This option translates to the following SQL expression: <field> in (<value1>, <value2>, ...). For example: team_id in (1, 2, 3).

Allow to View All Values

No filter will be applied, and the selected users will be able to see all values of this field.

Allow to View All Other Values by Default

Use this option to let users see all values except those specified in previous rules. This option translates to the following SQL expression: <field> not in (<value1>, <value2>, ...). For example: team_id not in (1, 2, 3).

All Users Option

Use this option to either not allow users any values by default, allow users specific values, or allow users all values.

Example 1

The following data filter example shows that MIKE is allowed to view the Sydney, New York and London values, while any other user will not be allowed to view any value.

Screenshot

If you need to create a condition that includes a user and or a group to view specific data while excluding either a user or a group from viewing the same data value then you must create a multiple line If and Allow Condition rule.

Example 2

The following data filter example shows that member of the ACME VIP Customer Success team can view the VIP field value, members of the ACME Customer Success team they can view the SILVER, GOLD and PLATINUM field values and finally, all users can view field values except for VIP, SILVER, GOLD and PLATINUM.

Screenshot

Renaming your Location Selectors and Data Filters

You can rename the locations and filters that you create with meaningful custom names or in accordance with your company naming policies.

  1. Place your mouse cursor over the name and click in the input field of your newly created rule of filter and rename it.
  2. Click in the blank space to save the new masking rule or filter name.

Screenshot

IMPORTANT: If you decide to change the name of your rule, ensure that you reselect the updated User Filter Value drop menu item that appears in the Location Selectors filter section.

Considerations and Known Limitations

The following scetion provides you with the relevant considerations and known limitations of the data filtering feature.

Supported Data Stores

Row-level security is only supported in relational data stores.

SQL MERGE in AWS Redshift

Row-level security does not work when copying data from a source table to a target table using MERGE in AWS Redshift because AWS Redshift only supports specifying a source table, not a subquery. To prevent users from executing MERGE in AWS Redshift with Satori, set the Baseline Security Policy to Block Query when recognizing unsupported queries and objects.

Filtering by Semi-Structured Fields

Filtering by semi-structured fields is only supported in data stores that use the proxy-based integration.