ITPub博客

首页 > Linux操作系统 > Linux操作系统 > FGAC(细粒度访问控制)/VPD

FGAC(细粒度访问控制)/VPD

原创 Linux操作系统 作者:landf 时间:2011-04-15 17:02:53 0 删除 编辑
FGAC/VPD可以从安全方面限制用户在行级别的访问权限,对于安全性考虑的应用有很大帮助。
另外,在一些特别特殊的情况下,可以通过它改写用户SQL的谓词(where condition)。
这里step by step做一个例子,以供以后参考。
SQL> show user
USER is "HR"
SQL> create table testfgac as select * from dba_tables;
Table created.
1.建立一个context用来保存希望保存的属性。
SQL>grant administer database trigger,create any trigger,create session,resource,create any procedure to hr
这里用USERNAME来代表当前用户是否是定义的“DB_OWNER”
SQL> create context syscontext using context_package;
Context created.
SQL> create or replace procedure context_package
( username in varchar2)
as
begin
    dbms_session.set_context (
            'SYSCONTEXT',
            'USERNAME',
            username
    );
end;
Procedure created.
2.在sys用户下建立一个login trigger,如果当前用户是'hr',那么设立它的username是hr.
SQL> create or replace trigger context_trig
after logon on database
declare
   username varchar2(50);
begin
   select decode(sys_context('USERENV','SESSION_USER'),'HR',
   'DB_OWNER','NOT_DB_OWNER')
   into username from dual;
   context_package(username);
exception
   when NO_DATA_FOUND then
null;
   when OTHERS then
        raise;
end;
Trigger created.
3.查看login trigger生效没有
SQL>  show user
USER is "SYS"
SQL>  select sys_context('SYSCONTEXT','username') myrole from dual;
MYROLE
--------------------
NOT_DB_OWNER
SQL> conn hr/hr
Connected.
SQL>  select sys_context('SYSCONTEXT','username') myrole from dual;
MYROLE
--------------------
DB_OWNER
4.最重要的一步,建立一个function用来作为返回你想修改的谓词。本例a是对hr用户返回空谓词,对其它用户返回'LAST_ANALYZED<=sysdate-1';本例b是对用户返回owner='session_user'串。

a,
SQL>  create or replace function return_secure
(my_owner in varchar2,
 my_obj in varchar2)
return varchar2
as
   ret varchar2(2000);
begin
select decode(sys_context('SYSCONTEXT','username'),'DB_OWNER',null,'LAST_ANALYZED<=sysdate-1') into ret from dual;
   return ret;
end;
Function created.
b,
SQL>  create or replace function return_sec
(my_owner in varchar2,
 my_obj in varchar2)
return varchar2
as
   ret varchar2(2000);
begin
   select 'owner='||chr(39)||sys_context('USERENV','SESSION_USER')||chr(39) into ret from dual;
   return ret;
end;
Function created.

5.在sys用户下,加上policy,对dml语句和select都生效
SQL>  begin
   dbms_rls.add_policy (
OBJECT_SCHEMA => 'HR',
object_name => 'TESTFGAC',
policy_name => 'my_p',
policy_function => 'RETURN_SECURE',
statement_types => 'INSERT, UPDATE, DELETE, SELECT',
update_check    => TRUE
);
end;
/
PL/SQL procedure successfully completed.
SQL>  begin
   dbms_rls.drop_policy (
OBJECT_SCHEMA => 'HR',
object_name => 'TESTFGAC',
policy_name => 'my_p');
end;
/
 begin
   dbms_rls.add_policy (
OBJECT_SCHEMA => 'HR',
object_name => 'TESTFGAC',
policy_name => 'my_p',
policy_function => 'RETURN_SEC',
statement_types => 'INSERT, UPDATE, DELETE, SELECT',
update_check    => TRUE
);
end;
/
6.测试
SQL> conn hr/hr
Connected.
SQL> select sys_context('SYSCONTEXT','username') username from dual;
USERNAME
------------------------
DB_OWNER
SQL> select count(*) from HR.TESTFGAC;
  COUNT(*)
----------
      1043
SQL> conn scott/tiger
Connected.
SQL> select count(*) from hr.TESTFGAC;
  COUNT(*)
----------
       934

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14359/viewspace-692618/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-14

  • 博文量
    68
  • 访问量
    495328