DATABASE SECURITY

Student: Date: MM/DD/YYYY

Student: Last, First Date: MM/DD/YYYY

Email: [email protected]

Student: Last, First Date: MM/DD/YYYY

Database Security Lab 3 Parts 1 & 2

1. The tables shown below were created using Oracle SQL. Only the faculty table has changed from the example in Lab 1. Drop and recreate the faculty table. Show your work for each question. Remember to consolidate all your files and output into one document.

STUDENT

STUDENTID	NAME	MAJOR	STATUS	ADDRESS	GPA
100	ABLE	HISTORY	SR	1 UTAH	3.00
200	BAKER	ACCOUNTING	JR	2 IOWA	2.70
300	CHARLES	MATH	SR	3 MAINE	3.50
400	DRAKE	COMPUTER SCIENCE	FR	4 IDAHO	2.80
500	ELLIOT	COMPUTER SCIENCE	SM	5 NEVADA	3.25

FACULTY

FACULTYID	NAME	ORGCD	ADDRESS	CC	SALARY	RANK	TITLE
0980	MARTIN	IM	11 MAIN	4560123450001234	250,000	DEAN	CEO
5430	SEAVER	IS	12 SOUTH	4560123450002345	180,000	PROFESSOR	CIO
7650	LOONEY	IT	14 NORTH	4560123450003456	160,000	INSTRUCTOR	CISO
9870	MILLS	SA	16 EAST	4560123450004567	90,000	LECTURER	CFO
9990	BOND	INT	007 NE	4560123450005678	225,000	COACH	SPY

OFFERING

OFFERINGNUM	COURSENUM	FACULTYID	TERM	YEAR	TIME
1111	IS320	5430	FALL	2012	10 AM
1233	IS320	0980	FALL	2012	11 AM
2222	IS460	7650	SPRING	2013	10 AM
3333	IT480	5430	SPRING	2013	11 AM

ENROLLMENT

OFFERINGNUM	STUDENTID
1111	100
1233	500
2222	300
3333	400

HEADER = Indicates Primary Key

Instructions for Submitting:

All assignments should be clearly formatted for readability and submitted using a cleanly formatted MS Word document or PDF document. (MS Word and PDF are the only acceptable formats. Lab is to be turned in as one document.

2. Create Roles ‘Student’ and ‘Faculty’.

a) Grant the Student Role the privilege to ‘Select on Student’.

b) Grant the Faculty Role the privilege to ‘Select on Faculty’.

c) Grant all students the Student Role and all faculty the Faculty Role.

d) Demonstrate the results of a student query on the Student table and of a faculty query on the Faculty table.

3. Create a View on the Student table called ‘S_Student’ that allows a student to select the Student table and update their own address, but no other student address.

a) Grant SELECT on ‘S_Student’ to the Student Role.

b) Grant UPDATE(address) on ‘S_Student’ to the Student Role.

c) Demonstrate how this works (this may be done with a before and after image of the student’s record with old and new student address).

4. Create a View that allows students to view their own information in the Student, Offering, and Enrollment tables.

a) Grant the necessary authority so that students can use this view.

b) Demonstrate that this works.

• Note: This should be one view that covers all three tables but allows students to view their own information only.

5. Create a View that allows Juniors and Seniors the right to change their major (but does not allow anyone else to change their major).

a) Grant the necessary authority so that Juniors and Seniors can use this view.

b) Demonstrate that this works.

6. User Martin, the Dean, has decided to start giving bonuses to outstanding faculty. To calculate the cost, the Dean decides to use the Faculty table to support this effort. This approach has the advantage that the Dean is the owner of the Faculty table..

a) Acting for the Dean, you should alter the Faculty table to add a bonus column that is invisible so that no one can see it except the Dean. Demonstrate that the Dean can do a select operation on Faculty after the table is altered and show what the Dean sees (do a select * on the table).

b) Show the Dean doing a ‘describe’ on the table (describe Faculty; the describe should not show the bonus column)

c) Show the Dean executing a Select Name, Bonus from Faculty (if you know the column exists it can be accessed by explicitly using the name of the column in the Select statement).

d) Show how the Dean can update the bonus information (execute an Update giving every faculty member a 10% raise).

e) Show what the other faculty see when they query the Faculty table (do a select)

7. The Dean has announced the bonuses. A notice has gone to each employee that will receive a bonus. The existence of bonus column is now known so a different way to restrict who can view the data is needed. (solution will use decode technique).

a) Generate the code that will enforce the new rule for access to the bonus information: bonus information can be viewed only by the person receiving the bonus (and the Dean).

b) Show what the other faculty see when they query the faculty table (do a select).

c) Show the Dean is still the only one who can update the bonus data. Show how to achieve this result. [Note: The solution is not a view with a Where clause of User = Name clause or a VPD].

8. The CISO is concerned that having both the ORGCD and TITLE in the same table reveals too much information--for example, Bond, ORGCD INT (Intelligence), TITLE SPY. Something must be done immediately to protect this information. As an interim step, TITLE should be completely redacted. Show how to achieve this result. (Use Full Redaction technique).

9. The CIO is irate about a hack against credit card numbers that the firm experienced. He has tasked the CISO with fixing the problem. The CISO has some ideas and quickly checks the recommendations from the National Credit Card Bureau (NCCB). The recommendation is to redact 12 of the 16 numbers for normal use (only the last 4 numbers will be visible (except to the CIO and CISO). The question is which design will be the most attractive to the CIO (what will she like?). The CISO decides to present 2 options for the redaction:

a) Completely blank the first 12 numbers. The CC list will look like:

CC

------------------------

1234

2345

3456

4567

5678

Show how you can achieve this result. (Use Partial Redaction of Type Character).

b) Mask the first 12 numbers with 9’s. The CC list will look like:

CC

--------------------

9999999999991234

9999999999992345

9999999999993456

9999999999994567

9999999999995678

Show how you can achieve this result. (Use Partial Redaction of Type Number).

10. The CISO requests a demonstration of the use of random redaction to protect the data in the FACULTYID and ORGCD columns (random numbers to replace the FACULTYID query response and random characters to protect the ORGCD). Show how you can achieve this result. (Use Redact random numbers for FACULTYID and Redact random characters for ORGCD).

Page 1 of 5

Page 1 of 5

Page 1 of 5