Permission Exercise 1

🏫 Exercise: Database Access Management — “School Canteen System”

Scenario

Your school has a canteen management system that keeps track of students, food items, and daily orders.

The same database is used by:

• Canteen staff (who manage food items and view all orders)
• Students (who can view the menu and their own orders)
• Accountants (who generate financial reports but must not modify data)
• A web application (which needs limited access to display menus and insert new orders)

Your task is to:

1. Create the database and insert sample data.
2. Create users and/or roles to reflect the access needs above.
3. Assign the appropriate privileges to each user or role.

---

🧱 Database Structure and Sample Data

CREATE DATABASE SchoolCanteen;
USE SchoolCanteen;
 
CREATE TABLE Students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    class VARCHAR(10) NOT NULL,
    balance DECIMAL(6,2) DEFAULT 0.00
);
 
CREATE TABLE FoodItems (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    item_name VARCHAR(100) NOT NULL,
    category ENUM('Main','Snack','Drink') NOT NULL,
    price DECIMAL(5,2) NOT NULL
);
 
CREATE TABLE Orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    item_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    order_date DATE NOT NULL,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (item_id) REFERENCES FoodItems(item_id)
);
 
INSERT INTO Students (full_name, class, balance) VALUES
('Alice Johnson', '3A', 15.50),
('Ben Walker', '4B', 7.20),
('Carla Diaz', '2C', 20.00);
 
INSERT INTO FoodItems (item_name, category, price) VALUES
('Chicken Sandwich', 'Main', 4.50),
('Apple Juice', 'Drink', 1.80),
('Chocolate Bar', 'Snack', 1.20);
 
INSERT INTO Orders (student_id, item_id, quantity, order_date) VALUES
(1, 1, 1, '2025-11-10'),
(1, 3, 2, '2025-11-10'),
(2, 2, 1, '2025-11-09'),
(3, 1, 1, '2025-11-08');
 

---

🎯 Requirements

1. There should be at least four distinct MySQL users or roles representing:
   • A canteen staff member
   • A student
   • An accountant
   • A web application
2. Each of these users should have different levels of access to the database, reflecting their real-life roles.
3. The canteen staff should be able to view, add, update, and delete food items and view all orders.
4. The student should be able to view the menu and see only their own orders, but not modify or see others’.
5. The accountant should be able to view all data for reporting, but not modify anything.
6. The web application user should have minimum access required to display the menu and insert new orders.
7. Use roles or direct privileges so that these access rules can be managed efficiently.
8. Verify that each user or role can only perform the actions they’re supposed to, and that any unauthorized actions fail with an error.