Solutions Chapter 14 | Oracle SQL Interactive Workbook, 2nd Edition

Solutions Chapter 14: Security Test Your Thinking

To complete the exercises below, create a new user called SCHOOL with the password PROGRAM, and grant CONNECT and RESOURCE privileges to it. Then logon as the STUDENT user.

Solution:

-- Create SCHOOL user
CONN SYSTEM/MANAGER
CREATE USER school IDENTIFIED BY program;
GRANT CONNECT, RESOURCE TO school;

Create two roles: one called REGISTRAR, the other called INSTRUCTOR.

Solution:
```
CONN student/learn
CREATE ROLE registrar;
CREATE ROLE instructor;
```

Create a view called CURRENT_REGS reflecting all students that registered today. Grant SELECT privileges on the new view to the REGISTRAR role.

Solution:


CREATE OR REPLACE VIEW current_regs AS
SELECT first_name, last_name
  FROM student
 WHERE TRUNC(registration_date) = TRUNC(SYSDATE);

GRANT SELECT ON current_regs TO registrar;

Create a view called roster reflecting all students taught by the instructor Marilyn Frantzen. Grant SELECT privileges on the new view to the INSTRUCTOR role.

Solution:

CREATE OR REPLACE VIEW roster AS
SELECT se.course_no course, se.section_id section,
       s.first_name first, s.last_name last,
       e.student_id
  FROM student s, enrollment e, section se, instructor i
 WHERE s.student_id = e.student_id
   AND e.section_id = se.section_id
   AND se.instructor_id = i.instructor_id
   AND i.first_name = 'Marilyn'
   AND i.last_name = 'Frantzen';

GRANT SELECT ON roster TO instructor;

Grant the REGISTRAR and INSTRUCTOR roles to the new user SCHOOL.

Solution:
GRANT registrar, instructor TO school;
Start a SQL*Plus session as the new user SCHOOL, and select from the two views created previously.

Solution:
```
CONNECT school/program

SELECT *
  FROM student.current_regs;

SELECT *
  FROM student.roster;
```
The output from the first SELECT statement returns 0 rows. The second query results in 31 rows.

Solutions Chapter 14: Security Test Your Thinking

Select Another Chapter