VPD--行级安全(转)

利用vpd实现security policy to tables

VPD的概念，大家应该都比较清楚(本文不做具体的介绍),今天在针对一个具体的case配置了一个vpd policy，下面把概要的测试过程简单的show一下，详细的技术细节，大家要参阅Oracle DOC.

场景模拟

比如实际的应用中有这样的需求：
系统中有user_a,user_b,usre_c,user_d,user_e 5个维护帐户，其中:
user_name user_region

--------------- ----------------
user_a 530
user_b 530
user_c 531
user_d 532
user_e 532

系统中所有的table(study schema下)中都有region这样的字段，但我们限制这些维护帐户只能select所属region的记录(比如采用了FGA,VPD策略）,那么我们就可以设置一个简单的上下文环境来替换每次不必要的table join或app定制的工作。

比如：select * from study.customer where region=sys_context('TEST_CONTEXT','USER_REGION');

备注：如果配置了vpd,这个SQL就可以简单的变为：select * from study.customer;

 

环境准备

*study 用户我已经提前创建

1）在study用户下创建一张帐户信息表和资料表

--帐户表

create table study.account_def
( user_name varchar2(30),
user_region varchar2(4)
) tablespace data_01;

--插入帐户信息
insert into study.account_def values('USER_A','530');
insert into study.account_def values('USER_B','530');
insert into study.account_def values('USER_C','531');
insert into study.account_def values('USER_D','532');
insert into study.account_def values('USER_E','532');
commit;

　

--资料表

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;

　

2）创建维护帐户并授予相应的访问权限

create user user_a identified by user_a default tablespace data_01;
create user user_b identified by user_b default tablespace data_01;
create user user_c identified by user_c default tablespace data_01;
create user user_d identified by user_d default tablespace data_01;
create user user_e identified by user_e default tablespace data_01;
　

grant create session to user_a,user_b,user_c,user_d,user_e;

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

Create an Application Context

1）创建一个package

create or replace package context_package is
procedure set_context;
end;
/

create or replace package body context_package is
procedure set_context is
v_region varchar2(4);
begin
dbms_session.set_context('TEST_CONTEXT','SETUP','TRUE');
begin
select user_region into v_region
from account_def
where user_name=USER;
dbms_session.set_context('TEST_CONTEXT','USER_REGION',v_region);
exception
when OTHERS then
dbms_session.set_context('TEST_CONTEXT','USER_REGION',0);
end;
dbms_session.set_context('TEST_CONTEXT','SETUP','FALSE');
end;
end;
/

　

2）将context_package授权到public并创建一个public synonym

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

　

3）Create context

create context test_context using study.context_package;

　

Create a Logon Trigger

create or replace trigger sys.trig_set_context
after logon on database
begin
context_package.set_context;
end;
/

　

Create a Security Policy

1)创建一个Package

create or replace package study.security_pkg as
function test_security(owner varchar2,objname varchar2) return varchar2;
end;
/
create or replace package body 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;
else
v_sql:='REGION=to_char(sys_context(''TEST_CONTEXT'',''user_region''))';
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;

　

2)Apply The Security Policy to table

begin
dbms_rls.add_policy('STUDY','CUSTOMER','C_SELECT_POLICY', 'STUDY','SECURITY_PKG.SELECT_SECURITY','SELECT',true);
end;
/

　

Test VPD

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as user_a

SQL> select sys_context('USERENV','SESSION_USER') username,
  2         sys_context('TEST_CONTEXT','user_region') user_attr
  3  from dual;

USERNAME        USER_ATTR
--------------- -------------
USER_A          530

SQL> select * from study.customer;

REGION MSISDN      STATUS
------ ----------- ------
530    13905301234 0
530    13905305678 1

SQL> insert into study.customer values('530','15805303333','9');

1 row inserted

SQL> insert into study.customer values('010','15801006666','9');

insert into study.customer values('010','15801006666','9')

ORA-28115: 策略违反检验选项

SQL> update study.customer set region='531' where region='530' and msisdn='15805303333' and status='9';

1 row updated

SQL> select * from study.customer;

REGION MSISDN      STATUS
------ ----------- ------
530    13905301234 0
530    13905305678 1

SQL> delete from study.customer where region='531' and msisdn='15805303333' and status='9';

0 rows deleted

SQL> 其他测试内容，就不再多贴了。有兴趣的朋友可以自己测试一下.
最后一步：清理测试环境

　

conn /as sysdba
begin
dbms_rls.drop_policy (object_schema => 'STUDY', object_name => 'CUSTOMER', policy_name => 'C_SELECT_POLICY');
end;
/
drop table study.account_def purge;
drop table study.customer purge;
drop user user_a cascade;
drop user user_b cascade;
drop user user_c cascade;
drop user user_d cascade;
drop user user_e cascade;
drop trigger sys.trig_set_context;

drop public synonym context_package;
drop package study.context_package;
drop public synonym security_pkg;
drop package study.security_pkg;
drop context test_context;

(完)