Lab Assignment
Based upon the relations shown below generate and run Oracle SQL queries to create the appropriate level of security for each scenario.
Step A: write and execute the SQL code necessary to create these table (note see school example attached).
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
|
DEPARTMENT
|
ADDRESS
|
RANK
|
|
0980
|
MARTIN
|
IM
|
11 MAIN
|
DEAN
|
|
5430
|
SEAVER
|
IS
|
12 SOUTH
|
PROFESSOR
|
|
7650
|
LOONEY
|
IT
|
14 NORTH
|
INSTRUCTOR
|
|
9870
|
MILLS
|
SA
|
16 EAST
|
LECTURER
|
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
|
Using account in which you have DBA privilege, complete the following steps:
Step B: Write and execute the SQL code necessary to Create User Accounts with passwords for All Students and Faculty.
Step C: Write and execute the SQL code necessary to Grant the following access modes to the Users and Tables as indicated below:
|
TABLE
|
USER
|
PRIVLEDGE
|
|
STUDENT
|
ABLE
|
SELECT
|
|
STUDENT
|
BAKER
|
SELECT, UPDATE
|
|
STUDENT
|
CHARLES
|
SELECT, INSERT
|
|
STUDENT
|
DRAKE
|
SELECT, DELETE
|
|
STUDENT
|
ELLIOT
|
SELECT, UPDATE ON MAJOR ONLY
|
|
FACULTY
|
MARTIN
|
SELECT, UPDATE
|
|
FACULTY
|
SEAVER
|
SELECT, INSERT, DELETE
|
|
FACULTY
|
LOONEY
|
SELECT, UPDATE ON ADDRESS ONLY
|
|
FACULTY
|
MILLS
|
SELECT, UPDATE, INSERT
|
Step D: Write and execute the SQL code necessary to Grant Create Session (or Connect if you have problems with Create Session) to each User
Step E: Write and execute the SQL code necessary to demonstrate the access that each user has and to show they do not have other access rights, i.e., log on (connect) as each User and Demonstrate that the user has the access as indicated in the table and does not have any other access (e.g., log on as Able and show that able can select from student, but cannot update on student; then log on as Baker, Charles, etc. and show that Baker, Charles, etc. can select from student, etc. Note if a user las 2 or 3 privileges demonstrate that they can do all of them)
Format your assignment according to the give formatting requirements:
The answer must be using Times New Roman font (size 12), double spaced, typed, with one-inch margins on all sides.
The response also includes a cover page containing the student's name, the title of the assignment, the course title, and the date. The cover page is not included in the required page length.
Also include a reference page. The references and Citations should follow APA format. The reference page is not included in the required page length.