Skip to content

Row-level Access Control

Row-level access control is a core data security feature of the Hydrolix platform. Using row policies and roles, data administrators can implement granular rules limiting access to their datasets.

How does it work?⚓︎

Data administrators construct row policies using the Config API.

Row policies are filters attached to a single table and one or more roles. They combine into a final filter expression returning only the allowable rows. Roles are associated with user and service accounts. Changes take place immediately.

The query system periodically retrieves the combined row policies which contains final filter expressions for roles and tables.

At query time, the query system installs a ClickHouse row policy to enforce the combined filter by reading only permitted rows from the raw partition.

Concepts⚓︎

Hydrolix supports a fully-featured account permissions system that allows definition of roles. Multiple roles can be assigned to users and service accounts, to describe rich access permissions.

Row-level access control is implemented by defining row policies and associating them with roles.

Hydrolix row policies

  • can be attached to multiple roles, supporting granular data access rules.
  • apply to a single table; consider raw and summary data tables, see data access policy design.
  • contain arbitrary SQL filters determining row visibility, see filters.
  • combine predictably and deterministically, see combining row policies.
  • can't use the primary timestamp column in any filter

When an authorized account initiates a query, the row policies for that account are installed into the query engine. Only rows passing this final filter expression are visible to this query.

Roles are intentionally composable in an additive fashion to express rich access rules. Roles can be empty or can contain arbitrary combinations of RBAC permissions, row policies, and column policies. Roles, permissions, row, and column policies combine with a design of positive permissions. See also data access policy design strategies.

Feature interaction⚓︎

Row policies are fully compatible with

Logical diagram⚓︎

This diagram illustrates the results of combining two roles, each with row policies. One of the row policies, section_sports_eu, demonstrates two restrictive and two non-restrictive filters.

Depiction of row policy application

Filters⚓︎

Filters are arbitrary SQL expressions which evaluate to a boolean or a uint8.

A uint8 is converted to a boolean using toBool meaning that 0 is FALSE and anything else is TRUE.

A row is accessible if the filter evaluates to true.

If the filter evaluates to false for a row, that row is inaccessible to the user's query. The query system won't return the row from the Hydrolix raw partition data to the user's query.

When multiple row policies or multiple roles with row policies are in effect, the filters are combined. See Combining row policies.

Filter verification and constraints⚓︎

When the Config API receives a request to create or modify a filter, it calls the core database engine table function hdx_verify_filter_expr with the candidate filter. This ensures that the database engine can enforce these constraints on the filter:

  • the primary column of a table can't be used in a filter
  • aggregation functions are forbidden: the filter must operate only on data in the row
  • non-aggregation functions are acceptable
  • calculated columns can't be used, as they don't exist in the underlying partitons
  • when referring to renamed columns, only the current column name is valid
  • column names in the SQL filter must exist in the autoview, the union of all columns defined in all transforms

Combining row policies⚓︎

The logical diagram illustrates the rules of row policy and role combination using a table similar to that shown in the Row-level Access How-to.

Each Hydrolix row policy consists of a filter and the boolean restrictive field.

The combining logic supports the additive access design. More roles and permissions means more access.

Combining from a single role⚓︎

Row policies from a single role combine into a single intermediate filter expression.

  1. Restrictive row filters, restrictive=True, are joined with the SQL AND into a single expression.
  2. Non-restrictive row filters, restrictive=False, are joined with SQL OR into a single expression.
  3. The expressions from the prior two steps are joined with SQL AND into an intermediate filter expression for that role.

Combining from multiple roles⚓︎

Intermediate row filter expressions from different roles merge using an SQL OR, or set union operation, into final filter expression.

  1. Intermediate filter expressions are built for each role according to combining from a single role.
  2. All intermediate expressions are joined with the SQL OR operator to form the final filter expression.
  3. The query system applies the final filter expression at data retrieval time.

Query-time operation⚓︎

When a query head receives a query over any interface, it uses the authenticated account's UUID to look up applicable row policies.

After parsing, it collects the final expression of the combined row policies and continues execution of the query. Enforcement of row policies occurs in the query peers, which perform data retrieval from the raw partitions.

Query peers initialize the ClickHouse engine with true for the internal function setEnabledUsersWithoutRowPoliciesCanReadRows. This allows smooth forward and backward compatibility for tables without any row policies.

When a query peer handles a query with a row-policy, it installs the filters before reading rows from the raw partitions.

The query head refreshes its view of row policies using the private Config API endpoint /config/v1/roles/row_policies/ on a frequency specified by the tunable user_acl_refresh_interval_secs, by default 30 seconds.

Limitations⚓︎

See data access policy design limitations.

Use cases⚓︎

Row-level access controls

  • allow multiple distinct user populations to be served from the same table, each with a different policy
  • decrease data ingestion complexity when a single data stream serves user populations with distinct data access permissions
  • decrease management effort for table settings and transforms when tables are structurally identical and differ only in data access permissions
  • decrease application or query complexity for cross-table joins where multiple tables were formerly in use
  • support backward compatibility by retaining default visibility when no policies are in effect