CREATE USER

## User Host[​](#user-host "Direct link to User Host")

User host is a host from which a connection to ClickHouse server could be established. The host can be specified in the `HOST` query section in the following ways:

- `HOST IP 'ip_address_or_subnetwork'` — User can connect to ClickHouse server only from the specified IP address or a [subnetwork](https://en.wikipedia.org/wiki/Subnetwork). Examples: `HOST IP '192.168.0.0/16'`, `HOST IP '2001:DB8::/32'`. For use in production, only specify `HOST IP` elements (IP addresses and their masks), since using `host` and `host_regexp` might cause extra latency.
- `HOST ANY` — User can connect from any location. This is a default option.
- `HOST LOCAL` — User can connect only locally.
- `HOST NAME 'fqdn'` — User host can be specified as FQDN. For example, `HOST NAME 'mysite.com'`.
- `HOST REGEXP 'regexp'` — You can use [pcre](http://www.pcre.org/) regular expressions when specifying user hosts. For example, `HOST REGEXP '.*\.mysite\.com'`.
- `HOST LIKE 'template'` — Allows you to use the [LIKE](/docs/sql-reference/functions/string-search-functions#like) operator to filter the user hosts. For example, `HOST LIKE '%'` is equivalent to `HOST ANY`, `HOST LIKE '%.mysite.com'` filters all the hosts in the `mysite.com` domain.

Another way of specifying host is to use `@` syntax following the username. Examples:

- `CREATE USER mira@'127.0.0.1'` — Equivalent to the `HOST IP` syntax.
- `CREATE USER mira@'localhost'` — Equivalent to the `HOST LOCAL` syntax.
- `CREATE USER mira@'192.168.%.%'` — Equivalent to the `HOST LIKE` syntax.

Tip

ClickHouse treats `user_name@'address'` as a username as a whole. Thus, technically you can create multiple users with the same `user_name` and different constructions after `@`. However, we do not recommend to do so.

## VALID UNTIL Clause[​](#valid-until-clause "Direct link to VALID UNTIL Clause")

Allows you to specify the expiration date and, optionally, the time for an authentication method. It accepts a string as a parameter. It is recommended to use the `YYYY-MM-DD [hh:mm:ss] [timezone]` format for datetime. By default, this parameter equals `'infinity'`.
The `VALID UNTIL` clause can only be specified along with an authentication method, except for the case where no authentication method has been specified in the query. In this scenario, the `VALID UNTIL` clause will be applied to all existing authentication methods.

Examples:

- `CREATE USER name1 VALID UNTIL '2025-01-01'`
- `CREATE USER name1 VALID UNTIL '2025-01-01 12:00:00 UTC'`
- `CREATE USER name1 VALID UNTIL 'infinity'`
- `` CREATE USER name1 VALID UNTIL '2025-01-01 12:00:00 `Asia/Tokyo`' ``
- `CREATE USER name1 IDENTIFIED WITH plaintext_password BY 'no_expiration', bcrypt_password BY 'expiration_set' VALID UNTIL '2025-01-01''`

## GRANTEES Clause[​](#grantees-clause "Direct link to GRANTEES Clause")

Specifies users or roles which are allowed to receive [privileges](/docs/sql-reference/statements/grant#privileges) from this user on the condition this user has also all required access granted with [GRANT OPTION](/docs/sql-reference/statements/grant#granting-privilege-syntax). Options of the `GRANTEES` clause:

- `user` — Specifies a user this user can grant privileges to.
- `role` — Specifies a role this user can grant privileges to.
- `ANY` — This user can grant privileges to anyone. It's the default setting.
- `NONE` — This user can grant privileges to none.

You can exclude any user or role by using the `EXCEPT` expression. For example, `CREATE USER user1 GRANTEES ANY EXCEPT user2`. It means if `user1` has some privileges granted with `GRANT OPTION` it will be able to grant those privileges to anyone except `user2`.

## Examples[​](#examples-1 "Direct link to Examples")

Create the user account `mira` protected by the password `qwerty`:

```
CREATE USER mira HOST IP '127.0.0.1' IDENTIFIED WITH sha256_password BY 'qwerty';
```

`mira` should start client app at the host where the ClickHouse server runs.

Create the user account `john` and assign roles:

```
CREATE USER john ROLE role1, role2;
```

Create the user account `john`, assign roles and make some of them default:

```
CREATE USER john ROLE role1, role2 DEFAULT ROLE role1;
```

or

```
CREATE USER john ROLE role1, role2 DEFAULT ROLE ALL EXCEPT role2;
```

Create the user account `john` and allow him to grant his privileges to the user with `jack` account:

```
CREATE USER john GRANTEES jack;
```

Use a query parameter to create the user account `john`:

```
SET param_user=john;
CREATE USER {user:Identifier};
```

- [Identification](#identification)- [Examples](#examples)- [User Host](#user-host)- [VALID UNTIL Clause](#valid-until-clause)- [GRANTEES Clause](#grantees-clause)- [Examples](#examples-1)

Was this page helpful?