对不同的用户授予不同的数据访问权限，这在大型集中系统以及数据仓库环境中

会经常用到，oracle从8i开始提供了VPD技术来从数据库底层实现。

在这里，演示用户TEST只能看表scott.emp中deptno=10的记录，
用户HR只能看deptno in (20,30)的记录，其它用户进去不做限制

1:建立用户对应权限表，模拟用户授权情况
connect sys/xxxx as sysdba
SQL> create table user_privs(username varchar2(100),deptno number(2));
SQL> insert into user_privs(username,deptno) values ('TEST',10);
SQL> insert into user_privs(username,deptno) values ('HR',20);
SQL> insert into user_privs(username,deptno) values ('HR',30);
SQL> commit;
SQL> grant select on scott.emp to hr,test;

2:sys用户下建立应用的上下文context
   SQL> create context mezi_context using sys.mezi_security_context;

3:为应用上下文context定义一个包头
   Create Or Replace Package sys.mezi_security_context
    Is
    Procedure  set_mezi_context;
    End;

4:定义设置上下文context的包体
Create Or Replace Package Body sys.mezi_security_context
Is
Procedure set_mezi_context
Is
v_deptno Varchar2(100);
v_username Varchar2(100);
Begin
select username,wmsys.wm_concat(deptno) Into v_username,v_deptno
from user_privs
Where username=sys_context('userenv','session_user')
group by username;
If sys_context('userenv','session_user') In ('TEST','HR') Then
   dbms_session.set_context('MEZI_CONTEXT','DEPTNO',v_deptno);
Else
    Null;
End If;
Exception
  When no_data_found Then
       Null;
End;
End;

5:定义一个DB级别的AFTER LOGON  trigger
CREATE OR REPLACE TRIGGER tri_mezi_security
AFTER LOGON ON DATABASE
BEGIN
  sys.mezi_security_context.set_mezi_context;
End;

测试
SQL> connect test/test@db1
SQL> select sys_context('mezi_context','DEPTno') from dual;
SYS_CONTEXT('MEZI_CONTEXT','DE
--------------------------------------------------------------------------------
10

6:定义策略函数包头
Create Or Replace Package SYS.mezi_security Is
Function set_deptno_where(OWNER VARCHAR2,OBJECT_NAME VARCHAR2)
Return Varchar2;
End;

7:定义策略函数应用包体

Create Or Replace Package Body SYS.mezi_security
Is
Function set_deptno_where(OWNER VARCHAR2,OBJECT_NAME VARCHAR2)
Return Varchar2
Is
Begin
If sys_context('USERENV','SESSION_USER') In ('TEST','HR') Then
   Return 'deptno in ( '||sys_context('mezi_context','deptno')||')';
Else
   Return '1=1';
End If;
End ;
End;
注意
/*+ 需要OWNER VARCHAR2,OBJECT_NAME VARCHAR2   此参数，否则报如下错
SQL> SELECT * FROM SCOTT.EMP;

SELECT * FROM SCOTT.EMP

ORA-28112: ÎÞ·¨Ö´ÐвßÂÔº¯Êý

SQL> select MEZI_SECURITY.SET_DEPTNO_WHERE from dual;

SET_DEPTNO_WHERE
--------------------------------------------------------------------------------
deptno='20'
*/

8:
建立包mezi_security  的public synonym，并把execute权限赋给public

create public synonym mezi_security for sys.mezi_security;

grant execute on mezi_security  to public;

否则其它用户无法执行策略函数

9:

对object应用策略
 exec DBMS_RLS.ADD_POLICY(OBJECT_SCHEMA=>'SCOTT',OBJECT_NAME=>'EMP',POLICY_NAME=>'EMP_POLICY',FUNCTION_SCHEMA=>'SYS',POLICY_FUNCTION=>'MEZI_SECURITY.SET_DEPTNO_WHERE',ENABLE=>TRUE);

取消object的策略
EXEC  DBMS_RLS.DROP_POLICY('SCOTT','EMP','EMP_POLICY');

10:
connect test/test  测试
connect hr/hr   测试
connect scott/tiger  测试