Views for security and abstraction in SQL - Mini Project: Build & Apply

Views for Security and Abstraction

📖 Scenario: You work for a company that stores employee information in a database. Some details, like salaries, should be kept private. You want to create a simple way for managers to see employee names and departments without exposing sensitive data.

🎯 Goal: Create a database view that shows only employee names and their departments. This view will help managers access necessary information securely without seeing confidential details like salaries.

📋 What You'll Learn

Create a table called employees with columns id, name, department, and salary.

Insert exactly three employees with specified values.

Create a view called employee_overview that shows only name and department from employees.

Use the view to select all employee names and departments.

💡 Why This Matters

🌍 Real World

Companies often need to protect sensitive employee data while allowing managers to see relevant information. Views help by showing only what is necessary.

💼 Career

Database administrators and developers use views to control data access and simplify complex data structures for different users.

1

Create the employees table and insert data

Create a table called employees with columns id (integer), name (text), department (text), and salary (integer). Then insert these three employees exactly: (1, 'Alice', 'HR', 50000), (2, 'Bob', 'IT', 60000), and (3, 'Charlie', 'Finance', 55000).

SQL

-- Create the employees table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Define the view employee_overview

Create a view called employee_overview that selects only the name and department columns from the employees table.

SQL

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    department TEXT,
    salary INTEGER
);

INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'HR', 50000),
(2, 'Bob', 'IT', 60000),
(3, 'Charlie', 'Finance', 55000);

-- Create the employee_overview view
-- Your code here

Need a hint?

Use CREATE VIEW view_name AS SELECT ... to define the view.

3

Query the employee_overview view

Write a SQL query to select all columns from the employee_overview view.

SQL

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    department TEXT,
    salary INTEGER
);

INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'HR', 50000),
(2, 'Bob', 'IT', 60000),
(3, 'Charlie', 'Finance', 55000);

CREATE VIEW employee_overview AS
SELECT name, department FROM employees;

-- Select all from employee_overview
-- Your code here

Need a hint?

Use SELECT * FROM employee_overview; to get all rows and columns from the view.

4

Secure the view by restricting direct access to employees

Add a SQL statement to revoke direct SELECT access on the employees table from public users, so only the view employee_overview can be used to see employee names and departments.

SQL

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    department TEXT,
    salary INTEGER
);

INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'HR', 50000),
(2, 'Bob', 'IT', 60000),
(3, 'Charlie', 'Finance', 55000);

CREATE VIEW employee_overview AS
SELECT name, department FROM employees;

SELECT * FROM employee_overview;

-- Revoke direct SELECT access on employees table
-- Your code here

Need a hint?

Use REVOKE SELECT ON employees FROM PUBLIC; to restrict direct access.