View Security

Views as an Access-Control Layer

Beyond convenience, views are one of the oldest and most reliable tools for enforcing column-level and row-level security in SQL databases. Rather than granting a user direct access to a sensitive table, you grant them access only to a view that exposes a restricted slice of it.

Hiding Columns

Suppose employees(id, name, department, salary) should be visible to general staff, but salary should be restricted to HR and the DBA.

sql
CREATE VIEW employee_public AS
SELECT id, name, department
FROM employees;

GRANT SELECT ON employee_public TO staff_role;
-- staff_role is NEVER granted SELECT on employees directly
GRANT SELECT ON employees TO hr_role, dba_role;

A member of staff_role who tries SELECT salary FROM employees gets a permission-denied error, because they were never granted access to the base table at all — only to employee_public, which simply doesn't expose that column.

Hiding Rows

Views can restrict rows as well as columns, layering a WHERE clause as a security filter:

sql
CREATE VIEW own_department_employees AS
SELECT id, name, department, salary
FROM employees
WHERE department = CURRENT_ROLE_DEPARTMENT();  -- illustrative; real impl varies by engine

A department manager granted access only to this view sees just their own department's rows, even though the view's column list is otherwise unrestricted.

Combining Both

sql
CREATE VIEW manager_view AS
SELECT id, name, department
FROM employees
WHERE department = 'Engineering';

GRANT SELECT ON manager_view TO engineering_manager;

This combines a column restriction (no salary) with a row restriction (only Engineering), all through one GRANT on one view — the manager never needs, and never receives, any privilege on employees itself.

Relationship to GRANT (Chapter 10) and Database Security (Chapter 27)

View-based security works in combination with GRANT/REVOKE: the view defines what slice of data exists, while GRANT defines who may see that slice. This two-layer model — restrict the shape of the data via a view, then restrict access to that view via privileges — is the standard pattern used before more advanced mechanisms like row-level security (RLS) policies, which some modern engines (PostgreSQL, Oracle VPD) provide as a first-class alternative. Chapter 27 covers the broader database-security picture (encryption, auditing, roles) that view-based restriction fits into.

Edge Cases

• The base-table grant must NOT be given to the restricted role — if a user has direct SELECT on employees in addition to the view, the view provides no protection at all; the two grants must be managed carefully (usually via REVOKE on the base table).
• View owner's privileges matter: in most engines, a view executes with the privileges of its owner/definer (not the querying user), which is exactly what allows a low-privilege user to query a view over a table they can't access directly. This is sometimes called "definer's rights."
• Column-level GRANT (GRANT SELECT (id, name) ON employees TO staff_role;) is a lighter-weight alternative some engines support directly, without needing a view — but views remain more portable and support row filtering too, which column-level grants alone cannot do.
• Views don't stop indirect leakage: if a view exposes salary indirectly through, say, a computed salary_band column, a determined user could infer bounds — security-by-view still requires careful design.

Key Takeaways / Interview Q&A

Q: How do you hide a sensitive column from a group of users using views? A: Create a view that excludes the sensitive column, grant SELECT on the view (not the table) to that group, and never grant them direct table access.

Q: Why does a view-based restriction still work even though the querying user has no privilege on the base table? A: Because the view typically runs with the privileges of its owner ("definer's rights"), not the querying user — the user only needs privilege on the view itself.

Q: What's the difference between view-based security and row-level security (RLS) policies? A: Views require creating and granting a separate object per access pattern; RLS policies attach filtering rules directly to the base table so the same table automatically filters rows per user/role without needing multiple views.