细粒度权限控制 linux,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