VPD(Virtual Private Database)

VPD可以直接在表，视图和同义词上实施安全策略，提供行或列级别的安全性

VPD可应用于SELECT, INSERT, UPDATE, INDEX和DELETE命令

VPD是在SQL访问受VPD保护的对象时，SQL被动态地修改加入限制where条件

1. 创建用户并授权

conn sys/oracle as sysdba

grant create session to adams identified by john7;

grant create session to burlington identified by newj2;

grant create session to practice identified by practice;

grant resource to practice;

grant create any context, create public synonym to practice;

grant create any procedure to practice;

grant unlimited tablespace to practice;

grant execute on dbms_rls to practice;

connect practice/practice

create table stock_account(account number(10), account_longname varchar2(50));

insert into  stock_account values (1234,'ADAMS');

insert into  stock_account values (7777,'BURLINGTON');

create table stock_trx( account number(10), symbol varchar2(20), price number(6,2), quantity number(6), trx_flag varchar2(1));

insert into stock_trx values(1234,'ADSP',31.75, 100, 'b');

insert into stock_trx values(7777,'ADSP',31.50,300,'s');

insert into stock_trx values(1234,'ADSP',31.55, 100,'b');

insert into stock_trx values(7777,'OCKS',21.75, 1000, 'b');

commit;

1. 创建应用上下文

connect practice/practice

create context practice using practice.context_package;

create or replace package context_package as

  procedure set_context;

end;

/

create or replace package body context_package is

  procedure set_context is

    v_user varchar2(30);

    v_id number;

  begin

    dbms_session.set_context('PRACTICE','SETUP','TRUE');

    v_user := sys_context('USERENV','SESSION_USER');

    begin

      select account into v_id from stock_account where account_longname = v_user;

      dbms_session.set_context('PRACTICE','USER_ID',v_id);

    exception

      when no_data_found then

        dbms_session.set_context('PRACTICE','USER_ID',0);

    end;

    dbms_session.set_context('PRACTICE','SETUP','FALSE');

  end set_context;

end context_package;

/

grant execute on practice.context_package to public;

create public synonym context_package for practice.context_package;

1. 创建登录触发器

conn sys/oracle as sysdba

create or replace trigger practice.set_security_context

after logon on database

begin

  practice.context_package.set_context;

end;

/

测试：

conn adams/john7

select sys_context('USERENV','SESSION_USER') username, sys_context('PRACTICE','USER_ID') id from dual;

1. 创建安全策略

connect practice/practice

create or replace package security_package as

  function stock_trx_insert_security(owner varchar2, objname varchar2)

    return varchar2;

  function stock_trx_select_security(owner varchar2, objname varchar2)

    return varchar2;

end security_package;

/

create or replace package body security_package is

  function stock_trx_select_security(owner varchar2, objname varchar2)

  return varchar2 is

     predicate varchar2(2000);

  begin

     predicate := '1=2';

     if (sys_context('USERENV','SESSION_USER') = 'PRACTICE') then

        predicate := null;

     else

        predicate := 'account = sys_context(''PRACTICE'',''USER_ID'')';

     end if;

     return predicate;

  end stock_trx_select_security;

  function stock_trx_insert_security(owner varchar2, objname varchar2)

    return varchar2 is

      predicate varchar2(2000);

    begin

      predicate := '1=2';

      if (sys_context('USERENV','SESSION_USER') = 'PRACTICE') then

        predicate := null;

      else

        predicate := 'account = sys_context(''PRACTICE'',''USER_ID'')';

      end if;

   return predicate;

  end stock_trx_insert_security;

end security_package;

/

grant execute on practice.security_package to public;

create public synonym security_package for practice.security_package;

1. 将安全策略应用于表

begin

dbms_rls.add_policy('PRACTICE','STOCK_TRX','STOCK_TRX_INSERT_POLICY','PRACTICE','SECURITY_PACKAGE.STOCK_TRX_INSERT_SECURITY','INSERT',TRUE);  dbms_rls.add_policy('PRACTICE','STOCK_TRX','STOCK_TRX_SELECT_POLICY','PRACTICE','SECURITY_PACKAGE.STOCK_TRX_SELECT_SECURITY','SELECT');

end;

/

1. 测试VPD

conn practice/practice

grant all on stock_trx to public;

connect adams/john7

select * from practice.stock_trx;

   ACCOUNT SYMBOL                    PRICE   QUANTITY T

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

      1234 ADSP                      31.75        100 b

      1234 ADSP                      31.55        100 b

insert into practice.stock_trx values(7777,'ADSP',31.5, 100, 'B');

ORA-28115: policy with check option violation

connect burlington/newj2

select * from practice.stock_trx;

   ACCOUNT SYMBOL                    PRICE   QUANTITY T

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

      7777 ADSP                       31.5        300 s

      7777 OCKS                      21.75       1000 b

connect scott/tiger

select * from practice.stock_trx;

no rows selected

1. 使用列级别限制

connect practice/practice

begin

  dbms_rls.add_policy

      (object_schema=>'PRACTICE',

       object_name=>'STOCK_TRX',

       policy_name=>'STOCK_TRX_SELECT_POLICY2',

       function_schema=>'PRACTICE',

       policy_function=>'SECURITY_PACKAGE.STOCK_TRX_SELECT_SECURITY',

       sec_relevant_cols=>'Price');

end;

/

列屏蔽只是查询时不显示列，不用于DML，因此屏蔽列必须支持显示NULL值

1. 如何禁用VPD

按上面反向操作即可，使用DBMS_RLS.DROP_POLICY，删除触发器，选择性删除其它程序包：

exec dbms_rls.drop_policy('PRACTICE','STOCK_TRX','STOCK_TRX_INSERT_POLICY');

exec dbms_rls.drop_policy('PRACTICE','STOCK_TRX','STOCK_TRX_SELECT_POLICY');

drop trigger practice.set_security_context;

1. 使用策略组

可以将相同表的策略添加到策略组中，在策略组中启用策略

默认所有表策略均属于sys_default策略组中，它不能删除

添加策略组：

desc dbms_rls

PROCEDURE CREATE_POLICY_GROUP

 Argument Name                  Type                    In/Out Default?

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

 OBJECT_SCHEMA                  VARCHAR2                IN     DEFAULT

 OBJECT_NAME                    VARCHAR2                IN

 POLICY_GROUP                   VARCHAR2                IN

PROCEDURE ADD_GROUPED_POLICY

 Argument Name                  Type                    In/Out Default?

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

 OBJECT_SCHEMA                  VARCHAR2                IN     DEFAULT

 OBJECT_NAME                    VARCHAR2                IN

 POLICY_GROUP                   VARCHAR2                IN     DEFAULT

 POLICY_NAME                    VARCHAR2                IN

 FUNCTION_SCHEMA                VARCHAR2                IN     DEFAULT

 POLICY_FUNCTION                VARCHAR2                IN

 STATEMENT_TYPES                VARCHAR2                IN     DEFAULT

 UPDATE_CHECK                   BOOLEAN                 IN     DEFAULT

 ENABLE                         BOOLEAN                 IN     DEFAULT

 STATIC_POLICY                  BOOLEAN                 IN     DEFAULT

 POLICY_TYPE                    BINARY_INTEGER          IN     DEFAULT

 LONG_PREDICATE                 BOOLEAN                 IN     DEFAULT

 SEC_RELEVANT_COLS              VARCHAR2                IN     DEFAULT

 SEC_RELEVANT_COLS_OPT          BINARY_INTEGER          IN     DEFAULT

使用时在下面步骤指定：

exec dbms_session.set_context('PRACTICE','SETUP','policy_group');