5-8 Data Warehouse Management - Create and manage Contexts

References: Oracle Docs ( Database Security Guide, PL/SQL language, SQL language )

 

Application context is stored in SGA and used to get the session information of the connected user. It is commonly applied with along the VPD and fine-grained access control policies. Each application context consists of name/value pairs which are called attributes. To prevent a malicious user from changing these attributes, its values can only be set inside a package (procedure) defined by you in the CREATE CONTEXT command.

SYS_CONTEXT function is used to retrieve the information about the session along with the pre-definded USERENV context and returns a lot of useful information such as current sql, current user, database name, hostname, ip address, module name, session number and etc.

 

 

/* To get the OS username of the session */
select sys_context('USERENV', 'OS_USER') FROM dual;

/* To get the instance name */
select sys_context('USERENV', 'INSTANCE_NAME') FROM dual;

/* To get the username by which the current user is authenticated */
select sys_context('USERENV', 'SESSION_USER') FROM dual;

/* To get the container ID */
select sys_context('USERENV', 'CON_ID') FROM dual;

 

 

 

난이도: ★★★★★

문제 1: 다음의 fine-grained auditing 작업을 수행한다.

세 유저 USR1, USR2, USR_MAIN을 생성한다.

USR_MAIN에서 두 테이블을 생성한다.

- USR_LIST: ID, NAME 두 컬럼

- USR_COURSE: ID, USER_ID, NAME 세 컬럼

USR_LIST 테이블에 다음 두 행을 추가한다.

1 USR1

2 USR2

USR_COURSE 테이블에 다음 두 행을 추가한다.

1 1 Oracle

2 2 PL/SQL

그리고 connected session의 username를 얻는 패키지의 프로시저를 활용하여 context를 생성하고, USR_LIST 테이블에서 유저의 id를 찾고, 값을 context의 attribute로 설정하여라.

USR_COURSE 테이블의 칼럼이 context의 값과 같은지 predicate하는 fine-grained audit policy를 생성하는 package를 생성하라.

USR1 유저로 접속하고 USR_COURSE 테이블을 쿼리하여라. 결과 Oracle를 얻는지 확인하여라. USR2 유저로 접속하고 USR_COURSE 테이블을 쿼리하면 PL/SQL을 결과로 얻는지 확인하여라. (15분)

 

conn / as sysdba

CREATE USER usr1 IDENTIFIED BY oracle;
CREATE USER usr2 IDENTIFIED BY oracle;
CREATE USER usr_main IDENTIFIED BY oracle;
GRANT connect, resource, unlimited tablespace TO usr1;
GRANT connect, resource, unlimited tablespace TO usr2;
GRANT connect, resource, unlimited tablespace, create any context, dba TO usr_main;

CONN usr_main/oracle;
CREATE TABLE usr_list (id NUMBER, name VARCHAR2(10));
CREATE TABLE usr_course (id NUMBER, user_id NUMBER, name VARCHAR2(10));
INSERT INTO usr_list VALUES (1, 'USR1');
INSERT INTO usr_list VALUES (2, 'USR2');
INSERT INTO usr_course VALUES (1,1,'Oracle');
INSERT INTO usr_course VALUES (2,2,'PL/SQL');

conn / as sysdba

CREATE OR REPLACE CONTEXT context1 USING package_1;

CREATE OR REPLACE PACKAGE package_1
IS
	PROCEDURE procedure1;
END;
/

CREATE OR REPLACE PACKAGE BODY package_1
IS
	PROCEDURE procedure1 
	IS
		genzai NUMBER;
	BEGIN
		SELECT	id INTO genzai
		FROM	usr_main.usr_list
		WHERE	name = SYS_CONTEXT('USERENV','SESSION_USER');
		DBMS_SESSION.SET_CONTEXT('CONTEXT1','id_number', genzai);
	EXCEPTION
		WHEN NO_DATA_FOUND THEN NULL;
	END;
END;
/



EXEC package_1.procedure1;
SELECT SYS_CONTEXT('USERENV','SESSION_USER') FROM dual;

CREATE OR REPLACE TRIGGER trigger_01 AFTER LOGON ON DATABASE
BEGIN
	context1.package_1.procedure1;
END;
/


GRANT SELECT ON usr_main.usr_course TO usr1;
GRANT SELECT ON usr_main.usr_course TO usr2;

CREATE OR REPLACE PACKAGE package_2
IS
	FUNCTION function_1 (C1 varchar2, C2 varchar2)
		RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY package_2
IS
	FUNCTION function_1 (C1 varchar2, C2 varchar2)
		RETURN VARCHAR2
	IS
		where_clause VARCHAR2(200);
	BEGIN
		where_clause := 'user_id = SYS_CONTEXT("CONTEXT1","id_number")';
		RETURN where_clause;
	END;
END;
/	

BEGIN
	DBMS_RLS.ADD_POLICY
	(object_schema => 'USR_MAIN',
 	 object_name => 'USR_COURSE',
 	 policy_name => 'POLICYA',
	 function_schema => 'SYS',
	 policy_function => 'FUNCTION_1',
	 statement_types => 'SELECT');
END;
/


GRANT select on usr_main.usr_course TO usr1;
GRANT select on usr_main.usr_course TO usr2;

CONN usr1/oracle;
SELECT * FROM usr_main.usr_course;
CONN usr2/oracle;
SELECT * FROM usr_main.usr_course;