Security View - Hide Sensitive Columns

basic

Employee table has salary and SSN. Create view for general access that hides sensitive data.

CREATE TABLE employees (

employee_id SERIAL PRIMARY KEY,

name VARCHAR(100) NOT NULL,

email VARCHAR(255) NOT NULL,

department VARCHAR(50),

salary DECIMAL(10,2),

ssn VARCHAR(11),

hire_date DATE

);

CREATE VIEW employee_directory AS

SELECT

employee_id,

name,

email,

department,

hire_date

FROM employees;

GRANT SELECT ON employee_directory TO public;

REVOKE SELECT ON employees FROM public;

SELECT * FROM employee_directory;

Shows only non-sensitive columns. Salary and SSN hidden.

View provides security layer. Users query employee_directory but can't see salary/SSN. Always current because it queries base table.

All

graph LR

A[employees table] --> B[All columns including salary, SSN]

C[employee_directory view] --> D[Only public columns]

E[Public Users] --> C

E -.->|No Access| A

F[HR Users] --> A

Materialized View - Daily Sales Dashboard

intermediate

Dashboard shows daily sales. Query joins orders, customers, products and takes 45 seconds. Viewed 500 times/day. Use materialized view to pre-compute results.

CREATE MATERIALIZED VIEW daily_sales_dashboard AS

SELECT

DATE(o.order_date) as sale_date,

COUNT(DISTINCT o.order_id) as total_orders,

COUNT(DISTINCT o.customer_id) as unique_customers,