# Tutorial - 02

MySQL User Privilege Management – PetsDB

This file describes how to create a MySQL user and grant privileges step by step using a sample PetsDB database in XAMPP.

-- Login as root user
-- (i) Create user
CREATE USER 'staff01'@'localhost' IDENTIFIED BY '123';

-- (ii) Check user created
SELECT User, Host FROM mysql.user WHERE User = 'staff01';

-- (iii) Login as staff01 (manually via terminal or client)
-- Try creating database (will fail if no privileges)
-- CREATE DATABASE petsDB; -- Permission denied expected

-- (iv) Back as root: Show Grants
SHOW GRANTS FOR 'staff01'@'localhost';

-- (v) View all users
SELECT * FROM mysql.user;

---

-- A. CREATE Privilege

---

-- (i) Create PetsDB
CREATE DATABASE PetsDB;

-- (ii) Check DB
SHOW DATABASES;

-- (iii) Use DB
USE PetsDB;

-- (iv) Grant Create privilege
GRANT CREATE ON PetsDB.* TO 'staff01'@'localhost';

-- (v) Show Grants
SHOW GRANTS FOR 'staff01'@'localhost';

-- (vi) Login as staff01
-- (vii) SHOW DATABASES;
-- (viii) USE PetsDB;
-- (ix) Create Owner table
CREATE TABLE Owner ( OwnerId INT PRIMARY KEY, OwnerName VARCHAR(100), Town VARCHAR(100) );

-- (x) SHOW TABLES;
-- DESC Owner;

---

-- B. SELECT Privilege

---

GRANT SELECT ON PetsDB.* TO 'staff01'@'localhost';
FLUSH PRIVILEGES;

-- As staff01:
-- DESC Owner;
-- SELECT * FROM Owner;
-- INSERT INTO Owner VALUES (2323, 'John', 'Jaffna'); -- Will fail

---

-- C. INSERT Privilege

---

GRANT INSERT ON PetsDB.* TO 'staff01'@'localhost';
FLUSH PRIVILEGES;

-- As staff01:
INSERT INTO Owner VALUES (2323, 'John', 'Jaffna');
SELECT * FROM Owner;

-- Try update (will fail)
-- UPDATE Owner SET Town = 'Omanthai' WHERE OwnerId = 2323;

---

-- D. UPDATE Privilege

---

GRANT UPDATE ON PetsDB.* TO 'staff01'@'localhost';
FLUSH PRIVILEGES;

-- As staff01:
UPDATE Owner SET Town = 'Omanthai' WHERE OwnerId = 2323;
SELECT * FROM Owner;

-- Try delete (will fail)
-- DELETE FROM Owner WHERE OwnerId = 2312;

---

-- E. DELETE Privilege

---

GRANT DELETE ON PetsDB.* TO 'staff01'@'localhost';
FLUSH PRIVILEGES;

-- As staff01:
DELETE FROM Owner WHERE OwnerId = 2312;
SELECT * FROM Owner;

---

-- Ex02: Create and Manipulate Pets Table

---

-- As staff01 in PetsDB
CREATE TABLE Pets ( PetId INT PRIMARY KEY, PetName VARCHAR(100), OwnerId INT );

INSERT INTO Pets VALUES (111, 'Rocky', 2323), (112, 'Tiger', 2323), (113, 'Jimmy', 2323);

UPDATE Pets SET PetName = 'Browny' WHERE PetId = 113;
DELETE FROM Pets WHERE PetId = 112;

-- Try dropping DB (will fail without DROP privilege)
-- DROP DATABASE PetsDB;

---

-- F. DROP Privilege

---

GRANT DROP ON PetsDB.* TO 'staff01'@'localhost';
FLUSH PRIVILEGES;

-- As staff01:
DROP DATABASE PetsDB;
SHOW DATABASES;