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?
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
