1) Connect to your Pluggable Database
2)
// Create Tables
create 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 TABLES
insert into department values (1, ‘Research and Development’);
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 other departments.
4)
// Start by creating a package.
create or replace package pck_vpd
as
p_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 as
procedure set_dep_id(v_dep_id department.dep_id%type) is
begin
p_dep_id := v_dep_id;
end set_dep_id;
function predicate (obj_schema varchar2, obj_name varchar2) return varchar2 is
begin
return ‘dep_id = ‘ || p_dep_id;
end predicate;
end pck_vpd;
/
5)
// Define the trigger.
create or replace trigger trg_vpd
after logon on database
declare
v_dep_id department.dep_id%type;
begin
select dep_id into v_dep_id
from 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 is triggered?
6)
//Define the policy. The policy states which procedure is used to add a where clause part to the where clause if someone executes a select statement.
BEGIN
SYS.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 frank
create user peter identified by peter
create user julia identified by julia
//Grant the required privileges
grant 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 synonym
create public synonym department_secrets for department_secrets;
8)
connect as frank and
select * from department_secrets;
QUESTION: What happened? Why?
connect as peter
select * from department_secrets;
QUESTION: What happened? Why?
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
