Answer Happy

1) Connect to your Pluggable Database2)// Create Tablescreate table department (dep_id int primary key,name varchar2(30));create table employee (dep_id references department,name varchar2(30));create table department_secrets (dep_id references department,secret varchar2(30));3)// FILL IN THE TABLESinsert into department values (1, 'Research andDevelopment');insert into department values (2, 'Sales' );insert into department values (3, 'Human Resources' );insert into employee values (2, 'Peter');insert into employee values (3, 'Julia');insert into employee values (3, 'Sandy');insert into employee values (1, 'Frank');insert into employee values (2, 'Eric' );insert into employee values (1, 'Joel' );insert into department_secrets values (1, 'R+D Secret #1' );insert into department_secrets values (1, 'R+D Secret #2' );insert into department_secrets values (2, 'Sales Secret #1');insert into department_secrets values (2, 'Sales Secret #2');insert into department_secrets values (3, 'HR Secret #1' );insert into department_secrets values (3, 'HR Secret #2' );// Allow any employee to see all secrets in their own department,but not any in any otherdepartments.4)// Start by creating a package.create or replace package pck_vpdasp_dep_id department.dep_id%type;procedure set_dep_id(v_dep_id department.dep_id%type);
function predicate (obj_schema varchar2, obj_name varchar2)return varchar2;end pck_vpd;/create or replace package body pck_vpd asprocedure set_dep_id(v_dep_id department.dep_id%type) isbeginp_dep_id := v_dep_id;end set_dep_id;function predicate (obj_schema varchar2, obj_name varchar2) returnvarchar2 isbeginreturn 'dep_id = ' || p_dep_id;end predicate;end pck_vpd;/5)// Define the trigger.create or replace trigger trg_vpdafter logon on databasedeclarev_dep_id department.dep_id%type;beginselect dep_id into v_dep_idfrom employee where upper(name) = user;pck_vpd.set_dep_id(v_dep_id);end;/QUESTION: When is this code triggered? What does it do when it istriggered?6)//Define the policy. The policy states which procedure is used toadd a where clause part to thewhere clause if someone executes a select statement.BEGINSYS.DBMS_RLS.ADD_POLICY(object_schema => 'SYSTEM',object_name => 'department_secrets',policy_name => 'emp_vpd_policy',function_schema => 'SYSTEM',policy_function => 'pck_vpd.predicate',
statement_types => 'select,update,delete');END;/7)//Create some users.create user frank identified by frankcreate user peter identified by petercreate user julia identified by julia//Grant the required privilegesgrant all on department_secrets to frank;grant all on department_secrets to peter;grant all on department_secrets to julia;grant create session to frank;grant create session to peter;grant create session to julia;//Create a public synonymcreate public synonym department_secrets fordepartment_secrets;8)connect as frank andselect * from department_secrets;QUESTION: What happened? Why?connect as peterselect * from department_secrets;QUESTION: What happened? Why?