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 ?
CodePudding user response:
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>