# 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.

<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.
</Warning>

### Synopsis

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

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

<Info>
  The `<entity>` referred to above can either be a role or user.
</Info>

<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.
</Info>

<Error>
  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.
</Error>

<Error>
  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.
</Error>

### 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.

```sql wordWrap
-- 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; 
```

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

```sql wordWrap
-- 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;
```

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

```sql wordWrap
-- 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);
```

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

```sql wordWrap
-- 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;
```