UserMangment Ex1.sql

-- 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


-- (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;