CREATE ROW POLICY

Creates filters for rows, which a user can read from a table.

Syntax:

  1. CREATE [ROW] POLICY [IF NOT EXISTS | OR REPLACE] policy_name1 [ON CLUSTER cluster_name1] ON [db1.]table1 
  2.         [, policy_name2 [ON CLUSTER cluster_name2] ON [db2.]table2 ...] 
  3.     [AS {PERMISSIVE | RESTRICTIVE}]
  4.     [FOR SELECT]
  5.     [USING condition]
  6.     [TO {role1 [, role2 ...] | ALL | ALL EXCEPT role1 [, role2 ...]}]

ON CLUSTER clause allows creating row policies on a cluster, see Distributed DDL.

AS Clause

Using this section you can create permissive or restrictive policies.

Permissive policy grants access to rows. Permissive policies which apply to the same table are combined together using the boolean OR operator. Policies are permissive by default.

Restrictive policy restricts access to rows. Restrictive policies which apply to the same table are combined together using the boolean AND operator.

Restrictive policies apply to rows that passed the permissive filters. If you set restrictive policies but no permissive policies, the user can’t get any row from the table.

TO Clause

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

Keyword ALL means all the ClickHouse users including current user. Keywords 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.tech/cdn-cgi/l/email-protection)

Examples

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

CREATE ROW POLICY filter ON mydb.mytable FOR SELECT USING a<1000 TO ALL EXCEPT mira