Oracle - procedure with AUTHID CURRENT_USER throws ORA-00942: table or view does not exist

Question

Do you know why procedure with AUTHID CURRENT_USER throws ORA-00942: table or view does not exist even after granting all required privileges to invoker.

I created a procedure proc1 under user1 to insert into logs table. Owner of logs table is user1.

Then granted permissions to user2

grant execute on proc1 to user2; grant insert on logs to user2;

When i execute proc1 from user2, it throws ORA-00942: table or view does not exist

Please anyone know reason ?

Answer 1

That's because current user (which is user2) doesn't "see" the table. You did grant privileges, but you should either create a (public) synonym, or - while creating the procedure - specify table's owner.

This is what you have now: my "user1" is scott, while "user2" is mike:

SQL> show user
USER is "SCOTT"
SQL> create table logs (id number, datum date);

Table created.

SQL> create sequence seq_log;

Sequence created.

SQL> create or replace procedure proc1
  2    authid current_user
  3  as
  4  begin
  5    insert into logs (id, datum)
  6      values (seq_log.nextval, sysdate);
  7  end;
  8  /

Procedure created.

SQL> grant execute on proc1 to mike;

Grant succeeded.

SQL> grant select on seq_log to mike;

Grant succeeded.

SQL> grant select, insert on logs to mike;

Grant succeeded.

SQL> exec proc1;

PL/SQL procedure successfully completed.

SQL> select * From logs order by id;

        ID DATUM
---------- --------
         1 02.01.22         --> scott successfully inserted a row (because he's the owner)

SQL> connect mike/lion
Connected.
SQL> exec proc1;
BEGIN proc1; END;             --> failed, because MIKE doesn't see SCOTT's procedure

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'PROC1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> exec scott.proc1;           --> failed, because MIKE doesn't see SCOTT's table
BEGIN scott.proc1; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCOTT.PROC1", line 5
ORA-06512: at line 1


SQL>

---

If SCOTT modifies the procedure (using owner's name while referencing objects):

SQL> connect scott/tiger
Connected.
SQL> create or replace procedure proc1
  2    authid current_user
  3  as
  4  begin
  5    insert into scott.logs (id, datum)           --> SCOTT here ...
  6      values (scott.seq_log.nextval, sysdate);   --> ... and here
  7  end;
  8  /

Procedure created.

SQL> connect mike/lion
Connected.
SQL> exec scott.proc1;        --> now MIKE successfully inserted a row

PL/SQL procedure successfully completed.

SQL> select * from scott.logs order by id;

        ID DATUM
---------- --------
         1 02.01.22
         2 02.01.22

SQL>

---

The question is: why did you use AUTHID CURRENT_USER? With DEFINER (which is default), you wouldn't have such problems.

SQL> connect scott/tiger
Connected.
SQL> create or replace procedure proc1
  2    authid definer                          --> DEFINER
  3  as
  4  begin
  5    insert into logs (id, datum)            --> no SCOTT here ...
  6      values (seq_log.nextval, sysdate);    --> ... nor here
  7  end;
  8  /

Procedure created.

SQL> connect mike/lion
Connected.
SQL> exec scott.proc1             --> no problems any more

PL/SQL procedure successfully completed.

SQL> select * From scott.logs order by id;

        ID DATUM
---------- --------
         1 02.01.22
         2 02.01.22
         3 02.01.22

SQL>