Lecture 11: Views, Functions & Database Security

1. Views in Depth

A View is a virtual table defined by an underlying SQL query. Views provide four core architectural benefits:

1. Security / Access Control: Restricts users to authorized rows and columns (e.g., exposing student names while hiding test grades).
2. Query Simplicity: Encapsulates 4-table joins into a single reusable entity.
3. Logical Data Independence: When underlying table schemas change, views can be remapped to prevent breaking external consumer applications.
4. Modularity: Allows standardizing business metrics across analytics teams.

2. Updatable Views & WITH CHECK OPTION

Can you execute an INSERT or UPDATE through a view?

Conditions for a View to Be Updatable (ANSI SQL Standard):

1. The FROM clause contains exactly one base table (no joins).
2. The SELECT clause does not contain DISTINCT, aggregate functions (AVG, SUM), or window functions.
3. The query does not use GROUP BY or HAVING.
4. Any columns in the base table that are NOT NULL without default values must be included in the view projection.

The Disappearing Row Problem & WITH CHECK OPTION

Consider a view of high-salary faculty:

CREATE VIEW high_salary_faculty AS
SELECT id, name, dept_name, salary
FROM instructor
WHERE salary >= 80000
WITH CHECK OPTION; -- Enforces predicate validity!

• Without WITH CHECK OPTION: If a user updates Bob's salary to $45,000 via this view, the update succeeds, but Bob immediately disappears from the view!
• With WITH CHECK OPTION: The DBMS rejects any INSERT or UPDATE through the view that produces a row where salary < 80000, returning a constraint violation error.

3. Materialized Views

4. Role-Based Access Control (RBAC) & Security

+---------------+      grants      +---------------+     grants      +-----------------+
|     User      | <-------------- |     Role      | <-------------- | Object Privilege|
| (e.g., 'john')|                  | ('ta_grader') |                  | (SELECT on takes)|
+---------------+                  +---------------+                  +-----------------+

The GRANT Statement

-- Grant read access on student table to role 'advisor'
GRANT SELECT ON student TO advisor;

-- Grant column-specific update
GRANT UPDATE (grade) ON takes TO ta_role;

-- Grant with delegating authority
GRANT SELECT ON course TO department_head WITH GRANT OPTION;

The REVOKE Statement

-- Revoke privilege
REVOKE SELECT ON student FROM advisor;

-- Cascade vs Restrict
-- CASCADE revokes from everyone whom the user granted permissions to via WITH GRANT OPTION
REVOKE SELECT ON course FROM department_head CASCADE;

5. Topic Practice Questions