Pete Finnigan's Oracle Security Weblog

I saw an interesting post on the Amis blog a few days ago and made a note to take a look at it. The blog entry is written by Raymond is titled "Recompiling invalid objects". The post talks about how Raymond had seen someone trying to recompile objects the hard way, i.e. many times! as each time the dependencies are not satisfied.

This is a good post as it discusses three ways to recompile objects in an Oracle database, the utlrp.sql script, the DBMS_UTILITY.COMPILE_SCHEMA procedure and Raymond’s own home grown code that works out the dependencies first before calling DBMS_DDL.ALTER_COMPILE.

The comments to this thread are also interesting as a poster points out the home grown solution cannot be used for re-compiling views as DBMS_DDL.ALTER_COMPILE does not work for views. And a second comment poster points out that utlrp.sql is much better nowadays as it calls utlrcmp.sql which works out the dependencies.

So what has this got to do with security? - Firstly I like thorough posts and articles. This short paper covers the subject of re-compiling invalid objects well. Invalid objects are interesting to understand if you are investigating any suspicious activities in your database. If someone has been tampering then objects can become invalid. Quite often it is a good idea to monitor the timestamps in DBA_OBJECTS to understand what objects have changed, been re-compiled or been added. If objects are invalid (and this is not common for your applications) than its good to understand why and also the dependencies involved. reading scripts supplied by Oracle to re-compile objects is educational. The script given by Raymond is also useful.