Privilege Is Everything, Sometimes

A common problem surfaced again in an Oracle forum I frequent — a user received a ‘table or view does not exist’ error when creating a procedure against a table he/she didn’t own. A straight select against the table succeeds from SQL*Plus so what is the problem?

It all boils down to privilege in this case. The user has grants on the table through a role, but no direct grants on that table have been made by the owner to the affected user. Since PL/SQL operates a bit differently than SQL*Plus direct grants are a necessity to create procedures and functions against tables and views a user does not own. Let’s look at an example:

SQL> create table location(
  2          yankpurt number,
  3          snozwert varchar2(12),
  4          hunlzap number);

Table created.

SQL>
SQL> begin
  2          for i in 1..100 loop
  3                  insert into location
  4                  values(i, 'SMAZOO!!!!', i);
  5          end loop;
  6
  7          commit;
  8
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> grant all on location to loc_role;

Grant succeeded.

SQL>
SQL> create public synonym location for bong.location;

Synonym created.

SQL>
SQL> connect bing/bong
Connected.
SQL> set echo on
SQL>
SQL> CREATE or replace PROCEDURE TESTPROC
  2  AS
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE('testing');
  5    select count(*) from LOCATION;
  6  END;
  7  /

Warning: Procedure created with compilation errors.

SQL>
SQL> show errors
Errors for PROCEDURE TESTPROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: SQL Statement ignored
5/24     PL/SQL: ORA-00942: table or view does not exist
SQL>
SQL>
SQL> connect bong/bing
Connected.
SQL>
SQL> grant all on location to bing;

Grant succeeded.

SQL>
SQL> connect bing/bong
Connected.
SQL> set echo on serveroutput on size 1000000
SQL>
SQL> CREATE or replace PROCEDURE TESTPROC
  2  AS
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE('testing');
  5    select count(*) from LOCATION;
  6  END;
  7  /

Warning: Procedure created with compilation errors.

SQL>
SQL> show errors
Errors for PROCEDURE TESTPROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PLS-00428: an INTO clause is expected in this SELECT statement
SQL>
SQL>
SQL> CREATE or replace PROCEDURE TESTPROC
  2  AS
  3    loc_ct number;
  4  BEGIN
  5    DBMS_OUTPUT.PUT_LINE('testing');
  6    select count(*) into loc_ct from LOCATION;
  7    dbms_output.put_line(loc_ct);
  8  END;
  9  /

Procedure created.

SQL>
SQL> exec testproc;
testing
100

PL/SQL procedure successfully completed.

SQL>

SQL>

Besides the user-inflicted error (PLS-00428: an INTO clause is expected in this SELECT statement) the main problem was the lack of direct grants against the table in question. Correcting that cleared up the “PL/SQL: ORA-00942: table or view does not exist” error; since PL/SQL cannot recognize grants through roles on objects a user doesn’t own the procedure creation failed even though a select through SQL*Plus succeeded. Without direct grants on an object a user cannot create procedures or functions against that object.

Privilege, sometimes, is everything.