0

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.

1 Answer 1

0

Looks like you'd have to grant select_catalog_role privilege to procedure which is using v_$restore_point.

grant select_catalog_role to procedure B.b_public;
Sign up to request clarification or add additional context in comments.

2 Comments

True, but Code Based Access Controls were not required for A.a_public. Why the difference? If the definer of w_restore_point really had insufficient privileges, then why did it compile (it's static SQL)?
v_$restore_point is not even granted to SELECT_CATALOG_ROLE. From catalog.sql > cdfixed.sql: 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; Or does this have to do with the hard coding of the 'SELECT_CATALOG_ROLE' predicate as identified here?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.