# Column-Level Security

Grant or revoke `SELECT` privileges to columns in a table. These privileges can be managed separately of table-level privileges, allowing for `SELECT` operations on a subset of columns.

{% hint style="warning" %}

* Column privileges are only enabled for tables.
* Column privileges other than `SELECT` such as `UPDATE`, `DELETE` are currently unsupported.
* Column-level security is not supported on queries that use one or more views.
  {% endhint %}

### Synopsis

```sql
GRANT SELECT (<column1>,<column2>,...<columnN>) ON TABLE <table> TO <entity>;

REVOKE SELECT (<column1>,<column2>,...<columnN>) ON TABLE <table> FROM <entity>;
```

{% hint style="info" %}
The `<entity>` referred to above can either be a role or user.
{% endhint %}

{% hint style="info" %}
The above `GRANT` and `REVOKE` commands can be compounded with other privileges. For example

```sql
GRANT SELECT (salary), UPDATE ON TABLE employees TO test_user;
```

grants the `SELECT` column privilege on the table `employees` to `test_user` as well as `UPDATE` privileges.
{% endhint %}

{% hint style="danger" %}
When using `UPDATE` or `DELETE` on a table, any columns used in the `WHERE` condition must allow for `SELECT`. That is, the entity issuing the command must have sufficient `SELECT` privileges to all columns in use. For example, `SELECT` privilege on the table being operated on is sufficient.
{% endhint %}

{% hint style="danger" %}
Currently, when a query utilizes a view, column-level privileges are disabled. In such cases, only table-level privileges are considered. Consequently, queries that might have adequate column-level privileges but also involve a view will result in an insufficient privileges error.
{% endhint %}

### Examples

```sql
CREATE USER test_user (PASSWORD='test');
CREATE TABLE employees (id INT, salary BIGINT);
```

1. Grant `SELECT` on a single column.

```sql
GRANT SELECT(id) ON TABLE employees TO test_user;
```

2. Revoke `SELECT` on a single column.

```sql
REVOKE SELECT(id) ON TABLE employees FROM test_user;
```

The following also revokes column privileges.

```sql
REVOKE ALL ON TABLE employees FROM test_user;
```

3. Grant `SELECT` on multiple columns.

```sql
GRANT SELECT (id,salary) ON TABLE employees TO test_user;
```

4. Revoke `SELECT` on multiple columns.

```sql
REVOKE SELECT (id,salary) ON TABLE employees FROM test_user;
```

5. Granting `SELECT` on any column allows access to metadata.

{% code overflow="wrap" %}

```sql
-- Without privilege, the following exception will occur for test_user.
-- "Violation of access privileges: user test_user has no proper privileges for object employees"
SELECT count(*) FROM employees;

-- The following is run as an super-user or administrator. 
GRANT SELECT(id) ON TABLE employees TO test_user;
-- The following works without issue for test_user.
SELECT count(*) FROM employees; 
```

{% endcode %}

6. Allowing `SELECT` privilege on a subset of columns will enable certain queries and disable others.

{% code overflow="wrap" %}

```sql
-- The following is run as an super-user or administrator.
GRANT SELECT(id) ON TABLE employees TO test_user;

-- The following query completes without error for test_user.
SELECT id FROM employees;
-- The following query does not complete and reports no proper privileges for test_user.
SELECT id, salary FROM employees;
```

{% endcode %}

7. Any subqueries used within a query will enforce similar column-level security.

{% code overflow="wrap" %}

```sql
-- The following query completes without error for test_user.
SELECT * FROM (SELECT id FROM employees);

-- The following query does not complete and reports no proper privileges for test_user.
SELECT * FROM (SELECT id, salary FROM employees);
```

{% endcode %}

8. Table-level privileges supersede column-level privileges. Revoking column-privilege will not affect table-level privileges.

{% code overflow="wrap" %}

```sql
-- The following is run as an super-user or administrator.
GRANT SELECT ON TABLE employees TO test_user;
GRANT SELECT(id) ON TABLE employees TO test_user;

-- The following query completes without error for test_user.
SELECT id FROM employees;
-- The following query completes without error for test_user.
SELECT id, salary FROM employees;

-- The following is run as an super-user or administrator.
REVOKE SELECT(id) ON TABLE employees FROM test_user;
-- The following query completes without error for test_user. The user still has table-level privileges.
SELECT id, salary FROM employees;
```

{% endcode %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.heavy.ai/installation-and-configuration/security/roles/column-level-security.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.