oracle 虚拟专用数据库（VPD）

 

 

实现用户存在scott.emp中，那么通过 select * from scott.emp时。实际语句为select * from scott.emp where ename =’ The current user’;

 

 

创建用户并授予权限。

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>

 

在scott用户下创建函数

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>

 

授予scott 权限

SQL> grant execute on dbms_rls to scott;   Grant succeeded.   SQL>

 

Scott用户创建一个policy

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>

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29532781/viewspace-1174703/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29532781/viewspace-1174703/