PL/SQL: Access view of different user in Stored Procedure

Question

Good afternoon,

I have 2 users in an ORACLE database, USER1 and USER2.

USER2 has a view VIEW1, and i want a procedure in USER1 to access VIEW1 through a cursor, like this:

    create or replace procedure PROCEDURE1   is
        CURSOR C_CURSOR
        IS

        select * from USER2.VIEW1;

      BEGIN
        RETURN; 
   end PROCEDURE1;

However, when i try to compile the PROCEDURE1, i get the error:

ORA-01031 - insufficient privelieges

However, i only get this error inside the stored procedure, as i can do the following query just fine from USER1:

select * from USER2.VIEW1;

Can you please tell me what i'm doing wrong?

Thanks a lot!

USER1 must have been granted access indirectly, via a role. To create the procedure it needs the explicit privilege `grant select on user2.view1 to user1;` — Tony Andrews, Aug 16 '17 at 16:01
Thanks a lot! I had tried that before, but i could'nt because USER2.VIEW1 was selecting from a table of a 3rd USER, USER3. I finally got it by granting select on the table from USER3 to USER2 with grant option. — miduarte, Aug 16 '17 at 16:11

score 0 · Answer 1 · answered Mar 09 '18 at 18:38

See Tony Andrews comment on the question.

USER1 must have been granted access indirectly, via a role. To create the procedure it needs the explicit privilege grant select on user2.view1 to user1; – Tony Andrews Aug 16 '17 at 16:01

(no I'm not fishing for reputation; I just want this to drop out of the unanswered queue)

PL/SQL: Access view of different user in Stored Procedure

1 Answers1