细粒度访问控制-VPD

1、创建测试环境
conn sys/password as sysdba
create user HJP identified by password default tablespace users temporary tablespace temp;
create user JDS identified by password default tablespace users temporary tablespace temp;
create user BBK identified by password default tablespace users temporary tablespace temp;
create user ZYM identified by password default tablespace users temporary tablespace temp;
grant connect,resource to HJP;
grant connect,resource to JDS;
grant connect,resource to BBK;
grant connect,resource to ZYM;

create user huang identified by password default tablespace users temporary tablespace temp;
grant connect to huang;
grant resource to huang;
grant execute on dbms_rls to huang;
--grant create any trigger to huang;
--grant ADMINISTER database trigger to huang;
--grant create session to huang with admin option;
commit;

conn scott/password
insert into emp(empno,ename,sal,deptno) values(8000,'HJP',5000,20);
insert into emp(empno,ename,sal,deptno) values(8001,'JDS',5000,30);
insert into emp(empno,ename,sal,deptno) values(8002,'BBK',5000,40);
insert into emp(empno,ename,sal,deptno) values(8003,'ZYM',5000,10);
commit;

create table people as select ename username,job,sal salary,deptno from emp;
grant select,update,insert,delete on scott.people to huang;
grant select,update,insert,delete on scott.people to HJP;
grant select,update,insert,delete on scott.people to JDS;
grant select,update,insert,delete on scott.people to BBK;
grant select,update,insert,delete on scott.people to ZYM;
commit;

2、创建数据表和应用上下文
conn huang/password
create table lookup_user as select username,deptno from scott.people;
create context people_ctx using huang.people_ctx_mgr;
--drop context people_ctx;

*************************************************************************************
create or replace package people_ctx_mgr
as
 procedure set_deptno;
 procedure clear_deptno;
end;
/
*************************************************************************************
create or replace package body people_ctx_mgr
as
procedure set_deptno
as
 l_deptno number;
begin
 select deptno into l_deptno from lookup_user
  where username=sys_context('userenv','session_user');
 dbms_session.set_context
  (namespace => 'people_ctx',attribute => 'deptno',value => l_deptno);
end set_deptno;
procedure clear_deptno
as
begin
 dbms_session.clear_context
  (namespace => 'people_ctx',attribute => 'deptno');
end clear_deptno;
end people_ctx_mgr;
/
*************************************************************************************
--drop package people_ctx_mgr;

3、创建登录触发器
conn sys/password as sysdba
********************************************
create or replace trigger set_user_deptno
 after logon on database
begin
 huang.people_ctx_mgr.set_deptno;
end;
/
********************************************
--drop trigger set_user_deptno;

4、测试应用上下文
conn scott/password 
select sys_context('people_ctx','deptno') deptno from dual; 

5、创建策略函数dept_only
conn huang/password
******************************************************************
create or replace function dept_only
 (p_schema in varchar2 default null,
  p_object in varchar2 default null)
  return varchar2
as
begin
 return 'deptno = sys_context(''people_ctx'',''deptno'')';
 --return 'deptno = '||sys_context('people_ctx','deptno');
 --返回值，需要重新分析SQL语句
exception
 when others then
  return '1=0';
end;
/
******************************************************************
--drop function dept_only;

6、查询策略函数
select dept_only from dual;

7、创建插入、更新策略people_iu
conn huang/password
*******************************************************
begin
 dbms_rls.add_policy
  (object_schema => 'SCOTT',
   object_name => 'people',
   policy_name => 'people_iu',
   function_schema => 'huang',
   policy_function => 'dept_only',
   statement_types => 'insert,update',
   update_check => TRUE);
end;
/
*******************************************************

8、删除策略people_iu
/*********************************************
begin
 dbms_rls.drop_policy
  (object_schema => 'SCOTT',
   object_name => 'people',
   policy_name => 'people_iu');
end;
/
*********************************************/

9、创建策略函数user_only
conn huang/password
***********************************************************************
create or replace function user_only
 (p_schema in varchar2 default null,
  p_object in varchar2 default null)
  return varchar2
as
begin
 return 'username = sys_context(''userenv'',''session_user'')';
exception
 when others then
  return '1=0';
end;
/
***********************************************************************
--drop function user_only;

10、创建删除策略people_del
conn huang/password
*************************************************
begin
 dbms_rls.add_policy
  (object_schema => 'SCOTT',
   object_name => 'people',
   policy_name => 'people_del',
   function_schema => 'huang',
   policy_function => 'user_only',
   statement_types => 'delete');
end;
/
*************************************************

11、删除策略people_del
/**********************************************
begin
 dbms_rls.drop_policy
  (object_schema => 'SCOTT',
   object_name => 'people',
   policy_name => 'people_del');
end;
/
**********************************************/

12、查询策略
select * from dba_policies;
select * from v$vpd_policy(v$sql);

13、RLS策略的免除访问，对数据库的备份和恢复非常必要
grant exempt access policy to system;
--revoke exempt access policy from system;

14、审核免除访问策略
audit exempt access policy by access;
******************************************************************************************
begin
 for i in (select * from dba_audit_trail) loop
  dbms_output.put_line('---------------------------------------');
  dbms_output.put_line('Who: '|| i.username);
  dbms_output.put_line('What: '||i.action_name||' on '||i.owner||'.'||i.obj_name);
  dbms_output.put_line('When: '||to_char(i.timestamp,'yyyy-mm-dd HH24:MI'));
  dbms_output.put_line('How: '||i.sql_text);
  dbms_output.put_line('Using: '||i.priv_used);
 end loop;
end;
/
******************************************************************************************

 

列敏感的VPD

创建列敏感的策略(当salary列被查询时激活VPD策略，仅返回符合salary列的行)
**************************************************
begin
 dbms_rls.add_policy
  (object_schema => 'SCOTT',
   object_name => 'people',
   policy_name => 'people_sal',
   function_schema => 'huang',
   policy_function => 'user_only',
   statement_types => 'select',
   sec_relevant_cols => 'SALARY');
end;
/
**************************************************

使用sec_relevant_cols_opt返回所有的行，不符合salary列的返回null，仅适用于选择语句
**************************************************************
begin
 dbms_rls.add_policy
  (object_schema => 'SCOTT',
   object_name => 'people',
   policy_name => 'people_sal',
   function_schema => 'huang',
   policy_function => 'user_only',
   statement_types => 'select',
   sec_relevant_cols => 'SALARY',
   sec_relevant_cols_opt => dbms_rls.all_rows);
end;
/
**************************************************************

 

1、建立应用上下文
conn sys/password as sysdba;
create or replace context empenv using scott.set_deptno;

2、设置应用上下文属性
create or replace procedure set_deptno
is
 id number;
begin
 if sys_context('userenv','session_user')='HJP' then
  dbms_session.set_context('empenv','deptno',10);
 end if;
 if sys_context('userenv','session_user')='JDS' then
  dbms_session.set_context('empenv','deptno',20);
 end if;
end;
/

3、建立登陆触发器
conn sys/password as sysdba;
create or replace trigger login_trig after logon on database call scott.set_deptno
/

4、建立策略函数
create or replace function emp_policy(p1 varchar2,p2 varchar2) return varchar2
is
 d_predicate varchar2(2000);
begin
 if user not in ('SCOTT') then
  d_predicate:='deptno=sys_context(''empenv'',''deptno'')';
  return d_predicate;
 end if;
 return '1=1';
end;
/

5、增加策略
conn sys/password as sysdba;
exec dbms_rls.add_policy('scott','emp','emp_policy','scott','emp_policy','select,update,delete')

exec dbms_rls.drop_policy('scott','emp','emp_policy') --删除策略

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

转载于:http://blog.itpub.net/17012874/viewspace-693804/