# 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 %}