Create Row-Level Security Permissions¶

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

Introduction¶

Row-level security permissions help you and your organization better secure data access while retaining the flexibility to customize that access based on unique restraints and user attributes. You can efficiently create and manage row-level access permissions in Okera.

Create Access Conditions with Row Filtering¶

Select the role or database you would like to create permissions for and open the permission builder. To create a new access condition, select Add an access condition and then select Restrict access with row filtering from the Condition dropdown menu. The UI provides fields in which you can create your first condition.

The condition prompts you to select from three types: (1) column, (2) user attribute, and (3) custom expression. Selecting column or user attribute results in similar, standard operations, whereas selecting custom expression allows you to write a custom SQL expression for a more complex use case.

Column Operators¶

is equal to: This expression searches for an exact string match and grants access to column values that equal a specific value.
is not equal to: This expression searches for an exact string match and grants access to column values that do not equal a specific value.
contains: This expression searches for a wildcard string match on a specific value and is not case-sensitive. It grants access to column values if they include the specific value.
does not contain: This expression searches for a wildcard string match on a specific value and is not case-sensitive. It grants access to column values if they do not include the specific value.
matches user attribute: This expression searches for a set match; instead of matching a specific value, it allows a user attribute to have multiple values. It grants access to column values if they match one of the values. For example, if a person's user attribute values were France, Germany, this expression would give access to columns matching either value.
does not match user attribute: This expression searches for a set match; instead of matching a specific value, it allows a user attribute to have multiple values. It grants access to column values if they do not match one of the values. For example, if a person's user attribute values were France, Germany, this expression would not give access to columns matching either value.
is equal to user attribute: This expression searches for an exact string match and grants access when column values equal the user attribute value.
is not equal to user attribute: This expression searches for an exact string match and grants access when column values do not equal the user attribute value.

User Attribute Operators¶

is equal to: This expression searches for an exact string match and grants access to table rows where a user attribute equals a specific value. If you create a permission on a table where the territory user attribute is equal to EMEA, only users with this territory value can view the rows in the table.
is not equal to: This expression searches for an exact string match and grants access to table rows where a user attribute does not equal a specific value.
contains: This expression searches for a wildcard string match on a specific value and is not case-sensitive. It grants access to table rows if they include the specific value. If you create a permission on a table where the territory user attribute contains EMEA, only users with territory values that contain EMEA can view the rows in the table.
does not contain: This expression searches for a wildcard string match on a specific value and is not case-sensitive. It grants access to table rows if they do not include the specific value.
matches column value: This expression searches for a set match; instead of matching a specific value, it allows a user attribute to have multiple column values. It grants access to table rows if they match one of the values. For example, if a person's user attribute values were France, Germany, this expression would give access to columns matching either value.
does not match column value: This expression searches for a set match; instead of matching a specific value, it allows a user attribute to have multiple values. It grants access to table rows if they do not match one of the values. For example, if a person's user attribute values were France, Germany, this expression would not give access to columns matching either value.
is equal to column: This expression performs an exact string match and restricts access where the column equals the user attribute value.
is equal to user attribute: This expression searches for an exact string match and grants access when column values equal the user attribute value.
is not equal to user attribute: This expression searches for an exact string match and grants access when column values do not equal the user attribute value.

The 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.

Geo-Fencing Permissions¶

Leveraging row-level filtering can be useful when data needs to be filtered by region or country, for example when complying with GDPR and CCPA standards or when simplifying organizational access.

In this example, we give access to transactions data in the salesdemo database for any user with the sales_analyst_role role only when the user territory attribute corresponds to the territory column value and when the user's country user attribute corresponds to the country column value.

Here's what the permission definition might look like in the permission builder.

Here's the permission definition in SQL:

GRANT SELECT ON TABLE `salesdemo`.`transactions`
WHERE (sets_intersect(`territory`, user_attribute('territory'))) AND (sets_intersect(`country`, user_attribute('')))
TO ROLE `sales_analyst_role`
POLICYPROPERTIES('enabled'='true');

Note: Row-level filter permissions can be applied at both the dataset or database level. However, if a column is chosen as the filter source and the row-level filter is applied at the database level, tables that do not contain the referenced column are no longer accessible. If a Column with tag (columns_with_attribute) is chosen as the filter source, all tables are accessible and the row-level filter permissions are applied accordingly.

Manage Row Filtering Permissions¶

After you have successfully created your row-level filtering access condition you can manage the conditions by accessing the Permissions page for the database/dataset or accessing the Roles page and selecting the role for which you created the condition.

To determine what a specific user can see, navigate to the Users page and select the user. You can quickly see the user attribute settings (such as their region, designation, and roles) for the user and compare them to the permissions you created.

Edit Row-Level Filtering Permissions¶

To change an access condition, select the edit icon () when you see it listed in the permissions list. This opens the permission build modal.

As with other access conditions, the editing mode for row-level filtering permissions displays using the same dropdown boxes used to create the original permission. However, permissions that were created using the custom expression box and more complicated permissions created during DDL appear in the custom expression box when you edit the permission so you can better understand what the permission is doing.

After making any necessary changes, select Update Permission. The updated version of the permission is listed in the permission list.

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 permission builder. When you create a user-attribute-based permission, you specify row-filtering for the condition choice in the dropdown menu. A common use of a user-attribute-based permission is to restrict a sales analyst to viewing only data pertaining to their region.
Who creates user-attribute-based permissions and who are they applied to? Administrators and data stewards who are granted ownership over certain datasets/databases can create user-attribute-based permissions within the permission builder. When an administrator grants admin privileges to a data steward, the data steward can also create all types of permissions, which includes user-attribute-based row filtering permissions.
How do row filtering permissions work with different data types? User attributes are always string values in Okera, so when creating permissions involving column values they will be compared as strings.

Who can see the row filtering permissions applied to a table? The administrator or data steward who created the permission will see it listed in the permission list along with any tag and transform-based permissions.
Where can an administrator or data steward manage or view the user attributes for a specific user? An administrator or data steward can see the user attributes associated with a user by selecting the user on the Users page.
How can an administrator or data steward manage or edit row filtering permissions for a table or file? An administrator or data steward can manage or edit the permissions after selecting a dataset from a database on the Databases tab or a file on the Files tab (these tabs can be accessed on the Data page). Permissions can also be managed or edited from the Roles page. To edit a specific permission, select the edit icon () which opens the custom SQL expression box so 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.