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>

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.

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>

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>

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>

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

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;

권한이 부여되었습니다.

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