Tuesday May 26, 2015

Oracle 12.1.0.2 - Security Behavior Change with non-SYSDBA Triggers

Oracle Database SecuritySometimes 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

Tuesday Nov 26, 2013

DOAG Conference - Recap

This year's 2013 conference was the best DOAG Conference I have attended so far (and it was my 11th conference).  Actually due to the fact that I've had just one presentation (Working with Oracle Multitenant in the Real World - thanks to everybody coming by in the huge TOKIO room - that was really fun!) and a DOAG TV interview I've had enough time to see some other presentations. I did actually enjoy the Oak Table Stream in room SHANGHAI a lot - so many good stuff, I have really learned a lot.

So thanks to the organizers from the user group (and especially to Christian Trieb and his colleagues from DOAG for pushing this extra stream).

And in case you'd like to download the slide deck:
Working with Oracle Multitenant in the Real World

-Mike

Friday Oct 25, 2013

Nordics OTN ACE Tour 2013 - Recap

The Nordics OTN ACE Tour 2013 with stops in Stockholm, Ballerup/Copenhagen and Oslo is over. A very intense week with plenty of excellent presentations from Lonneke Dikmans, Sten Vesterli, Tim Hall and others. I'm always impressed how much those people know and how good they present. It's such a great learning experience. And there's always some time to talk about weired things apart from the Oracle cosmos. So thanks a lot, folks - it was a pleasure to travel with you.

And many many thanks also to the people from ORCAN, DOUG and OUGN. Everything worked out so well. And thanks for the great gifts. the dinners, everything!!!

Of course a special thanks to all the people who went to my presentations. Hope you've enjoyed it - and sorry for any overtiming ;-) But as Tim said yesterday in the Shuttle Bus back to the airport: "45 min slots don't work out at all" :-) The final slide set about "Different Ways to Upgrade, Migrate and Consolidate into Oracle Database 12c including Oracle Multitenant, New Features and other stuff" can be downloaded via this link.

Hope to see you all again soon - and let me know once you have successfully upgraded to Oracle Database 12c or in case you'd like to become one of our Upgrade Reference Customers.

Cheers - Mike

PS: One thing I couldn't really understand - why is that thing below not labeled simply GRAPE JUICE??? And who's honestly drinking that?



Wednesday Oct 16, 2013

October 2013 PSUs and CPUs - News for 12c

Last night CET the most recent Patch Set Updates (PSU) and Critical Patch Updates (CPU aka SPU) got published on MOS. And there's a significant and remarkable change for Oracle Database 12c onwards. MOS Note: 1571391.1 - Patch Set Update and Critical Patch Update October 2013 Availability Document says:

2.1 Database Security Patching from 12.1.0.1 Onwards

Starting with Oracle Database version 12.1.0.1, Oracle only provides Patch Set Updates (PSU) to meet the Critical Patch Update (CPU) program requirements for security patching. Security Patch Updates (SPU) will no longer be available. Oracle has moved to this simplified model due to popular demand. The PSU is Oracle's preferred proactive patching mechanism since 2009.

For more information, see My Oracle Support Note 1581950.1, Database Security Patching from 12.1.0.1 onwards.

That's a real change. It's not harmful at all as our recommendation for years now is to apply PSUs as they contain not only the security patches but also the important fixes for critical issues. And apply them asap - the day we publish the Security Patch Advisory with some details is the day when external security experts go public as well with their findings.

MOS Note: 756671.1 - Oracle Recommended Patches -- Oracle Database will guide you to the databases patches for your platform. If you miss the PSU for Oracle Database 10.2.0.5 (the Pre-Release Announcement stated that there will be a PSU for 10.2.0.5) my personal understanding is: as Oracle 10.2.0.5 went out of Extended Support in July 2013 there won't be any October PSU released anymore.

And I'll apply the new 12.1.0.1 Oct13 PSU now to my Oracle Database 12c Multitenant environment as well :-)

Don't forget:
MOS Note: 224346.1 - OPatch: Where Can I Find the Latest Version of OPatch?
Find it via Patch Placeholder 6880880

-Mike

Thursday Oct 10, 2013

OOW 2013: Recap - Thanks a lot!!!

OOW 2013 is over for a while now. And yesterday we've received the session results from our talk and the HOL. And we have to say Thank You Very Much! For your time, for the great discussions, for your feedbacks. This is the highest session count we've had in the past years.

Generally my overall feeling this year was that database sessions were very well attended - and the two sessions I wanted to see in my limited spare time were already sold out. And we've really had so many great discussion at our booth 007 - and Joe was an excellent "double" as Daniel Craig was kept up with other stuff ;-)


Joe Errede and Carol Tagliaferri

So thanks again - and we hope to make you the VBox image for the lab available soon for download. In between please feel free to download the slides from the download center - or simply grab the big deck about Upgrade to Oracle Database 12c.

Furthermore let me introduce the new sections with White Papers making it easier to find them and the new Oracle Blogs section with blogs I fequently visit.

And finally here are some impressions from OOW 2013:


Adam Levine from Maroon 5


Matt Flynn and Adam Levine from Maroon 5


At the "Friends of Pythian" event - thanks to Alex Gorbatchov for the invitation :-)


Yes - and OOW happens every year in SF :-)

Cheers - Mike

Tuesday Jul 23, 2013

OOW Shanghai - Slides for Hands-On-Lab (HOL)

Dear all,

thanks for your participation on the Hands-On-Lab for Upgrade and Plugin to Oracle Database 12c today at OOW Shanghai 2013. It was a pleasure for me to work with you and my colleagues from Oracle China. And please forgive us the slowness of the machines. When I did shutdown the VBox images after the session I realized that some noncdb_to_pdb.sql scripts were still recompiling. 


You'll be able to download the slides:

谢谢 - Mike


Thursday Apr 11, 2013

Mile High ... Collaborate 2013 in Denver

Collaborate 2013 is almost over - we are running our final Hands-On-Lab session right now in the Convention Center in Denver, Colorado. For those seeking for the Hands-On-Lab Instructions for our session please be patient until Oracle Database 12c gets released. As soon as this happens we'll offer you the HOL Instructions plus many more information about Oracle Database 12c and the different upgrade and migration paths.

Denver apparently has become my no.3 US city - not only because the mountains are so close by. But the city itself is really worth a visit :-) Please find some impressions below :-) And see you next year at Collaborate 2014 on April 7-11 in Las Vegas.


Yes, again SNOW in Denver ... well ...

-Mike

About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle Corp

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite or remotely with reference customers. Acting as interlink between customers/partners and the Upgrade Development.

Follow me on TWITTER

Contact me via LinkedIn or XING

Search

Archives
« August 2015
SunMonTueWedThuFriSat
      
2
3
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
     
Today
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers