Common access management queries

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/cloud/guides/security/01_cloud_access_management/06_common-access-management-queries.md)

Self-managed

If you're working with self-managed ClickHouse please see [SQL users and roles](/docs/operations/access-rights).

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

## Admin user[​](#admin-user "Direct link to Admin user")

ClickHouse Cloud services have an admin user, `default`, that is created when the service is created. The password is provided at service creation, and it can be reset by ClickHouse Cloud users that have the **Admin** role.

When you add additional SQL users for your ClickHouse Cloud service, they will need a SQL username and password. If you want them to have administrative-level privileges, then assign the new users the role `default_role`. For example, adding user `clickhouse_admin`:

```
CREATE USER IF NOT EXISTS clickhouse_admin
IDENTIFIED WITH sha256_password BY 'P!@ssword42!';
```

```
GRANT default_role TO clickhouse_admin;
```

Note

When using the SQL Console, your SQL statements won't be run as the `default` user. Instead, statements will be run as a user named `sql-console:${cloud_login_email}`, where `cloud_login_email` is the email of the user currently running the query.

These automatically generated SQL Console users have the `default` role.

## Passwordless authentication[​](#passwordless-authentication "Direct link to Passwordless authentication")

There are two roles available for SQL console: `sql_console_admin` with identical permissions to `default_role` and `sql_console_read_only` with read-only permissions.

Admin users are assigned the `sql_console_admin` role by default, so nothing changes for them. However, the `sql_console_read_only` role allows non-admin users to be granted read-only or full access to any instance. An admin needs to configure this access. The roles can be adjusted using the `GRANT` or `REVOKE` commands to better fit instance-specific requirements, and any modifications made to these roles will be persisted.

### Granular access control[​](#granular-access-control "Direct link to Granular access control")

This access control functionality can also be configured manually for user-level granularity. Before assigning the new `sql_console_*` roles to users, SQL console user-specific database roles matching the namespace `sql-console-role:<email>` should be created. For example:

```
CREATE ROLE OR REPLACE sql-console-role:<email>;
GRANT <some grants> TO sql-console-role:<email>;
```

When a matching role is detected, it will be assigned to the user instead of the boilerplate roles. This introduces more complex access control configurations, such as creating roles like `sql_console_sa_role` and `sql_console_pm_role`, and granting them to specific users. For example:

```
CREATE ROLE OR REPLACE sql_console_sa_role;
GRANT <whatever level of access> TO sql_console_sa_role;
CREATE ROLE OR REPLACE sql_console_pm_role;
GRANT <whatever level of access> TO sql_console_pm_role;
CREATE ROLE OR REPLACE `sql-console-role:[email protected]`;
CREATE ROLE OR REPLACE `sql-console-role:[email protected]`;
CREATE ROLE OR REPLACE `sql-console-role:[email protected]`;
GRANT sql_console_sa_role to `sql-console-role:[email protected]`;
GRANT sql_console_sa_role to `sql-console-role:[email protected]`;
GRANT sql_console_pm_role to `sql-console-role:[email protected]`;
```

## Test admin privileges[​](#test-admin-privileges "Direct link to Test admin privileges")

Log out as the user `default` and log back in as user `clickhouse_admin`.

All of these should succeed:

```
SHOW GRANTS FOR clickhouse_admin;
```

```
CREATE DATABASE db1
```

```
CREATE TABLE db1.table1 (id UInt64, column1 String) ENGINE = MergeTree() ORDER BY id;
```

```
INSERT INTO db1.table1 (id, column1) VALUES (1, 'abc');
```

```
SELECT * FROM db1.table1;
```

```
DROP TABLE db1.table1;
```

```
DROP DATABASE db1;
```

## Non-admin users[​](#non-admin-users "Direct link to Non-admin users")

Users should have the privileges necessary, and not all be admin users. The rest of this document provides example scenarios and the roles required.

### Preparation[​](#preparation "Direct link to Preparation")

Create these tables and users to be used in the examples.

#### Creating a sample database, table, and rows[​](#creating-a-sample-database-table-and-rows "Direct link to Creating a sample database, table, and rows")

Create a test database

##### Create a test database[​](#create-a-test-database "Direct link to Create a test database")

```
CREATE DATABASE db1;
```

Create a table

##### Create a table[​](#create-a-table "Direct link to Create a table")

```
CREATE TABLE db1.table1 (
   id UInt64,
   column1 String,
   column2 String
)
ENGINE MergeTree
ORDER BY id;
```

Populate the table with sample rows

##### Populate the table with sample rows[​](#populate "Direct link to Populate the table with sample rows")

```
INSERT INTO db1.table1
   (id, column1, column2)
VALUES
   (1, 'A', 'abc'),
   (2, 'A', 'def'),
   (3, 'B', 'abc'),
   (4, 'B', 'def');
```

Verify the table

##### Verify the table[​](#verify "Direct link to Verify the table")

```
SELECT *
FROM db1.table1
```

```
Query id: 475015cc-6f51-4b20-bda2-3c9c41404e49

┌─id─┬─column1─┬─column2─┐
│  1 │ A       │ abc     │
│  2 │ A       │ def     │
│  3 │ B       │ abc     │
│  4 │ B       │ def     │
└────┴─────────┴─────────┘
```

Create

##### Create `column_user`[​](#create-a-user-with-restricted-access-to-columns "Direct link to create-a-user-with-restricted-access-to-columns")

Create a regular user that will be used to demonstrate restrict access to certain columns:

```
CREATE USER column_user IDENTIFIED BY 'password';
```

Create

##### Create `row_user`[​](#create-a-user-with-restricted-access-to-rows-with-certain-values "Direct link to create-a-user-with-restricted-access-to-rows-with-certain-values")

Create a regular user that will be used to demonstrate restricting access to rows with certain values:

```
CREATE USER row_user IDENTIFIED BY 'password';
```

#### Creating roles[​](#creating-roles "Direct link to Creating roles")

With this set of examples:

- roles for different privileges, such as columns and rows will be created
- privileges will be granted to the roles
- users will be assigned to each role

Roles are used to define groups of users for certain privileges instead of managing each user separately.

Create a role to restrict users of this role to only see in database and :

##### Create a role to restrict users of this role to only see `column1` in database `db1` and `table1`:[​](#create-column-role "Direct link to create-column-role")

```
CREATE ROLE column1_users;
```

Set privileges to allow view on

##### Set privileges to allow view on `column1`[​](#set-column-privileges "Direct link to set-column-privileges")

```
GRANT SELECT(id, column1) ON db1.table1 TO column1_users;
```

Add the user to the role

##### Add the `column_user` user to the `column1_users` role[​](#add-column-user-to-role "Direct link to add-column-user-to-role")

```
GRANT column1_users TO column_user;
```

Create a role to restrict users of this role to only see selected rows, in this case, only rows containing in

##### Create a role to restrict users of this role to only see selected rows, in this case, only rows containing `A` in `column1`[​](#create-row-role "Direct link to create-row-role")

```
CREATE ROLE A_rows_users;
```

Add the to the role

##### Add the `row_user` to the `A_rows_users` role[​](#add-row-user-to-role "Direct link to add-row-user-to-role")

```
GRANT A_rows_users TO row_user;
```

Create a policy to allow view on only where has the values of

##### Create a policy to allow view on only where `column1` has the values of `A`[​](#create-row-policy "Direct link to create-row-policy")

```
CREATE ROW POLICY A_row_filter ON db1.table1 FOR SELECT USING column1 = 'A' TO A_rows_users;
```

Set privileges to the database and table