oracle vpd 虚拟专用数据库

CREATEORREPLACEfunction func_vpd(p_ownervarchar2,p_objectnamevarchar2)

RETURNVARCHAR2

AS

 v_predicate varchar2(4000);

 BEGIN

    v_predicate :='last_name=initcap(sys_context(''userenv'',''session_user''))';

    RETURN v_predicate;

    end;

  

   

    select a.object_name,a.status

    from dba_objects awhere a.object_name='FUNC_VPD'

   

 SELECT *FROM USER_ERRORS

 

BEGIN

 DBMS_RLS.ADD_POLICY (object_schema    => 'hr',

                       object_name       => 'employees',

                       policy_name       => 'sp_job',

                       function_schema   => 'sys',

                       policy_function   => 'FUNC_VPD',

                        statement_types=>'select,insert',

                       sec_relevant_cols =>'salary,commission_pct'

                       );

END;

 

SQL> update employees setlast_name='King2' where employee_id=100;

 

1 row updated.

 

SQL> commit;

 

SQL> create user king identified byoracle;

 

User created.

 

SQL> grant connect to king;

 

Grant succeeded.

 

SQL> grant select on hr.employees toking;

 

SQL> select count(last_name) fromhr.employees;

 

COUNT(LAST_NAME)

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

            107

 

Grant succeeded.

 

SQL> select last_name,salary  from hr.employees;

 

LAST_NAME                                                                      SALARY

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

King                                                                            10000

 

SQL> selectlast_name,commission_pct  fromhr.employees;

 

LAST_NAME                                                                  COMMISSION_PCT

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

King                                                                                  .35

由于受到policy的影响，一旦查询到salary 或者是commissi_pct 时，查询

条件（谓语）会自动带有where  last_name=

 

下面演示如何从策略中豁免

SQL> conn / as sysdba                                                                                                   

Connected.

SQL> grant exempt access policy to king;

 

Grant succeeded.

 

SQL> conn king/oracle

Connected.

SQL> select last_name,commission_pct  from hr.employees;

.

.

.

Everett

McCain

Jones

Walsh

Feeney

 

107 rows selected.