Oracle VPD(Virtual Private Database)

• ORACLE VPD란?

   

  • Virtual Private Database(이하 VPD) 란 하나의 DATABASE내의 수 많은 data 에서 보안상 혹은 특정 서비스를 목적으로 필요한 유저에 해당하는 data만을 다룰 수 있게 하는 기술을 말한다.
  • 일반적으로 보안레벨, 부서별 혹은 사이트별 등 특정 목적에 맞게 구분되지만, 사용자는 이러한 구분을 받고 있다는 것을 전혀 알지 못하며, 마치 자신만의 DATABASE 를 다루는 것처럼 보이게 되어 Virtual Private Database 라고 부른다.

   

1. 스키마 단위 TEST Script 및 확인

User, table 생성 SYS> GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO sysadmin_vpd IDENTIFIED BY test;     Grant succeeded.     SYS> SYS> SYS> GRANT EXECUTE ON DBMS_SESSION TO sysadmin_vpd;     Grant succeeded.     SYS> SYS> SYS> GRANT EXECUTE ON DBMS_RLS TO sysadmin_vpd;     Grant succeeded.     SYS> SYS> GRANT CREATE SESSION TO tbrooke IDENTIFIED BY test;     Grant succeeded.     SYS> SYS> SYS> GRANT CREATE SESSION TO owoods IDENTIFIED BY test;     Grant succeeded.     SYS> SYS> SYS> ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY tiger 2 ;     User altered.     SYS> SYS> SYS> conn scott/tiger Connected. SCOTT> SCOTT> create table customers ( cust_no number(4), cust_email varchar2(20), 4 cust_name varchar2(20));     Table created.     SCOTT> SCOTT> SCOTT> CREATE TABLE orders_tab ( 2 cust_no NUMBER(4), 3 order_no NUMBER(4));     Table created.     SCOTT> SCOTT> INSERT INTO customers VALUES (1234, 'TBROOKE', 'Thadeus Brooke');     1 row created.     SCOTT> SCOTT> SCOTT> INSERT INTO customers VALUES (5678, 'OWOODS', 'Oberon Woods');     1 row created.     SCOTT> SCOTT> SCOTT> GRANT SELECT ON customers TO sysadmin_vpd;     Grant succeeded.     SCOTT> SCOTT> select * from orders_tab;     no rows selected     SCOTT> SCOTT> SCOTT> INSERT INTO orders_tab VALUES (1234, 9876);     1 row created.     SCOTT> SCOTT> SCOTT> INSERT INTO orders_tab VALUES (5678, 5432);     1 row created.     SCOTT> SCOTT> SCOTT> INSERT INTO orders_tab VALUES (5678, 4592);     1 row created.     SCOTT> SCOTT> GRANT SELECT ON orders_tab TO tbrooke;     Grant succeeded.     SCOTT> SCOTT> SCOTT> GRANT SELECT ON orders_tab TO owoods;     Grant succeeded.     SCOTT>     application context 생성 SCOTT> conn sysadmin_vpd/test Connected. SYSADMIN_VPD> SYSADMIN_VPD> SYSADMIN_VPD> SYSADMIN_VPD> SYSADMIN_VPD> SYSADMIN_VPD> CREATE OR REPLACE CONTEXT orders_ctx USING orders_ctx_pkg;     Context created.     SYSADMIN_VPD> SYSADMIN_VPD> CREATE OR REPLACE PACKAGE orders_ctx_pkg END; IS IS PROCEDURE set_custnum; END; 5 /     Package created.     SYSADMIN_VPD> SYSADMIN_VPD> CREATE OR REPLACE PACKAGE BODY orders_ctx_pkg IS PROCEDURE set_custnum AS custnum NUMBER; BEGIN SELECT cust_no INTO custnum FROM scott.customers WHERE cust_email = SYS_CONTEXT('USERENV', 'SESSION_USER'); DBMS_SESSION.SET_CONTEXT('orders_ctx', 'cust_no', custnum); 10 EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END set_custnum; 13 END; 14 /     Package body created.     Logon trigger 생성 SYSADMIN_VPD> CREATE TRIGGER set_custno_ctx_trig AFTER LOGON ON DATABASE BEGIN sysadmin_vpd.orders_ctx_pkg.set_custnum; END; 5 /     Trigger created.     SYSADMIN_VPD> SYS> conn tbrooke/test Connected. TBROOKE> TBROOKE> TBROOKE> SELECT SYS_CONTEXT('orders_ctx', 'cust_no') custnum FROM DUAL;     CUSTNUM ---------------------------------------------------------------------------------------------------- 1234     TBROOKE>            DBMS_RLS 에 사용할 policy function 생성 SYSADMIN_VPD> CREATE OR REPLACE FUNCTION get_user_orders( orders_pred VARCHAR2 (400); schema_p IN VARCHAR2, table_p IN VARCHAR2) RETURN VARCHAR2 AS orders_pred VARCHAR2 (400); BEGIN orders_pred := 'cust_no = SYS_CONTEXT(''orders_ctx'', ''cust_no'')'; RETURN orders_pred; END; /     Function created.     SYSADMIN_VPD>     DBMS_RLS.ADD_POLICY 설정 SYSADMIN_VPD> BEGIN DBMS_RLS.ADD_POLICY ( object_schema => 'scott', object_name => 'orders_tab', function_schema => 'sysadmin_vpd', policy_name => 'orders_policy', function_schema => 'sysadmin_vpd', policy_function => 'get_user_orders', statement_types => 'select'); END; /     PL/SQL procedure successfully completed.     SYSADMIN_VPD>     TEST         SYS> select * from scott.orders_tab;     CUST_NO ORDER_NO ---------- ---------- 1234 9876 5678 5432 5678 4592     SYS>     TBROOKE> TBROOKE> select * from scott.orders_tab;     CUST_NO ORDER_NO ---------- ---------- 1234 9876     TBROOKE>     SYS> conn owoods/test Connected. OWOODS> OWOODS> OWOODS> OWOODS> select * from scott.orders_tab;     CUST_NO ORDER_NO ---------- ---------- 5678 5432 5678 4592     OWOODS>     확인 OWOODS> select * from scott.orders_tab where ORDER_NO=5432 and cust_no=5678;     CUST_NO ORDER_NO ---------- ---------- 5678 5432         Execution Plan ---------------------------------------------------------- Plan hash value: 3940001695     --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 | |* 1 | FILTER | | | | | | |* 2 | TABLE ACCESS FULL| ORDERS_TAB | 1 | 26 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------     Predicate Information (identified by operation id): ---------------------------------------------------     1 - filter(5678=TO_NUMBER(SYS_CONTEXT('orders_ctx','cust_no'))) 2 - filter("ORDER_NO"=5432 AND "CUST_NO"=5678)     ========================================== OWOODS> select * from scott.orders_tab;     CUST_NO ORDER_NO ---------- ---------- 5678 5432 5678 4592         Execution Plan ---------------------------------------------------------- Plan hash value: 1302839296     -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 52 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| ORDERS_TAB | 2 | 52 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------     Predicate Information (identified by operation id): ---------------------------------------------------     1 - filter("CUST_NO"=TO_NUMBER(SYS_CONTEXT('orders_ctx','cust_no')))     Note ----- - dynamic sampling used for this statement         Statistics ---------------------------------------------------------- 2 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 513 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed     ========================================================     OWOODS> select * from scott.orders_tab where order_no=5432;     CUST_NO ORDER_NO ---------- ---------- 5678 5432         Execution Plan ---------------------------------------------------------- Plan hash value: 1302839296     -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| ORDERS_TAB | 1 | 26 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------     Predicate Information (identified by operation id): ---------------------------------------------------     1 - filter("ORDER_NO"=5432 AND "CUST_NO"=TO_NUMBER(SYS_CONTEXT('order s_ctx','cust_no')))     Note ----- - dynamic sampling used for this statement         Statistics ---------------------------------------------------------- 2 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 473 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

   

1. 컬럼단위 Script 및 확인

권한 부여     GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO SCOTT; 권한이 부여되었습니다. SQL> GRANT EXECUTE ON DBMS_SESSION TO scott; 권한이 부여되었습니다. SQL> GRANT EXECUTE ON DBMS_RLS TO scott; 권한이 부여되었습니다.     Funtion 및 policy 추가 CREATE OR REPLACE FUNCTION hide_sal_comm ( v_schema IN VARCHAR2, v_objname IN VARCHAR2)     RETURN VARCHAR2 AS con VARCHAR2 (200); begin con := 'deptno=30'; RETURN (con); END hide_sal_comm; /     BEGIN DBMS_RLS.ADD_POLICY ( object_schema => 'scott', object_name => 'emp',a policy_name => 'hide_sal_policy', policy_function => 'hide_sal_comm', sec_relevant_cols => 'sal,comm'); END; /     SELECT d.deptno, ENAME, d.dname, JOB , SAL FROM emp e, dept d WHERE d.deptno = e.deptno;   SELECT d.deptno, ENAME, d.dname, JOB , SAL FROM emp e, dept d 3 WHERE d.deptno = e.deptno;     DEPTNO ENAME DNAME JOB SAL ---------- ---------- -------------- --------- ---------- 30 ALLEN SALES SALESMAN 1600 30 WARD SALES SALESMAN 1250 30 MARTIN SALES SALESMAN 1250 30 BLAKE SALES MANAGER 2850 30 TURNER SALES SALESMAN 1500 30 JAMES SALES CLERK 950     6 rows selected.         Execution Plan ---------------------------------------------------------- Plan hash value: 568005898     ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 170 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 5 | 170 | 4 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | EMP | 5 | 105 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------     Predicate Information (identified by operation id): ---------------------------------------------------     3 - access("D"."DEPTNO"=30) 4 - filter("DEPTNO"=30)         Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 10 consistent gets 0 physical reads 0 redo size 789 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6 rows processed       SELECT d.deptno, ENAME, d.dname, JOB FROM emp e, dept d WHERE d.deptno = e.deptno;   SELECT d.deptno, ENAME, d.dname, JOB FROM emp e, dept d WHERE d.deptno = e.deptno;     DEPTNO ENAME DNAME JOB ---------- ---------- -------------- --------- 20 SMITH RESEARCH CLERK 30 ALLEN SALES SALESMAN 30 WARD SALES SALESMAN 20 JONES RESEARCH MANAGER 30 MARTIN SALES SALESMAN 30 BLAKE SALES MANAGER 10 CLARK ACCOUNTING MANAGER 20 SCOTT RESEARCH ANALYST 10 KING ACCOUNTING PRESIDENT 30 TURNER SALES SALESMAN 20 ADAMS RESEARCH CLERK 30 JAMES SALES CLERK 20 FORD RESEARCH ANALYST 10 MILLER ACCOUNTING CLERK     14 rows selected.         Execution Plan ---------------------------------------------------------- Plan hash value: 351108634     ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 420 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 14 | 420 | 4 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | ----------------------------------------------------------------------------------------     Predicate Information (identified by operation id): ---------------------------------------------------     4 - access("D"."DEPTNO"="DEPTNO")         Statistics ---------------------------------------------------------- 4 recursive calls 0 db block gets 24 consistent gets 0 physical reads 0 redo size 980 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed       exec DBMS_RLS.DROP_POLICY('scott','emp','hide_sal_policy');   BEGIN DBMS_RLS.ADD_POLICY( object_schema => 'scott', object_name => 'emp', policy_name => 'hide_sal_policy', policy_function => 'hide_sal_comm', sec_relevant_cols =>' sal,comm', sec_relevant_cols_opt => dbms_rls.ALL_ROWS); END; /   SELECT ENAME, d.dname, job, sal, comm FROM emp e, dept d WHERE d.deptno = e.deptno;     SCOTT> SCOTT> select ENAME, d.dname, job, sal, comm, d.deptno 2 from emp e, dept d 3 where d.deptno = e.deptno;     ENAME DNAME JOB SAL COMM DEPTNO ---------- -------------- --------- ---------- ---------- ---------- SMITH RESEARCH CLERK 20 ALLEN SALES SALESMAN 1600 300 30 WARD SALES SALESMAN 1250 500 30 JONES RESEARCH MANAGER 20 MARTIN SALES SALESMAN 1250 1400 30 BLAKE SALES MANAGER 2850 30 CLARK ACCOUNTING MANAGER 10 SCOTT RESEARCH ANALYST 20 KING ACCOUNTING PRESIDENT 10 TURNER SALES SALESMAN 1500 0 30 ADAMS RESEARCH CLERK 20 JAMES SALES CLERK 950 30 FORD RESEARCH ANALYST 20 MILLER ACCOUNTING CLERK 10     14 rows selected.         Execution Plan ---------------------------------------------------------- Plan hash value: 351108634     ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 504 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 14 | 504 | 4 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | EMP | 14 | 322 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | ----------------------------------------------------------------------------------------     Predicate Information (identified by operation id): ---------------------------------------------------     4 - access("D"."DEPTNO"="DEPTNO")         Statistics ---------------------------------------------------------- 3 recursive calls 0 db block gets 24 consistent gets 0 physical reads 0 redo size 1129 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed