---
title: Persistent Query ACLs
sidebar_label: Persistent Query ACLs
---

Deephaven makes it simple to share the results of Persistent Queries (PQs) with other users. You can add groups to your PQ as either "Admins" or "Viewers". If you have not defined any ACLs, then all Admin and Viewer groups can see all the results of the query. This makes it simple to share results with a chosen set of users.

Different users are likely members of different groups and have different restrictions that must be followed when providing them data. Deephaven provides Edge ACLs for this purpose.

Edge ACLs function the same way [Table ACLs](./table-acls.md) do in that, based on the user requesting the data, a set of filters are generated and applied to the table before being returned. The difference is that Edge ACLs are attached directly to derived tables within a PQ and are applied dynamically when a user fetches the table.

> [!WARNING]
> As soon as you add an ACL to one result, the remainder of the exported objects are blocked by default. You must apply ACLs to each object that you want to make available to Viewers. The owner of a query and users in one of the admin groups do not have ACLs applied, and tables are returned without filtering.

## Open sharing default

When no per-table ACLs are defined, the behavior is controlled by the property `PersistentQuery.openSharingDefault`, which defaults to `true`.

| Setting          | Behavior                                                                                                                                                                                                                                                              |
| ---------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `true` (default) | If no filters are defined on any table, viewers can see all rows in all tables. If a filter is defined for at least one table, viewers cannot see any tables without filters. This makes it convenient to share results while hiding parts that haven't been secured. |
| `false`          | Viewers cannot load any tables unless explicit ACLs are defined. This requires more configuration but makes inadvertent sharing less likely.                                                                                                                          |

## Creating Edge ACLs

Edge ACLs are created using the `EdgeAclProvider.builder()` method, and then are built up using the `rowAcl()` and `columnAcl()` methods. For each of these, you provide a group and a filter generator to produce the desired ACL when a user requests the table.

Once you have specified the ACLs desired, you create the ACL object using the `build()` method and can then attach the ACLs using the `applyTo()` method, as in the example below.

The following example assumes that a table "TickingTable" has already been created. Edge ACLs are created using a builder that contains a few simple methods for building up ACL sets.

Once `build()` is called, you have an ACL object that can then be used to transform one or more tables using the `applyTo()` method.

Note that you must overwrite the scope variable with the result of the application, since Table properties are immutable.

```groovy
import io.deephaven.enterprise.acl.EdgeAclProvider
import io.deephaven.enterprise.acl.AclFilterGenerator

def ACL = EdgeAclProvider.builder()
        .rowAcl("NYSE", AclFilterGenerator.where("Exchange in `NYSE`"))
        .columnAcl("LimitPrice", "*", AclFilterGenerator.fullAccess())
        .columnAcl("LimitPrice", ["Price", "TradeVal"], AclFilterGenerator.group("USym"))
        .build()

TickingTable = ACL.applyTo(TickingTable)
```

```python
from deephaven_enterprise.edge_acl import EdgeAclProvider
import deephaven_enterprise.acl_generator as acl_generator

ACL = (
    EdgeAclProvider.builder()
    .row_acl("NYSE", acl_generator.where("Exchange in `NYSE`"))
    .column_acl("LimitPrice", "*", acl_generator.full_access())
    .column_acl("LimitPrice", ["Price", "TradeVal"], acl_generator.group("USym"))
    .build()
)

TickingTable = ACL.apply_to(TickingTable)
```

## Hierarchical tables

For hierarchical tables (i.e., rollup tables and tree tables), the same builder and `applyTo` method are used. For these tables, the ACL filters are applied to the source of the rollup or tree, and then the rollup or tree operation is re-applied to the filtered source. This ensures that the aggregated results for a given user are derived from only those rows that the ACL permits.

For tree tables, the builder includes an additional `orphanPromotion` method (called `orphan_promotion` in Python). If orphans are not promoted, then when the ACL permits a row but not all of its parents, the child row is not accessible. If orphans are promoted, then when the ACL permits a child row but not its parent, the child row is displayed at the top level. Orphans are not promoted by default. The following example filters viewers to only rows with odd IDs, both with and without orphan promotion:

```groovy
import io.deephaven.enterprise.acl.EdgeAclProvider
import io.deephaven.enterprise.acl.AclFilterGenerator
import io.deephaven.api.filter.Filter;

src = newTable(intCol("ID", 1, 2, 3, 4, 5, 6, 7, 8, 9, 10),
        intCol("Parent", NULL_INT, NULL_INT, 1, 1, 2, 3, 5, 8, 13, 21)).update("OParent=Parent")

tree = src.tree("ID", "Parent")

def ACL1 = EdgeAclProvider.builder()
        .rowAcl("allusers", AclFilterGenerator.where("ID % 2 == 1"))
        .build()

treeOrphaned = ACL1.applyTo(tree)

def ACL2 = EdgeAclProvider.builder()
        .rowAcl("allusers", AclFilterGenerator.where("ID % 2 == 1"))
        .orphanPromotion(true)
        .build()

treeDeorphaned = ACL2.applyTo(tree)
```

```python
from deephaven_enterprise.edge_acl import EdgeAclProvider
import deephaven_enterprise.acl_generator as acl_generator


from deephaven import new_table
from deephaven.column import int_col

src = new_table(
    [
        int_col("ID", [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]),
        int_col("Parent", [None, None, 1, 1, 2, 3, 5, 8, 13, 21]),
    ]
).update("OParent=Parent")

tree = src.tree("ID", "Parent")

ACL = (
    EdgeAclProvider.builder()
    .row_acl("allusers", acl_generator.where("ID % 2 == 1"))
    .build()
)

tree_orphaned = ACL.apply_to(tree)

ACL = (
    EdgeAclProvider.builder()
    .row_acl("allusers", acl_generator.where("ID % 2 == 1"))
    .orphan_promotion(True)
    .build()
)

tree_deorphaned = ACL.apply_to(tree)
```

The parent identifier column is an important part of the table structure when traversing a tree. When a row is orphaned and subsequently promoted, the orphan promotion process sets the parent value to null, and the original value for the parent is no longer available. To account for this change, the example assigns the original parent value to the `OParent` column, which is not used as a parent identifier column so it is unchanged by the orphan promotion.

The [`AclFilterGenerator`](https://docs.deephaven.io/javadoc/coreplus/20240517/io/deephaven/enterprise/acl/AclFilterGenerator.html) class provides some helpful factory methods for commonly used ACL types. See the language API documentation for more details.

## Row ACLs

Row ACLs control access to the rows of a table based on the user requesting the table. These ACLs control the overall visibility of a table to users. For example, if the ACLs attached to a table do not contain any mappings for any group a user is a member of, that user is denied access to the table entirely.

When a user is denied access to a table, the server log contains an error message of the form `DheAuthorizationProvider.transform: Transformer TableTicketTransformer denied access with TableAccessException: User {iris operating as username} may not access this Table`. The user trying to retrieve the table is told the table is not found, with a message of the form `NOT_FOUND: Could not resolve 'sourceId: variable 'tableName'`.

## Column ACLs

Column ACLs allow you to restrict access to values within a column. You could use these to hide pricing data in a table from a specific group while allowing data in other columns to be present.

> [!CAUTION]
> Column ACLs require a Default ACL to be applied for unmentioned columns. If no default is provided, when Column ACLs are applied, values in any columns not explicitly mentioned are hidden from view.
>
> A simple default ACL would allow access to all unmentioned columns. The "\*" parameter only matches columns that have not already been matched by a more specific ACL.
>
> ```groovy
> aclBuilder.columnAcl("allusers", "*", AclFilterGenerator.fullAccess())
> ```
>
> ```python
> aclBuilder.column_acl("allusers", "*", acl_generator.full_access())
> ```

## Filter generators

Filter generators form the backbone of the ACL system. Since the system cannot apply filters to a table before it knows the user who is requesting access, it encodes the ACL as a set of instructions on how to _produce_ the filters, called filter generators.

A filter generator takes the user's credentials and produces one or more filters that are applied disjunctively to the table before providing the result to the user. There are a few built-in filter generator types that can be easily constructed using the `AclFilterGenerator` class.

### Where

```groovy
AclFilterGenerator.where(@NotNull String filter)
```

```python
acl_generator.where(filter: str)
```

This filter generator simply produces the provided where clause. This is useful when you need to apply a blanket filter to a table based on a group. For example:

```groovy
aclBuilder.rowAcl("MarketUs", AclFilterGenerator.where("Exchange in `NYSE`"))
```

```python
acl_builder.row_acl("MarketUs", acl_generator.where("Exchange in `NYSE`"))
```

When the requesting user is a member of the 'MarketUs' group, this produces a filter that selects only the rows in the 'Exchange' column that match 'NYSE' .

### Where In

The Where In filter generator is a bit more complicated than the others. This filter generator produces a whereIn clause using a "Set" table containing the user's grouping information.

```groovy
AclFilterGenerator.whereIn(String setNamespace, String setTableName, String setGroupColumn, String[] setFilters, boolean useHistorical, String... matchExpressions)

AclFilterGenerator.whereIn(Table setTable, String setGroupColumn, String[] setFilters, String... matchExpressions)
```

```python
acl_generator.where_in(set_namespace: str, set_table_name: str, set_group_column: str, set_filters: list[str], use_historical: bool, *match_expressions: str)

acl_generator.where_in_table(set_table: Table, set_group_column: str, set_filters: list[str], *match_expressions: str)
```

When the generator creates the filter, it filters the "Set" table down to only groups the user is a member of using the "groupColumn" parameter. Next, it applies a `whereIn` to the requested table using the filtered set table and the match expressions provided.

The "Set" table may be provided either as a `Namespace.TableName` pair to be fetched from the database, or directly as a Table instance.

### Group

This filter generator produces a filter that selects rows from the table where the value in the 'group' column matches a group the user belongs to.

```groovy
AclFilterGenerator.group(@NotNull String groupColumn)
```

```python
acl_generator.group(groupColumn: str)
```

In the next example, let's assume the user 'mark' is a member of 'Restricted', 'ETF', and 'Derivative', and the table has an ACL constructed like this:

```groovy
aclBuilder.rowAcl("Restricted", AclFilterGenerator.group("SecurityType"))
```

```python
acl_builder.row_acl("Restricted", acl_generator.group("SecurityType"))
```

Mark can only see rows in the table where 'SecurityType' is either 'ETF' or 'Derivative'.

### Conjunctive

The following filter generator simply combines the results of the parameter filter generators conjunctively; in other words, users can only see rows that match ALL of the provided ACLs.

```groovy
AclFilterGenerator.conjunctive(AclFilterGenerator... generators)
```

```python
acl_generator.conjunctive(generators: list[AclFilterGenerator])
```

### Disjunctive

The following filter generator simply combines the results of the parameter filter generators disjunctively; in other words, users can only see rows that match ANY of the provided ACLs.

```groovy
AclFilterGenerator.disjunctive(AclFilterGenerator... generators)
```

```python
acl_generator.disjunctive(generators: list[AclFilterGenerator])
```

### Full access

This filter generator simply gives the group full access to the table.

```groovy
AclFilterGenerator.fullAccess()
```

```python
acl_generator.full_access()
```

### No access

This filter generator denies all access to the table for this group. Note that ACLs are a union of group permissions, so if a user has permission to view data via another group, then they can still access the data.

```groovy
AclFilterGenerator.noAccess()
```

```python
acl_generator.no_access()
```

### Time-based filters

These filter generators restrict access based on the age of data:

<!-- TODO: Add in_window and out_of_window to deephaven_enterprise.acl_generator Python module -->

**In window** - Only permits rows with timestamps within the specified period:

```groovy
AclFilterGenerator.inWindow("Timestamp", "PT1H")  // Only data from the last hour
```

```python
import jpy

AclFilterGenerator = jpy.get_type("io.deephaven.enterprise.acl.AclFilterGenerator")
AclFilterGenerator.inWindow("Timestamp", "PT1H")
```

**Out of window** - Only permits rows with timestamps older than the specified period:

```groovy
AclFilterGenerator.outOfWindow("Timestamp", "P1D")  // Only data older than 1 day
```

```python
import jpy

AclFilterGenerator = jpy.get_type("io.deephaven.enterprise.acl.AclFilterGenerator")
AclFilterGenerator.outOfWindow("Timestamp", "P1D")
```

## Related documentation

- [Table ACLs](./table-acls.md)
- [Persistent Query Access Control](../../interfaces/web/permissions.md#persistent-query-access-control)
- [Permissions overview](./permissions-overview.md)