Database SQL coding

fidiofei

Lab2.pdf

Home >Information Systems homework help >Database SQL coding

Database Security

Lab 2 – Virtual Private Databases 2019

Part I: Implementing VPD by Views

This lab shows an example of implementing access control through views and triggers. Notice:

In this lab and the following ones, only the SYS user is the SYSDBA, the DBSEC user is a

normal user.

What to submit: Your answers to the questions in steps 5(a), 6, and 7.

1. Create DBSEC account. (using Database Control Tool)

(a) For the sake of simplicity, set its password as dbsec.

(b) Assign the following systems privileges to DBSEC.

CREATE ANY CONTEXT

CREATE PROCEDURE

CREATE SESSION

CREATE SEQUENCE

CREATE TABLE

CREATE TRIGGER

CREATE USER

CREATE VIEW

GRANT ANY OBJECT PRIVILEGE

GRANT ANY PRIVILEGE

GRANT ANY ROLE

2. Create a user SCOTT. For the sake of simplicity, set its password as dbsec. Assign the following system privileges to SCOTT:

CREATE SESSION

3. Logon as DBSEC and execute the following commands

drop table employee;

create table employee (

empID number(3) primary key,

Fname varchar2(25),

Lname varchar2(25),

Email varchar2(50),

Tel char(11),

Hdate Date,

JobID varchar2(10),

Salary number(6),

ManagerID number(3),

DeptID number(3),

ctl_upd_user varchar2(25));

drop view employee_view1;

create view employee_view1 as

select empID, Fname, Lname, Email, Tel, Hdate, JobID, Salary, managerID,

deptID, ctl_upd_user user_name

from employee

where ctl_upd_user = user;

grant select, insert, delete, update on employee_view1 to scott;

insert into employee_view1 values (100,'Sam','Doe', 'sdoe', '501-1112222',

sysdate, 'job1','60000', 199, 1, user);

commit;

4. Logon as SYS, and execute the following commands:

create or replace trigger TRG_EMPLOYEE_VIEW1_BF_INS

instead of insert on dbsec.EMPLOYEE_VIEW1

for each row

begin

insert into dbsec.EMPLOYEE values

(:new.empID, :new.Fname, :new.Lname, :new.Email, :new.Tel, :new.Hdate, :new.J

obID, :new.Salary, :new.ManagerID, :new.deptID, user);

end;

5. Logon as SCOTT and execute the following commands:

insert into dbsec.employee_view1 values (101,'Julia','Rice', 'sdoe', '501-

1013333', sysdate, 'job_julia','50000', 299, 1, user);

commit;

select *

from dbsec.employee_view1;

(a) Did the above query output the only rows that Scott is allowed to see?

6. Read the code of create or replace trigger TRG_EMPLOYEE_VIEW1_BF_INS. (in step 4)

(a) It differs from the create trigger syntax that we discussed in PL/SQL review. Google the

internet for the key words “instead of” and “trigger”, and explain what is instead of

trigger mainly used for.

(b) Explain what does Oracle do when Scott performs

insert into dbsec.employee_view1 values (101,'Julia','Rice', 'sdoe', '501-

1013333', sysdate, 'job_julia','50000', 299, 1, user);

commit;

(c) Create a trigger for update Employee_view1 and delete Employee_view1 respectively so that a user cannot update or delete the rows that created (owned) by other users.

7. Create another user Mary. Logon as Mary and insert a row into employee_view1, and

demonstrate (by a query result) Mary can only see the row(s) that she has inserted.

8. Roll back the changes made by this project

(a) Log on as SYS, and perform the following commands:

drop trigger TRG_EMPLOYEE_VIEW1_BF_INS;

COMMIT;

(b) Log on as DBSEC and perform the following commands:

drop table employee;

drop view employee_view1;

COMMIT;

Part II: VPD by Application Context

The objective of this lab is to learn how to implement access control using application context.

What to submit: Your answers to the questions in steps 2(b), 3(b), and 4.

1. Create a user HR. For the sake of simplicity, set its password as dbsec. Assign the following system privileges to HR:

CREATE SESSION

2. Logon as SYS and execute the following commands:

DROP TABLE APP_CONTEXT_USERS;

CRATE TABLE APP_CONTEXT_USERS

(

APP_CONTEXT_ATTR VARCHAR2(80),

APP_CONTEXT_VALUE VARCHAR2(255),

USER_NAME VARCHAR2(30)

);

INSERT INTO APP_CONTEXT_USERS VALUES('SECURITY_LEVEL','1','SCOTT');

INSERT INTO APP_CONTEXT_USERS VALUES('SECURITY_LEVEL','2','HR');

INSERT INTO APP_CONTEXT_USERS VALUES('SECURITY_LEVEL','3','DBSEC');

DROP TABLE ORDERS;

CREATE TABLE ORDERS

(

ORDER_ID NUMBER,

ORDER_DATE DATE,

CUSTOMER_ID NUMBER,

ORDER_AMOUNT NUMBER,

CTL_REC_STAT NUMBER

);

INSERT INTO ORDERS VALUES(1, SYSDATE, 200,1203.22,1);

INSERT INTO ORDERS VALUES(2, SYSDATE, 210,5431.23,1);

INSERT INTO ORDERS VALUES(3, SYSDATE, 212,100023.29,2);

INSERT INTO ORDERS VALUES(4, SYSDATE, 210,999210.55,3);

DROP VIEW ORDER_VIEW;

CREATE VIEW ORDER_VIEW AS

SELECT ORDER_ID,ORDER_DATE,CUSTOMER_ID,ORDER_AMOUNT

FROM ORDERS

WHERE CTL_REC_STAT <=

TO_NUMBER(SYS_CONTEXT('ORDERS_APP','SECURITY_LEVEL'));

CREATE CONTEXT ORDERS_APP USING SYS.CONTEXT_PKG;

CREATE OR REPLACE PACKAGE CONTEXT_PKG AS

PROCEDURE SET_APP_CONTEXT(P_LEVEL VARCHAR2);

END;

CREATE OR REPLACE PACKAGE BODY CONTEXT_PKG AS

PROCEDURE SET_APP_CONTEXT(P_LEVEL VARCHAR2) IS

BEGIN

DBMS_SESSION.SET_CONTEXT('ORDERS_APP','SECURITY_LEVEL',P_LEVEL);

END;

GRANT EXECUTE ON CONTEXT_PKG TO DBSEC;

CREATE OR REPLACE TRIGGER TRG_DB_LOGON

AFTER LOGON ON DATABASE

DECLARE

V_LEVEL VARCHAR2(255);

BEGIN

SELECT APP_CONTEXT_VALUE

INTO V_LEVEL

FROM APP_CONTEXT_USERS

WHERE USER_NAME=USER;

CONTEXT_PKG.SET_APP_CONTEXT(V_LEVEL);

END;

commit;

(a) Perform the following command:

SELECT SYS_CONTEXT('ORDERS_APP','SECURITY_LEVEL')

FROM DUAL;

(b) What is the output of the above command, and why it is so?

3. Logon as HR and select from the view:

SELECT SYS_CONTEXT('ORDERS_APP','SECURITY_LEVEL')

FROM DUAL;

SELECT * FROM SYS.ORDER_VIEW;

(a) You will get an error from the above SELECT statement as below:

ERROR at line 1: ORA-00942: table or view does not exist

(b) How would you do to enable the users DBSEC and HR to view the contents of ORDER_VIEW?

4. Logon as HR and execute the following SQL statement to demonstrate your solution to 3(b) is working properly.

SELECT * FROM SYS.ORDER_VIEW;

5. Roll back the changes made by this project

(a) Log on as SYS, and perform the following commands:

DROP TRIGGER TRG_DB_LOGON;

DROP TABLE ORDERS;

DROP VIEW ORDER_VIEW;

DROP CONTEXT ORDERS_APP;

DROP PACKAGE CONTEXT_PKG;

DROP TABLE APP_CONTEXT_USERS;

COMMIT;

Part III: VPD -- Row Owner Security

The objective of this lab is to learn, by example, how to implement access control based on row

owner security.

The access control policy of this lab requires that a user can only access records he/she owns,

which means only records with CTL_UPD_USER matching with user’s login name can be

viewed by the user.

What to submit: Your answers to the questions in steps 4(b), 5(b), and 7(for graduate students

only).

1. Assign the following system privileges to DBSEC.

CREATE ANY CONTEXT

CREATE ANY TRIGGER

CREATE PROCEDURE

CREATE SEQUENCE

CREATE SESSION

CREATE TABLE

CREATE USER

CREATE VIEW

GRANT ANY OBJECT PRIVILEGE

GRANT ANY PRIVILEGE

2. Log on as DBSEC and perform the following statements:

(a) Create a customer table:

DROP TABLE CUSTOMERS;

CREATE TABLE CUSTOMERS (

SALES_REP_ID NUMBER(4),

CUSTOMER_ID NUMBER(8),

CTL_UPD_DTTM DATE,

CTL_UPD_USER VARCHAR2(30),

CTL_REC_STAT CHAR(1),

primary key (sales_rep_id, customer_id)

);

(b) Populate the customer table:

insert into customers values (1000, 90000,NULL, 'VPD_CLERK1','1');

insert into customers values (1000, 90001,NULL, 'VPD_CLERK1','2');

insert into customers values (1000, 90002,NULL, 'VPD_CLERK2','3');

insert into customers values (1000, 90003,NULL, 'VPD_CLERK2','4');

insert into customers values (1000, 90004,NULL, 'VPD_CLERK3','5');

insert into customers values (1001, 90000,NULL, 'VPD_CLERK3','1');

insert into customers values (1001, 90001,NULL, 'VPD_CLERK2','2');

insert into customers values (1001, 90002,NULL, 'VPD_CLERK2','3');

insert into customers values (1001, 90003,NULL, 'VPD_CLERK1','4');

insert into customers values (1001, 90004,NULL, 'VPD_CLERK1','5');

insert into customers values (1002, 90005,NULL, 'VPD_CLERK2','1');

insert into customers values (1003, 90006,NULL, 'VPD_CLERK2','2');

insert into customers values (1004, 90007,NULL, 'VPD_CLERK3','3');

insert into customers values (1005, 90008,NULL, 'VPD_CLERK3','4');

insert into customers values (1006, 90009,NULL, 'VPD_CLERK1','5');

insert into customers values (1007, 90005,NULL, 'VPD_CLERK2','1');

insert into customers values (1008, 90006,NULL, 'VPD_CLERK3','2');

insert into customers values (1009, 90007,NULL, 'VPD_CLERK1','3');

insert into customers values (1010, 90008,NULL, 'VPD_CLERK3','4');

insert into customers values (1011, 90009,NULL, 'VPD_CLERK2','5');

commit;

3. Log in as SYS user

(a) Create users VPD_CLERK1, VPD_CLERK2, and VPD_CLERK3

CREATE USER VPD_CLERK1 IDENTIFIED BY VPD_CLERK1;

GRANT CREATE SESSION TO VPD_CLERK1;

CREATE USER VPD_CLERK2 IDENTIFIED BY VPD_CLERK2;

GRANT CREATE SESSION TO VPD_CLERK2;

CREATE USER VPD_CLERK3 IDENTIFIED BY VPD_CLERK3;

GRANT CREATE SESSION TO VPD_CLERK3;

(b) Grant access rights on customer table to VPD_CLERK1, VPD_CLERK2, and VPD_CLERK3

GRANT SELECT, INSERT, DELETE, UPDATE ON DBSEC.CUSTOMERS TO VPD_CLERK1;

GRANT SELECT, INSERT, DELETE, UPDATE ON DBSEC.CUSTOMERS TO VPD_CLERK2;

GRANT SELECT, INSERT, DELETE, UPDATE ON DBSEC.CUSTOMERS TO VPD_CLERK3;

CREATE OR REPLACE FUNCTION

DBSEC_ROW_OWNER_WHERE(

P_SCHEMA_NAME IN VARCHAR2,

P_OBJECT_NAME IN VARCHAR2)

RETURN VARCHAR2 AS

con VARCHAR2 (200);

BEGIN

con := 'CTL_UPD_USER=USER';

RETURN (con);

END;

(d) execute the following command: (all should be in one line when executing it)

exec dbms_rls.add_policy(object_schema =>'DBSEC',object_name =>

'CUSTOMERS',policy_name => 'dbsec_row_onwer_policy', function_schema => 'SYS',

policy_function =>'DBSEC_ROW_OWNER_WHERE', enable => true);

COMMIT;

4. Test the implementation

(a) Logon as VPD_CLERK1 and what is the output of the following SQL statement?

SELECT *

FROM DBSEC.CUSTOMERS;

(b) Repeat the above steps as VPD_CLERK2, VPD_CLERK3 respectively and show the result

of the SQL statement.

5. Log on as SYS, and perform the following commands: (all should be in one line when executing it)

exec dbms_rls.drop_policy(object_schema =>'DBSEC',object_name =>

'CUSTOMERS',policy_name => 'dbsec_row_onwer_policy');

COMMIT;

(a) Logon as VPD_CLERK1 and execute the SQL statement:

SELECT *

FROM DBSEC.CUSTOMERS;

(b) Is the result of the above SQL statement different from that in Step 4? If yes, why?

6. Roll back the changes made by this project

(a) Log on as SYS and perform the following statements:

REVOKE SELECT, INSERT, DELETE ON CUSTOMERS FROM VPD_CLERK1, VPD_CLERK2,

VPD_CLERK3;

DROP TABLE CUSTOMERS;

DROP FUNCTION DBSEC_ROW_OWNER_WHERE;

COMMIT;

7. (For graduate students) Revise the code of this lab so that the user HR is able to see the rows

owned by VPD_CLERK1 and VPD_CLERK3. (Hint: revise DBSEC_ROW_OWNER_WHERE

function)

(a) Demonstrate your implementation works properly by the SQL command

SELECT *

FROM DBSEC.CUSTOMERS;

(b) Rollback the changes appropriately.

Part IV: VPD-- Role Security Level

The objective of this lab is to learn, by example, how to implement access control based on row

security level.

The access control policy of the scenario in this lab is as follows:

 User VPD_CLERK1 can only access records with sales_rep_id=1000.

 User VPD_CLERK2 can only access records with sales_rep_id=1001.

 User VPD_CLERK3 can only access records with sales_rep_id=1002.

What to submit: Your answers to the questions in steps 2(a), 3(a), 4, 5(c), and 5(d).

1. Log on as DBSEC and execute the following statements: