SQL GRANT: Privileges on Tables, Schemas and Columns with Least Privilege

GRANT hands out the right to do something: read a table, insert rows, use a schema. In PostgreSQL an ordinary role sees nothing beyond what it was explicitly allowed, so a well-built set of GRANT statements is your access model. Make it too broad and an analyst accidentally rewrites production data.

Table privileges

The basic form lists the actions and the object, then the recipient role. Take the familiar users, orders and employees.

CREATE ROLE analyst NOLOGIN;

GRANT SELECT, INSERT ON orders TO analyst;
GRANT SELECT ON users TO analyst;

Table privileges are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES and TRIGGER. The keyword ALL grants every one at once:

GRANT ALL PRIVILEGES ON employees TO analyst;

Gotcha: granting SELECT on a table is not enough if the analyst cannot enter the schema. To reach an object you also need USAGE on the schema that contains it:

GRANT USAGE ON SCHEMA public TO analyst;

Without USAGE the query fails with permission denied for schema, even though the table privilege technically exists.

Columns and whole schemas

Privileges can be narrowed down to individual columns. That is handy when employees.salary is sensitive but the name and department are readable by everyone.

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

Now SELECT id, name FROM employees works, but SELECT salary FROM employees is rejected. UPDATE can be column-scoped too: GRANT UPDATE (status) ON orders TO analyst lets the role change only the order status.

Going the other way, you can grant on every table in a schema in one command:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

Important gotcha: ALL TABLES IN SCHEMA only touches tables that exist at the moment it runs. A table created tomorrow gets no privilege. To grant automatically going forward, set up ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO analyst;

WITH GRANT OPTION and passing rights on

By default the recipient of a privilege cannot pass it further. WITH GRANT OPTION changes that:

GRANT SELECT ON orders TO team_lead WITH GRANT OPTION;

Now team_lead can run GRANT SELECT ON orders TO ... themselves. That is convenient for delegation but risky: you lose control over who eventually gets access. Another gotcha is that REVOKE then needs CASCADE, otherwise the revoke is blocked by dependent grants:

REVOKE SELECT ON orders FROM team_lead CASCADE;

CASCADE also revokes every privilege team_lead handed out based on that grant option.

Object privileges versus role membership

These are two different mechanisms that are easy to confuse.

• An object privilege is a right like SELECT ON orders, attached to a specific object.
• Role membership is GRANT role_a TO role_b, which makes role_b a member of role_a and gives it all of role_a's privileges in one shot.

Grouping roles are the backbone of manageable access control:

CREATE ROLE readonly NOLOGIN;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

CREATE ROLE alice LOGIN PASSWORD 'secret';
GRANT readonly TO alice;

Now alice reads everything through membership in readonly. Change the privileges on readonly and they apply to every member at once, without naming anyone individually.

Designing for least privilege

The least-privilege principle is simple: a role gets exactly what it needs to do its job and not a byte more.

• Do not grant rights directly to users; collect them into grouping roles (readonly, app_write, reporting).
• Lock down sensitive columns with column-level grants instead of SELECT on the whole table.
• Remember that a table owner and a superuser have full access that bypasses GRANT entirely; REVOKE does not touch them.

Engine differences:

• MySQL has no ALL TABLES IN SCHEMA; use GRANT SELECT ON mydb.* for a whole database. Column grants do exist: GRANT SELECT (name) ON employees TO 'analyst'@'%'.
• ClickHouse supports GRANT, roles and WITH GRANT OPTION, but it has no schema USAGE concept; rights are granted on the database and table directly.

The baseline rule: give USAGE on the schema, the minimum privileges on objects, group through roles, and keep WITH GRANT OPTION under tight control.