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