bys是个DBA用户。数据库版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
1.创建函数 hide_sal_comm 和策略。
SCOTT@ bys001>conn bys/bys
Connected.
create or replace function hide_sal_comm(v_schema varchar2,v_object varchar2)
return varchar2 as com varchar2(200);
begin
com:='deptno=30';
return(com);
end hide_sal_comm;
/
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');
end;
/2.进行查询，验证上面的限制
查询的字段中中涉及comm,sal字段，默认加上条件 where deptno=30
SCOTT@ bys001>select deptno,empno,comm,sal from emp;
DEPTNO EMPNO COMM SAL
---------- ---------- ---------- ----------
30 7499 300 1600
30 7521 500 1250
30 7654 1400 1250
30 7698 2850
30 7844 0 1500
30 7900 950
6 rows selected.
查询的字段中涉及comm,sal字段之一，默认同样会加上条件 where deptno=30
SCOTT@ bys001>select deptno,empno,comm from emp;
DEPTNO EMPNO COMM
---------- ---------- ----------
30 7499 300
30 7521 500
30 7654 1400
30 7698
30 7844 0
30 7900
6 rows selected.
查询的字段中不涉及comm,sal字段，查出整张表的。
SCOTT@ bys001>select deptno,empno from emp;
DEPTNO EMPNO
---------- ----------
20 7369
30 7499
30 7521
20 7566
30 7654
30 7698
10 7782
20 7788
10 7839
30 7844
20 7876
30 7900
20 7902
10 7934
14 rows selected.
#################################################################################
3.删除策略，并新建策略，对需要隐藏的列以NULL显示
查询的字段中涉及comm,sal字段，14行都可以显示，非deptno=30的行中的comm,sal字段被NULL取代
BYS@ bys001>exec dbms_rls.drop_policy('scott','emp','hide_sal_policy');
PL/SQL procedure successfully completed.
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;
/
BYS@ bys001>conn scott/tiger
Connected. SCOTT@ bys001>select deptno,empno,comm,sal from emp order by deptno;
DEPTNO EMPNO COMM SAL
---------- ---------- ---------- ----------
10 7782
10 7839
10 7934
20 7566
20 7902
20 7876
20 7369
20 7788
30 7521 500 1250
30 7844 0 1500
30 7499 300 1600
30 7900 950
30 7698 2850
30 7654 1400 1250
14 rows selected.
查询的字段中涉及comm,sal字段之一。14行都可以显示，非deptno=30的行中的comm,sal字段被NULL取代
SCOTT@ bys001>select deptno,empno,comm from emp order by deptno;
DEPTNO EMPNO COMM
---------- ---------- ----------
10 7782
10 7839
10 7934
20 7566
20 7902
20 7876
20 7369
20 7788
30 7521 500
30 7844 0
30 7499 300
30 7900
30 7698
30 7654 1400
14 rows selected.
查询的字段中不涉及comm,sal字段
SCOTT@ bys001>select deptno,empno from emp order by deptno desc;
DEPTNO EMPNO
---------- ----------
30 7698
30 7844
30 7499
30 7654
30 7521
30 7900
20 7788
20 7566
20 7369
20 7876
20 7902
10 7839
10 7934
10 7782
14 rows selected.
#################################################
4.清除策略后查询可以正常显示
BYS@ bys001>exec dbms_rls.drop_policy('scott','emp','hide_sal_policy');
PL/SQL procedure successfully completed.
BYS@ bys001>conn scott/tiger
Connected.
SCOTT@ bys001>select deptno,empno,comm,sal from emp order by deptno;
DEPTNO EMPNO COMM SAL
---------- ---------- ---------- ----------
10 7782 2450
10 7839 5000
10 7934 1300
20 7566 2975
20 7902 3000
20 7876 1100
20 7369 800
20 7788 3000
30 7521 500 1250
30 7844 0 1500
30 7499 300 1600
30 7900 950
30 7698 2850
30 7654 1400 1250
14 rows selected.
