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.
–Mike
Hi,
in ESRI website: http://resources.arcgis.com/en/help/system-requirements/10.2/index.html#//01510000006s000000
they certify 12.1.0.1 with ArcSDE 10.2.x
i think Oracle 12.1.0.2 have fixed a lot of problems and could work smoothly.
i personally upgraded a database from 11.2.0.3 to 12.10.2 and upgraded ArcSDE to 10.2.2 and everything seems fine.
Thanks mike for your post.
Interesting find. That explicit privilege should be granted to the SDE user for an ArcGIS application database.
Details here… http://resources.arcgis.com/en/help/main/10.2/index.html#//002n0000002v000000
Cheers!
Steve
Hi , I have upgraded a database from 10.2.0.5 to 11.2.0.4 and am facing these errors. Could you please suggest how to proceed.
Well, this is a 12.1 behavior change. If you’d upgraded to 11.2.0.4 you shouldn’t see any issues as far as I understand. If you still see the same issues please (a) let me know and (b) test the workaround mentioned above.
Cheers
Mike
It seems you had a typo, I think you meant backported as part of CPU July 2014, not 2013. 12.1.0.2 was released on July 2014.
Hi,
no, this is correct.
The change got deployed with 12.1.0.2 out of the box – and with the CPUs since July 2013 for all previous database versions.
And you are right – 12.1.0.2 got released in July 2014 🙂
Cheers,
Mike