Pete Finnigan's Oracle Security Weblog

I saw a very interesting post on the Amis blog this evening by Peter Kok titled "Updateable External Tables". This post starts by setting out the limitations of external tables (that is that they are read only) and then goes on to talk about how they can be made updatable for inserts, updates and deletes. They then suggest that this is an example of how to get around this limitation and that this technique should not be used in a production situation. Also Peter tells us not to confuse updatable external tables with writable external tables that have become possible in 10g. Howard Rogers talks about this in his paper "Writeable External Tables". This 10g function works via the data pump API.

Peter goes on to give some great examples of how to implement updatable external tables via a VIEW and an instead of trigger and some PL/SQL code. The functionality is in the PL/SQL package and this can be downloaded in a zip file.

This article provides some interesting examples and ideas. Well worth reading. It can also be surmised that these techniques would work for altering config files or any other Oracle file that could be accessed via an external table. One more reason to audit directory objects, existing external tables and anyone with system privileges to create such tables.