Question

Your database contains a role called nurse and many users who have that role. One of the users in the system is jackie. You need to write statements to implement the following privileges: All nurses should have read access to the shifts table; Jackie happens to be a nurse, but also has administrative duties and should be able to update, insert, and delete from the shifts table.

Write SQL that accomplishes this

Answer 1

--Create a role named nurse
CREATE ROLE nurse;

--Grant read access on shifts table to role nurse
GRANT SELECT ON shifts TO nurse;

--Grant role nurse to user Jackie
GRANT nurse TO 'Jackie'@'localhost';

--Grant additional privileges to user Jackie
GRANT UPDATE, INSERT, DELETE ON shifts TO 'Jackie'@'localhost';