rajesh@10GR2> create table emp 2 as 3 select empno, 4 ename, 5 deptno, 6 dbms_random.random as ssn 7 from scott.emp; Table created. Elapsed: 00:00:00.26 rajesh@10GR2> create or replace function foo(p_schema in varchar2,p_object_name in varchar2) 2 return varchar2 3 as 4 begin 5 if ( p_schema = user ) then 6 return '1 = 1'; 7 else 8 return '1=0'; 9 end if; 10 end; 11 / Function created. Elapsed: 00:00:00.09 rajesh@10GR2>
Now, lets say that we need to hide this SSN details from others. This can be done by using new features available dbms_rls API in Oracle 10g database.
rajesh@10GR2> begin 2 dbms_rls.add_policy( 3 object_schema =>user, 4 object_name =>'EMP', 5 policy_name => 'SSN_HIDING', 6 function_schema => user, 7 policy_function =>'FOO', 8 statement_types =>'SELECT', 9 sec_relevant_cols => 'SSN', 10 sec_relevant_cols_opt => dbms_rls.all_rows 11 ); 12 end; 13 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.04 rajesh@10GR2> rajesh@10GR2> select * from emp; EMPNO ENAME DEPTNO SSN ---------- ---------- ---------- ---------- 7369 SMITH 20 407740162 7499 ALLEN 30 -128160633 7521 WARD 30 378242413 7566 JONES 20 -542663968 7654 MARTIN 30 1929020028 7698 BLAKE 30 408643855 7782 CLARK 10 -569575368 7788 SCOTT 20 1718524881 7839 KING 10 -2.139E+09 7844 TURNER 30 -1.033E+09 7876 ADAMS 20 -607357748 7900 JAMES 30 2099341193 7902 FORD 20 1979593316 7934 MILLER 10 -615781088 14 rows selected. Elapsed: 00:00:00.01 rajesh@10GR2> rajesh@10GR2> connect scott/tiger Connected. scott@10GR2> scott@10GR2> select * from rajesh.emp; EMPNO ENAME DEPTNO SSN ---------- ---------- ---------- ---------- 7369 SMITH 20 7499 ALLEN 30 7521 WARD 30 7566 JONES 20 7654 MARTIN 30 7698 BLAKE 30 7782 CLARK 10 7788 SCOTT 20 7839 KING 10 7844 TURNER 30 7876 ADAMS 20 7900 JAMES 30 7902 FORD 20 7934 MILLER 10 14 rows selected. Elapsed: 00:00:00.07 scott@10GR2> scott@10GR2>
