INFS2200 Chap.2 Views, Security and Updateability
Views, Security and Updateability
A view is a named query that offers a stable logical interface over base tables. This chapter compares query modification with materialisation, explaining the freshness, storage and maintenance trade-offs. It also treats views as part of enforced authorisation rather than cosmetic hiding, and tests updateability by mapping each requested view change back to a unique legal base-table change.
Aggregate and join views are examined through that reverse-mapping test.
Ordinary views store a definition and are evaluated against current base data. Query modification replaces a view reference with the underlying tables and predicates. This preserves freshness but can repeat an expensive computation.
A materialised view stores the query result, making repeated analytical reads cheaper at the cost of extra storage and refresh management. Read frequency, update frequency and accepted staleness activate the choice.
Updateability is treated as a mapping problem. A filtered view over one table can often identify one base row, while an aggregate cannot tell the DBMS which contributing rows should change.
Join views need an explicit key-preservation argument. Security follows similar discipline: restrict base privileges, grant access to a deliberately limited view and describe what it exposes. Hiding a field in an application is not the same as enforced authorisation.
Freshness is an operational property. A materialised result needs a schedule or event trigger, a visible data timestamp and failure handling.
The chapter therefore closes every implementation choice with its maintenance consequence, ensuring that a faster read is not recommended without accounting for stale results or additional work.
Query modification preserves one source of truth by expanding the view definition into the user's statement. The optimiser can then combine predicates and choose access paths across the expanded expression.
This logical convenience does not guarantee low cost: a repeated aggregation or complex join may be recomputed for every reference. The workload determines whether current evaluation or stored reuse is the better service contract.
Materialised refresh needs a publication boundary. Rebuilding into a separate object and switching readers only after validation prevents a failed job from exposing a partial mixture.
Incremental refresh can process recorded changes instead of all base rows, but it must correctly represent insertions, deletions and updates. Counts, totals and a source-data cut-off help demonstrate what the published result contains.
View security follows least privilege. Users receive only the operations needed on the view while direct base-table access remains restricted.
A row or column omitted by the definition is protected only if alternative paths cannot reveal it. The design should also consider inference through aggregates and whether error messages expose hidden structure. A limited interface supports authorisation but does not replace a complete privilege review.
For writes, narrate the inverse operation.
Identify the base row, supply every required base value and show that the result still satisfies the view predicate and underlying constraints. WITH CHECK OPTION can reject changes that move a row outside the view's visible population. Aggregation, duplicate elimination or a non-key-preserving join breaks a simple inverse, so any procedural update rule must state the intended allocation rather than letting the DBMS guess.
What this chapter covers
- 01
Views as named queries
- 02
Logical independence and access control
- 03
Query modification
- 04
Materialised results and refresh
- 05
Reverse mapping for updates
Worked example · free
Choose a materialised dashboard view
- +1Identify the repeated expensive read.
- +1State the accepted freshness window.
- +1Choose materialisation for reuse.
- +2Name refresh, storage and failure obligations.
Key terms
- View
- A named query exposed as a virtual table.
- Query modification
- Rewriting a view query onto its base tables.
- Materialised view
- A stored view result maintained through a refresh policy.
- Updateability
- The ability to translate a view change unambiguously to legal base changes.
Views, Security and Updateability FAQ
Why use a view instead of copying data?
A view supplies a reusable logical perspective without creating another uncontrolled copy. Ordinary views stay tied to current base data, while copied tables can drift unless a separate maintenance process keeps them aligned. The definition also communicates the intended interface.
When should a view be materialised?
Materialisation suits a costly result that is read repeatedly when bounded staleness is acceptable. The benefit is reuse; the obligations are refresh scheduling, storage, monitoring and a clear data timestamp. A real-time requirement can reverse the decision.
Why are aggregate views difficult to update?
One aggregate row summarises many base rows. Changing the displayed total or average does not identify a unique set of underlying changes, so the DBMS would need an invented allocation rule. Updates should target identified base rows or an explicit procedure.
Exam move
For each view scenario, write the user purpose, implementation path, freshness rule, privilege boundary and reverse mapping. Compare a current virtual view with a stored materialised result using the workload rather than memorising one as universally better. Practise three contrasting cases: a lightweight current-data interface, a costly dashboard with nightly freshness and an aggregate a user wants to edit.
Narrate how each read reaches base rows and whether a proposed write has one unambiguous reverse path. Add a privilege sentence stating what the user may query and what remains inaccessible. Finish materialisation answers with the refresh owner, timestamp and failure response. These details convert a generic performance claim into an operational design.