# GRANT reference

GRANT SQL keywords reference documentation.  Applies to RBAC in QuestDB Enterprise.

<EnterpriseNote>
  RBAC provides fine-grained database permissions management.
</EnterpriseNote>

`GRANT` - grants permissions to a user, group or service account.

For full documentation of the Access Control List and Role-based Access Control,
see the [RBAC operations](/docs/security/rbac) page.

---

## Syntax

```questdb-sql title="Database scope"
GRANT permission [, permission ...] TO entityName
    [WITH GRANT OPTION]
    [WITH VERIFICATION];
```

```questdb-sql title="Table or column scope"
GRANT permission [, permission ...]
    ON { ALL TABLES
       | tableName [(columns)] [, tableName [(columns)] ...] }
    TO entityName
    [WITH GRANT OPTION]
    [WITH VERIFICATION];
```

Where `columns` is either an explicit list of columns, or `*` for all of the
table's current columns with an optional `EXCLUDE` list:

```questdb-sql title="Column specification"
{ columnName [, columnName ...]
| * [EXCLUDE (columnName [, columnName ...])] }
```

## Description

- `GRANT [permissions] TO entity` - grant database level permissions on database
  level to an entity
- `GRANT [permissions] ON ALL TABLES TO entity` - grant table/column level
  permissions on database level to an entity
- `GRANT [permissions] ON [table] TO entity` - grant table/column level
  permissions on table level to an entity
- `GRANT [permissions] ON [table(columns)] TO entity` - grant column level
  permissions on column level to an entity
- `GRANT [permissions] ON [table(*)] TO entity` - grant column level permissions
  on all of the table's current columns, with an optional `EXCLUDE` list

### Grant database level permissions

```questdb-sql
GRANT CREATE TABLE, SNAPSHOT TO john;
```

| permission   | table_name | column_name | grant_option | origin |
| ------------ | ---------- | ----------- | ------------ | ------ |
| CREATE TABLE |            |             | f            | G      |
| SNAPSHOT     |            |             | f            | G      |

### Grant table level permissions for entire database

```questdb-sql
GRANT ADD INDEX, REINDEX ON ALL TABLES TO john;
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |
| ADD INDEX  |            |             | f            | G      |
| REINDEX    |            |             | f            | G      |

### Grant table level permissions on specific tables

```questdb-sql
GRANT ADD INDEX, REINDEX ON orders, trades TO john;
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |
| ADD INDEX  | trades     |             | f            | G      |
| REINDEX    | trades     |             | f            | G      |
| ADD INDEX  | orders     |             | f            | G      |
| REINDEX    | orders     |             | f            | G      |

### Grant column level permissions for entire database

```questdb-sql
GRANT SELECT ON ALL TABLES TO john;
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |
| SELECT     |            |             | f            | G      |

### Grant column level permissions on specific tables

```questdb-sql
GRANT SELECT ON orders TO john;
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |
| SELECT     | orders     |             | f            | G      |

### Grant column level permissions on specific columns

```questdb-sql
GRANT SELECT ON orders(id, name), trades(id, quantity) TO john;
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |
| SELECT     | trades     | id          | f            | G      |
| SELECT     | trades     | quantity    | f            | G      |
| SELECT     | orders     | id          | f            | G      |
| SELECT     | orders     | name        | f            | G      |

### Grant on all columns of a table

Use the `*` wildcard to grant a column level permission on every column the table
currently has, without naming each one:

```questdb-sql
CREATE TABLE trades (
    symbol SYMBOL, price DOUBLE, quantity DOUBLE,
    counterparty SYMBOL, ts TIMESTAMP
) TIMESTAMP(ts);
GRANT SELECT ON trades(*) TO john;
```

| permission | table_name | column_name  | grant_option | origin |
| ---------- | ---------- | ------------ | ------------ | ------ |
| SELECT     | trades     | symbol       | f            | G      |
| SELECT     | trades     | price        | f            | G      |
| SELECT     | trades     | quantity     | f            | G      |
| SELECT     | trades     | counterparty | f            | G      |
| SELECT     | trades     | ts           | f            | G      |

The `*` is expanded to the table's columns at the time the statement runs, so the
result is a set of individual column level grants.

:::note

`GRANT SELECT ON trades(*)` is not the same as `GRANT SELECT ON trades`. The
wildcard grants only on the columns that exist at that moment and does not extend
to columns added later. A table level grant (without parentheses) covers all
current and future columns. Use the table level form when new columns should be
included automatically.

:::

#### Exclude columns from the wildcard

Add an `EXCLUDE` list to grant on all columns except the ones you name. This is
useful when you want to expose most of a table but keep a few sensitive columns
private:

```questdb-sql
GRANT SELECT ON trades(* EXCLUDE (counterparty)) TO john;
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |
| SELECT     | trades     | symbol      | f            | G      |
| SELECT     | trades     | price       | f            | G      |
| SELECT     | trades     | quantity    | f            | G      |
| SELECT     | trades     | ts          | f            | G      |

`EXCLUDE` can only be used together with `*`. The following rules apply:

- Every excluded column must exist, otherwise the statement fails. This catches
  typos that would otherwise silently grant a column you meant to keep private.
- The `EXCLUDE` list cannot be empty and cannot cover every column of the table.
- The target table must exist. Unlike other grants, a wildcard grant cannot be
  issued ahead of table creation.

:::note

Excluding the designated timestamp column does not necessarily hide it. If the
entity still holds `SELECT` or `UPDATE` on another column of the same table, the
timestamp keeps that permission as an
[implicit permission](#implicit-permissions) shown with origin `I`.

:::

### Grant option

If the `WITH GRANT OPTION` clause is present, then the target entity is allowed
to grant the permissions to other entities. If the entity already has
permissions matching those being granted, their grant option is overwritten.

```questdb-sql
GRANT SELECT ON ALL TABLES TO john WITH GRANT OPTION;
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |
| SELECT     |            |             | t            | G      |

```questdb-sql
GRANT SELECT ON ALL TABLES TO john;
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |
| SELECT     |            |             | f            | G      |

### Verification

By default, `GRANT` does not check whether entities exist, making it possible to
grant permissions to users, groups or service accounts that are later created.

To make sure that the target entity of the grant statement exists, use
[verification](/docs/security/rbac/#grant-verification). The
`WITH VERIFICATION` clause enables checks on the target entity and causes the
`GRANT` statement to fail if the entity does not exist.

```questdb-sql
GRANT SELECT ON orders TO john WITH VERIFICATION;
```

### Implicit permissions

In QuestDB, the timestamp column of a table is crucial for time-series
operations like `ASOF` and `LT` joins, `SAMPLE BY` and interval scans. If a user
can access some columns but not the timestamp column, they cannot execute most
queries.

Therefore when a table has a designated timestamp, granting `SELECT` or `UPDATE`
permissions on any column will automatically extend those permissions to the
timestamp column. These are known as
[implicit permissions](/docs/security/rbac/#implicit-permissions), and they're
indicated by an `I` in the `origin` column of the `SHOW PERMISSIONS` output.

For example, if you grant `UPDATE` permission on the `id` column of the
`products` table, the timestamp column also receives `UPDATE` permission:

```questdb-sql
CREATE TABLE products(id int, name string, ts timestamp) timestamp(ts);
GRANT UPDATE ON products(id) TO john;
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |
| UPDATE     | products   | id          | f            | G      |
| UPDATE     | products   | ts          | f            | I      |

### Optimization

When granting permissions on the table or column level, sometimes it might seem
like there is no effect when cross-checking with the `SHOW permissions` command.
If QuestDB detects that the permission is already granted on a higher level, it
optimizes and removes any child permissions. Doing so keeps the access list
model simple and permission checks faster.

For example, granting the same permission on the database and table level shows
will show the permission on database level only:

```questdb-sql
GRANT INSERT ON ALL TABLES TO john;
GRANT INSERT ON products TO john;
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |
| INSERT     |            |             | f            | G      |

Granting the same permission on the table and column level shows permission on
the table level only:

```questdb-sql
GRANT SELECT ON products TO john;
GRANT SELECT ON products(id) TO john;
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |
| SELECT     | products   |             | f            | G      |

### Grant ahead of table or column creation

Grant permissions ahead of table or column creation:

```questdb-sql
GRANT SELECT ON countries TO john;
GRANT UPDATE ON countries(id) TO john;
GRANT UPDATE ON countries(description) TO john;
```

Such permissions do not show on `SHOW PERMISSIONS` output.

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |

However, when the table is created, then the applicable permissions appear:

```questdb-sql
CREATE TABLE countries (id INT, name STRING, iso_code STRING);
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |
| SELECT     | countries  |             | f            | G      |
| UPDATE     | countries  | id          | f            | G      |

When 'missing' columns are later added to the table, then more permissions
appear:

```questdb-sql
ALTER TABLE countries ADD COLUMN description string;
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |
| SELECT     | countries  |             | f            | G      |
| UPDATE     | countries  | id          | f            | G      |
| UPDATE     | countries  | description | f            | G      |

### Grant when table or column is dropped and recreated

Granted permissions are not automatically revoked when related tables or columns
are dropped. Instead, they have no effect until table or column is recreated.

```questdb-sql
CREATE TABLE countries (id INT, name STRING, iso_code STRING);
GRANT SELECT ON countries TO john;
GRANT UPDATE ON countries(iso_code) TO john;
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |
| SELECT     | countries  |             | f            | G      |
| UPDATE     | countries  | iso_code    | f            | G      |

Now, if the table is dropped, then permission stops being visible:

```questdb-sql
DROP TABLE countries;
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |

When the table is later recreated, permission are in full effect again :

```questdb-sql
CREATE TABLE countries (id INT, name STRING, iso_code int, alpha2 STRING);
```

| permission | table_name | column_name | grant_option | origin |
| ---------- | ---------- | ----------- | ------------ | ------ |
| SELECT     | countries  |             | f            | G      |
| UPDATE     | countries  |             | f            | G      |

:::note

Only the table and/or column name is used when applying permission. The type is
ignored. In the example above `iso_code` was initially of string type, then
recreated as int.

:::

### Owner grants

In QuestDB there are no owners of database objects. Instead, there are
[owner grants](/docs/security/rbac/#owner-grants).

An owner grant means:

- if a user creates a table, the user automatically gets all table level
  permissions with the grant option on the table
- if a user adds a new column to an existing table, the user automatically gets
  all column level permissions with the grant option on the column