5-6 Data Warehouse Management - Fine_grained access control

References: Oracle Docs(Database PL/SQL Packages and Types Reference, Database Security Guide)

 

 

Virtual Private Database (VPD) is used to prevent a user from access to unrelated data. VPD uses DBMS_RLS (row level security) package that contains fine grained access control interface to limit specific data to specific users by applying the security policy directly to the database objects and modifies the SQL statement automatically before accessing the data by adding a WHERE condition that is returned from the security policy function.

 

DBA_POLICIES 뷰를 통해 현재 생성되어 있는 policy의 리스트를 확인할 수 있다.

conn / as sysdba

SELECT	object_owner, object_name, policy_name, function
FROM	dba_policies
WHERE	object_name = 'TBL_VPD_TEST';

 

 

 

 

실습: usr_vpd 유저와 usr_vpd_2 유저를 생성하고, usr_vpd 유저에 테이블을 생성하고 usr_vpd_2 유저가 해당 테이블을 참조할 수 있게 해준 뒤, usr_vpd_2유저가 object_id 가 1000을 넘는 데이터를 읽지 못하도록 policy를 생성한다.

CREATE USER usr_vpd IDENTIFIED BY asdf0930k;
CREATE USER usr_vpd_2 IDENTIFIED BY asdf0930k;
GRANT connect, resource, unlimited tablespace TO usr_vpd, usr_vpd_2;

CONN usr_vpd/asdf0930k;
CREATE TABLE tbl_vpd_test AS SELECT * FROM all_objects;
GRANT select, update, delete ON usr_vpd.tbl_vpd_test TO usr_vpd_2;



/* this function must take a schema and table name as an input, must provide a return value for the WHERE clause and must generate a valid WHERE clause. */
conn / as sysdba

CREATE OR REPLACE FUNCTION hide_all_except_100
(v_schema IN VARCHAR2,
 v_objname IN VARCHAR2)
	RETURN VARCHAR2
IS
	where_clause	VARCHAR2(100);
BEGIN
	if USER = 'USR_VPD_2'
	then
		where_clause := 'object_id < 1000';
	else
		where_clause := '';
	end if;
	
	RETURN (where_clause);
END;
/

BEGIN
	dbms_rls.add_policy
	(object_schema =>'USR_VPD',
	 object_name => 'TBL_VPD_TEST',
	 policy_name => 'MY_VPD_POLICY_01',
	 function_schema => 'SYS',
	 policy_function => 'HIDE_ALL_EXCEP_100',
	 statement_types => 'SELECT');
END;
/


conn usr_vpd/asdf0930k;
SELECT count(1) FROM usr_vpd.tbl_vpd_test;
conn usr_vpd_2/asdf0930k;
SELECT count(1) FROM usr_vpd.tbl_vpd_test;


conn / as sysdba
exec dbms_rls.drop_policy('USR_VPD','TBL_VPD_TEST',MY_VPD_POLICY_01');

 

 

 

실습: DBMS_RLS.ADD_POLICY 프로시저에서 update_check 파라미터를 사용하여, object_id 가 1000을 넘는 데이터의 조회 및 변경할 수 없도록 policy를 생성한다.

 

conn usr_vpd_2/asdf0930k;
SELECT object_id FROM usr_vpd.tbl_vpd_test;

UPDATE usr_vpd.tbl_vpd_test SET object_id = 2000;
ROLLBACK;

CONN / as sysdba
GRANT UPDATE ON usr_vpd.tbl_vpd_test TO usr_vpd_2;

exec dbms_rls.drop_policy('USR_VPD','TBL_VPD_TEST','MY_VPD_POLICY_01');

BEGIN
	dbms_rls.add_policy
	(object_schema => 'USR_VPD',
	 object_name => 'TBL_VPD_TEST',
	 policy_name => 'MY_VPD_POLICY_01',
	 function_schema => 'SYS',
	 policy_function => 'HIDE_ALL_EXCEPT_100',
	 update_check => TRUE,
	 statement_types => 'SELECT, UPDATE');
END;
/


CONN usr_vpd_2/asdf0930k;
UPDATE usr_vpd.tbl_vpd_test SET object_id = 2000;

 

 

 

실습: DBMS_RLS.ADD_POLICY 프로시저에서 sec_relevant_cols와 sec_relevant_cols_apt 파라미터를 사용하여, 조건에 맞는 데이터 중에서 일부 컬럼만 모두 표시할 수 있게 조정할 수도 있다. 이것을 Column-level VPD 이라고 한다.

 

CONN / as sysdba
EXEC DBMS_RLS.DROP_POLICY ('USR_VPD', 'TBL_VPD_TEST', 'MY_VPD_POLICY_01');

BEGIN
	dbms_rls.add_policy
	(object_schema => 'USR_VPD',
	 object_name => 'TBL_VPD_TEST',
	 policy_name => 'MY_VPD_POLICY_01',
	 function_schema => 'SYS',
	 policy_function => 'HIDE_ALL_EXCEPT_100',
	 update_check => TRUE,
	 sec_relevant_cols => 'object_name',
	 statement_types => 'SELECT, UPDATE');
END;
/

CONN usr_vpd_2/asdf0930k;
SELECT count(object_id) FROM usr_vpd.tbl_vpd_test;
SELECT object_id,  object_name FROM usr_vpd.tbl_vpd_test;

EXEC DBMS_RLS.DROP_POLICY ('USR_VPD', 'TBL_VPD_TEST', 'MY_VPD_POLICY_01');

BEGIN
	dbms_rls.add_policy
	(object_schema => 'USR_VPD',
	 object_name => 'TBL_VPD_TEST',
	 policy_name => 'MY_VPD_POLICY_01',
	 function_schema => 'SYS',
	 policy_function => 'HIDE_ALL_EXCEPT_100',
	 update_check => TRUE,
	 sec_relevant_cols => 'object_name',
	 statement_types => 'SELECT',
	 sec_relevant_cols_opt => dbms_rls.ALL_ROWS);
END;
/

conn usr_vpd_2/asdf0930k;
SELECT count(object_id) FROM usr_vpd.tbl_vpd_test;
SELECT object_id,  object_name FROM usr_vpd.tbl_vpd_test;

 

 

 

 

 

난이도: ★★★★☆

문제 1: DBA_SEGMENTS뷰를 토대로 테이블을 생성하고, SYSMAN 유저의 오브젝트만을 select할 수 있도록 하는 policy를 생성하라. (10분)

 

conn / as sysdba
CREATE TABLE usr_vpd.table1 AS SELECT * FROM dba_segments;
GRANT select on usr_vpd.table1 TO usr_vpd_2;
SELECT count(*) FROM usr_vpd.table1;


CREATE OR REPLACE FUNCTION function1 
(v_schema IN VARCHAR2, v_objname IN VARCHAR2)
	RETURN VARCHAR2 
IS
	con VARCHAR2(200);
BEGIN
	if USER = 'USR_VPD_2'
	then 
		con := 'owner=''SYSMAN''';
	else
		con := '';
	end if;

	RETURN (con);
END;
/

BEGIN
	dbms_rls.add_policy
	(object_schema => 'USR_VPD',
	 object_name => 'TABLE1',
	 policy_name => 'POLICY1',
	 function_schema => 'SYS',
	 policy_function => 'FUNCTION1',
	 statement_types => 'SELECT');
END;
/

CONN usr_vpd_2/asdf0930k;
SELECT count(*) FROM usr_vpd.table1;

 

 

 

 

 

 

난이도: ★★★★☆

문제 2: WHERE절 policy에 위반되는 row를 접근하지 못하게 하는 policy를 생성하라. (10분)

 

CONN / as sysdba
EXEC DBMS_RLS.DROP_POLICY('USR_VPD','TABLE1','POLICY1');

CREATE OR REPLACE FUNCTION function2
(v_schema IN VARCHAR2, v_objname IN VARCHAR2)
	RETURN VARCHAR2
IS
	con VARCHAR2(200);
BEGIN
	con := 'bytes <= 20';
	RETURN(con);
END;
/

BEGIN
	dbms_rls.add_policy
	(object_schema => 'USR_VPD',
	 object_name => 'TABLE1',
	 policy_name => 'POLICY1',
	 function_schema => 'SYS',
	 policy_function => 'FUNCTION2',
	 statement_types => 'SELECT');
END;
/

 

 

 

 

 

난이도: ★★★★☆

문제 3: 새로운 유저 USR_VPD_TEST2를 생성하고, column-level의 VPD를 생성하고 SEGMENT_TYPE 컬럼에 정책을 적용하여라. (12분)

 

CONN / as sysdba
CREATE USER usr_vpd_test2 IDENTIFIED BY asdf0930k;

EXEC DBMS_RLS.DROP_POLICY('USR_VPD','TABLE1','POLICY1');

BEGIN
	dbms_rls.add_policy
	(object_schema => 'USR_VPD',
	 object_name => 'TABLE1',
	 policy_name => 'POLICY1',
	 function_schema => 'SYS',
	 policy_function => 'FUNCTION2',
	 statement_types => 'SELECT',
	 sec_relevant_cols => 'SEGMENT_TYPE');
END;
/