Skip to content

Creating Row-Filtering Permissions Tutorial

This document is intended for DBAs, developers, data stewards, and data architects who need to understand how ABAC works in ODAS.

Introduction

This feature will enable you to more efficiently create and manage row-level access policies. This will help you and your organizations to better secure access within their organizations while still having the flexibility to customize them based on unique restraints and user attributes.

Creating Access Conditions with Row Filtering

  • select the role or database you would like to create the policy for and open the policy builder. When creating a new access condition select the option "Restrict access with row filtering" from the drop-down menu. This will instruct you to create your first condition.

The condition will prompt you to select from three types: (1) column, (2) user attribute, and (3) custom expression. Selecting column or user attribute as type will have similar operators pertaining to each use case whereas selecting custom expression will enable you to write a custom SQL expression for a very complex use case.

Column Operators

  • is equal to: This expression performs an exact string match and restricts access where the column equals a specific value

  • is not equal to: This expression functions similarly to is equal to but is the negative version

  • contains: This expression performs a wildcard string match on the specific value and is not case sensitive

  • does not contain: This expression functions similarly to contains but is the negative version

  • matches user attribute: This expression does a set match so instead of matching one specific value it allows a user attribute to have multiple values. For example, if a person's user attribute values were France, Germany, this expression would give access to columns matching both values

  • does not match user attribute: This expression functions similarly to matches user attribute but is the negative version

  • is equal to user attribute: This expression performs an exact string match and restricts access where the column equals the user attribute value.

  • is not equal to user attribute: This expression functions similarly to is equal to user attribute but is the negative version

User Attribute Operators

  • is equal to: This expression performs an exact string match and grants access to rows in a table where the user attribute equals a specific value. If you create a permission on a table where a user attribute territory is equal to 'EMEA' then only users with this value will view the rows in the table.

  • is not equal to: This expression functions similarly to is equal to but is the negative version

  • contains: This expression performs a wildcard string match on the specific value and is not case sensitive. If you create a permission on a table where a user attribute territory contains 'EMEA' then only users with this value will view the rows in the table.

  • does not contain: This expression functions similarly to contains but is the negative version

  • matches column value: This expression does a set match so instead of matching one specific value it allows a user attribute to match multiple column values. For example, if a person's user attribute values were France, Germany, this expression would give access to columns matching both values

  • does not match column value: This expression functions similarly to matches column value but is the negative version

  • is equal to user attribute: This expression performs an exact string match and restricts access where the column equals the user attribute value.

The new row-filtering feature supports both simple access conditions as well as more complex use cases that require multiple logical groupings combined into one access condition. In the example section we will run through how to implement conditions that support both simple and more complex use cases such as hierarchical row filtering.

Geo-fencing policies

Leveraging row based filtering can be useful when data needs to be filtered by region or country such as for complying with GDPR and CCPA or for simplifying organizational access.

Policy example: For any user with the role sales_analyst, give access to data in the sales database only when the user attribute territory corresponds to the column territory and when the user's user attribute country corresponds to the column country value.

Here's what our policy looks like in the policy builder. In this example we are giving access to the sales.transactions table

Okera Row filtering UI

Note

Row filter policies can be applied at both the dataset or database scope, however if applied at the database level then tables that do not contain the referenced column will no longer be accessible.

Policy Result

Okera Row filter policy result

  • The screenshot above shows the analyst view after applying this filter. As you can see, the analyst can now only view data that pertains to their corresponding country and territory, which in this case are 'France' and 'EMEA.'

Managing Row Filtering Permissions

After you have successfully created your row filtering based access condition you can manage the conditions by either going to the permissions page under the database/dataset you created them for or by going to the roles page and selecting the role you created the condition for. In this case, it would be the sales_analyst_role

Okera Row filter Permission List

How do I know what a specific user can see?

If you created a policy and want to know what condition is applied to a specific user, you can simply navigate to the Users page and select the user. Under the user you will be able to see which user attributes the user has such as their region, designation, etc and you can compare these to the permission you created.

Editing Row Filtering Permissions

If you would like to make changes to the access condition you just created select the edit button when you see it listed under the permissions list and it will open the policy builder modal. Unlike with other access conditions, the editing mode for Row Filtering permissions will direct you to the custom SQL version of the policy so you can better understand what the policy is doing. After making the necessary changes, click update permission and you will see the new verison listed in the permission list.

Okera Row filter Editing Mode

FAQ

  • How do user attribute based permissions work?

User attribute based permissions are implemented the same way that other permissions such as transforms are created within the policy builder. When creating a user-attribute based permission, the user would specify “row-filtering” as the condition choice in the drop down. One common example of a user-attribute based permission would be if you want to ensure that sales analyst can only see data pertaining to their region. So if a user has a user attribute region: USA then they will only be able to see rows of data in a table that have the specified region.

  • Who creates user attribute based permissions and who are they applied to? Admin users and data stewards who are granted ownership over certain datasets/databases have the ability to create user-based policies within the policy builder. When an admin user grants admin privileges to a data steward they will automatically have access to create all types of permissions, which includes user-attribute based row filtering permissions as well.

  • How do row filtering permissions work with different datatypes? User attributes are always string values in Okera so when creating permissions involving column values they will be compared as strings.

  • Are row filtering permissions case sensitive? No, however, certain operators such as the contains operator are case sensitive so if a user enters ‘emea’ as a region value it will also match to user attributes where the value is ‘EMEA.’

  • Who will be able to see row filtering permissions, which apply to a table? The admin or data steward who created the permission will see it listed in the permission list along with tag and transform based permissions.

  • Where can an admin or data steward manage/view what user attributes a specific user has? An admin or data steward can go directly to the users page to see what user attributes are associated with the particular user.

  • How can an admin or data steward manage/edit row filtering permissions for a table? They can go directly to the database/dataset or manage and change the permission through the roles page as well. If they want to edit or make changes to the particular permission they will press the edit button which will open up the custom SQL expression box where they can directly edit the SQL for the permission. We do not currently support the UI for editing row-filtering permissions in the same manner as we support creating them.