SQL92_SECURITY and ORA-01031

SQL92_SECURITY has changed its default value to TRUE starting from 12.2.0.1: Upgrade Guide.
That might lead to ORA-01031: insufficient privileges errors being thrown in runtime as the following script demonstrates.

SQL> grant create session, create table, unlimited tablespace to tc_data identified by tc_data;

Grant succeeded.

SQL>
SQL> create table tc_data.t
  2  as
  3  select *
  4    from dual;

Table created.

SQL>
SQL> grant create session, create procedure to tc_app identified by tc_app;

Grant succeeded.

SQL>
SQL> grant delete on tc_data.t to tc_app;

Grant succeeded.

SQL>
SQL> create or replace procedure tc_app.p
  2  is
  3  begin
  4    delete tc_data.t
  5     where dummy = 'X';
  6  end;
  7  /

Procedure created.

SQL>
SQL> exec tc_app.p
BEGIN tc_app.p; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TC_APP.P", line 4
ORA-06512: at line 1

Although the procedure is valid, it throws an ORA-01031 error as soon as the statement refers to any table columns including pseudo-columns, such as ROWID (I have tested it only for ROWID).

I have even seen a case similar to the one below, when the code throws an error depending on its input parameters:

SQL> create or replace procedure tc_app.p_collection(p_tbl sys.odcivarchar2list)
  2  is
  3  begin
  4    forall i in 1..p_tbl.count
  5      delete tc_data.t
  6       where dummy = p_tbl(i);
  7  end;
  8  /

Procedure created.

SQL>
SQL> exec tc_app.p_collection(sys.odcivarchar2list())

PL/SQL procedure successfully completed.

SQL> exec tc_app.p_collection(sys.odcivarchar2list('y'))
BEGIN tc_app.p_collection(sys.odcivarchar2list('y')); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TC_APP.P_COLLECTION", line 4
ORA-06512: at line 1

I can imagine that Oracle has not implemented the behavior when the code does not compile if it is known that there is a missing privilege due to SQL92_SECURITY=TRUE - it is just an instance parameter and it can be changed back and forth. Thus, it would make the status of the objects misleading. For instance, a valid PL/SQL unit with SQL92_SECURITY=FALSE, should either become invalid or throw a runtime ORA-01031 error when SQL92_SECURITY=TRUE.
However, it might come in handy to have a PL/SQL warning at least to identify possible missing privileges.
It can also be a good idea to change the scope of the SQL92_SECURITY parameter and make it one of the PL/SQL compiler settings of the stored objects shown in DBA_PLSQL_OBJECT_SETTINGS.

ORA-01031 select V$RESTORE_POINT in PL/SQL

It is a well known fact that V$RESTORE_POINT requires special handling, namely the SELECT_CATALOG_ROLE should be granted to a low-privileged user trying to access this view.
I had several PL/SQL units working with V$RESTORE_POINT in a 12.1 database. Those units were owned by a user that has SELECT_CATALOG_ROLE.
Once I upgraded the database to 12.2, those units stopped working and I started getting an infamous ORA-1031 error.
This blog post is about how I fixed that issue for definer rights program units.

Here is a simple test case demonstrating the initial ORA-1031 error:

SYS@CDB$ROOT> create restore point rp_test;

Restore point created.

SYS@CDB$ROOT> alter session set container=pdb;

Session altered.

SYS@PDB> grant connect, create procedure to tc identified by tc;

Grant succeeded.

SYS@PDB> grant read on v_$restore_point to tc;

Grant succeeded.

SYS@PDB> conn tc/tc@localhost/pdb
Connected.
TC@PDB>
TC@PDB> create or replace procedure p_test
  2  is
  3  begin
  4    for test_rec in (
  5      select *
  6        from v$restore_point)
  7    loop
  8      dbms_output.put_line(test_rec.name);
  9    end loop;
 10  end;
 11  /

Procedure created.

TC@PDB>
TC@PDB> set serverout on
TC@PDB>
TC@PDB> exec p_test
BEGIN p_test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TC.P_TEST", line 4
ORA-06512: at line 1

Despite the fact that the user TC does have the READ privilege on V_$RESTORE_POINT, it still is not able to access it.
Till 12.2 it was enough to grant SELECT_CATALOG_ROLE to the owner of a program unit to avoid the error:

SYS@PDB> grant select_catalog_role to tc;

Grant succeeded.

SYS@PDB> conn tc/tc@localhost/pdb
Connected.
TC@PDB>
TC@PDB> set serverout on
TC@PDB>
TC@PDB> exec p_test
RP_TEST

PL/SQL procedure successfully completed.

It is not the case anymore in 12.2 and subsequent versions which I tested: 18c and 19c.
The output from 19c is below:

SYS@PDB> grant select_catalog_role to tc;

Grant succeeded.

SYS@PDB> conn tc/tc@localhost/pdb
Connected.
SYS@PDB>
TC@PDB> set serverout on
TC@PDB>
TC@PDB> exec p_test
BEGIN p_test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TC.P_TEST", line 5
ORA-06512: at "TC.P_TEST", line 5
ORA-06512: at line 1

BTW, the line 'ORA-06512: at "TC.P_TEST", line 5' is reported twice, and 19c shows a slightly different errorstack than 12.1.
The following solution works in 12.2 on:

SYS@PDB> grant select_catalog_role to procedure tc.p_test;

Grant succeeded.

SYS@PDB>
SYS@PDB> conn tc/tc@localhost/pdb
Connected.
TC@PDB>
TC@PDB> set serverout on
TC@PDB>
TC@PDB> exec p_test
RP_TEST

PL/SQL procedure successfully completed.

The need for having the SELECT_CATALOG_ROLE granted to the user in 12.1 does not make much sense as roles do not work in named PL/SQL definer rights program units. I am not talking about roles granted to PL/SQL units here.
Therefore, the "new" behavior requiring the role to be granted to PL/SQL units appears to be more proper and logical.

While working on this issue, I was tinkering with gdb a little bit in an attempt to find an explanation to that SELECT_CATALOG_ROLE requirement - that role is not coming from V$-views as it was said in the blogpost which I referred before.
It turns out that role is used in Oracle code:

(gdb) disassemble kccxrsp
Dump of assembler code for function kccxrsp:
   0x000000000a225740 <+0>:     xchg   %ax,%ax
   0x000000000a225742 <+2>:     push   %rbp
   0x000000000a225743 <+3>:     mov    %rsp,%rbp
   0x000000000a225746 <+6>:     sub    $0x60,%rsp
   0x000000000a22574a <+10>:    mov    %rbx,-0x58(%rbp)
   0x000000000a22574e <+14>:    mov    %rdx,%rbx
..skip..
   0x000000000a2257e5 <+165>:   mov    $0xdda7b48,%edi
   0x000000000a2257ea <+170>:   mov    $0x13,%esi
   0x000000000a2257ef <+175>:   callq  0x859bd90 <kzsrol>
..skip..
---Type <return> to continue, or q <return> to quit---q
Quit
(gdb) x/s 0xdda7b48
0xdda7b48:      "SELECT_CATALOG_ROLE"

GV$RESTORE_POINT is based on x$kccrsp and x$kccnrs. The former is seems to be accessed through the kccxrsp function.
kccxrsp calls kzsrol to perform extra security checks and passes SELECT_CATALOG_ROLE to it.

TL;DR: V$-views are really special views (i.e. no read consistency) and V$RESTORE_POINT has its own little peculiarity among them.
Not only does it require to have the SELECT_CATALOG_ROLE granted to a non-administrative user but also the definer rights PL/SQL unit owned by such a user should have that role granted as well.