Pete Finnigan's Oracle Security Weblog

I saw, this evening a post on the Amis blog by Lucas Jellema titled "Oracle Quiz on SQL and PL/SQL - See Water Burning - paper for ODTUG 2005" and went to take a look as it sounded very interesting. Whilst it is not an Oracle security paper it is about hard to find or undocumented info so it is something I am interested in. The paper is from ODTUG 2005 by Lucas and covers as Lucas puts it:

"quirky questions, tantalizing puzzles and cheeky challenges"

...."that are often useful, yet overlooked and forgoten"

The paper is called "Oracle Quiz on SQL and PL/SQL – See water burning" and covers some good topics including in-line views including how to use them in update statements. There is a discussion on the use of DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE whilst being a tool to tell the CBO that results can be found in a different way but could be used for nefarious reasons!. Lucas talks about pivoting with 8i functionality and then updatable external tables ( I mentioned these here before in a post titled "Amis Blog talks about writable external tables" ). There is an interesting flashback example, a very interesting discussion of mandatory master/detail enforcement. There is a very interesting example of how to create your own aggregates where the Lucas creates a sum_varchar2() aggregate function that returns a comma separated list by using the Oracle data cartridge framework. Having the database count out numbers is a great example of how to get the database to talk in English for numbers such as first, second, third etc and to write one, two, three instead of 1,2,3...Lucas finishes with an example of how to draw pie charts in SQL*Plus.

This is a good paper and whilst not security related apart from the external tables and flashback gives some great ideas. When people push the envelope of a systems functions then security issues can pop out, the advanced rewrite could be used maliciously for instance.