Oracle-函数不起作用 [英] Oracle - Function not working

问题描述

Oracle-功能不起作用

Oracle - Function not working

所以我不知道我在做什么错.我已经花了几个小时了，非常感谢您的帮助.

So I got no idea what i'm doing wrong. I've been at it for hours and i'd really appreciate some help.

所以基本上我有2个表，一个叫student，这是一个以student_no为主键的学生列表，另一个叫enrol的表，该表基本上具有该学生所要学习的程序的列表.报名参加.

So basically I have 2 tables, one is called student and it's a list of students with student_no being the primary key, and the other table called enrol which basically has the list of programs that the student is enrolled into.

因此，我编写了一个函数，用于将登录学生的用户名(在本例中为student_no)与学生列表进行比较，并确保登录用户是学生.然后，它将student_no与enrol表进行比较，以查找用户注册的任何程序.因此，实质上，当我SELECT * FROM yaser.enrol(表所有者是yaser)时，我应该只从注册列表中看到几条记录.相反，我得到了错误:failed to execute policy function.

So i've written a function that compares the logged in student's username, which in this case is the student_no, to the list of students and makes sure that the logged in user is a student. Then it compares the student_no with the enrol table to seek out any programs the user is enrolled into. So that in essence when I SELECT * FROM yaser.enrol (the table owner is yaser) I should see only a few records from the enrol list. Instead I get the error: failed to execute policy function.

这是我的职责:

-- Create policy function to be called when 'ENROL' table is accessed
create or replace function f_policy_enrol (schema in varchar2, tab in varchar2)
-- Function will return a string that is used as a WHERE clause                       
return varchar2
as
 v_student_no     varchar2(10);
 is_student       number:=0;
 v_user           varchar2(100);
 out_string       varchar2(400) default '1=2 ';

begin
  -- get session user
  v_user := lower(sys_context('userenv','session_user'));

  -- Is the user a student?
  begin
    select student_no into v_student_no from student where lower(student_no) = v_user;
    is_student:=1;
  exception
    when no_data_found then
    v_student_no := 0;
  end;

  -- If it's a student, then they are only allowed to see their record only.
  if is_student = 1 then
     out_string := out_string||'or student_no = '||v_student_no||' ';
  end if;

  return out_string;
end;
/

这是我引用的政策:

begin
dbms_rls.add_policy('yaser',
                    'enrol',
                    'accesscontrol_enrol',
                    'yaser',
                    'f_policy_enrol',
                    policy_type => dbms_rls.context_sensitive);
end;
/

如前所述..我不确定我要去哪里错了.无论是在策略还是功能上.任何帮助将不胜感激！如果您有任何疑问，只要有空闲时间，我就会很乐意回答！

As said before..I'm not sure where i'm going wrong. Whether in the policy or function. ANY HELP would be greatly appreciated! If you have any questions, i'm happy to answer just as soon as I get some free time at work!

提前谢谢！

Yaser

推荐答案

student_no列的数据类型是什么?列名称表示它是NUMBER.但是，您要在其上调用lower并将其与会话用户进行比较的事实表明它是VARCHAR2.

What is the data type of the student_no column? The column name implies that it is a NUMBER. But the fact that you're calling lower on it and comparing it to the session user implies that it is a VARCHAR2.

假设student_no是VARCHAR2，一个问题是您的谓词缺少该值前后的单引号.例如，如果v_student_no是"jcave"，则您的out_string将是

Assuming student_no is a VARCHAR2, one problem is that your predicate is missing single quotes around the value. If v_student_no is, for example, "jcave", your out_string would be

1=2 or student_no = jcave

由于未引用"jcave"，因此Oracle假定它必须是标识符，因此它将在名为jcave的表中查找列.没有找到这样的列，它将引发错误.如果在字符串两边加上单引号，则会更有运气

Since "jcave" isn't quoted, Oracle assumes that it must be an identifier so it looks for a column in the table named jcave. Finding no such column, it throws an error. You'd have more luck if you put single quotes around the string

out_string := out_string||'or student_no = '''||v_student_no||''' ';

可能还会有其他错误.您是否尝试过手动调用该函数以查看其返回的确切信息?如果在session_user设置为"jcave"时手动调用了该函数，则应该看到缺少单引号的结果.如果复制并粘贴返回值并将其添加到SELECT语句的末尾，则会立即看到错误.如果要避免手动调用该函数，还可以查询v$vpd_policy以查看已添加到特定SQL语句中的特定策略谓词-当您尝试调试VPD问题时，这非常有用.会话状态，说明您无法轻松复制.

There may be additional errors as well. Have you tried calling the function manually to see exactly what it returns? If you had called the function manually when session_user was set to "jcave", you should have seen the result that is lacking the single quotes. If you copy and paste the return value and add that to the end of your SELECT statement, you'd see the error straight away. You can also query v$vpd_policy to see the particular policy predicate(s) that have been added to a particular SQL statement if you want to avoid calling the function manually-- this is very useful when you're trying to debug VPD problems with session state that you can't easily reproduce.

这篇关于Oracle-函数不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！