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)时，我应该只看到注册列表中的几条记录.相反，我收到错误:未能执行策略功能.

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!

提前致谢！

亚瑟

推荐答案

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屋！