A reporting role must read the customer table but must never see the ssn and salary columns. Compare enforcing that with column-level SELECT privileges versus exposing only a view that projects the allowed columns.

middlemust knowfreq 55%

answer

grant per column: star selects fail loudly
view: revoke base table or it is decoration
new sensitive column: view hides by default
views can also coarsen, filter, join
column-level UPDATE grants for write subsets

basics

~20 s

Column-level SELECT grants let the role query the real table but error on forbidden columns. A view revokes table access entirely and exposes a fixed projection. Grants are precise with nothing extra to maintain; views are flexible, tool-friendly and hide new columns by default.

solid answer

~50 s

Column-level grants attach the restriction to the table itself: the role keeps a direct handle on customer but any reference to ssn — including SELECT * — is rejected. Nothing to maintain beside the table. The costs: errors instead of graceful degradation, uneven engine support (some enforce on SELECT but leak through predicates or error messages), and new sensitive columns may be exposed by default depending on how the grant was written.

A restricted view revokes SELECT on the base table and grants it on customer_reporting, which projects only allowed columns. It is composable — you can also filter rows, coarsen values, rename, join lookups — and SELECT * just works. The costs: another object in the release process, definer-rights semantics to understand, and the base table must stay locked down or the view is decoration.

In practice: views to shape each consumer's contract, column grants as the backstop.

**Column-level grants** attach the restriction to the table itself: the role keeps a direct handle on `customer` but any reference to `ssn` — including `SELECT *` — is rejected. Nothing to maintain beside the table. The costs: errors instead of graceful degradation, uneven engine support (some enforce on SELECT but leak through predicates or error messages), and new sensitive columns may be exposed by default depending on how the grant was written. **A restricted view** revokes SELECT on the base table and grants it on `customer_reporting`, which projects only allowed columns. It is composable — you can also filter rows, coarsen values, rename, join lookups — and `SELECT *` just works. The costs: another object in the release process, definer-rights semantics to understand, and the base table must stay locked down or the view is decoration. In practice: views to shape each consumer's contract, column grants as the backstop.

code

sql · 9 lines

sql

-- column-level privileges
REVOKE SELECT ON customer FROM reporting;
GRANT SELECT (id, name, city, signup_date) ON customer TO reporting;

-- restricted projection via a view
CREATE VIEW customer_reporting AS
  SELECT id, name, city, signup_date FROM customer;
REVOKE SELECT ON customer FROM reporting;
GRANT SELECT ON customer_reporting TO reporting;

go deeper

for a junior

Know both mechanisms exist and that a view only helps if direct access to the table is revoked.

for a middle

Compare failure modes, behaviour when columns are added, and tool friendliness of SELECT *; mention column-level UPDATE grants.

for a senior

Discuss definer rights, predicate and error-message leakage, review burden of grant sprawl, and standardising one consumer-facing view per role.

for a principal

Treat views as the published contract with downstream teams, and set an estate rule that no application role holds direct SELECT on PII-bearing tables.

Two ways to hide a column

A restricted projection is the general idea: a principal sees a subset of the columns of a relation. Relational engines offer two mechanisms.

Column-level privileges. The privilege system supports granting SELECT on an enumerated list of columns rather than the whole table. The role can issue queries against the base table; referencing an ungranted column raises a permission error. Because SELECT * expands to every column, star queries fail — noisy but honest.

View-based subsetting. Create a view whose select list contains only permitted columns, grant SELECT on the view, and revoke it on the base table. The consumer's world is the view; hidden columns are not merely denied, they are invisible in the view's metadata.

What differs in practice

Failure mode. Grants fail loudly at authorization time. Views degrade gracefully: the column simply is not there, so tools that introspect metadata generate correct queries. For BI tools and ORMs that love star selects, the view is far less painful.

Default on schema change. Add a new sensitive column and a view keeps it hidden automatically — its select list is fixed. With column grants, whether the role can read the new column depends on how the grant was written; a table-level grant plus per-column revokes is fail-open. Prefer enumerating what is allowed.

Expressiveness. A view can do more than drop columns: partially mask (right(card,4)), coarsen (birth year instead of birth date), filter rows, or join reference data. Column grants are all-or-nothing per column.

Bypass risk. A view only protects if the base table is not readable. The most common mistake is creating the view while leaving SELECT on the base table granted to PUBLIC or inherited through another role. Also, views typically resolve base-table access with the definer's rights, so whoever may create views in a schema they own can potentially widen their own access — restrict who may create objects.

Predicate and side-channel behaviour. Some engines allow a column to be referenced in WHERE or ORDER BY without SELECT on it, or reveal values through constraint-violation and error messages. A view removes the column from the namespace entirely, so it cannot be referenced at all — a stronger guarantee.

Operational cost. Views are objects: migrations, ownership, invalidation when the base table changes, and they can complicate certain DDL. Column grants live in the catalog with no extra object, but grants sprawl across many roles and are harder to review at a glance.

Write paths. Column-level privileges also exist for INSERT/UPDATE, which views handle only awkwardly (updatable-view rules, or triggers). If the requirement is "may update these two columns only", column-level UPDATE grants are the direct tool.

The usual recommendation

Use views to define each consumer's contract — self-documenting and stable under schema growth — and use ordinary privileges so no role holds direct SELECT on tables carrying PII. Where a role genuinely needs the base table, enumerate column grants and re-review them whenever columns are added. Both are real authorization; unlike masking, the value never reaches the client.

## Two ways to hide a column A *restricted projection* is the general idea: a principal sees a subset of the columns of a relation. Relational engines offer two mechanisms. **Column-level privileges.** The privilege system supports granting SELECT on an enumerated list of columns rather than the whole table. The role can issue queries against the base table; referencing an ungranted column raises a permission error. Because `SELECT *` expands to every column, star queries fail — noisy but honest. **View-based subsetting.** Create a view whose select list contains only permitted columns, grant SELECT on the view, and revoke it on the base table. The consumer's world is the view; hidden columns are not merely denied, they are invisible in the view's metadata. ## What differs in practice **Failure mode.** Grants fail loudly at authorization time. Views degrade gracefully: the column simply is not there, so tools that introspect metadata generate correct queries. For BI tools and ORMs that love star selects, the view is far less painful. **Default on schema change.** Add a new sensitive column and a view keeps it hidden automatically — its select list is fixed. With column grants, whether the role can read the new column depends on how the grant was written; a table-level grant plus per-column revokes is fail-open. Prefer enumerating what is allowed. **Expressiveness.** A view can do more than drop columns: partially mask (`right(card,4)`), coarsen (birth year instead of birth date), filter rows, or join reference data. Column grants are all-or-nothing per column. **Bypass risk.** A view only protects if the base table is not readable. The most common mistake is creating the view while leaving SELECT on the base table granted to PUBLIC or inherited through another role. Also, views typically resolve base-table access with the *definer's* rights, so whoever may create views in a schema they own can potentially widen their own access — restrict who may create objects. **Predicate and side-channel behaviour.** Some engines allow a column to be referenced in WHERE or ORDER BY without SELECT on it, or reveal values through constraint-violation and error messages. A view removes the column from the namespace entirely, so it cannot be referenced at all — a stronger guarantee. **Operational cost.** Views are objects: migrations, ownership, invalidation when the base table changes, and they can complicate certain DDL. Column grants live in the catalog with no extra object, but grants sprawl across many roles and are harder to review at a glance. **Write paths.** Column-level privileges also exist for INSERT/UPDATE, which views handle only awkwardly (updatable-view rules, or triggers). If the requirement is "may update these two columns only", column-level UPDATE grants are the direct tool. ## The usual recommendation Use views to define each consumer's contract — self-documenting and stable under schema growth — and use ordinary privileges so no role holds direct SELECT on tables carrying PII. Where a role genuinely needs the base table, enumerate column grants and re-review them whenever columns are added. Both are real authorization; unlike masking, the value never reaches the client.

You created the reporting view but the role can still read ssn. What did you most likely miss?
SELECT on the base table is still granted, most often through PUBLIC or an inherited role rather than a direct grant. A view restricts nothing unless the underlying table is unreadable by that principal. Audit effective privileges, including role inheritance and default privileges, not just the grants you issued by hand.
Someone adds a new column holding passport numbers. What happens under each approach?
The view keeps it hidden because its select list is fixed — fail-closed. With column grants it depends: an enumerated per-column grant does not cover the new column, but a table-wide grant with per-column revokes exposes it immediately. That asymmetry is why enumerating allowed columns, or using views, is the safer default.

saying these in an interview costs you the question

Creating a view but leaving SELECT on the base table (often via PUBLIC)
Claiming column grants and masking policies are the same control
Assuming a column you cannot SELECT can never be used in WHERE or ORDER BY
Ignoring that new columns may be exposed by default under table-level grants
Letting the restricted role create its own views or functions in a schema it owns

A reporting role must read the customer table but must never see the ssn and salary columns. Compare enforcing that with column-level SELECT privileges versus exposing only a view that projects the allowed columns.

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Two ways to hide a column

What differs in practice

The usual recommendation

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions7 questions

▾related topics3 topics

Two ways to hide a column

What differs in practice

The usual recommendation

answer

go deeper

context