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.

Synopsis

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

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

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

The above GRANT and REVOKE commands can be compounded with other privileges. For example

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.

Examples

CREATE USER test_user (PASSWORD='test');
CREATE TABLE employees (id INT, salary BIGINT);
  1. Grant SELECT on a single column.

GRANT SELECT(id) ON TABLE employees TO test_user;
  1. Revoke SELECT on a single column.

REVOKE SELECT(id) ON TABLE employees FROM test_user;

The following also revokes column privileges.

REVOKE ALL ON TABLE employees FROM test_user;
  1. Grant SELECT on multiple columns.

GRANT SELECT (id,salary) ON TABLE employees TO test_user;
  1. Revoke SELECT on multiple columns.

REVOKE SELECT (id,salary) ON TABLE employees FROM test_user;
  1. Granting SELECT on any column allows access to metadata.

-- 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; 
  1. Allowing SELECT privilege on a subset of columns will enable certain queries and disable others.

-- 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;
  1. Any subqueries used within a query will enforce similar column-level security.

-- 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);
  1. Table-level privileges supersede column-level privileges. Revoking column-privilege will not affect table-level privileges.

-- 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;

Last updated