设置基于Column - Level 的VPD

从Oracle 10g开始，我们可以设置基于column-level的VPD，从而保护那些资料表中的重要信息列，比如password column、Price等（这个要根据具体的应用需求来定），有两种column-level的方法可以使用：

1）使用column-level VPD 保护数据不被访问

2）Display the column with NULL values

说明：1）一个查询中包含被保护的column

2）和基于row-based VPD有所不同，在column-level VPD中，所有的行均可以被访问，但是被保护列可以被隔离或采用MASK显示。

3）column-level VPD在表和视图上均可设置

实现：和row-based VPD相似，我们只需在调用dbms_rls.add_policy的时候,指定sec_relevant_cols（指定被保护列）,sec_relevant_cols_opt（MASK）两个参数即可.

　

下面我通过一个test case来简单的介绍一下

　

需求说明：

对于study.customer 表中的status 字段，不允许维护帐户（user_a,user_b）查看或显示为NULL。

实现过程

1）创建测试表、用户，并授予相应的权限

--建表

create table study.customer
(region varchar2(4),
msisdn varchar2(11),
status varchar2(2)
);
--插入数据
insert into study.customer values('530','13905301234','0');
insert into study.customer values('530','13905305678','1');
insert into study.customer values('531','13805318888','0');
insert into study.customer values('531','13605319999','1');
insert into study.customer values('532','15805320000','0');
insert into study.customer values('533','15905336666','0');
commit;

--授权
create user user_a identified by user_a default tablespace data_01;
create user user_b identified by user_b default tablespace data_01;

grant create session to user_a,user_b;

grant select,insert,update,delete on study.customer to user_a,user_b;
grant execute on dbms_rls to user_a,user_b;

　

2）创建一个security package，并授权

create or replace package study.security_pkg as
function test_security(owner varchar2,objname varchar2) return varchar2;
end;
/
create or replace package body study.security_pkg is
function test_security(owner varchar2,objname varchar2) return varchar2 is
v_sql varchar2(2000):='1=0';
   begin
     if (sys_context('USERENV','SESSION_USER') ='STUDY') then
       v_sql:=null;
     end if;
     return v_sql;
     end;
end;
/


grant execute on study.security_pkg to public;
create or replace public synonym security_pkg for study.security_pkg;

　

3）设置policy

begin
dbms_rls.add_policy(
object_schema => 'STUDY',
object_name => 'CUSTOMER',
policy_name => 'C_COL_POLICY',
function_schema => 'STUDY',
policy_function => 'SECURITY_PKG.TEST_SECURITY',
sec_relevant_cols => 'STATUS');
end;
/

　

--在这个security policy 设置中，我们指定了status列是被保护的列，所有的维护帐户不能访问该列，否则将返回0行。

　

4）测试column-level VPD

下面我们来感受一下吧（user_a,user_b是受限的维护帐户，看看效果）

SQL> conn user_a/user_a
已连接。
SQL> select * from study.customer;

未选定行

SQL> select region,msisdn from study.customer;

REGI MSISDN
---- -----------
530  13905301234
530  13905305678
531  13805318888
531  13605319999
532  15805320000
533  15905336666

已选择6行。

SQL> select msisdn,status from study.customer;

未选定行

SQL> conn user_b/user_b
已连接。
SQL> select * from study.customer;

未选定行

SQL> select region,msisdn from study.customer;

REGI MSISDN
---- -----------
530  13905301234
530  13905305678
531  13805318888
531  13605319999
532  15805320000
533  15905336666

已选择6行。

SQL> select msisdn,status from study.customer;

未选定行

SQL> conn study/study
已连接。
SQL> select * from study.customer;

REGI MSISDN      ST
---- ----------- --
530  13905301234 0
530  13905305678 1
531  13805318888 0
531  13605319999 1
532  15805320000 0
533  15905336666 0

已选择6行。

SQL> select msisdn,status from study.customer;

MSISDN      ST
----------- --
13905301234 0
13905305678 1
13805318888 0
13605319999 1
15805320000 0
15905336666 0

已选择6行。

SQL>
5）补充说明

一、如果要保护多列，我们只需要在sec_relevant_cols参数中指定多个列即可（用,分开），比如


begin
   dbms_rls.add_policy(
         object_schema => 'STUDY',
          object_name => 'CUSTOMER',
           policy_name => 'C_COL_POLICY',
       function_schema => 'STUDY',
       policy_function => 'SECURITY_PKG.TEST_SECURITY',
     sec_relevant_cols => 'MSISDN,STATUS');
end;
/
二、如果要采用masking option,我们只需要在设置policy的时候，指定sec_relevant_cols_opt 参数为DBMS_RLS.ALL_ROWS即可,比如：
begin
  dbms_rls.add_policy(
         object_schema => 'STUDY',
           object_name => 'CUSTOMER',
           policy_name => 'C_COL_POLICY',
       function_schema => 'STUDY',
       policy_function => 'SECURITY_PKG.TEST_SECURITY',
     sec_relevant_cols => 'STATUS',
sec_relevant_cols_opt => dbms_rls.all_rows);
end;
/
在这种保护模式下，被保护的列，均显示是NULL。

　

三、同样的如果我们需要保护涉及到该列的DML操作时，需要设置如下2个参数（前面的row-based VPD中已经介绍过了）

begin
  dbms_rls.add_policy(
         object_schema => 'STUDY',
           object_name => 'CUSTOMER',
           policy_name => 'C_COL_POLICY',
       function_schema => 'STUDY',
       policy_function => 'SECURITY_PKG.TEST_SECURITY',
       statement_types => 'SELECT,INSERT,UPDATE,DELETE',
          update_check => TRUE,
     sec_relevant_cols => 'STATUS');
end;
/
四、masking option只支持查询操作，对DML操作无效

　

五、捎带说一下

如果要删除一个policy,可以通过dbms_rls.drop_policy完成，如

begin
dbms_rls.drop_policy (object_schema =>'STUDY', object_name =>'CUSTOMER', policy_name=> 'C_COL_POLICY');
end;
/

　

如果要enable/disable一个policy可以通过dbms_rls.enable_policy完成，如

SQL> exec dbms_rls.enable_policy('STUDY','CUSTOMER','C_COL_POLICY',FALSE);
PL/SQL procedure successfully completed

SQL> exec dbms_rls.enable_policy('STUDY','CUSTOMER','C_COL_POLICY',TRUE);

PL/SQL procedure successfully completed

SQL>