Call: +44 (0)7759 277220 Call

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.

ERP thesis questionnaire

I got an email from Bojan Jovičić's for his magister (masters?) thesis where he would like as many people to visit his anonymous online questionnaire and answer some simple questions about common ERP systems. If you have 5 minutes to spare to help Bojan's education please do so.

The link is

Script to find all privileges assigned to a user/role - users complaint

I received an email from Peter Teoh today that said:

I ran the find_all_privs.sql on my SYS for 10gR2 and got the following limits error:

ERROR (write_op) => -20000
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 158
ORA-06512: at "SYS.DBMS_OUTPUT", line 121
ORA-06512: at line 179

Thank you very much for the script!!!!! Terribly useful......

He is the first person to write to me and complain about a free script he has downloaded from my site. I wanted to investigate.

This issue refers to my free script find_all_privs.sql that is available from my Oracle security tools page or directly as find_all_privs.sql. This is one of a series of free scripts written in PL/SQL that i make available from my site tools page for checking privileges and access rights. This is a useful script as it gives a heirarchical view of privileges assigned, i.e. if scott gets single privilege via role_a via role_b via role_c this is visible in a heirarchical listing.

I ran the same test on 10gR2 against the SYS user and got:

SQL> set serveroutput on size 1000000
SQL> @c:\scripts\find_all_privs
find_all_privs: Release - Production on Sat Jun 23 15:10:34 2007
Copyright (c) 2004 Limited. All rights reserved.

OUTPUT METHOD Screen/File [S]: S


TABLE PRIV => SELECT object => SYS.DBA_HIST_LOG grantable => NO
ERROR (main) => -20000
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 180

For updates please visit


It fails in the same mode. whats wrong with this picture in 10gR2? - well the limits for DBMS_OUTPUT for line size (up to 32767 from 255) and buffer (up to unlimited) were removed. Edit the script and comment out the line:

--set serveroutput on size 1000000

as so then run the same script again with the following change:

Connected to:
Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, OLAP and Data Mining options

SQL> set serveroutput on size unlimited format word_wrapped
SQL> @c:\scripts\find_all_privs

and it runs successfully:


TABLE PRIV => UPDATE object => SYSTEM.DEF$_TEMP$LOB grantable => YES

PL/SQL procedure successfully completed.

For updates please visit

SQL> sho serveroutput

For 10gR1 and earlier where the DBMS_OUTPUT buffer limit still applies you have two options, the first is to replace DBMS_OUTPUT with your own package that doesnt have a buffer limit. I seem to remember that Tom showed how to do this in one of his books. I have done this myself also but only implememnted the functions i needed.

The second easier option is to use the other output format of my scripts to write to a file, enter 'F' for the second option (write the output to a file) and first set up utl_file_dir or a DIRECTORY object first and specifiy a file for the output. This option was added to avoid the buffer overflow ORU-10027 error in the first place as there is no easy workround prior to 10gR2.

I find it hard to believe that people take the time to complain about free scripts without instead taking the effort to look at the problem themselves and fix it. Afterall there is an interface to the script included to avoid the issue in the first place and for 10gR2 a simple edit will make it work.

Data breach concerns running rampant, survey finds

Data breach concerns running rampant, survey finds - By Mark Brunelli

"IT security and compliance practitioners are exhibiting a "disturbing lack of confidence" in the ability of organizations to use sensitive information securely, a new survey finds.

The survey, which was sponsored by Oracle and conducted by the Traverse City, Mich.-based Ponemon Institute, looked at the data privacy and data protection concerns of 1,000 IT security workers and compliance professionals. It found that many see the potential for disastrous data loss and feel that their organizations aren't equipped to deal with the risk."

A New Approach to Database Security

A New Approach to Database Security -

"Startup vendor Sentrigo Monday will unveil a new database security tool that can detect unauthorized changes by hackers or insiders -- without hogging all of your database server cycles.

The product rollout will be the first to come out of Sentrigo, a venture-backed company that received $3.5 million in first-round funding last month. At that time, the company would only give a general description of its technology, without product names or technical specifics."

Database Vault presentation slides available

Today David Bergmeier posted a thread in my Oracle Security Forum titled "Database Vault presentation" with a link to a presentation he has made recently to the Oracle user group in Melbourne Australia. The presentation is titled "A first look at database vault".

This is a great presentation, around 120 slides. It covers the installation, how it works and then shows some real world examples and covers some of the issues. Well worth a look, this is the best practical overview I have seen of Database Vault so far.

Imperva launches a free database security scanner

Imperva have launched a free database security scanner called Scuba. I have downloaded and tested the tool and whilst it has some teething troubles its a great tool not just because its free. Imperva via their ADC (Application Defence Center) will support the tool and add checks to it.

The tool supports Oracle, IBM DB2, MS SQL Server and Sybase. The tool is written in Java and employs a framework approach so adding new checks is done via an upgrade rather than a re-install.

I tested the tool locally against an Oracle database and got some results. There is a lot of Oracle checks, in excess of 100, some of which are old and I felt incorrect - in terms of results and also levels of severity. Also I was not enamoured by the registration process which failed for me! I sent some feedback to Imperva and they will take it on board, they have let me know that they will start a forum to allow feedback to be given more easily.

The tool is free though and its a good tool that wll get better with feedback and development. Get on over to Imperva and download it, its worth a look.

Nice list of security papers

There was a question posted to my Oracle Security Forum a couple of weeks ago titled Security White paper. I made a note to mention here the answer posted by Ivan in "Re: Security Whitepaper" as it was a great list of database security links / papers and books and well worth anyone taking a look through.

Amichai Schulman has started a database security blog

Amichai, who is the CTO of Imperva and also the head of the ADC (Application Defence Center), impervas research arm has started a new database security weblog. He will of course cover Oracle security from time to time as well as general database security issues. The blog is called "Imperva Application Defense Center (ADC)" and is hosted at typepad. The feed has been added to my Oracle blogs aggregator. The blog so far has some good posts. The most recent "What's the color of hacking?" is very good and talks about how javascript can be used to steal browser history. I saw Jeremiah Grossman speak about this at BlackHat in Las Vegas and was impressed at the deviousness of this sort of hack that could be used to tunnel into an organisation via an unspecting employees browser.

Some good posts Amichai.

Valid node checking as a simple free firewall for the database

I was looking for a good reference for valid node checking last week to explain to someone who emailed me how it works and why its a great free Oracle firewall at the TNS level. I remembered I had seen a good paper by Arup Nanda some time ago and went for a look for it. The paper is called "Building a Simple Firewall Using Oracle Net" and is very well written as we have come to expect from Arup and is worth reading again hence I thought i would mention it here.

Another new paper on Oracle password cracking

Alex has posted today about a new tool from THC in a post titled "Oracle password sniffer THC Orakel". As Alex points out there are a few mistakes in the paper. The most glaring is that the SANS paper by Josh Wright and Carlos Cid did not reveal the Oracle password algorithm first. The first release was in 1993 in a paper by Bob Baldwin the original author. The proper algorithm was released on August 11th by a poster to and then a whole swath of C based tools appeared. In terms of sniffing AUTH_SESSION and AUTH_PASSWORD and attacking the password this has also been shown a number of times by Laszlo Toth, David Litchfield and more. The papers by Laszlo are the most interesting as he doesnt stop at release 8i and Java drivers. Also Laszlo and David show how to downgrade a session in their papers. The first person (probably) was Ian Redfern to document the authentication protocol in a long since disapeared paper. There is a copy on Paul Wrights site though.

There are also some great tools with the paper - OrakleCrypt and OrakleSniffert and a Java Oracle client. Download the zip and you get the paper as well. Have a look!