Question

Write a script that creates a user-defined database role named OrderEntry in the MyGuitarShop database. Give INSERT and UPDATE permission to the new role for the Orders and OrderItems table. Give SELECT permission for all user table.

Answer 1

USE MyGuitarShop ;

CREATE ROLE OrderEntry ;

GRANT INSERT ON Orders ,OrderItems TO OrderEntry ;

GRANT UPDATE ON Orders ,OrderItems TO OrderEntry ;

GRANT SELECT ON Orders ,OrderItems TO ALL;

Step-by-step explanation:

USE MyGuitarShop ;

This statement is used to make MyGuitarShop as the current database

CREATE ROLE OrderEntry ;

This statement creates new role called "OrderEntry".Roles are useful in database to grant permissions for users on particular objects of the database.

GRANT INSERT ON Orders ,OrderItems TO OrderEntry ;

GRANT UPDATE ON Orders ,OrderItems TO OrderEntry ;

above two GRANT statements are used to grant Insert,Update permissions to the Role "OrderEntry " for the tables Orders ,OrderItems.All the users of Role "OrderEntry" are able to grant Insert,Update on the tables Orders ,OrderItems.

GRANT SELECT ON Orders ,OrderItems TO ALL;

above GRANT statement is used to give select permission on specified tables to all users.