CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Yaser Sleiman
Yaser Sleiman

Reputation: 95

Oracle - Function not working

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.

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.

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.

Here is my 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;
/

And this is the Policy that I invoke:

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!

Thanks in advance!

Yaser

Upvotes: 1

Views: 2591

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

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.

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

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||''' ';

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.

Upvotes: 2

Related Questions