We have provided you with the creation script for the Treasure Hunter’s database

Question

We have provided you with the creation script for the Treasure Hunter’s database. You should run this script in MySQL Workbench and use this database to extract the necessary information. The script is based on the following schematic: TREASURE HUNTER’S RELATIONAL MODEL Player (username, firstName, lastName, gender, DOB, email, streetNo, streetName, suburb, state, postcode, creationDateTime, totalPoints) PhoneNumber (phoneNumber, username) Treasure (treasureID, description, points, webpage, type, questID) Quest (questID, questName, story, beacon, advancedQuestID) Store (storeID, storeName, openTime, closeTime) Badge (badgeID, badgeName, badgeDescription) PlayerProgress (questID, username, progress) PlayerTreasure (username, treasureID) Purchase (purchaseID, storeID, username, badgeID, purchaseDateTime, cost) FOREIGN KEYS • PhoneNumber (username) is dependent on Player(username) • Quest (advancedQuestID) is dependent on Quest(questID) • Treasure (questID) is dependent on Quest (questID) • PlayerProgress (questID) is dependent on Quest (questID) • PlayerProgress (username) is dependent on Player (username) • PlayerTreasure (username) is dependent on Player (username) • PlayerTreasure (treasureID) is dependent on Treasure (treasureID) • Purchase (storeID) is dependent on Store (storeID) • Purchase (username) is dependent on Player (username) • Purchase (badgeID) is dependent on Badge (badgeID) OTHER CONSTRAINTS • Player (gender) must be female, male, other or prefer not to disclose. • Player (state) domain is [QLD, SA, TAS, NSW, WA, NT or ACT]. • Treasure (type) domain is [common, uncommon, rare, ultra-rare or elite]. • Players may enter up to three phone numbers. • Players must enter at least one phone number. • PlayerProgress (progress) domain is [active, inactive or complete]. • Player (email) is mandatory

Task 5 [2 marks] Treasure Hunters Company have two employees, Nikki and Phil, to work with the MySQL database. Working as MySQL database administrator, provide the commands required to grant or revoke access so the following security requirements are met: A. User Nikki must be able to add records to the PLAYER table (0.5 Marks) B. User Nikki must be able to remove records from the PLAYER table (0.5 Marks) C. User Phil is no longer allowed to add data to the PLAYER table (0.5 Marks) D. User Phil is no longer allowed to delete records from the PLAYER table (0.5 Marks) Assume usernames of employees Nikki and Phil are nikki and phil respectively.

Explanation / Answer

Hi,
we can use the grant statement of SQL to give permissions to users on tables,
A. GRANT SELECT,INSERT ON PLAYER TO nikki;
this or you can try GRANT SELECT,INSERT TO 'nikki'@'localhost'; --since a local database

this will grant select and update statements privilege on player table to nikki user
B.GRANT DELETE ON PLAYER TO nikki;
this or you can try GRANT DELETE ON PLAYER TO 'nikki'@'localhost'; --since a local database
this will grant delete privelege to user nikki
Now, to revoke also mysql provides the revoke command
C.REVOKE INSERT ON PLAYER FROM phil;
or you can try .REVOKE INSERT ON PLAYER FROM 'phil'@'localhost'; --since a local database
this will revoke insert privilege from phil
D.REVOKE DELETE ON PLAYER FROM phil;
or you can try .REVOKE DELETE ON PLAYER FROM 'phil'@'localhost'; --since a local database
this will revoke delete privilege from phil

Thumbs up if this was helpful otherwise let me know in comments

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.