利用ORACLE VPD實現使用者安全控制

對不同的使用者授予不同的資料訪問許可權，這在大型集中系統以及資料倉儲環境中

會經常用到，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  測試

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10159839/viewspace-584287/，如需轉載，請註明出處，否則將追究法律責任。