CREATE ROW POLICY

Warning

Row policies makes sense only for users with readonly access. If user can modify table or copy partitions between tables, it defeats the restrictions of row policies.

Syntax:

Allows to specify a condition to filter rows. An user will see a row if the condition is calculated to non-zero for the row.

In the section you can provide a list of users and roles this policy should work for. For example, CREATE ROW POLICY ... TO accountant, [[email protected]](https://clickhouse.com/cdn-cgi/l/email-protection).

Keyword ALL means all the ClickHouse users including current user. Keyword ALL EXCEPT allow to exclude some users from the all users list, for example, CREATE ROW POLICY ... TO ALL EXCEPT accountant, [[email protected]](https://clickhouse.com/cdn-cgi/l/email-protection)

Note

CREATE ROW POLICY pol1 ON mydb.table1 USING b=1 TO mira, peter

forbids the users mira and to see the rows with b != 1, and any non-mentioned user (e.g., the user paul) will see no rows from mydb.table1 at all.

If that’s not desirable it can’t be fixed by adding one more row policy, like the following:

CREATE ROW POLICY pol2 ON mydb.table1 USING 1 TO ALL EXCEPT mira, peter

It’s allowed to have more than one policy enabled on the same table for the same user at the 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

enables the user peter to see rows with either b=1 or .

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

Here is the general formula:

For example, the following policies

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

Allows creating row policies on a cluster, see Distributed DDL.

CREATE ROW POLICY filter1 ON mydb.mytable USING a<1000 TO accountant, [[email protected]](https://clickhouse.com/cdn-cgi/l/email-protection)