Skip to main content
Skip to main content
Edit this page

Creating Users and Roles in ClickHouse

ClickHouse supports access control management based on RBAC approach.

ClickHouse access entities:

You can configure access entities using:

We recommend using SQL-driven workflow. Both of the configuration methods work simultaneously, so if you use the server configuration files for managing accounts and access rights, you can smoothly switch to SQL-driven workflow.

note

You can't manage the same access entity by both configuration methods simultaneously.

note

If you are looking to manage ClickHouse Cloud Console users, please refer to this page

To see all users, roles, profiles, etc. and all their grants use SHOW ACCESS statement.

Overview​

By default, the ClickHouse server provides the default user account which is not allowed using SQL-driven access control and account management but has all the rights and permissions. The default user account is used in any cases when the username is not defined, for example, at login from client or in distributed queries. In distributed query processing a default user account is used, if the configuration of the server or cluster does not specify the user and password properties.

If you just started using ClickHouse, consider the following scenario:

  1. Enable SQL-driven access control and account management for the default user.
  2. Log in to the default user account and create all the required users. Don't forget to create an administrator account (GRANT ALL ON *.* TO admin_user_account WITH GRANT OPTION).
  3. Restrict permissions for the default user and disable SQL-driven access control and account management for it.

Properties of Current Solution​

  • You can grant permissions for databases and tables even if they do not exist.
  • If a table is deleted, all the privileges that correspond to this table are not revoked. This means that even if you create a new table with the same name later, all the privileges remain valid. To revoke privileges corresponding to the deleted table, you need to execute, for example, the REVOKE ALL PRIVILEGES ON db.table FROM ALL query.
  • There are no lifetime settings for privileges.

User Account​

A user account is an access entity that allows to authorize someone in ClickHouse. A user account contains:

  • Identification information.
  • Privileges that define the scope of queries the user can execute.
  • Hosts are allowed to connect to the ClickHouse server.
  • Assigned and default roles.
  • Settings with their constraints applied by default at user login.
  • Assigned settings profiles.

Privileges can be granted to a user account by the GRANT query or by assigning roles. To revoke privileges from a user, ClickHouse provides the REVOKE query. To list privileges for a user, use the SHOW GRANTS statement.

Management queries:

Settings Applying​

Settings can be configured differently: for a user account, in its granted roles and in settings profiles. At user login, if a setting is configured for different access entities, the value and constraints of this setting are applied as follows (from higher to lower priority):

  1. User account settings.
  2. The settings for the default roles of the user account. If a setting is configured in some roles, then order of the setting application is undefined.
  3. The settings from settings profiles assigned to a user or to its default roles. If a setting is configured in some profiles, then order of setting application is undefined.
  4. Settings applied to the entire server by default or from the default profile.

Role​

A role is a container for access entities that can be granted to a user account.

A role contains:

  • Privileges
  • Settings and constraints
  • List of assigned roles

Management queries:

Privileges can be granted to a role by the GRANT query. To revoke privileges from a role ClickHouse provides the REVOKE query.

Row Policy​

Row policy is a filter that defines which of the rows are available to a user or a role. Row policy contains filters for one particular table, as well as a list of roles and/or users which should use this row policy.

note

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

Management queries:

Settings Profile​

Settings profile is a collection of settings. Settings profile contains settings and constraints, as well as a list of roles and/or users to which this profile is applied.

Management queries:

Quota​

Quota limits resource usage. See Quotas.

Quota contains a set of limits for some durations, as well as a list of roles and/or users which should use this quota.

Management queries:

Enabling SQL-driven Access Control and Account Management​

  • Setup a directory for configuration storage.

    ClickHouse stores access entity configurations in the folder set in the access_control_path server configuration parameter.

  • Enable SQL-driven access control and account management for at least one user account.

    By default, SQL-driven access control and account management is disabled for all users. You need to configure at least one user in the users.xml configuration file and set the values of the access_management, named_collection_control, show_named_collections, and show_named_collections_secrets settings to 1.

Defining SQL Users and Roles​

tip

If you are working in ClickHouse Cloud, please see Cloud access management.

This article shows the basics of defining SQL users and roles and applying those privileges and permissions to databases, tables, rows, and columns.

Enabling SQL user mode​

  1. Enable SQL user mode in the users.xml file under the <default> user:

    note

    The default user is the only user that gets created with a fresh install, and is also the account used for internode communications, by default.

    In production, it is recommended to disable this user once the inter-node communication has been configured with a SQL admin user and internode communications have been set with <secret>, cluster credentials, and/or internode HTTP and transport protocol credentials since the default account is used for internode communication.

  2. Restart the nodes to apply the changes.

  3. Start the ClickHouse client:

Defining users​

  1. Create a SQL administrator account:
  2. Grant the new user full administrative rights

ALTER permissions​

This article is intended to provide you with a better understanding of how to define permissions, and how permissions work when using ALTER statements for privileged users.

The ALTER statements are divided into several categories, some of which are hierarchical and some of which are not and must be explicitly defined.

Example DB, table and user configuration

  1. With an admin user, create a sample user
  1. Create sample database
  1. Create a sample table
  1. Create a sample admin user to grant/revoke privileges
note

To grant or revoke permissions, the admin user must have the WITH GRANT OPTION privilege. For example:

To GRANT or REVOKE privileges, the user must have those privileges themselves first.

Granting or Revoking Privileges

The ALTER hierarchy:

  1. Granting ALTER Privileges to a User or Role

Using an GRANT ALTER on *.* TO my_user will only affect top-level ALTER TABLE and ALTER VIEW , other ALTER statements must be individually granted or revoked.

for example, granting basic ALTER privilege:

Resulting set of privileges:

This will grant all permissions under ALTER TABLE and ALTER VIEW from the example above, however, it will not grant certain other ALTER permissions such as ALTER ROW POLICY (Refer back to the hierarchy and you will see that ALTER ROW POLICY is not a child of ALTER TABLE or ALTER VIEW). Those must be explicitly granted or revoked.

If only a subset of ALTER permissions is needed then each can be granted separately, if there are sub-privileges to that permission then those would be automatically granted also.

For example:

Grants would be set as:

This also gives the following sub-privileges:

  1. Revoking ALTER privileges from Users and Roles

The REVOKE statement works similarly to the GRANT statement.

If a user/role was granted a sub-privilege, you can either revoke that sub-privilege directly or revoke the higher-level privilege it inherits from.

For example, if the user was granted ALTER ADD COLUMN

A privilege can be revoked individually:

Or can be revoked from any of the upper levels (revoke all of the COLUMN sub privileges):

Additional

The privileges must be granted by a user that not only has the WITH GRANT OPTION but also has the privileges themselves.

  1. To grant an admin user the privilege and also allow them to administer a set of privileges Below is an example:

Now the user can grant or revoke ALTER COLUMN and all sub-privileges.

Testing

  1. Add the SELECT privilege
  1. Add the add column privilege to the user
  1. Log in with the restricted user
  1. Test adding a column
  1. Test deleting a column
  1. Testing the alter admin by granting the permission
  1. Log in with the alter admin user
  1. Grant a sub-privilege
  1. Test granting a privilege that the alter admin user does not have is not a sub privilege of the grants for the admin user.

Summary The ALTER privileges are hierarchical for ALTER with tables and views but not for other ALTER statements. The permissions can be set in granular level or by grouping of permissions and also revoked similarly. The user granting or revoking must have WITH GRANT OPTION to set privileges on users, including the acting user themselves, and must have the privilege already. The acting user cannot revoke their own privileges if they do not have the grant option privilege themselves.