SET ROLE

Activates roles for the current user.

SET ROLE {DEFAULT | NONE | role [,...] | ALL | ALL EXCEPT role [,...]}

SET DEFAULT ROLE

Sets default roles to a user. Default roles are automatically activated at user login. You can set as default only the previously granted roles. If the role isn’t granted to a user, ClickHouse throws an exception.

SET DEFAULT ROLE {NONE | role [,...] | ALL | ALL EXCEPT role [,...]} TO {user|CURRENT_USER} [,...] [ON CLUSTER cluster_name]

ON CLUSTER is accepted either right after SET DEFAULT ROLE or at the end of the query, and executes it on every host of the cluster. CURRENT_USER is resolved on the initiator before the query is distributed. SET ROLE only changes the current session and therefore takes no ON CLUSTER clause.

Examples

Set multiple default roles to a user:

SET DEFAULT ROLE role1, role2, ... TO user

Set all the granted roles as default to a user:

SET DEFAULT ROLE ALL TO user

Purge default roles from a user:

SET DEFAULT ROLE NONE TO user

Set all the granted roles as default except for specific roles role1 and role2:

SET DEFAULT ROLE ALL EXCEPT role1, role2 TO user

Set default roles on every host of a cluster:

SET DEFAULT ROLE role1 TO user ON CLUSTER my_cluster
SET DEFAULT ROLE ON CLUSTER my_cluster role1 TO user

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

SET DEFAULT ROLE

Examples

​SET DEFAULT ROLE

​Examples

SET DEFAULT ROLE

Examples