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 | 
