04 Aug Connect to your Pluggable Database

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?