在我们目前的生产系统中,尤其是业务系统,如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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/175005/viewspace-612568/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/175005/viewspace-612568/