Database Security -Exercise and Case study Solution

Created Feb. 7, 2024 by Girija Narasimhan

Unit 4 - Virtual Private Database Exercise solution

Create two table Technician and Lab table in scott schema

create table technician(tno number(5),tname varchar2(20),specialized varchar2(25));

insert into technician values(111,'INDUJA','ORACLE');

insert into technician values(112,'LAKSHMI','.NET');

insert into technician values(113,'AMBU','.NETWORK');

COMMIT;

[Hint: Tname should be in capital letters. i.e. “INDUJA”]


CREATE TABLE LAB(LABNO NUMBER(5),TNO NUMBER(5),SUBJECT VARCHAR2(30),NO_OF_HOuRS NUMBER(5));

INSERT INTO LAB VALUES(222,111,'Data warehousing',4);

insert into lab values(231,112,'ASP.NET',4);

INSERT INTO LAB VALUES(234,111,'Database Administration',4);

insert into lab values(218,112,'Visual Basic .net',5);

insert into lab values(425,113,'Network Administration',5);

COMMIT;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Q1) Apply column Masking for the below given question:

Create function “Get_lab” using condition as it will display only tno = 111. Create policy “Lab_cmask_policy” , in the policy

apply column masking for subject column. use object name as “lab table”. Login HR, display all the rows of lab table.

Drop Policy "Lab_cmask_policy" and drop function "Get_lab".


Ans:

Conn scott/tiger

create or replace function Get_lab (

schema_var in varchar2,

table_var in varchar2 )

return varchar2

is

return_val varchar2(400);

begin

return_val:='tno=111';

return return_val;

end Get_lab;

/

conn sys as sysdba/sys

BEGIN

DBMS_RLS.ADD_POLICY(

OBJECT_SCHEMA=>'SCOTT',

OBJECT_NAME=>'LAB',

POLICY_NAME=>'LAB_POLICY',


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Q2)       Create the virtual Private database application context by using appropriate statements:

CREATE TABLE CUSTOMER1(CID NUMBER(10) PRIMARY KEY,CNAME VARCHAR2(25));

INSERT INTO CUSTOMER1 VALUES(11,'SAMER');

INSERT INTO CUSTOMER1 VALUES(12,'AISYA');


CREATE TABLE SHOPPING(CARDNO NUMBER(10),CID NUMBER(10),AMOUNT NUMBER(5));

INSERT INTO SHOPPING VALUES(1,11,1000);

INSERT INTO SHOPPING VALUES(2,11,2000);

INSERT INTO SHOPPING VALUES(3,12,1500);

INSERT INTO SHOPPING VALUES(4,12,2500);


  •   Create user admin_jun2 and assign following privileges CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER. Give DBMS_SESSION and DBMS_RLS execute privileges to admin_jun

*          Create and insert in hr schema, customer1 and shopping table        

                       

*          Assign select privilege as customer1 table to user admin_jun2 and shopping table to user “SAMER”

*          Connect

admin_jun2

, create context

shop_ctx

using package name

shop_pkg.

*          In the shop_pkg, create the procedure set_custno which is based on customer name(cname) of hr. customer1 table.

*          Create logon trigger

shop_trig

to fetch the

shop_pkg.                               

*          Create function

get_cust(),

using cid of the user SAMER   

*          Create add policy

“shop_pol

”, use the table

hr.shopping

and

get_cust

function                   

*         

connect SAMER

, display

SHOPPING TABLE

.       

*          Remove shop_pol policy


ans:

GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER,ADMINISTER DATABASE TRIGGER TO ADMIN_JUN2 IDENTIFIED BY admin123;


GRANT EXECUTE ON DBMS_SESSION TO admin_jun2;


GRANT EXECUTE ON DBMS_RLS TO admin_jun2;


GRANT CREATE SESSION TO samer IDENTIFIED BY sam;


GRANT SELECT ON hr.customer1 TO admin_jun2;


GRANT SELECT ON hr.shopping TO samer;


CREATE OR REPLACE CONTEXT shop_ctx USING shop_pkg;


CREATE OR REPLACE PACKAGE shop_pkg IS 

PROCEDURE set_custno; 

END;

/

CREATE OR REPLACE PACKAGE BODY shop_pkg IS 

PROCEDURE set_custno 

AS 

custnum NUMBER; 

BEGIN 

SELECT cid INTO custnum FROM hr.CUSTOMER1 

WHERE cname = SYS_CONTEXT('USERENV', 'SESSION_USER'); 

DBMS_SESSION.SET_CONTEXT('shop_ctx', 'cid', custnum); 

EXCEPTION 

WHEN NO_DATA_FOUND THEN NULL; 

END set_custno;

END;


CREATE TRIGGER shop_trig AFTER LOGON ON DATABASE 

BEGIN 

admin_jun2.shop_pkg.set_custno; 

END;



CREATE OR REPLACE FUNCTION get_cust( 

schema_p IN VARCHAR2, 

table_p IN VARCHAR2) 

RETURN VARCHAR2 

AS 

orders_pred VARCHAR2 (400); 

BEGIN 

orders_pred := 'cid = 11'; 

RETURN orders_pred;

END;


BEGIN 

DBMS_RLS.ADD_POLICY ( 

object_schema => 'hr', 

object_name => 'shopping', 

policy_name => 'shop_policy', 

function_schema => 'admin_jun2', 

policy_function => 'get_cust', 

statement_types => 'select');

END;


EXEC DBMS_RLS.DROP_POLICY ('hr', 'shopping', 'shop_policy');


Return to top