CODEWITHSUNDEEP
sqloracle-databaseplsql
Yaser Sleiman
Yaser Sleiman

Reputation: 95

Oracle Procedure - Return all rows

So I have several tables and I've created different roles to go with different users so that each can access a portion of the tables.

Right now, whenever I try to SELECT * FROM yaser.enrol; with a coordinator who is meant to see everything, I get the error numeric or value error: character to number conversion error which points to the lines where i'm querying the employee_no to determine the employee role.

Theres 4 overall types of users: student, tutor, lecturer, coordinator.

EDIT ** Added all code.

-- Create policy function to be called when ‘ENROL’ table is accessed under user Yaser.
create or replace function f_policy_enrol (schema in varchar2, tab in varchar2)

return varchar2
as
  v_emp_no            varchar2(10);
  v_student_no        varchar2(10);
  v_tutor_emp_no      varchar2(10);
  v_lecturer_emp_no   varchar2(10); 
  v_coord_emp_no      varchar2(10);
  is_tutor            number:=0;
  is_lecturer         number:=0;
  is_coordinator      number:=0;
  is_student          number:=0;
  is_employee         number:=0;
  v_program_code      varchar2(10);
  v_user              varchar2(100);
  out_string          varchar2(400) default '1=2 ';
 -- The return value will be out_string. '1=2' means 'Nothing to access'. 

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;

  -- Is the user an employee?
  begin
    SELECT emp_no INTO v_emp_no FROM employee WHERE lower(emp_no) = v_user;
    is_employee:=1;
  exception
    when no_data_found then
    v_emp_no := 0;
  end;

  -- Query the employee number to determine role. 
  -- If Tutor.
  SELECT MAX(tutor_emp_no) INTO v_tutor_emp_no FROM tutorial WHERE lower(tutor_emp_no) = v_user;
  -- If Lecturer.
  SELECT MAX(course_coord_emp_no) INTO v_lecturer_emp_no FROM course WHERE lower(course_coord_emp_no) = v_user;
  -- If Coordinator.
  SELECT MAX(prog_coord_emp_no) into v_coord_emp_no FROM program WHERE lower(prog_coord_emp_no) = v_user;

  -- Get role of the employee if the user is an employee.
  if v_emp_no != 0 and v_tutor_emp_no is NOT NULL then
     -- Employee is a Tutor.
     is_tutor := 1;
  elsif v_emp_no != 0 and v_lecturer_emp_no is NOT NULL then
     -- Employee is Lecturer.
     is_lecturer := 1;
  elsif v_emp_no != 0 and v_coord_emp_no is NOT NULL then
     -- Employee is Coordinator.
     is_coordinator := 1;
  end if;

  -- Create the string to be used as the WHERE clause.
  if is_student = 1 then
     -- Students are allowed to see their orders only.
     out_string := out_string||'or student_no = '''||v_student_no||''' ';
  end if;

  if is_tutor = 1 then
     -- Tutors are allowed to see enrolments of students that they tutor.
----     out_string := out_string||'or student_no in (select student_no from tutorial where tutor_emp_no = '||v_tutor_emp_no||') ';
---- NOT WORKING.
     out_string := out_string||'or student_no in (select student_no from tutorial where lower(tutor_emp_no) = v_tutor_emp_no) ';

  end if;

  if is_coordinator = 1 then
     -- The coordinator is allowed to see all records in ENROL (WHERE 1=1 or anything) means all rows.
     out_string := out_string||'or 1=1 ';
  end if;

  return out_string;
end;
/

These are the tables i'm referencing:

CREATE TABLE course
(
    course_code varchar(10),
    course_title varchar(50),
    course_coord_emp_no varchar(10),
    primary key (course_code)
);

And - all employees:

CREATE TABLE employee
(
    emp_no varchar(10),
    name   varchar(100)
);

All other tables are basically the same - VARCHARS

Any help would be good! :) Yaser

Upvotes: 0

Views: 190

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

The question has evolved a lot and much of this answer was about a missing end if that wasn't relevant to your actual code. To go actually go back to your original question, using or 1=1 as a catch-all is OK even though the other branch of the if if comparing strings - it makes no difference at all. If you really did want to compare strings you can do the same thing:

'or ''x''=''x'' '

... or

'or v_user=v_user '

But you can't compare empty strings as you seem to show in a comment. An empty string, '', is the same as null in Oracle , and you can't equate anything to null, even itself. (The previous check would fail if v_user was null, for the same reason). So another possibility would be:

'or null is null '

Note that the comparator here is is, not =.

None of which addresses why you get the VPD error, as all of those are equivalent really - they all always evaluate to true and it doesn't matter which you use. Anything that effectively ends up as or true would work exactly the same; just a shame that Oracle SQL doesn't understand booleans like that. The datatypes being compared in any other clauses are irrelevant to this clause.

You need to see what the function is actually returning in both cases, verify it's what you expect, and verify that it works as a restriction when you query the table the VPD is against directly.

Upvotes: 1

Related Questions