Database Management Systems II $($DBMS$-2006)$

Challenge $5$ Users and Roles

Note: Use the JR$\_$Movie database in this challenge.

$1.$ Create logins and user accounts for the following users. The login for each user must be their full name, capitalizing the first character of first and last name e$.$g JenniferGarnet. The user id must be the first letter of the user$$s first name and their full last name $($permissions will be given shortly$).$ Use your Red River Userid as the password for all user accounts.

User Name Employee Job

Jennifer Garnet Receptionist

Phil Blat Salesperson

Rhonda Seymore Sales Manager

Role Creation: Create the following roles. Do not embed permissions $($do not grant a role to a role$).$ Each role must have all required permissions defined.

$2.$ Create a role called receptionist$\_$role.

$3.$ The receptionist role needs to have SELECT on the following tables:

$$ Customer

$$ RentalAgreement

$$ MovieRented

$$ Movie

$4.$ Give Jennifer Garnet the receptionist role.

$5.$ Create a role called salesperson$\_$role.

$6.$ The salesperson role needs select on the following tables:

$$ Customer

$$ RentalAgreement

$$ MovieRented

$$ Movie

In addition, the role requires INSERT on the RentalAgreement and MovieRented tables.

$7.$ Give Phil Blat the sales person role.

$8.$ Create a role called salesmanager$\_$role

$9.$ The salesmanager role needs full permissions $($SELECT$,$ INSERT, UPDATE and DELETE$)$ on the following tables:

$$ Customer

$$ RentalAgreement

$$ MovieRented

$$ Movie

$10.$ Give Rhonda Seymore both the salesperson and and sales manager roles.

$11.$ Perform the following for all three accounts

$$ Show the command necessary to impersonate the account

$$ Write a sql statement $($SELECT$,$ INSERT, UPDATE and DELETE$)$ to test the permissions of each account for each table $($Customer$,$ RentalAgreement, MovieRented and Movie$).$ For each insert statement into the customer table ensure that the FNAME and LNAME columns your first name and last name

$$ Include a comment before each statement of $$success$$ or $$failure$.$ Was this expected why or why not?