Grant Command - Exercise

Course project: Granting access to database objects
Objective: Grant access to the database objects in the COIN database.

Exercise Scoring

This exercise is worth a total of 10 points. Each of the indexes is worth 5 points.

Background | Overview

You already have created a script that can create database objects in the COIN database. In this exercise, you will create a script that will grant access to these tables to a series of users.

Download files

You can download a correct version of the script for this exercise, grants.sql, from the project download file found on the Resources page.

Instructions

Add an index for the two foreign keys BIDDER_CLIENT_ID in the BID table and OWNER_CLIENT_ID in the COIN table.

Hints

The following table sets out the access requirements for the tables in the COIN database. Your script should assume that there are five database users

ADMIN,
BID1,
BID2,
SELLER1 and
SELLER2.

Each cell in the table describes the privileges for each table

S(elect),
I(nsert),
U(pdate),
D(elete), or
A(ll).

If there is a dash (-) in a cell, it means that the user has no privileges on the table:

User name/Table name	ADMIN	BID1	BID2	SELLER1	SELLER2
`AUCTION`	A	-	-	-	-
`COIN`	A	S	S	A	A
`COINS_IN_LOT`	A	-	-	A	A
`CLIENT`	A	A	A	A	A
`LOT`	A	-	-	-	-
`BID`	A	A	A	S	S

If all users will receive the same privileges for a table, you can assign those privileges to the PUBLIC system role.

Submitting your exercise

Submit your answer in the text box below.