[Main Page]      [Literature]      [Software]      [People]      [Conferences]      [Courses]      [Web-DB]

Homework Assignment #10:    Access Rights, Views

Reading Assignment

Please read the following chapter from the book by Elmasri and Navathe (3rd Edition):

• Chapter 22, "Database Security and Authorization"

Exercise 10 (12 Points)

Suppose you work for a small company which produces a guide to the restaurants of Pittsburgh.

    a)

Create tables for storing the results of tests of the restaurants:

• Restaurants(RID, RName, Owner^o)
• Tests(RID -> Restaurants, TestDate, Tester, Grade, Comments^o, Cost)

"Tester" is the name of the person who tried the food in the restaurant, and "Grade" is e.g. "A+" "Comments" are a short explanation for the grade (not more than 2000 characters). "Cost" is the reimbursement for the tester (i.e. the cost of the meal). Make sure that only "Owner" and "Comments" can be null, and declare keys and foreign keys. For names, usually 20 characters are sufficient. Use the data type "DATE" for "TestDate". Declare a constraint that enforces that "Cost" is not negative.

    b)

Enter at least one tuple into Restaurants and two tuples into Tests.

    c)

The owner of Scott's Restaurant (Owner = 'Scott') has a database account (user "scott"). He should be able to see the test results of his restaurant before the guide is published. He should not be able to see the results of other restaurants. It is also very important that the testers remain anonymous, so he should only see the columns "TestDate", "Grade", and "Comments". In order to make the identification of the testers more difficult, only tests from at least 7 days ago should be shown (He might remember who ate what on the day before.). "TestDate+7" gives the date one week after "TestDate", "SYSDATE" is the current date. Dates are written e.g. '05-APR-00'.
Please define a view "ScottsTest" for the user "scott" and make it readable to him.

    d)

Log in as the user "scott" and check that you can access the view, but not the base table.

Due Date:

Please submit your solution on next Wednesday (April 12) before the lecture.

Stefan Brass (sbrass@sis.pitt.edu), April 5, 2000

Original URL: http://www2.sis.pitt.edu/~sbrass/db00_2/ha_secur.html   [HTML 3.2 Checked]