---
alias: operation-guide-data-set-data-access-security
tags:
- data platform
- security
description: "Data Platform queries return only the rows a user can reach, based on the Data Groups of the roles of that user"
---

# Data Set Data Access Security

## Overview

Object Level security controls access to object instances inside the MES. **Data Set Data Access Security** extends that control to the data that leaves the system through the [[operation-guide-messtackcomponents#data-manager|Data Manager]], the **Data Platform** component that gives access to **Data Sets**. A user who queries a **Data Set** receives only the rows they can reach. Rows outside the reach of a user are removed from the result, without an error and without a marker to say that anything was removed.

Three elements work together to filter the rows:

- [Data Manager](#data-manager) - sends the **Data Groups** of the caller with every query, so that the row policies can apply them.
- [Data Group references](#data-group-references) - each row of the data carries the **Data Group** of the objects it represents.
- [Row policies](#row-policies) - a ClickHouse row policy on each table defines the condition a row must meet to be returned.

**Data Set Data Access Security** covers the Operational Data Store (ODS) and the Canonical Data Model (CDM), and only data read through the **Data Manager**. It does not cover the Data Warehouse (DWH) or Data Silos, and a client that connects to ClickHouse with a different account reads the data without restriction. For more information, see [Scope and Limits](#scope).

## Data Set Access and Row Access

As an entity, a **Data Set** can carry a **Data Group** of its own, and that assignment controls whether a user can open and query it. **Data Set Data Access Security** controls something different: the rows that the query returns after the user is allowed to run it.

The two controls are independent. A user who holds the **Data Group** of a **Data Set** can query it and still receive no rows, if the roles of that user grant access to none of the **Data Groups** carried by the data.

## How Filtering Works

The following sections describe each element of the filtering.

### Data Group References

The two data sources carry the **Data Group** reference in different ways:

- **ODS** - Each replicated table carries a single `DataGroupId` column, which holds the **Data Group** of the object that the row represents.
- **CDM** - Each table built from an **IoT Event Definition** carries two array columns of the same length:
    - `Header_DataGroupRefs` holds the name of the object at each position, such as `Site`, `Resource`, or `Material`.
    - `Header_DataGroups` holds the **Data Group** value of that object at the same position.

A value of `-1` in `Header_DataGroups` means that no **Data Group** is assigned to the object for that row.

### Protected CDM Objects

The protected objects of a CDM table are the objects that the **IoT Event Definition** selects for filtering. A row records the **Data Group** of every object it touches, and the row policy reads only the **Data Group** of each protected object.

A **MaterialOperations** row, for example, carries the **Data Groups** of the **Product**, the **Parent Material**, the **Material**, the **Product Group**, and the **Flow**. When **Material** is the only protected object, the **Data Group** of the **Material** determines if the row is returned, while the other values remain on the row but are not evaluated.

A definition can set more than one object to `true`, and the row policy then checks each of them. When the definition of the **MaterialOperations** event protects both the **Material** and the **Resource**, the row reaches a user only when that user reaches the **Data Group** of both, so a single object out of reach removes the row from the result.

![A MaterialOperations row showing five captured Data Groups. Only the Material Data Group is used to filter the row.](diagrams/cdm_captured_data_groups.drawio.svg)

### Row Policies

HouseKeeper creates a ClickHouse row policy for each table, named after the table in the form `<table_name>_ROWPOLICY`, and grants it to the analytics read role of the system, named `<SystemName>_CLICKHOUSE_ANALYTICS_READ_ROLE`. The moment of creation differs between the two sources:

- For an ODS table, HouseKeeper creates the policy when it creates the table, and only for entity tables that do not come from a Data Silo.
- For a CDM table, HouseKeeper creates or replaces the policy every time the **IoT Event Definition** is created or updated. The objects to protect come from the `DataGroupsToApplySecurityLevel` property of that definition.

When a table does not have the columns that the policy needs, HouseKeeper creates no policy for that table, and the table returns every row. For more information, see [Verify That a Table Is Protected](#verify-table-protection).

### Data Manager

Before it runs a query, the **Data Manager** creates a temporary table named `RLS_DataGroups_Input` and inserts the **Data Groups** of the caller into it. The row policy reads that table and removes every row that the caller cannot reach.

The **Data Manager** applies the restriction to the following operations:

- Reading a **Data Set** through OData.
- Counting the rows of a **Data Set**.
- Reading the statistics of a column.
- Exporting a **Data Set**.

Schema requests are the exception: a user who can access a **Data Set** always sees the full list of columns, even when the query returns no rows. For users that hold every **Data Group**, the **Data Manager** sets a flag that bypasses the check, so the query runs without the extra comparison.

### Joins

A row policy belongs to a single table, so when a query joins two tables, ClickHouse applies the policy of each table on its own before the join runs on the rows that remain.

A joined result therefore holds a row only when the caller reaches the matching row on both sides. Where the caller reaches a row in one table but not its match in the other, the pair produces no result.

![Two overlapping sets of rows, Table A and Table B. Each table is filtered by its own row policy first. Only the matching rows that the caller reaches in both tables reach the result.](diagrams/row_policy_join.drawio.svg)

## Query Results

The following sections describe what a query returns to the user.

### Row Outcomes

The result of a query depends on the **Data Group** that each row carries and on the roles of the user, as described in the table below.

| Case                                                                                                                                  | Result                                                                                             |
|:--------------------------------------------------------------------------------------------------------------------------------------|:---------------------------------------------------------------------------------------------------|
| The row carries a **Data Group** that a role of the user grants access to.                                                            | The row is returned.                                                                               |
| The row carries a **Data Group** that no role of the user grants access to.                                                           | The row is not returned.                                                                           |
| The row carries no **Data Group**, in an ODS table.                                                                                   | The row is returned.                                                                               |
| In a CDM table, the row carries the value `-1` for a protected object, which means that no **Data Group** is assigned to that object. | The row is returned.                                                                               |
| The `DataGroupsToApplySecurityLevel` property sets more than one object to `true`.                                                    | The row is returned only when the user can reach the **Data Group** of every one of those objects. |
| The name of a protected object is missing from `Header_DataGroupRefs` in that row.                                                    | The row is not returned.                                                                           |
| The table has no row policy.                                                                                                          | Every row is returned.                                                                             |
| The query joins two tables, and the user reaches the row in one table only.                                                           | The joined row is not returned.                                                                    |
| The row is returned, and it carries columns that describe another object.                                                             | Every column of the row is returned, including the columns of objects the user cannot reach.       |

Table: Outcome of a Data Platform query for each row case

!!! note
    The rule for rows without a Data Group matches the rule that applies inside the MES. If an object instance has no Data Group, every user can access it. For more information, see [[user-guide-security-data-groups]].

### Column Values

The restriction works on whole rows, and it does not hide the value of a column.

A CDM row carries columns that describe several objects. A **MaterialOperations** row, for example, carries columns that describe the **Resource** as well as the **Material**, and the definition names **Material**, so the **Data Group** of the **Material** decides whether the row is returned. When the row is returned, the user reads every column of it, including the columns that describe the **Resource**. A user can therefore read an attribute of an object whose **Data Group** they cannot reach, where that attribute sits on a row they can reach.

!!! warning
    Consider this when you build a Data Set over a CDM table. Where a column must not reach a user, leave it out of the query of the Data Set, because the row restriction does not hide it.

### Role and Data Group Changes

A change to the roles of a user, or to the **Data Groups** of a role, applies to the next **Data Platform** query. The user does not need to sign out and sign in again.

## Requirements and Limits

The following sections describe when the filtering applies.

### Object Level Security

**Data Set Data Access Security** follows the Object Level security setting of the system:

- When Object Level security is active, the **Data Manager** sends the **Data Groups** of the caller with each query, and the row policies restrict the result.
- When Object Level security is inactive, the **Data Manager** marks every request as exempt, and the row policies return every row.

You do not configure this behavior separately. Changing the Object Level security setting changes what **Data Platform** returns at the same time as it changes access inside the MES.

!!! note
    The row policies exist on the tables regardless of the Object Level security setting, and only the outcome changes. A row policy on a table does not mean that queries against that table are currently restricted.

### Scope

**Data Set Data Access Security** applies to the ODS and to the CDM. It does not apply to:

- DWH - Row policies are not created for DWH tables, and every row is returned.
- Data Silos - Tables replicated from a Data Silo do not receive a row policy.
- Tables that do not carry the columns the policy needs.

The restriction also applies only to data read through the **Data Manager**. A client that connects to ClickHouse with a different account does not carry the row policy, and reads the data without restriction.

## Verify Table Protection

A table without a row policy returns every row, so confirm which tables are protected by listing the row policies of the database in ClickHouse:

```sql
SELECT name, database, table FROM system.row_policies ORDER BY database, table;
```

Each protected table appears once, with a policy named `<table_name>_ROWPOLICY`. When a table you expect to be protected does not appear:

1. Confirm that the table carries the required columns. An ODS table needs `DataGroupId`. A CDM table needs both `Header_DataGroupRefs` and `Header_DataGroups`.
2. For a CDM table, confirm that the **IoT Event Definition** sets at least one object to `true` in the `DataGroupsToApplySecurityLevel` property.
3. Check the HouseKeeper logs. When a required column is missing, HouseKeeper records the table and the missing columns.

!!! info
    Upgrading to version 12.0.0 creates the row policies for the tables that already exist, as part of the installation process. You do not need to recreate a table to protect it.

## Related Documentation

- [[user-guide-security-data-groups]] - describes **Data Groups** and Object Level security.
- [[user-guide-data-set-index]] - describes **Data Sets** and how to access **Data Platform** data.
- [[operation-guide-clickhouse-backupandrestoreoverview]] - describes how row policies are carried in a backup.