How can a view act as an access-control boundary — for example exposing an employees table without its salary and national-ID columns — and what are the limits of relying on that?
answer
- project allowed columns, grant on view only
- definer's rights make it work
- stale base-table grant defeats everything
- caller predicates can be evaluated first → leak
- RLS on the table for per-row rules
basics
~20 sGrant SELECT on a view that projects only the allowed columns and rows, and grant nothing on the base table. Because the view typically executes with its owner's privileges, callers can read exactly what it projects and no more. Limits: it does not stop anyone holding direct base-table rights, and clever predicates can leak information.
solid answer
~60 sThe pattern: create a view projecting only the permitted columns, optionally filtered to permitted rows; grant SELECT on the view to the role; grant nothing on the underlying table. The view usually runs with its definer's privileges, which is why a caller with no rights on employees can still read employees_public. The engine enforces it — there is no application layer to bypass. The limits matter as much as the pattern: - It only closes the path *through the view*. A role that also holds SELECT on the base table reads everything. The grant hygiene is the control, not the view. - Filters can leak. A caller supplying an expression that the optimizer evaluates before the view's own predicate — a function raising errors on values it sees, for example — can infer hidden data. Engines mitigate this with security-barrier-style flags that stop predicate reordering, at a real performance cost. - Error messages, constraint violations and row counts can reveal the existence of hidden rows. - For per-row rules that must hold for every writer, row-level security policies on the table are the stronger primitive; views leave the base table unguarded.
code
sql · 6 linesCREATE VIEW employees_public AS
SELECT employee_id, full_name, department
FROM employees;
REVOKE ALL ON employees FROM reporting_role; -- the step that actually matters
GRANT SELECT ON employees_public TO reporting_role;go deeper
Know the pattern: project only allowed columns, grant on the view, and the caller cannot see the rest.
Explain definer's rights as the reason it works and that the base-table grant must be revoked for it to mean anything.
Bring the limits: predicate-reordering leaks and the security-barrier mitigation with its plan cost, plus error-message and constraint-violation channels.
Choose the primitive deliberately — row-level security on the table for invariants that must hold on every path, views for curated shaping, separate storage when the data has a different lifecycle — and make boundary views auditable.
## The mechanism Access control by view rests on two facts. First, a view's result contains only what its select list projects and its predicates admit — a column not projected cannot be named by a caller. Second, in most engines a view executes with the privileges of its owner, not the caller. So a role can be granted SELECT on the view while holding no privilege whatsoever on the underlying table; the read succeeds because the owner's rights are used to touch the table. That yields two complementary restrictions: - **Column subsetting (projection).** employees_public exposes id, name and department. salary and national_id are not in the result at all. - **Row subsetting (selection).** A predicate such as department = 'SUPPORT', or a tenant scoping expression tied to the session, restricts which rows are visible. Both are enforced inside the database. This is genuinely stronger than filtering in application code, because it survives a second application, an ad-hoc psql session, a BI tool, or a bug in a service layer. ## Why the grant hygiene is the real control The view restricts what can be read *through the view*. It removes nothing from the base table and revokes nothing. If the same role retains SELECT on employees — through a direct grant, a role it inherits, a broad PUBLIC grant, or ownership — the whole scheme is decorative. In practice this is where these designs fail: the view is created correctly and the old grant is never revoked. Auditing effective privileges, not just reading the view definition, is the check that matters. A related trap is the default privileges on new objects and the tendency to hand out broad roles for convenience during incidents. Any of these can reopen the path silently. ## Information leakage through predicates A subtler limit is that a view is a query, and the optimizer is free to reorder predicates for cost. If a caller can supply an expression — a user-defined function, or an operator with observable side effects such as raising an error containing its argument — the optimizer may evaluate that expression on rows *before* applying the view's own restricting predicate. The caller then observes values from rows they were never supposed to see. Variants exist using error messages, timing, and unique-constraint violations. Engines address this with a marker that designates a view as a security barrier: predicates the caller supplies are not allowed to be evaluated before the view's own restrictions. The protection is real and the cost is real, because forbidding reordering can prevent an index from being used for the caller's predicate. This is one of the few places where a security requirement directly buys a slower plan, and it should be a conscious decision. ## Other leakage channels Even a correct view leaks some information. A unique-constraint violation on insert tells you a hidden row exists with that key. A foreign-key error names a table. Aggregate views can expose individual values when a group has one member, which is why statistical disclosure control exists as a separate discipline. Error text and row counts should be considered part of the interface when the threat model includes a curious authenticated user. ## When to prefer other primitives - **Row-level security policies** attach the row predicate to the *table*, so it applies to every access path including direct queries and writes. If the rule is "a user may only ever see their tenant's rows", that belongs on the table; a view can then be used for convenience rather than as the boundary. - **Column-level grants** exist in several engines and express "no access to salary" directly, without a wrapper object. - **Separate physical tables** are the blunt instrument when the sensitive data has a genuinely different lifecycle, retention or encryption requirement. Views remain attractive because they combine access control with shaping — you hand a consumer a curated, meaningful surface and the restriction comes along with it. That combination is why the pattern persists. ## Operating it well Own the view with a role that is not the application role. Revoke base-table privileges explicitly and verify effective privileges as a test, not as a review comment. Treat the view definition as security-relevant code: any change to its select list or predicate is a change to an access boundary and deserves the corresponding review. If the view is also a write surface, remember its read predicate does not constrain writes unless the check option is declared. And document which views are boundaries — an undocumented one will eventually be "simplified" by someone adding the column back. ## Interview framing Describe the mechanism including definer's rights, then immediately give the limits: direct base-table grants defeat it, predicate reordering can leak, and row-level security is the stronger primitive for per-row rules. Candidates who only recite the pattern sound like they have read about it; the limits are what show operational experience.
- A role can still read salaries despite only being granted SELECT on the restricted view. What do you check first?Effective privileges on the base table, not the view definition. The role may hold a direct grant on employees, inherit one through another role, or benefit from a broad grant to PUBLIC. A view restricts only the path through itself; it revokes nothing, so a leftover base-table grant makes the whole scheme decorative.
- How can a user infer hidden row values from a view that filters rows, even without base-table access?By supplying a predicate whose evaluation is observable — for example a function that raises an error containing its argument — and relying on the optimizer evaluating it before the view's own restricting predicate for cost reasons. Engines counter this with a security-barrier designation that forbids reordering caller predicates ahead of the view's, at the cost of losing some index usage.
saying these in an interview costs you the question
- Believing a view revokes access to the base table
- Assuming a view executes with the caller's privileges rather than its owner's
- Treating a filtering view as leak-proof against a user who can supply expressions
- Using views for per-row tenant isolation while leaving direct table access open
- Ignoring that error messages and constraint violations disclose hidden rows