I need the SQL statements for these. If you aren\'t sure of your answers its oka
ID: 3831091 • Letter: I
Question
I need the SQL statements for these. If you aren't sure of your answers its okay, they dont have to be 100% right. I would just like them all answered, and as correct as possible. Thank you.
tables:
CUSTOMER
9) Write the SQL commands to grant the following privileges on the Colonial Adventure Tours Database a) User Lopez must be able to retrieve data from the TRIP table b) Users Brown and Merrill must be able to add new trips and trip guides to the database. c) Users Jenkins and Sherman must be able to change the price of any reservation. d) All users must be able to retrieve the trip id, trip name, start location and state for each trip. e) User Scholten must be able to add and delete reservations. f User Verner must be able to create an index for the RESERVATION table. g) Users Verner and Scholten must be able to change the structure ofthe TRIP GUIDES table. h) User Scholten must have all privileges on the TRIP, CUSTOMER, and RESERVATION tables. erner has just been fired from Colonial Adventure Tours. Write the command to revoke all of user Verner's privileges.Explanation / Answer
Below are your SQL statements: -
a) (#Note: - Lopez was not found in customer table. If it exists, below command will work.)
GRANT SELECT
ON TRIP TO LOPEZ
WITH GRANT OPTION
b)
create role tripCreaters
GRANT INSERT
ON TRIP,TRIP_GUIDES,GUIDE TO tripCreaters
WITH GRANT OPTION
GRANT tripCreaters to Brown,Merrill
c)
create role tripCreaters
GRANT UPDATE(TRIP_PRICE)
ON RESERVATION TO tripCreaters
WITH GRANT OPTION
grant tripCreaters to Jenkins,Sherman
d)
GRANT SELECT(TRIP_ID,TRIP_NAME,START_LOCATION,STATE)
ON TRIP TO ALL
WITH GRANT OPTION
e)
GRANT INSERT,DELETE
ON RESERVATION TO Scholten
WITH GRANT OPTION
f)
GRANT INDEX
ON RESERVATION TO Verner
WITH GRANT OPTION
g)
create role tripAlters
GRANT ALTER
ON TRIP_GUIDES TO tripAlters
WITH GRANT OPTION
GRANT tripAlters to Verner,Scholten
h)
GRANT ALL
ON TRIP,CUSTOMER,RESERVATION TO Scholten
WITH GRANT OPTION
i)
REVOKE ALL
ON ALL FROM Verner