ORACLE Virtual Private Database 全新體驗

　　在開始對ORACLE的Virtual Private Database的介紹之前筆者想就ROWRULE control(行記錄級訪問控制)的概念簡單地說幾句
　　
　　行記錄級訪問控制問題的提出和意義？
　　企業的應用系統都離不開數據庫系統數據庫系統的權限控制是很重要的一個環節大型數據庫系統（ORACLEDBSYBASEMS SQLSERVER）都提供完善的用戶管理機制從而可以嚴密地控制數據庫對象(表視圖函數存儲過程程序包等等)的訪問但是這往往是對象級別的
　　
　　隨著商務需求地不斷地提出出現了對於行記錄控制的要求:
　　
　　) 數據查詢和報表輸出數據需要能夠進行有效地隔離如在一個簡單地銷售數據統計應用中大區經理地區經理和銷售員查詢的數據就不同
　　
　　) ASP（應用服務供應商）系統出現在系統結構上就出現了許多企業用戶的數據都會存放在同一個數據庫種但是系統需要能夠有效地隔離
　　
　　為了滿足這樣的需求在業務數據表需要中加上一些字段來進行控制應用的開發往往會另行開發很多代碼來實現行記錄控制但是隨著業務的變化我們會發現開發和維護這種管理需求的成本越來越高
　　
　　我們需要尋找一種新的解決方案能夠使應用系統的架構設計簡單擴展性強管理和維護的成本很低
　　
　　ORACLEi的一個新特性Virtual Private Database
　　
　　ORACLE i提供了一個新的特性來實現行級規則的控制稱之為Virtual Private Database充分利用i提供的Virtual Private Database技術可以實現一個數據庫Schema的數據同時給多個數據庫用戶訪問但是又能很好地隔離各自的數據內容顯然這已經不是原來的對象級的控制的概念
　　
　　下面是筆者的體驗寫下來供大家參考我先通過一個簡單的例子來說明ORACLE i的這個新特性(需ORACLE i的企業版才支持)
　　
　　環境:Windows Server + ORACLE (Enterprise Edition)
　　
　　在SCOTT用戶下有一個Customers表記錄著客戶資料以後為每個客戶分配一個ORACLE數據庫登陸賬號客戶可以登陸查詢自己的訂單情況那麼我們需要做的就是能夠把每個登陸賬號和客戶代碼對應起來就是說要實現一個映射關系當然通過自己建關系映射表寫代碼做也可以實現但是ORACLE I把這一切變地非常簡單（我接下來的介紹都會圍繞著ORACLE i這項技術是我們的工作如何更加簡單如何更加容易控制請記住這是筆者寫這篇文章的目的）
　　
　　建立一個SECUSR的賬號用於權限控制用
　　
　　connect system/manager@oracle;
　　
　　create user secusr identified by secusr;
　　
　　grant connectresourcedba to secusr;
　　
　　把Customers的查詢權利賦予secusr
　　
　　connect scott/tiger@oracle;
　　
　　grant select on Customers to secusr;
　　
　　連接到secusr用戶
　　
　　connect secusr/secusr@oracle;
　　
　　創建上下文
　　
　　create context Customer_context USING secusrCUSTOMER_SECURITY_CONTEXT;
　　
　　創建程序包customer_security_context
　　
　　create or replace package
　　
　　secusrcustomer_security_context is
　　
　　procedure set_customerid;
　　
　　end;
　　
　　create or replace package body
　　
　　secusrCustomer_security_context is
　　
　　procedure set_customerid is
　　
　　begin
　　
　　IF SYS_CONTEXT(USERENVSESSION_USER)=SCOTT THEN
　　
　　　 DBMS_SESSIONSET_CONTEXT(customer_contextcustomeridALFKI);
　　
　　END IF;
　　
　　end;
　　
　　end;
　　
　　授權
　　
　　grant execute on secusrCustomer_security_context to public;
　　
　　ORACLE i中提供了Context(連接上下文)的概念類似於asp中的session你可以為當前這個連接設置多個全局變量記錄信息這個信息一直保持到連接被釋放上面的代碼就是為用SCOTT賬號登陸的連接進行了一次客戶代碼的映射(SCOTT>ALFKI)而且隨時可以SYS_CONTEXT來查詢
　　
　　具體代碼如下
　　
　　SQL> connect scott/tiger@oracle;
　　
　　已連接
　　
　　SQL> execute secusrCustomer_security_contextset_customerid;
　　
　　PL/SQL 過程已成功完成
　　
　　SQL> select SYS_CONTEXT(CUSTOMER_CONTEXTCUSTOMERID) FROM DUAL;
　　
　　SYS_CONTEXT(CUSTOMER_CONTEXTCUSTOMERID)
　　
　　
　　
　　ALFKI
　　
　　但是我覺的還不夠能夠做到每個連接建立的時候就自動完成這種映射令人高興的是ORACLE i提供了系統級的觸發器讓我輕松地實現
　　
　　SCOTT用戶登陸觸發器
　　
　　connect system/manager@oracle
　　
　　CREATE OR REPLACE TRIGGER scotttg_set_usr_context
　　
　　AFTER LOGON ON DATABASE
　　
　　BEGIN
　　
　　secusrcustomer_security_contextset_customerid;
　　
　　END;
　　
　　斷掉connection重新登陸
　　
　　Oraclei Enterprise Edition Release Production
　　
　　With the Partitioning option
　　
　　JServer Release Production
　　
　　SQL> CONNECT scott/tiger@oracle
　　
　　已連接
　　
　　SQL> select SYS_CONTEXT(CUSTOMER_CONTEXTCUSTOMERID) FROM DUAL;
　　
　　SYS_CONTEXT(CUSTOMER_CONTEXTCUSTOMERID)
　　
　　
　　
　　ALFKI
　　
　　SQL>
　　
　　好了夠簡單吧
　　
　　采用Virtual Private Database如何達到SQL DML上的數據控制訪問要求呢？
　　
　　Virtual Private Database技術可以對一張表的記錄設置DML操作的過濾策略ORACLEi提供了POLICY的概念並且為此配備了一套系統程序包來完成設置下面我來介紹一下
　　
　　connect secusr/secusr@oracle
　　
　　做一個函數返回對應的過濾條件
　　
　　create or replace package secusrcustomer_security is
　　
　　function customer_sec
　　
　　return VARCHAR;
　　
　　end;
　　
　　create or replace package body secusrcustomer_security
　　
　　is
　　
　　function customer_sec(d varchard varchar)
　　
　　return varchar
　　
　　IS
　　
　　begin
　　
　　IF SYS_CONTEXT(USERENVSESSION_USER) IN (SYSSYSTEMSECUSR) THEN
　　
　　　 RETURN NULL;
　　
　　ELSE
　　
　　　 RETURN customerid=|| SYS_CONTEXT(CUSTOMER_CONTEXTCUSTOMERID) || ;
　　
　　END IF;
　　
　　end;
　　
　　end;
　　
　　設置表數據的分割過濾
　　
　　EXECUTE DBMS_RLSADD_POLICY(SCOTTCustomersCustomers_sec_Policy
　　
　　SECUSR
　　
　　customer_securitycustomer_sec
　　
　　SELECTUPDATEDELETE);
　　
　　customer_security程序包的customer_sec函數實現了根據但前的CONTEXT中CUSTOMERID的內容來返回一個過濾的策略函數的參數形式是固定的通過dbms_rls程序包的ADD_POLICY過程把策略條件同表綁定以來以後每次selectupdatedelete都會自動應用這個策略
　　
　　好了現在我們來測試一下
　　
　　用SCOTT登陸只能看到自己的信息
　　
　　SQL> connect scott/tiger@oracle;
　　
　　已連接
　　
　　SQL> select customeridcity from Customers;
　　
　　CUSTOMERID CITY
　　
　　
　　
　　ALFKI　　　Berlin
　　
　　用SYSTEM登陸可以看到所有的
　　
　　SQL> connect system/manager@oracle;
　　
　　已連接
　　
　　SQL> select customeridcity from SCOTTCustomers;
　　
　　CUSTOMERID CITY
　　
　　
　　
　　ALFKI　　　Berlin
　　
　　ANATR　　　México DF
　　
　　ANTON　　　México DF
　　
　　AROUT　　　London
　　
　　BERGS　　　Lule?　 DF
　　
　　BLAUS　　　Mannheim
　　
　　BLONP　　　Strasbourg
　　
　　BOLID　　　Madrid
　　
　　BONAP　　　Marseille
　　
　　BOTTM　　　Tsawassen
　　
　　BSBEV　　　London
　　
　　非常好完全滿足了要求現在可以放心地把SCOTT賬號給客戶(ALFKI)了客戶可以查詢自己的訂單情況當然只能是自己的
　　
　　SQL> SELECT aorderidacustomeridaorderdate
　　
　　　　sum(cUnitPrice*cQuantity*(cDiscount)) as TotalMoney
　　
　　　　FROM Orders aCustomers b Order Details c
　　

From:http://tw.wingwit.com/Article/program/Oracle/201311/18040.html