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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14359/viewspace-692618/