VPD相关问题

snowfox326

问题也解决了，还是策略函数的问题
create or replace function f_emp_policy
(schema in varchar2, tab in varchar2)
return varchar2 as

v_deptno    scott.emp.deptno%type;
is_emp      number:=0;
v_job       scott.emp.job%type;
v_user          varchar2(100);
out_string      varchar2(400) default '1=2 ';

begin
  v_user := sys_context('userenv','session_user');

  begin
     SELECT deptno,job into v_deptno,v_job FROM SCOTT.EMP --这里，换成test1成功。
     WHERE ename = v_user;
     is_emp:= 1;
  exception
    when no_data_found then
     is_emp:= 0;
  end;

  if is_emp != 0  and v_job='PRESIDENT' then
     out_string:= out_string||'or 1=1';

  elsif is_emp !=0 and v_user='SCOTT' then
     out_string:= out_string||'or 1=1';

  elsif is_emp !=0 and v_job='MANAGER' then
     out_string:= out_string||'or deptno='||v_deptno;

  end if;     

  return out_string;
end;

这个函数正确

Yong Huang

原帖由 snowfox326 于 2009-1-23 22:59 发表
问题也解决了，还是策略函数的问题
create or replace function f_emp_policy
...
begin
  v_user := sys_context('userenv','session_user');

  begin
     SELECT deptno,job into v_deptno,v_job FROM SCOTT.EMP --这里，换成test1成功。
     WHERE ename = v_user;
     is_emp:= 1;
...
这个函数正确

The only change you made is
changing
   v_user := lower(sys_context('userenv','session_user'));
to
   v_user := sys_context('userenv','session_user');
and changing
      WHERE lower(ename) = v_user;
to
      WHERE ename = v_user;

The only explanation I can think of is that this line

elsif is_emp !=0 and v_user='SCOTT' then

still assumes v_user is uppercase. I bet if you change 'SCOTT' to 'scott' and leave the two lower() functions above alone, it still works. But of course unnecessarily forcing case-insensitive check is a waste of CPU time.

Yong Huang

snowfox326

you are right ! two way are correct