A grant on a table covers every column. Often a role needs only some of them. The company phone book needs names and titles, not salaries.
Grant some columns
Put the columns in parentheses after the privilege:
sql
GRANT SELECT (id, name, title) ON employees TO directory;employees
| id | name | title | salary | hired_on |
|---|---|---|---|---|
| 1 | Rosa Martin | CEO | 185000.00 | 2019-03-01 |
| 2 | Ken Adams | Head of Engineering | 150000.00 | 2019-06-15 |
| 3 | Priya Shah | Backend Engineer | 112000.00 | 2021-01-10 |
The role can read those columns only:
Query as directory | Result |
|---|---|
SELECT name, title FROM employees; | works |
SELECT * FROM employees; | permission denied |
SELECT name FROM employees WHERE salary > 100000; | permission denied: salary is in the WHERE |
UPDATE and INSERT take a column list too:
sql
GRANT UPDATE (title) ON employees TO hr;Check a column
has_column_privilege(role, table, column, privilege) returns true or false:
sql
SELECT has_column_privilege('directory', 'employees', 'salary', 'SELECT');has_table_privilege(role, table, privilege) does the same for a whole table.
A table grant and a column grant are separate. If a role has SELECT on the whole table, a column grant does not limit it. Revoke the table grant first.