Oracle 12.1.0.2 – Security Behavior Change with non-SYSDBA Triggers

Sometimes things get revealed at unexpected occasions. This one happened during a recent customer upgrade to Oracle Database 12c with a 3rd party geospatioanl application installed (ESRI).

At the very end of the upgrade the customer saw many ORA-1031 (insufficient privileges) errors and it seemed to be that nothing was working correctly anymore.

This happened during the run of catupend.sql. The following code path in  catupend.sql causes the error.

cursor ddl_triggers
is
select o.object_id from dba_triggers t, dba_objects o
where t.owner = o.owner and t.trigger_name = o.object_name and o.object_type = 'TRIGGER'
and (t.triggering_event like '%ALTER%' or t.triggering_event like '%DDL%');
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
SDE.DB_EV_ALTER_ST_METADATA
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_UTILITY", line 1279
ORA-06512: at line 20

Apparently there’s no access to an application trigger anymore – which got deployed as a system trigger (for more information about ESRI’s system trigger please click this link). Even though this is strange it doesn’t seem like a big issue. But in fact it is as this procedure failed and caused other stuff not getting validated correctly. So subsequent actions (for instance the run of utlu121s.sql, the post upgrade script) failed with ORA-1031 as well pointing to DBMS_UTILITY.

The customer [Danke Andy!!!] itself found the workaround by pattern matching similar issues in MOS and trying some grants – the 3rd one did the trick:

• GRANT ADMINISTER DATABASE TRIGGER to SDE;

So it was obvious that something in the security architecture in Oracle Database 12.1.0.2 had been changed – and somebody forgot to document it. Later on I’ve learned that this change got introduced with the July 2013 PSU/CPU as well. I don’t blame the customer for not applying PSUs since almost two years – I knew that upfront and we are implementing a 2-PSUs-per-year strategy now with the upgrade to Oracle Database 12c.

The system trigger ESRI had created couldn’t be validated anymore under the context of the SDE (ESRI’s application) user. Therefore it failed but caused other actions to fail as well.

This behavior change is related to “SYSDBA privilege should not be available in non-SYS owned DR procedure / trigger execution” which is first fixed into 12.1.0.2, and then backported as part of CPU July-2013.

When SYS executes a non-SYS owned DR procedure or a Trigger, the SYS privileges would not available during the procedure/trigger execution. The procedure/trigger owner privileges prevail.