oracle 虛擬專用資料庫（VPD）

SQL> conn / as sysdba

Connected.

SQL> create user king identified by oracle;

User created.

SQL> create user ward identified by oracle;

User created.

SQL> grant resource,connect to king,ward;

Grant succeeded.

SQL> grant select on scott.emp to king,ward;

Grant succeeded.

SQL>

SQL> create or replace function empvpd(owner varchar2,objrctname varchar2)  return varchar2 is v varchar2(2000);

  2   begin

  3  v:='ename=sys_context(''userenv'',''session_user'')';

  4  return v;

  5  end;

  6  /

Function created.

SQL>

SQL> grant execute on dbms_rls to scott;

Grant succeeded.

SQL>

SQL>begin

  2  dbms_rls.add_policy(object_schema=>'scott',object_name=>'emp',

policy_name=>'policyemp',function_schema=>'scott',policy_function=>'empvpd',statement_types=>'select',sec_relevant_cols=>'sal');

  3 end;

SQL> /

PL/SQL procedure successfully completed.

SQL>

SQL> conn king/oracle

Connected.

SQL>

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM

---------- ---------- --------- ---------- ------------ ---------- ----------

    DEPTNO

----------

      7839 KING       PRESIDENT            17-NOV-81          5500

        10

SQL>

SQL> conn ward/oracle

Connected.

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM

---------- ---------- --------- ---------- ------------ ---------- ----------

    DEPTNO

----------

      7521  WARD       SALESMAN        7698 22-FEB-81          1250        500

        30

SQL>

在scott使用者下也是一樣的，因為emp表中ename包含scott

SQL> conn scott/tiger

Connected.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM

---------- ---------- --------- ---------- ------------ ---------- ----------

    DEPTNO

----------

      7788 SCOTT      ANALYST         7566 19-APR-87          3000

        20

SQL>

只要包含sal列，那麼就會只顯示當前使用者資訊。

查詢時不包含sal列會顯示有用資訊。

SQL> select ename from scott.emp;

ENAME

----------

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

ENAME

----------

JAMES

FORD

MILLER

14 rows selected.

SQL>