细粒度访问控制-视图

1、在视图中内嵌decode/case函数实现对列的屏蔽
create or replace view cls_emp
as
select empno,ename,deptno,decode(ename,USER,sal,null) sal
from emp;

使用替代触发器执行DML操作
create or replace trigger cls_emp_sal_update
instead of update on cls_emp
for each row
begin
 if :old.sal is not null then
  update emp set sal=:new.sal where ename=:new.ename;
 end if;
end;
/

2、在视图中植入PL/SQL函数实现对列的屏蔽
create or replace function view_filter(v_user in varchar2)
return number
as
begin
 if (v_user=USER or sys_context('userenv','isdba')='TRUE') then
  return 1;
 else
  return 0;
 end if;
end;
/

create or replace view cls_filter_emp
as
select * from emp
where 1=view_filter(ename);

结果相同，性能较好
create or replace view cls_filter_emp2
as
select * from emp
where 1=decode(ename,user,1,0)
or sys_context('userenv','isdba')='TRUE';

3、在视图中使用应用上下文实现对列的屏蔽
create or replace view cls_edit
as
select * from emp
where ename=sys_context('userenv','session_user')
with check option;

 

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

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