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.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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.