Skip over navigation to the main content.

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;

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

    Solution:

    CONN student/learn
CREATE ROLE registrar;
CREATE ROLE instructor;

  2. 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;

  3. 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;

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

    Solution:

    GRANT registrar, instructor TO school;

  5. 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.

Further Reading

Read Prentice Hall PTR to stay on the cutting edge of computer science and engineering.