Snippet

Restricting Sensitive Data Exposure via Dynamic Views

Views serve as virtual tables that hide underlying schema complexity and restrict column or row exposure. By exposing a curated view instead of direct table access, sensitive data like salary or home address remains secure.

snippet.sql

sql

CREATE VIEW public_employee_directory AS
SELECT
    employee_id,
    first_name,
    last_name,
    department_id,
    COALESCE(work_email, 'N/A') AS contact_email
FROM employees
WHERE status = 'ACTIVE';

Breakdown

CREATE VIEW public_employee_directory AS

Establishes a named virtual dataset based on a predefined SQL query.

employee_id, first_name, last_name, department_id,

Explicitly selects non-sensitive columns while excluding confidential fields.

COALESCE(work_email, 'N/A') AS contact_email

Provides a fallback text string when the email address column contains NULL.

WHERE status = 'ACTIVE';

Enforces row-level filtering to present only currently employed personnel.

Previous snippet Next snippet

More Intermediate SQL exercises →

Restricting Sensitive Data Exposure via Dynamic Views

Related