ITPub博客

利用VPD实施FGAC(细粒度访问控制)

原创 作者:赵宇 时间:2009-08-18 11:25:17 0 删除 编辑

在我们目前的生产系统中,尤其是业务系统,如ERP系统、银行系统、计费系统等,这些系统都存储着重要的个人账户信息、订单信息、信用卡号等,所以这些信息的保护就尤为重要
Oracle 的细粒度访问控制功能,可以利用Oracle的VPD,虚拟私有数据库功能,利用应用上下文可以把用户的SESSION信息储存到数据库缓存中,这个缓存会从PGA或者SGA中分配,
这样用户只能读取或修改,他可以访问的数据或者可以查看的数据,比如销售人员只能查看自己的订单,不可以修改和查看别人订单。这样,比我们通过基础表做连接查询,性能要提高很多

 

创建一个安全的账号,做上下文包的拥有者,并赋予权限
SQL> grant administer database trigger,create any trigger,create session,resource,create any procedure to sec;

Grant succeeded.


创建上下文:
SQL> conn sec/sec
Connected.
SQL> create context emp_user using current_emp;

Context created.

 

创建可以为上下文附属性的包
SQL> create or replace package current_emp as
  2  procedure set_emp_info;
  3  end;
  4  /

Package created.


SQL> create or replace package body current_emp as
      procedure set_emp_info is
     v_cus_id rscott.customers.cus_id%type;
     v_name rscott.customers.cus_fname%type;
      begin
     select cus_id,cus_fname into v_cus_id,v_name from rscott.customers where
     upper(cus_fname)=sys_context('USERENV','SESSION_USER');
     DBMS_SESSION.SET_CONTEXT('emp_user','id',v_cus_id);
     DBMS_SESSION.SET_CONTEXT('emp_user','name',v_name);
      END set_emp_info;
 END;
/
  2    3    4    5    6    7    8    9   10   11   12 
Package body created.

 

通过SEC账户设置触发器,在每个用户访问应用程序时,都触发上下文环境变量。

SQL> conn sec/sec
Connected.
SQL> create or replace trigger emp_logon
  2  after logon on database
  3  begin
  4  current_emp.set_emp_info;
  5  end;
  6  /
Trigger created.

测试Scott用户是否能获得应用上下文的信息

SQL> select sys_context('emp_user','name') from dual;

SYS_CONTEXT('EMP_USER','NAME')
--------------------------------------------------------------------------------
Scott

SQL> select sys_context('emp_user','id') from dual;

SYS_CONTEXT('EMP_USER','ID')
--------------------------------------------------------------------------------
2


没有应用FGAC前,SCOTT用户可以访问所有订单数据
SQL> conn scott/tiger
Connected.
SQL> select * from rscott.customers;
rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated


    CUS_ID CUS_AGR_NO CUS_FNAME
---------- ---------- ----------------------------------------------------------
         1          1 Adam
         2          2 Scott
         3          3 John
         4          4 Steven
         5          5 Neena
         6          6 De Haan
         7          7 Alexander
        68          8 Igor
        69          9 Dmitry
        70         10 Alex

10 rows selected.


实施FGAC,创建一个函数,可以隐式为SQL语句提供谓词,但是这些SQL只限于select,insert,update,delete

SQL> conn sec/sec
Connected.

"afiedt.buf" 5L, 133C written

  1  CREATE OR REPLACE PACKAGE Oe_security AS
  2  FUNCTION Custnum_sec (object_schema VARCHAR2, object_name VARCHAR2)
  3  RETURN VARCHAR2;
  4* END;
SQL> /

Package created.


"afiedt.buf" 17L, 509C written

  1  CREATE OR REPLACE PACKAGE BODY Oe_security AS
  2  FUNCTION Custnum_sec (object_schema VARCHAR2,object_name VARCHAR2)
  3   RETURN VARCHAR2
  4  IS
  5      D_predicate VARCHAR2 (2000);
  6      BEGIN
          #如果是这个表的拥有者,可以返回所有行
  7       if lower(sys_context('USERENV','SESSION_USER'))=lower(object_schema) then
  8       return null;
  9       end if;
 10       D_predicate := 'cus_id = SYS_CONTEXT(''emp_user'', ''id'')';
 11      if lower(sys_context('USERENV','SESSION_USER'))<>lower(object_schema) then
 12       RETURN D_predicate;
 13      end if;
 14      END Custnum_sec;
 15* END Oe_security;
 16  /

Package body created.


SQL> conn / as sysdba
Connected.

实施FGAC,Fine_grained Access Control需要添加策略
SQL> grant execute on dbms_rls to sec;

Grant succeeded.

SQL> grant exempt access policy to sec;

Grant succeeded.

定义策略,在rscott用户的customers表上实施策略,使用sec用户的函数oe_security,只限于select 语句

"afiedt.buf" 4L, 109C written

  1  begin
  2  dbms_rls.add_policy('rscott','customers','oe_policy','sec','oe_security.custnum_sec','select');
  3* end;
SQL> /

PL/SQL procedure successfully completed.


可以看到,现在scott用户只能看到一行
SQL> conn scott/tiger
Connected.
SQL> select * from rscott.customers;
rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated


    CUS_ID CUS_AGR_NO CUS_FNAME
---------- ---------- ----------------------------------------------------------
         2          2 Scott


 

下一篇: Today I become a OCM
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-25

  • 博文量
    73
  • 访问量
    891446