CREATE ROW POLICY

Creates a row policy, i.e. a filter used to determine which 行 a user can read from a テーブル.

ヒント

Row policies make sense only for users with readonly access. If a user can modify a テーブル or copy パーティション between テーブル, it defeats the restrictions of row policies.

Syntax:

USING Clause

Allows specifying a condition to filter 行. A user will see a 行 if the condition is calculated to non-zero for the 行.

TO Clause

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

注記

If there are no row policies defined for a テーブル, then any user can SELECT all the 行 from the テーブル. Defining one or more row policies for the テーブル makes access to the テーブル dependent on the row policies, no matter if those row policies are defined for the current user or not. For example, the following policy:

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

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

If that's not desirable, it can 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

AS Clause

It's allowed to have more than one policy enabled on the same テーブル 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:

enable the user peter to see 行 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:

For example, the following policies:

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

Database policies are combined with テーブル policies.

For example, the following policies:

enable the user peter to see table1 行 only if both b=1 AND c=2, although any other テーブル in mydb would have only b=1 policy applied for the user.

ON CLUSTER Clause

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

Examples

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