TREASURE HUNTER’S RELATIONAL MODEL Player ( username , firstName, lastName, gend

Question

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.

                                                                               

Question

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

B. User Nikki must be able to remove records from the PLAYER table

C. User Phil is no longer allowed to add data to the PLAYER table

D. User Phil is no longer allowed to delete records from the PLAYER table

Assume usernames of employees Nikki and Phil are nikki and phil respectively.

Explanation / Answer

The Syntax to grant/revoke access is :

GRANT/REVOKE privileges ON object TO user;

Various Permissions are :

Access to SELECT statements on the table

Using above syntax : We will Grant Permission to Nikki. As Nikki is allowed to add/remove records from table PLAYER. So granting her access:

GRANT INSERT, DELETE ON PLAYER TO 'nikki';

Or we can set ALL permissions to nikki.

GRANT ALL ON PLAYER TO 'nikki';

As Phil is not allowed to insert or delete a record, we will grant All permission to him and revoke INSERT DELETE permissions.

Grant All

GRANT ALL

ON PLAYER

TO 'phil';

Revoke INSERT and DELETE

REVOKE INSERT, DELETE

ON PLAYER

TO 'phil';

Or We can manully grant each permissions. Like :

GRANT SELECT, INDEX,CREATE.. etc

ON PLAYER

TO 'phil';

SELECT

Access to SELECT statements on the table

INSERT Access to INSERT statements on the table UPDATE Access to UPDATE statements on the table DELETE Access to DELETE statements on the table INDEX Access to INDEX statements on the table. CREATE Access to create an index on an existing table. ALTER Access to ALTER TABLE statements DROP Access to DROP TABLE statements ALL Grants all permissions except GRANT OPTION

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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