Manage database users

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/cloud/guides/security/01_cloud_access_management/04_manage-database-users.md)

This guide demonstrates two ways to manage database users, within SQL console and directly within the database.

### SQL console passwordless authentication[​](#sql-console-passwordless-authentication "Direct link to SQL console passwordless authentication")

SQL console users are created for each session and authenticated using X.509 certificates that are automatically rotated. The user is removed when the session is terminated. When generating access lists for audits, please navigate to the Settings tab for the service in the console and note the SQL console access in addition to the database users that exist in the database. If custom roles are configured, the user's access is listed in the role ending with the user's username.

## SQL console users and roles[​](#sql-console-users-and-roles "Direct link to SQL console users and roles")

Basic SQL console roles can be assigned to users with Service Read Only and Service Admin permissions. For more information, refer to [Manage SQL Console Role Assignments](/docs/cloud/guides/sql-console/manage-sql-console-role-assignments). This guide demonstrates how to create a custom role for a SQL console user.

To create a custom role for a SQL console user and grant it a general role, run the following commands. The email address must match the user's email address in the console.

Create and grant permissions

#### Create `database_developer` and grant permissions[​](#create-role-grant-permissions "Direct link to create-role-grant-permissions")

Create the `database_developer` role and grant `SHOW`, `CREATE`, `ALTER`, and `DELETE` permissions.

```
CREATE ROLE OR REPLACE database_developer;
GRANT SHOW ON * TO database_developer;
GRANT CREATE ON * TO database_developer;
GRANT ALTER ON * TO database_developer;
GRANT DELETE ON * TO database_developer;
```

Create SQL console user role

#### Create SQL console user role[​](#create-sql-console-user-role "Direct link to Create SQL console user role")

Create a role for the SQL console user [[email protected]](/cdn-cgi/l/email-protection#92ffebbce7e1f7e0d2f6fdfff3fbfcbcf1fdff) and assign it the database\_developer role.

```
CREATE ROLE OR REPLACE `sql-console-role:[email protected]`;
GRANT database_developer TO `sql-console-role:[email protected]`;
```

The user is assigned the new role when they use SQL console

#### The user is assigned the new role when they use SQL console[​](#use-assigned-new-role "Direct link to The user is assigned the new role when they use SQL console")

The user will be assigned the role associated with their email address whenever they use SQL console.

## Database authentication[​](#database-authentication "Direct link to Database authentication")

### Database user ID and password[​](#database-user-id--password "Direct link to Database user ID and password")

Use the SHA256\_hash method when [creating user accounts](/docs/sql-reference/statements/create/user) to secure passwords. ClickHouse database passwords must contain a minimum of 12 characters and meet complexity requirements: upper case characters, lower case characters, numbers and/or special characters.

Generate passwords securely

Since users with less than administrative privileges can't set their own password, ask the user to hash their password using a generator
such as [this one](https://tools.keycdn.com/sha256-online-generator) before providing it to the admin to setup the account.

```
CREATE USER userName IDENTIFIED WITH sha256_hash BY 'hash';
```

### Database user with secure shell (SSH) authentication[​](#database-ssh "Direct link to Database user with secure shell (SSH) authentication")

To set up SSH authentication for a ClickHouse Cloud database user.

1. Use ssh-keygen to create a keypair.
2. Use the public key to create the user.
3. Assign roles and/or permissions to the user.
4. Use the private key to authenticate against the service.

For a detailed walkthrough with examples, check out [How to connect to ClickHouse Cloud using SSH keys](/docs/knowledgebase/how-to-connect-to-ch-cloud-using-ssh-keys) in our Knowledgebase.

## Database permissions[​](#database-permissions "Direct link to Database permissions")

Configure the following within the services and databases using the SQL [GRANT](/docs/sql-reference/statements/grant) statement.

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Role Description|  |  |  |  | | --- | --- | --- | --- | | Default Full administrative access to services|  |  | | --- | --- | | Custom Configure using the SQL [`GRANT`](/docs/sql-reference/statements/grant) statement | | | | | |

- Database roles are additive. This means if a user is a member of two roles, the user has the most access granted to the two roles. They don't lose access by adding roles.
- Database roles can be granted to other roles, resulting in a hierarchical structure. Roles inherit all permissions of the roles for which it is a member.
- Database roles are unique per service and may be applied across multiple databases within the same service.

The illustration below shows the different ways a user could be granted permissions.

### Initial settings[​](#initial-settings "Direct link to Initial settings")

Databases have an account named `default` that is added automatically and granted the default\_role upon service creation. The user that creates the service is presented with the automatically generated, random password that is assigned to the `default` account when the service is created. The password isn't shown after initial setup, but may be changed by any user with Service Admin permissions in the console at a later time. This account or an account with Service Admin privileges within the console may set up additional database users and roles at any time.

Note

To change the password assigned to the `default` account in the console, go to the Services menu on the left, access the service, go to the Settings tab and click the Reset password button.

We recommend creating a new user account associated with a person and granting the user the default\_role. This is so activities performed by users are identified to their user IDs and the `default` account is reserved for break-glass type activities.

```
CREATE USER userID IDENTIFIED WITH sha256_hash by 'hashed_password';
GRANT default_role to userID;
```

You can use a SHA256 hash generator or code function such as `hashlib` in Python to convert a 12+ character password with appropriate complexity to a SHA256 string to provide to the system administrator as the password. This ensures the administrator doesn't see or handle clear text passwords.

### Database access listings with SQL console users[​](#database-access-listings-with-sql-console-users "Direct link to Database access listings with SQL console users")

The following process can be used to generate a complete access listing across the SQL console and databases in your organization.

Get a list of all database grants

#### Get a list of all database grants[​](#get-a-list-of-all-database-grants "Direct link to Get a list of all database grants")

Run the following queries to get a list of all grants in the database.

```
SELECT grants.user_name,
grants.role_name,
users.name AS role_member,
grants.access_type,
grants.database,
grants.table
FROM system.grants LEFT OUTER JOIN system.role_grants ON grants.role_name = role_grants.granted_role_name
LEFT OUTER JOIN system.users ON role_grants.user_name = users.name

UNION ALL

SELECT grants.user_name,
grants.role_name,
role_grants.role_name AS role_member,
grants.access_type,
grants.database,
grants.table
FROM system.role_grants LEFT OUTER JOIN system.grants ON role_grants.granted_role_name = grants.role_name
WHERE role_grants.user_name is null;
```

Associate grant list to Console users with access to SQL console