Object Privileges «Prev  Next»

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
  1. ADMIN,
  2. BID1,
  3. BID2,
  4. SELLER1 and
  5. SELLER2.
Each cell in the table describes the privileges for each table
  1. S(elect),
  2. I(nsert),
  3. U(pdate),
  4. D(elete), or
  5. 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.