GRANT Statement

**Syntax:**

```
GRANT READ ON S3('regexp_pattern') TO user
```

This grant will allow the user to read only from S3 URIs that match the specified regular expression pattern.

**Examples:**

Grant access to specific S3 bucket paths:

```
-- Allow user to read only from s3://foo/ paths
GRANT READ ON S3('s3://foo/.*') TO john

-- Allow user to read from specific file patterns
GRANT READ ON S3('s3://mybucket/data/2024/.*\.parquet') TO analyst

-- Multiple filters can be granted to the same user
GRANT READ ON S3('s3://foo/.*') TO john
GRANT READ ON S3('s3://bar/.*') TO john
```

Note

Source filter takes **regexp** as a parameter, so a grant
`GRANT READ ON URL('http://www.google.com') TO john;`

will allow queries

```
SELECT * FROM url('https://www.google.com');
SELECT * FROM url('https://www-google.com');
```

because `.` is treated as an `Any Single Character` in the regexps.
This may lead to potential vulnerability. The correct grant should be

```
GRANT READ ON URL('https://www\.google\.com') TO john;
```

**Re-granting with GRANT OPTION:**

If the original grant has `WITH GRANT OPTION`, it can be re-granted using `GRANT CURRENT GRANTS`:

```
-- Original grant with GRANT OPTION
GRANT READ ON S3('s3://foo/.*') TO john WITH GRANT OPTION

-- John can now regrant this access to others
GRANT CURRENT GRANTS(READ ON S3) TO alice
```

**Important limitations:**

- **Partial revokes are not allowed:** You cannot revoke a subset of a granted filter pattern. You must revoke the entire grant and re-grant with new patterns if needed.
- **Wildcard grants are not allowed:** You cannot use `GRANT READ ON *('regexp')` or similar wildcard-only patterns. Specific source must be provided.

### dictGet[​](#dictget "Direct link to dictGet")

- `dictGet`. Aliases: `dictHas`, `dictGetHierarchy`, `dictIsIn`

Allows a user to execute [dictGet](/docs/sql-reference/functions/ext-dict-functions#dictGet), [dictHas](/docs/sql-reference/functions/ext-dict-functions#dictHas), [dictGetHierarchy](/docs/sql-reference/functions/ext-dict-functions#dictGetHierarchy), [dictIsIn](/docs/sql-reference/functions/ext-dict-functions#dictIsIn) functions.

Privilege level: `DICTIONARY`.

**Examples**

- `GRANT dictGet ON mydb.mydictionary TO john`
- `GRANT dictGet ON mydictionary TO john`

### displaySecretsInShowAndSelect[​](#displaysecretsinshowandselect "Direct link to displaySecretsInShowAndSelect")

Allows a user to view secrets in `SHOW` and `SELECT` queries if both
[`display_secrets_in_show_and_select` server setting](/docs/operations/server-configuration-parameters/settings#display_secrets_in_show_and_select)
and
[`format_display_secrets_in_show_and_select` format setting](/docs/operations/settings/formats#format_display_secrets_in_show_and_select)
are turned on.

### NAMED COLLECTION ADMIN[​](#named-collection-admin "Direct link to NAMED COLLECTION ADMIN")

Allows a certain operation on a specified named collection. Before version 23.7 it was called NAMED COLLECTION CONTROL, and after 23.7 NAMED COLLECTION ADMIN was added and NAMED COLLECTION CONTROL is preserved as an alias.

- `NAMED COLLECTION ADMIN`. Level: `NAMED_COLLECTION`. Aliases: `NAMED COLLECTION CONTROL`
  - `CREATE NAMED COLLECTION`. Level: `NAMED_COLLECTION`
  - `DROP NAMED COLLECTION`. Level: `NAMED_COLLECTION`
  - `ALTER NAMED COLLECTION`. Level: `NAMED_COLLECTION`
  - `SHOW NAMED COLLECTIONS`. Level: `NAMED_COLLECTION`. Aliases: `SHOW NAMED COLLECTIONS`
  - `SHOW NAMED COLLECTIONS SECRETS`. Level: `NAMED_COLLECTION`. Aliases: `SHOW NAMED COLLECTIONS SECRETS`
  - `NAMED COLLECTION`. Level: `NAMED_COLLECTION`. Aliases: `NAMED COLLECTION USAGE, USE NAMED COLLECTION`

Unlike all other grants (CREATE, DROP, ALTER, SHOW) grant NAMED COLLECTION was added only in 23.7, while all others were added earlier - in 22.12.

**Examples**

Assuming a named collection is called abc, we grant privilege CREATE NAMED COLLECTION to user john.

- `GRANT CREATE NAMED COLLECTION ON abc TO john`

### TABLE ENGINE[​](#table-engine "Direct link to TABLE ENGINE")

Allows using a specified table engine when creating a table. Applies to [table engines](/docs/engines/table-engines).

**Examples**

- `GRANT TABLE ENGINE ON * TO john`
- `GRANT TABLE ENGINE ON TinyLog TO john`

Note

By default, for backward compatibility reasons, creating a table with a specific table engine ignores grants,
however you can change this behaviour by setting [`table_engines_require_grant` to true](https://github.com/ClickHouse/ClickHouse/blob/df970ed64eaf472de1e7af44c21ec95956607ebb/programs/server/config.xml#L853-L855)
in config.xml.

### ALL[​](#all "Direct link to ALL")

Not supported in ClickHouse Cloud

Grants all the privileges on regulated entity to a user account or a role.

Note

The privilege `ALL` is not supported in ClickHouse Cloud, where the `default` user has limited permissions. Users can grant the maximum permissions to a user by granting the `default_role`. See [here](/docs/cloud/security/manage-cloud-users) for further details.
Users can also use the `GRANT CURRENT GRANTS` as the default user to achieve similar effects to `ALL`.

### NONE[​](#none "Direct link to NONE")

Doesn't grant any privileges.

### ADMIN OPTION[​](#admin-option "Direct link to ADMIN OPTION")

The `ADMIN OPTION` privilege allows a user to grant their role to another user.

- [Granting Privilege Syntax](#granting-privilege-syntax)- [Assigning Role Syntax](#assigning-role-syntax)- [Grant Current Grants Syntax](#grant-current-grants-syntax)- [Usage](#usage)- [Wildcard grants](#wildcard-grants)- [Privileges](#privileges)
            - [SELECT](#select)- [INSERT](#insert)- [ALTER](#alter)- [BACKUP](#backup)- [CREATE](#create)- [CLUSTER](#cluster)- [DROP](#drop)- [TRUNCATE](#truncate)- [OPTIMIZE](#optimize)- [SHOW](#show)- [KILL QUERY](#kill-query)- [ACCESS MANAGEMENT](#access-management)- [SYSTEM](#system)- [INTROSPECTION](#introspection)- [SOURCES](#sources)- [Source Filter Grants](#source-filter-grants)- [dictGet](#dictget)- [displaySecretsInShowAndSelect](#displaysecretsinshowandselect)- [NAMED COLLECTION ADMIN](#named-collection-admin)- [TABLE ENGINE](#table-engine)- [ALL](#all)- [NONE](#none)- [ADMIN OPTION](#admin-option)

Was this page helpful?