Grant privileges

ClickHouse® supports a Role Based Access Control model and allows you to configure access privileges by using SQL statements. You can either use the query editor or rely on the command-line interface.

This article shows you examples of how to create roles and grant privileges. The ClickHouse documentation includes detailed documentation for access rights.

Create a new role

To create a new role named auditor, run the following command:

CREATE ROLE auditor;

You can find more information on role creation here.

Grant permissions

You can grant permissions both to specific roles and to individual users. The grants can be also granular, targeting specific databases, tables, columns, or rows.

For example, the following request grants the auditor role permissions to select data from the transactions database:

GRANT SELECT ON transactions.* TO auditor;

You can limit the grant to a specified table:

GRANT SELECT ON transactions.expenses TO auditor;

Or to particular columns of a table:

GRANT SELECT(date,description,amount) ON transactions.expenses TO auditor

To grant the auditor and external roles to several users, run:

GRANT auditor, external TO Mary.Anderson, James.Miller;

To allow the creation of new users:

GRANT CREATE USER ON transactions.* TO administrator

There are a variety of privileges that you can grant, and you can find the full list in the ClickHouse documentation.

Note

You can grant permissions to a table that does not yet exist.

Note

Users can grant permissions according to their privileges. If the user lacks the required permissions for a requested operation, they receive a Not enough privileges exception.

Warning

Privileges are not revoked when a table or database is removed. They continue to be active for any new table or database that is created with the same name.

You can find the full list of supported functionality related to grants in the ClickHouse documentation.

Set roles

A single user can be assigned different roles, either individually or simultaneously.

SET ROLE auditor;

You can also specify a role to be activated by default when the user logs in:

SET DEFAULT ROLE auditor, external TO Mary.Anderson, James.Miller;

Delete a role

If you no longer need a role, you can remove it:

DROP ROLE auditor;

Revoke permissions

Remove all or specific privileges from users or roles:

REVOKE SELECT ON transactions.expenses FROM Mary.Anderson;

Revoke all privileges to a table or database simultaneously:

REVOKE ALL PRIVILEGES ON database.table FROM external;

See the ClickHouse documentation for more information on revoking privileges.

See current permissions

Run the following commands to see all available grants, users, and roles:

SHOW GRANTS;
SHOW USERS;
SHOW ROLES;

See users and roles in the console

You can also see the users, their roles and permissions in the Aiven web console. You will find these on the Users & Roles tab of your service. Next to every user there is a View Details & Grants button that shows you a list of all grants for that user.