week 8

8.4 Database Views for Security

A VIEW is a virtual table based on the result of a SELECT query. Views provide security by allowing users to see only specific rows and columns, hiding sensitive data.

 

-- Create a view that hides salary information from non-HR staff

CREATE VIEW EMPLOYEE_PUBLIC AS

SELECT EmployeeID, FirstName, LastName, Department, JobTitle, Extension

FROM EMPLOYEE

WHERE IsActive = TRUE;

 

-- Create a view for a specific branch manager — only their branch's customers

CREATE VIEW MINNA_BRANCH_CUSTOMERS AS

SELECT CustomerID, FirstName, LastName, AccountType, Balance

FROM CUSTOMER

WHERE BranchCode = 'MNA001';

 

-- Grant access to the view, not the base table

GRANT SELECT ON bank_db.EMPLOYEE_PUBLIC TO 'branch_user'@'localhost';

 

View Security Benefit: A bank branch manager can SELECT from MINNA_BRANCH_CUSTOMERS and see only their branch's customers. They cannot see customers from other branches even if they try SELECT * FROM CUSTOMER (they don't have permission on the base table). This is security through abstraction.