If user A creates a view and grants READ on it to PUBLIC.
SQL>create view A.a_public as select 3 id from dual;
View created.
SQL>select * from A.a_public;
ID
----------
3
SQL>grant read on A.a_public to public;
Grant succeeded.
Then user B can create a stored procedure selecting from A's view and successfully execute that stored procedure.
SQL>create or replace procedure B.b_public
2 as
3 num NUMBER;
4 begin
5 select id into num from A.a_public;
6 dbms_output.put_line('~' || num || '~');
7 end;
8 /
Procedure created.
SQL>show errors
No errors.
SQL>set serveroutput on
SQL>exec B.b_public();
~3~
PL/SQL procedure successfully completed.
SQL>
READ of SYS.V_$RESTORE_POINT has been granted to PUBLIC.
For the stored procedure below, why does the compilation succeed, but the execution fail?
SQL>select count(*) from dba_tab_privs where table_name = 'V_$RESTORE_POINT' and grantor = 'SYS' and grantee = 'PUBLIC' and privilege = 'READ';
COUNT(*)
----------
1
SQL>create or replace procedure w_restore_point
2 as
3 num_scn NUMBER;
4 begin
5 select scn into num_scn from
6 SYS.v_$restore_point
7 where rownum = 1;
8 dbms_output.put_line(num_scn);
9 end;
10 /
Procedure created.
SQL>show errors
No errors.
SQL>set serveroutput on
SQL>exec w_restore_point();
BEGIN w_restore_point(); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCHEMA_NAME.W_RESTORE_POINT", line 5
ORA-06512: at line 1
SQL>
Using Oracle 19c EE.
EDIT
4.8.1.8.1 Roles Used in Named Blocks with Definer's Rights
All roles are disabled in any named PL/SQL block that executes with definer's rights.
I am assuming this is really supposed to read "All roles except PUBLIC are disabled...". Otherwise, definer rights stored procedures that use dbms_output, for example, would not execute successfully if the privilege domain of the definer (with regard to dbms_output) was only EXECUTE on dbms_output being granted to PUBLIC.
EDIT 2
I now realize the issue may be isolated to v_$restore_point.
Even though catalog.sql > cdfixed.sql are analogous -
create or replace view v_$restore_point as
select * from v$restore_point;
create or replace public synonym v$restore_point
for v_$restore_point;
grant read on v_$restore_point to public;
create or replace view v_$session_connect_info as
select * from v$session_connect_info;
create or replace public synonym v$session_connect_info
for v_$session_connect_info;
grant read on v_$session_connect_info to public;
the issue does not occur for SYS.v_$session_connect_info.
SQL>create or replace procedure w_session_connect_info
2 as
3 num_sid NUMBER;
4 begin
5 select sid into num_sid from
6 SYS.v_$session_connect_info
7 where rownum = 1;
8 dbms_output.put_line(num_sid);
9 end;
10 /
Procedure created.
SQL>show errors
No errors.
SQL>set serveroutput on
SQL>exec w_session_connect_info();
5
PL/SQL procedure successfully completed.
SQL>
This wordpress article discusses that it simply may be sufficient to have a role named SELECT_CATALOG_ROLE even if there are no privileges granted to that role due to the hardcoding of a literal 'SELECT_CATALOG_ROLE' predicate.