SQL Academy · Lesson

Column-Level Permissions

Hide sensitive columns.

Column-Level Permissions is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Column-Level Permissions Matter

Not every user should see every column in a table. A salary column, a password_hash, or a credit_card_number may live in the same table as perfectly public data like a username or email.

Column-level permissions let you grant access to specific columns rather than the entire table, keeping sensitive data hidden from users who have no business reason to see it.

GRANT on a Whole Table

By default, GRANT SELECT ON table gives a role the ability to read all columns. This is fine for public data, but problematic when the table mixes sensitive and non-sensitive columns.

The query below gives the analyst role full read access to the employees table — including salary and SSN.

GRANT SELECT ON employees TO analyst;

Column-Level GRANT Syntax

PostgreSQL (and standard SQL) allows you to list specific column names inside a GRANT statement. The syntax is:

GRANT privilege (col1, col2) ON table TO role;

The example below grants the analyst role permission to read only id, name, and department — but NOT salary or ssn.

GRANT SELECT (id, name, department) ON employees TO analyst;

Verifying Column Permissions

You can inspect column-level privileges in PostgreSQL by querying the information_schema.column_privileges view. It lists which grantee has which privilege on which column.

SELECT grantee, table_name, column_name, privilege_type
FROM information_schema.column_privileges
WHERE table_name = 'employees'
ORDER BY grantee, column_name;

What Happens Without the Right Columns

If a role tries to read a column it has not been granted access to, the database returns a permission denied error. Only referencing permitted columns will succeed.

Assuming analyst was granted only id, name, and department, the first query below fails; the second succeeds.

-- This will fail for analyst (no permission on salary):
-- SELECT id, name, salary FROM employees;

-- This succeeds:
SELECT id, name, department FROM employees;

Column-Level UPDATE Permission

Column-level restrictions also apply to UPDATE. You can allow a role to update only specific columns — for example, letting a helpdesk role update a user's status without being able to change their email or password_hash.

GRANT UPDATE (status) ON users TO helpdesk;

-- helpdesk can now run:
UPDATE users SET status = 'suspended' WHERE id = 42;

Hiding Columns with Views

Another common approach is to create a view that exposes only the safe columns, and then grant access to the view instead of the base table. This works across all databases, not just those that support column-level GRANT.

CREATE VIEW public_employees AS
SELECT id, name, department, hire_date
FROM employees;

GRANT SELECT ON public_employees TO analyst;

Revoking Column-Level Access

Just like GRANT, you can use REVOKE with a column list to remove access to specific columns. If a role had broad table-level access, you may need to revoke it entirely before granting restricted column-level access.

-- Remove all SELECT on the table first
REVOKE SELECT ON employees FROM analyst;

-- Then grant only safe columns
GRANT SELECT (id, name, department) ON employees TO analyst;

Column Permissions and Row-Level Security Together

Column-level permissions and Row-Level Security (RLS) are complementary. RLS controls which rows a user can see; column-level permissions control which columns in those rows are visible.

Together they form a powerful two-dimensional access control: restrict the row set AND hide sensitive fields within each row.

-- RLS policy: employees can see only their own row
CREATE POLICY own_row ON employees
  FOR SELECT
  USING (user_id = current_user_id());

-- Column grant: hide salary even for own row
GRANT SELECT (id, name, department) ON employees TO employee_role;

Using Security-Definer Functions

When you want fine-grained logic beyond simple column lists, a SECURITY DEFINER function can be a bridge. The function runs with the privileges of its owner (who has full column access) and returns only what it chooses to expose, no matter who calls it.

CREATE OR REPLACE FUNCTION get_employee_summary(emp_id INT)
RETURNS TABLE(id INT, name TEXT, department TEXT)
SECURITY DEFINER
LANGUAGE sql AS
$$
  SELECT id, name, department
  FROM employees
  WHERE id = emp_id;
$$;

GRANT EXECUTE ON FUNCTION get_employee_summary(INT) TO analyst;

Practical Design: Layered Column Security

A solid production pattern combines three layers:

Table ownership — only the app service account owns the base table.
Views or column GRANTs — read roles get access only to non-sensitive columns.
Audit columns — log which user and timestamp touched sensitive data via triggers.

This ensures that even if a role is accidentally over-privileged at one layer, the other layers still protect the data.

-- Layer 1: revoke public access
REVOKE ALL ON employees FROM PUBLIC;

-- Layer 2: expose safe columns via view
CREATE VIEW employee_public AS
SELECT id, name, department, hire_date FROM employees;

GRANT SELECT ON employee_public TO reporting_role;

-- Layer 3: audit trigger logs sensitive field reads (pseudocode)
-- CREATE TRIGGER audit_salary AFTER SELECT ON employees ...

Quick Check

Which SQL statement correctly grants the hr_viewer role the ability to read only the name and department columns of the employees table?

Recap: Column-Level Permissions

Column-level permissions let you restrict access to individual fields rather than entire tables, keeping sensitive data like salaries, SSNs, and password hashes hidden from unprivileged roles.

Key takeaways:

Use GRANT SELECT (col1, col2) ON table TO role to restrict readable columns.
Use REVOKE with a column list to remove specific column access.
Views are a portable alternative that works across all databases.
Combine column-level permissions with RLS for two-dimensional access control.
SECURITY DEFINER functions provide programmatic column-level filtering with added logic.

Applied consistently, column-level security is one of the simplest and most effective ways to enforce the principle of least privilege at the data layer.

Frequently asked questions

Is the “Column-Level Permissions” lesson free?

Yes — the full text of “Column-Level Permissions” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Column-Level Permissions”?

Hide sensitive columns. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Column-Level Permissions” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy