ora 01031 insufficient privileges oracle apex 12cr2

Error: Getting the following error after upgrade Database from Oracle 12c R1 to Oracle 12c R2

ora 01031 insufficient privileges oracle apex 12cr2

Solution

SQL> show parameter sql

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
plsql_ccflags                        string
plsql_code_type                      string      INTERPRETED
plsql_debug                          boolean     FALSE
plsql_optimize_level                 integer     2
plsql_v2_compatibility               boolean     FALSE
plsql_warnings                       string      DISABLE:ALL
sql92_security                       boolean     TRUE
sql_trace                            boolean     FALSE
sqltune_category                     string      DEFAULT
SQL>

younus01
689456

SQL> alter system set sql92_security=FALSE scope=spfile;

System altered.

SQL>

If the SQL92_SECURITY initialization parameter is set to TRUE and the DELETE operation references table columns, such as the columns in a where_clause, then you must also have the SELECT object privilege on the object from which you want to delete rows.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/DELETE.html#GUID-156845A5-B626-412B-9F95-8869B988ABD7

So if you only grant delete, a plain delete with no where clause will work. But you need select to use a where clause (assuming you've set SQL92_SECURITY; true is the default).