> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for Row Policy

# CREATE ROW POLICY

Creates a [row policy](/concepts/features/security/access-rights#row-policy-management), i.e. a filter used to determine which rows a user can read from a table.

<Tip>
  Row policies make sense only for users with readonly access. If a user can modify a table or copy partitions between tables, it defeats the restrictions of row policies.
</Tip>

Syntax:

```sql theme={null}
-- Multiple names on one table target
CREATE [ROW] POLICY [IF NOT EXISTS | OR REPLACE] policy_name [, ...]
    [ON CLUSTER cluster_name]
    ON { [db.]table | db.* }
    [IN access_storage_type]
    [[FOR SELECT] USING {condition | NONE}]
    [AS {PERMISSIVE | RESTRICTIVE}]
    [TO {role1 [, role2 ...] | ALL | ALL EXCEPT role1 [, role2 ...]}]

-- One name on multiple table targets
CREATE [ROW] POLICY [IF NOT EXISTS | OR REPLACE] policy_name
    [ON CLUSTER cluster_name]
    ON { [db.]table | db.* } [, ...]
    [IN access_storage_type]
    [[FOR SELECT] USING {condition | NONE}]
    [AS {PERMISSIVE | RESTRICTIVE}]
    [TO {role1 [, role2 ...] | ALL | ALL EXCEPT role1 [, role2 ...]}]

-- Mixed packing: each name paired with its own table target
CREATE [ROW] POLICY [IF NOT EXISTS | OR REPLACE]
    policy_name ON { [db.]table | db.* } [, policy_name ON { [db.]table | db.* } ...]
    [ON CLUSTER cluster_name]
    [IN access_storage_type]
    [[FOR SELECT] USING {condition | NONE}]
    [AS {PERMISSIVE | RESTRICTIVE}]
    [TO {role1 [, role2 ...] | ALL | ALL EXCEPT role1 [, role2 ...]}]
```

`ParserRowPolicyNames` accepts **three** packing forms (not a full Cartesian product):

1. **Multiple names, one target** — `pol1, pol2 ON table1` creates each listed name on that single table (or `db.*`).
2. **One name, multiple targets** — `pol1 ON table1, table2` creates the same short name on each listed target.
3. **Mixed pairs** — `p1 ON t1, p2 ON t2` creates each name only on its paired target.

A multi-name list **cannot** be combined with a multi-table `ON` list in one group: `p1, p2 ON t1, t2` is rejected. After a multi-name group, you also cannot append another comma-separated `name ON target` group in the same statement.

Optional `ON CLUSTER` applies to the whole statement (one cluster name). ClickHouse does **not** accept a different `ON CLUSTER` per policy name packed into a single create — run separate `CREATE ROW POLICY` statements when policies must be created on different clusters.

`CREATE ROW POLICY` requires the [CREATE ROW POLICY](/reference/statements/grant#access-management) privilege on the table the policy is created on. `OR REPLACE` throws away an existing policy of the same name, including which roles it applies to, so it additionally requires the [DROP ROW POLICY](/reference/statements/grant#access-management) privilege on that table. The `DROP ROW POLICY` privilege is required whether or not the policy already exists, so the statement cannot be used to find out which policies exist.

<h2 id="multiple-names-and-tables">
  Multiple names and tables
</h2>

Valid:

```sql theme={null}
-- Several policy names, one table
CREATE ROW POLICY pol1, pol2, pol3 ON table1
    FOR SELECT USING id = 1
    TO accountant;

-- One policy name, several tables
CREATE ROW POLICY IF NOT EXISTS pol1 ON table1, table2, table3
    FOR SELECT USING id = 1
    TO accountant;

-- Mixed packing: different name per table
CREATE ROW POLICY p4 ON db.table, p5 ON db2.table2
    USING a = b;

-- Same policy on several tables, on a cluster
CREATE ROW POLICY IF NOT EXISTS pol1 ON CLUSTER replicated_cluster ON table1, table2
    FOR SELECT USING id = 1
    TO accountant;
```

Invalid:

```sql theme={null}
-- Multi-name × multi-table in one ON-group (not a Cartesian product)
CREATE ROW POLICY p1, p2 ON t1, t2
    FOR SELECT USING id = 1
    TO accountant;

-- Different clusters per name in one statement
CREATE ROW POLICY pol1 ON CLUSTER cluster1 ON table1, pol2 ON CLUSTER cluster2 ON table2
```

<h2 id="using-clause">
  USING clause
</h2>

Defines a filter condition for a table. A user can only see rows for which the condition is true (evaluates to a non-zero value). This is similar to adding an extra `WHERE` condition to every query the user runs against the table.

For example, the following policy limits `analyst_role` to rows from the EU:

```sql theme={null}
CREATE ROW POLICY region_filter ON db.orders
USING region = 'EU'
TO analyst_role;
```

With this policy, `SELECT * FROM db.orders` returns the same rows as `SELECT * FROM db.orders WHERE region = 'EU'` would.

<h2 id="to-clause">
  TO Clause
</h2>

In the `TO` section you can provide a list of users and roles this policy should work for. For example, `CREATE ROW POLICY ... TO accountant, john@localhost`.

Keyword `ALL` means all the ClickHouse users, including current user. Keyword `ALL EXCEPT` allows excluding some users from the all users list, for example, `CREATE ROW POLICY ... TO ALL EXCEPT accountant, john@localhost`

Roles named in the `TO` section, including those after `ALL EXCEPT`, are matched against the current user's enabled roles ([`system.enabled_roles`](/reference/system-tables/enabled_roles)), not against every role granted to the user, so [`SET ROLE`](/reference/statements/set-role) can change which policies apply.

<h2 id="as-clause">
  AS Clause
</h2>

It's allowed to have more than one policy enabled on the same table for the same user at one time. So we need a way to combine the conditions from multiple policies.

By default, policies are combined using the boolean `OR` operator. For example, the following policies:

```sql theme={null}
CREATE ROW POLICY pol1 ON mydb.table1 USING b=1 TO mira, peter
CREATE ROW POLICY pol2 ON mydb.table1 USING c=2 TO peter, antonio
```

enable the user `peter` to see rows with either `b=1` or `c=2`.

The `AS` clause specifies how policies should be combined with other policies. Policies can be either permissive or restrictive. By default, policies are permissive, which means they are combined using the boolean `OR` operator.

A policy can be defined as restrictive as an alternative. Restrictive policies are combined using the boolean `AND` operator.

Here is the general formula:

```text theme={null}
row_is_visible = (one or more of the conditions from the permissive policies that apply to the current user and their enabled roles are non-zero) AND
                 (all of the conditions from the restrictive policies that apply to the current user and their enabled roles are non-zero)
```

If no permissive condition applies, the first condition has no effect and only the restrictive policies decide, because `access_control_improvements.users_without_row_policies_can_read_rows` is enabled by default. A user to whom no condition applies therefore sees every row, and `access_control_improvements.throw_on_unmatched_row_policies`, disabled by default, raises an exception instead when the table does have conditions and none of them apply.

For example, the following policies:

```sql theme={null}
CREATE ROW POLICY pol1 ON mydb.table1 USING b=1 TO mira, peter
CREATE ROW POLICY pol2 ON mydb.table1 USING c=2 AS RESTRICTIVE TO peter, antonio
```

enable the user `peter` to see rows only if both `b=1` AND `c=2`.

Database policies are combined with table policies.

For example, the following policies:

```sql theme={null}
CREATE ROW POLICY pol1 ON mydb.* USING b=1 TO mira, peter
CREATE ROW POLICY pol2 ON mydb.table1 USING c=2 AS RESTRICTIVE TO peter, antonio
```

enable the user `peter` to see table1 rows only if both `b=1` AND `c=2`, although
any other table in mydb would have only `b=1` policy applied for the user.

<h2 id="tables-that-read-from-other-tables">
  Tables that read from other tables
</h2>

A row policy filters rows where the data is actually read. An `Alias` table returns the rows of its target table as its own, so the row policies of the target apply to reads through the alias as well, combined with the policies of the alias itself using a logical `AND`. A `Merge` table applies the policies of the tables it reads from. One exception: when a matched table reads remotely, such as a `Distributed` table, the remote server processes the query before the policy is applied, because the policy runs above that table's read rather than at the read. Such a query can fail, when it aggregates without selecting the policy's columns, or return fewer rows than the policy allows, when the remote server applies an `ORDER BY ... LIMIT` to rows the policy would have hidden. Define the policy on the underlying local tables of each remote server instead.

This does not extend to every table that reads from another table. A `Buffer` table and a materialized view read through their destination or target table do **not** inherit that table's row policies: the policy is written against the target's schema and, for a view with `SQL SECURITY DEFINER`, is evaluated for a different user than the one running the read. Define the policy on the table users actually query in those cases.

<h2 id="distributed-and-remote-backed-tables">
  Distributed and remote-backed tables
</h2>

A row policy filters rows where the table data is actually read. A table that delegates reading to remote servers, such as a [Distributed](/reference/engines/table-engines/special/distributed) table or a wrapper over one (for example, a materialized view with a `Distributed` target), only ships the query text to the remote servers and cannot apply the policy filter to the remote read. To keep the filter from being silently dropped, queries to such a table by users the policy applies to are rejected with an `ILLEGAL_PREWHERE` error.

Instead, define the policy on the underlying local tables on each remote server; it is applied there when the shipped query reads them:

```sql theme={null}
-- Filters reads of local_table on this server, including reads shipped by a Distributed table over it.
CREATE ROW POLICY filter ON mydb.local_table USING a < 1000 TO john;
```

<Warning>
  This works while the query is shipped as text, which is the default. With [`serialize_query_plan = 1`](/reference/settings/session-settings/serialize#serialize_query_plan) the initiator ships an already-built read plan instead, and a remote server executing such a plan does not apply its own row policies, so a read of a `Distributed` table over `local_table` returns unfiltered rows. Keep `serialize_query_plan = 0` for users whose row policies must be enforced. See [issue #112891](https://github.com/ClickHouse/ClickHouse/issues/112891).
</Warning>

<h2 id="join-tables">
  Join tables
</h2>

A [Join](/reference/engines/table-engines/special/join) table is a prepared hash table that a `JOIN` or `joinGet` reads as is, so its rows cannot be filtered there. A policy on such a table, including a database-wide `ON db.*` policy, filters a plain `SELECT` from the table, but while it applies, `JOIN` and `joinGet` queries against the table fail with `ACCESS_DENIED`.

<h2 id="on-cluster-clause">
  ON CLUSTER Clause
</h2>

Allows creating row policies on a cluster, see [Distributed DDL](/reference/statements/distributed-ddl). This is also the convenient way to create the policy on the local tables of every server of the cluster.

<h2 id="examples">
  Examples
</h2>

`CREATE ROW POLICY filter1 ON mydb.mytable USING a<1000 TO accountant, john@localhost`

`CREATE ROW POLICY filter2 ON mydb.mytable USING a<1000 AND b=5 TO ALL EXCEPT mira`

`CREATE ROW POLICY filter3 ON mydb.mytable USING 1 TO admin`

`CREATE ROW POLICY filter4 ON mydb.* USING 1 TO admin`