'oracle vpd'에 해당되는 글 1건


  • 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

   

   

   

   

  

   

   

   

   

   

   

'1. IT Story > DB' 카테고리의 다른 글

Oracle Golden Gate 개념 및 Installaton  (2) 2013.04.08
Oracle 11gR2 TDE(Transparent Data Encryption)  (0) 2013.03.31
Oracle 10g CRS 제거  (0) 2013.03.31
Oracle 11g RAC with ASM TEST  (2) 2013.03.31
Splunk install-Linux  (0) 2013.02.09
Big-Data Hive 환경구성(MySql)  (0) 2013.02.09
Big-Data Hadoop 환경구성  (0) 2013.02.09
Big-Data Hadoop기초  (0) 2013.02.09
블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,