Non Materialized Views

Overview

Non-materialized views, also known as virtual views, are database objects that store SQL queries rather than physical data. Unlike materialized views, which persist query results, non-materialized views dynamically retrieve data from underlying tables whenever queried.

For IT and Security Engineers, understanding non-materialized views is crucial for access control, performance optimization, and compliance enforcement

Why Are Non-Materialized Views Important?

1. Access Control & Data Security

   • Views can restrict access to specific columns or rows, ensuring users see only authorized data.

   • By exposing only required fields (e.g., hiding sensitive columns like SSN or credit_card_number), views help enforce Principle of Least Privilege (PoLP).

2. Performance Considerations

   • Non-materialized views do not store results, so query execution depends on the underlying table’s performance.

   • Indexing and database optimization techniques (e.g., partitioning, caching) must be considered to avoid performance bottlenecks.

3. Compliance & Auditing

   • Regulatory standards (GDPR, HIPAA, PCI-DSS) require data masking and access restrictions. Non-materialized views help meet these compliance requirements.

   • Audit logging can be implemented to track queries accessing sensitive information via views.

4. Abstraction & Data Consistency

   • Views provide a layer of abstraction, allowing changes in the underlying schema without impacting applications.

   • They help enforce data consistency by presenting a unified interface to multiple tables.

Best Practices for Security and IT Operations

1. Implement Role-Based Access Control (RBAC)

• Grant least privilege access using database roles.

• Restrict access to base tables and allow users to interact only with views.

Example (PostgreSQL):

CREATE VIEW secure_view AS 
SELECT user_id, username FROM users WHERE role = 'customer';

GRANT SELECT ON secure_view TO app_user;