Call: +44 (0)7759 277220 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "Tom writes about anonymous postings"] [Next entry: "Richard talks about diagnostics support pack and applications collection tool (ACT)"]

Amis blog has a good paper on SQL quirks



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.