Call: +44 (0)1904 557620 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.

Howard Rogers new paper on secure application roles

Howard Rogers has just released a new paper discussing the implementation and use of secure application roles in an Oracle database. This is written in the now usual question and answer session that Howard has used recently very successfully.

The paper covers some interesting points, the main one being that secure application roles can be used to protect access to data or rather privileges by not enabling the roles if an incorrect application is used. The point Howard makes is that unlike VPD this can be done on standard edition installations.

This method also gets around the issue of password protected roles being possibly bypassed. I talked about this some time ago in a short paper.

Howard covers a couple of good examples, the first gets a secure application role up and running and then he modifies it to be a bit more workable by using application contexts and logon triggers to set a token. The point being made is that the implementation can be changed without changing the client application code.

Excellent paper!

Interesting question about Sarbanes-Oxley on Oracle 7.3.3

I found a good question posted on today by Daniel Morgan. He has just inherited a 7.3.3 database on Solaris (Not current version) and wanted to know how to audit connections and actions as SYS, SYSTEM and INTERNAL to meet requirements of Sarbanes-Oxley.

A number of people have posted so far to the thread. This question is interesting as the version of Oracle is before Fine Grained Audit and Row Level security, system triggers, auditing of SYS operations and any other useful feature that could be used to audit these users actions.

My contribution to the thread was this:

"Hi Daniel,

I can think of one way, which is not particularly practical. You could
sniff the network traffic to the server and extract the SQL, DDL and
connections to and from it. To do so you would need to sit directly in
front of the server hosting the database. You would need to extract the
time, user and the SQL from the packets and ideally store them in
another database for querying. You could use a packet sniffer or
possibly SQL*Net trace on the server.

Don't forget about SQL*net logs and the listener log to get connections.

There are commercial products available that already do this. I don't
know the licence costs of them. There is Chakra from OR Solutions,
Guardium SQL Guard from Guardium, Entregra for Oracle from Lumigent,
Zeus Extensible Traffic Manager from Zeus technology and also Integrigy
and Application security Inc are both about to release IDS / firewall
type products which slightly less fill the bill. There are links to all
of these on my tools page in the commercial section - see

A possible other way would be to poll the SGA and extract the SQL, but
this method could "lose" SQL if you do not poll fast enough and also
would hurt the database. It is possible to do the same by reading the
SGA directly with C programs. Writing a program to just extract SQL
would not be that difficult. There are commercial tuning products that
do this (access the SGA directly) but whether you can stream the SQL out
of them or not, i am not sure. There are some papers on direct SGA
access on my site at /other.htm - I also
talked about the same in my Oracle security web log recently -
see /weblog/entries/index.htm

hope this helps a bit,

kind regards


Can I connect to the database as the user PUBLIC?

I was playing around with the PUBLIC user the other day, or is it a role? or what is it? - PUBLIC is actually defined as a user group. This is a great idea that Oracle introduced to group users together into a group. But they only ever created the PUBLIC group. I think it would be useful to have the possibility to create other user groups. As it stands every user is a member of the user group PUBLIC, presumably if the feature was extended it would have been possible to create multiple groups and decide which users were members of the group.

So let's play. Is PUBLIC a role or a user according to the SYS.USER$ table:

SQL> select user#,name,type#
2 from sys.user$;

---------- ------------------------------ ----------
0 SYS 1
4 DBA 0
57 QS_CB 1
58 QS_CS 1
59 SCOTT 1
130 PUP 1

This shows that PUBLIC has a type# of "0" so its a ROLE. Or is it.
more.. white papers section updated for Roby Sherman papers

A few people have emailed me over the last couple of months to let me know that the links on my white papers page to Roby Sherman's excellent Oracle security based white papers are broken. I was aware of this some time ago and tried to contact Roby to find out what was happening but got no response. The links then worked again for a while but now they seem to be broken again. This seemed to be a pity as the papers are very useful.

Today I have found alternate links to Roby's papers:

  • Implementing Data Encryption

  • Internet Security With Oracle Row-Level Security

  • Implementing Data-Level Monitoring With Oracle Fine-Grained Auditing

  • Implementing the Database Resource Manager

  • A Major Oracle 9.0.x Security Hole (unbreakable my foot...)

  • Symbolic Link Inconsistency and Behavioral Change in 9i

  • Oracle 9i Rel 2 - XDB Port Nightmares

I came across a web site that archives the internet called Internet Archive - wayback machine that includes copies of various websites at different points in time. This site included copies of Roby's papers from early 2004. I have updated my white papers page to include both the original links and the new alternate links. You can search in the page using CTRL-F for either "roby" or "Alternate Link" to access the papers.

Brian Duff talks about connecting to Oracle servers with ssh

Yesterday Brian Duff of Orablogs fame wrote a short entry in his personal blog Oracle Through a Firewall which describes how you can use ssh to connect or rather tunnel through a firewall to an Oracle server outside of his firewall.

This is an excellent technique to protect access to Oracle servers to ensure that the traffic is not sniffed. There are also links to two papers on my white papers section that also describe how to use ssh to tunnel through a firewall or to encrypt network traffic between an application or application server and the database. This is a good technique to prevent sniffing of password protected role setting or even password changes in the database never mind the protection of th whole connection itself. A good technique to use with a dedicated connection protected with ssh is to use the valid node checking parameters that can be used with the listener (set up in sqlnet.ora) to ensure that the database connections are restricted to the source protected by ssh tunnelling.

massive data theft from a database in California

I found this news item today posted on Network World Fusion and written by Paul Roberts. It was published a few days ago but is still relevant. The state of California has announced that a massive theft of personal data has taken place from a database at the University of California, Berkeley.

The database contained up to 1.4 million records of a personal nature such as social security numbers, birth dates, names and addresses etc. According to a spokesman:

"investigators know a malicious hacker exploited a vulnerability in "commercially available database software" and compromised the computer, but they don't know if the attack was targeted, speculating that malicious hackers possibly discovered the system by scanning for machines running vulnerable versions of the database software."

We do not know if this database is an Oracle database or an MS SQL database or another database. It more than likely wasn't but it doesn't really matter to the rest of us. This item should be a warning to all those who run Oracle databases, even if this issue was not involving Oracle. Any database that is exposed to the Internet or even a wide area network and if that database has not been patched then it is vulnerable to this sort of attack.

Hackers are out there writing scripts to find vulnerable databases with known vulnerabilities. Do not let them get yours.

interesting thread on how to secure a third party application

I saw an interesting thread on entitled Adding some random characters to Oracle password yesterday. This thread is interesting for two reasons. The poster is trying to secure a third party application that he does not have access to source code for and also he is asking a question about an Oracle tool to generate random passwords.

The poster explained that the application connected through a shared account to the database from a separate server and all users then connected via the application and this account. He wanted to secure the shared accounts password and also he said he had heard about an Oracle tool that added random characters to a users password to make it secure. I am not sure what this tool is if it exists but the original question to securing access from a server running an application which uses a shared database account is an old question solved by many people.

Tales of the Oak Table - Dave Ensors comments on Oracle security

I mentioned that I got the new book "Oracle insights - Tales of the Oak Table" published by Apress / Oak Table press (many Oak Table members are authors) recently in this web log. I had read the chapter by Kyle Hailey first as I was interested in the information about direct SGA access that Kyle described. I have since started to read the book from the beginning and have read the introduction and Dave Ensors first chapter on a selective history of Oracle which is excellent. The main focus of the book, it has to be said is performance. There was one more mention about Oracle security (so far) after Dave discussed the 12 rules designed by Codd in the 1980's and related them to Oracle. He then made a comment about what is missing. He said that the 12 rules say nothing about privilege management and enforcement and that this is an area which Oracle has been arguably less successful.

Dave then talks about privileges and security in a one and a bit page section (page 26-27). He talks about the unbreakable campaign and also tells us how version 4 authentication used to work. The passwords were stored in clear text in a table in the SYSTEM tablespace and a weak encryption was used in version 5. Dave gives some good comments and he suggests that VPD doesn't support shared sessions. This is not quite true as secure contexts and proxy accounts and connection pooling can be used effectively with VPD and shared accounts. I suspect Dave meant that it won't work with existing traditional shared account applications. David Knox discussed this is his new Oracle security book published by Oracle press. I will talk about this book soon as well.

Daves made a good point that I have thought about for a long time and never talked about. This is why there is no distinction between users and schemas. Its not intuative. It works as it is but could it be better?

This is an excellent book, so far.


I talked about DBMS_SYSTEM.KSDWRT yesterday in an entry to this web log about the security dangers involved in using DBMS_SYSTEM.KSDWRT to write arbitrary text strings to the alert log. I have just received an email about this issue pointing out that the entire database will crash if an overly long string is passed to this function rather than simply a session crash or denial of service.

This can only be fixed by applying the patches from alert 68. A good practice if you do use this function is to wrapper the function with code of your own to check the length of the parameters used. Keep the parameter lengths to suitable values such as 80 characters so that the text fits on a standard screen or any other suitably short value. This will also enable you to still use long strings but they would be transposed to multiple calls to DBMS_SYSTEM.KSDWRT.

The ideal situation is to not allow access to this package or its functions.

Oracle applications auditing

I was browsing the net in the last couple of days looking for articles and information about auditing functionality in Oracle in particular and auditing in general. I found a good paper about auditing Oracle applications on Integrigy's web site.

The paper I downloaded is called "Guide to auditing in Oracle applications" and covers how to implement auditing in the database and also in Oracle applications. It explains the features available, simple configuration steps and discusses best practices for auditing within Oracle applications.

The paper warns us that setting up audit in Oracle applications is complex and error prone but it can satisfy most organisations requirements. The paper also talks about the fact that most companies do not use its full capabilities because of the perceived complexity and performance issues. This is not true (performance issues) if audit is configured correctly. I also discussed some of the same issues in my paper "An introduction to simple Oracle auditing" written for security focus but this paper was aimed solely at the database not applications.

The paper is very well written and structured and gives a good overview of the audit features and how they can be used.

Allowing a user read-only access to stored procedure source code

I found an interesting post to titled Read-only proc privledges on this morning. The poster said:

"Hi all.
I'm an old-school SQLServer DBA (don't throw any fruit please!) and
now I'm working on a project with Oracle. I'm trying to use PL/SQL to
view table structures, stored proc text, and view data in tables.

Things are working fine accept that I'm unable to see any stored
procedures. My DBA said that he couldn't create a login that just has
stored proc view privledges without also giving my stored proc execute
privledges. Which of course he doesn't want to do."


2 new books on Oracle security

I was made aware of two new books on the subject of Oracle security by Stephen Kost a couple of days ago and thought I would mention them here. I will be adding them to my books page when time allows along with details of every other Oracle security book or book that includes something about Oracle security either as a complete section or chapters.

The books are published by - (broken link) Information Systems Audit and Control Association and are directed squarely at the audit market. I have not seen the books yet, I have ordered them but in the mean time I can tell you about what the web site says about them.

The first book is - (broken link) Oracle database security, audit and control features. This was written by Price Waterhouse consultants and it provides guidance on understanding the IT environment and developing a strategy to plan an Oracle audit. It also suggests a security framework for Oracle, talks about general control and application level security. It includes a list of frequently asked questions / answers and a list of audit tools and a control questionnaire and audit program linked to COBIT.

The second book is - (broken link) Security, audit and control features - Oracle applications - A technical and risk management reference guide, wow what a title! - This book is aimed at risk professionals and auditors (IT and non IT) to evaluate the risks in ERP implementations. This book will aid implementation and aid better design and controls. This is the second book in a series covering the major ERP systems, SAP R3 audit, Oracle Financials and Peoplesoft. There is some commonality in the three systems. The book sounds useful and covers how to adopt a risk based audit approach to ERP, an overview of Oracle authorisation, how to test the security etc. The COBIT framework is also covered.

Frank Nimphius talks about JAAS and declarative J2EE security

I found - (broken link) Franks post made yesterday to his weblog discussing Java Authentication and Authorization Services (JAAS) and declarative J2EE security interesting. He is commenting on the subject as JAAS has just been added to the J2EE platform as an official component. There now seem to be two security methods for declaring security. The declarative method is not as fine grained as the JAAS method.

Franks discusses the merits of both and also discusses his research into frameworks and support of the methods in the only other blog i know of about Oracle that has the word "security" in its title.

Another issue with alert 68 on AIX 32 bit

Finding issues with the infamous alert 68 today seems to be like UK buses. For ages there is nothing to write about on this subject and all of a sudden there are three interesting threads on ORACLE-L in one day. This time i have found an issue raised by Peter Schauss about an email sent to Oracle customers who use the AIX 32 bit platform. Peter said:

"I just received an email from Oracle Global Product Support
announcing that they had found a "problem" with the original
version of the patch."

He goes on to say:

"As far as I can tell the only difference between the old
and new versions is that they have replaced an object called
"ngslcst.o" with "2#nglcst.o"."

Oracle issue an ALERT note saying use of OPatch for multiple patches can corrupt the inventory

I came across Paul Drakes post to the ORACLE-L list this morning that described a new ALERT about using the OPatch tool that is used to install patches in an Oracle database. If it is used to install multiple patches against the Oracle database software that can cause the inventory to become corrupted. Jared Still followed up with a second post where he said:

"So if, I understand this correctly, I need to install OUI, displacing
the current 10.1 version, so as to avoid this bug. Is that they way you
read this?

The reason for my confusion is that the 10.1 OUI was *required* to install
the patch set. So, what happens when I try to install, whenever
it appears?

And patches to 10g?

This alert is not giving me a warm fuzzy feeling."

The alert itself from Oracle is here.

Ken Jacobs talks about the monthly patch release cycle

I was checking ORACLE-L this morning and found this extremely interesting posting from Carel-Jan Engel
about an interview he had with Ken Jacobs. He said in his post:

"Recently (during OOW/Amsterdam), I had an interview with Dr. DBA Ken Jacobs.
During this hour I asked him abouth this 'monthly' schedule. Ken told me
that there was some miscommunication from PR which resulted in wat I would
call now 'the Monthly Myth'.

He litterally said:
'Monthly is a wild word. [..] I'd rather call it periodic, and it won't be
necessarily a month's update'.

So, don't hold your breath, while waiting......"

Interesting comments from an Oracle insider that we are not likely to see a monthly patch release schedule as originally intended. says "Oracle's Security Luck Runs Out"

I just came across this news article posted on 18th Oct 2004 on and found it interesting. The author Kelly Shermach starts with:

"Oracle is so well known for its security that it may have become complacent. "This is a wake-up call to Oracle to take security more seriously," said Noel Yuhanna, senior analyst with Forrester Research."

The news article makes interesting reading in the wake of recent similar news from other sites.

Steve Feuerstein talks about best practices for NDS in 10g

I was just doing some Sunday afternoon surfing before the Brazilian GP coverage starts at 5:20pm and went over to OTN and noticed an article by Steve on best practices on using NDS (Native Dynamic SQL) in 10g, the article is called "Better to Best NDS". With my interest in SQL injection techniques i found this article very interesting. Steve covers some good sense points, for example on error trapping, he suggests saving the string used so it can be printed out, include an exception handler in the block that includes the EXECUTE IMMEDIATE statement, consolidate the error handling in one function and use the new features of 10g such as DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and add specific error handlers for common errors. The main point he makes is to store and display the dynamic SQL close to where its used.

Steve also talks about using AUTHID CURRENT_USER for all code (procedures and functions) that eventually use NDS. This means that the dynamic code will run as the invoker of the procedure rather than the owner. There are for's and against's on this subject when we consider security and SQL Injection. If you create a procedure that has NDS in it and create it as definer rights then you have the option of absolutely controlling the privileges that the person using it has. i.e. you create the procedure with the least privileges possible. In the real world this doesnít happen. If a procedure is created as an invoker rights procedure then there are less general errors due to object resolution as Steve suggests but we also benefit from the fact that the person running it (maybe a application user account) is not likely to have dangerous privileges that can be abused with SQL Injection. The jury is out on which is best but i would go with invoker rights.

Steve also talks about the benefits of binding rather than concatenation. Steve makes the point based on an example that shows better readability and maintainability with binds used. I would make the argument that SQL Injection is virtually impossible if bind variables are used. This and performance make it mandatory to use bind variables.

The article closes with some suggestions on when its better to use DBMS_SQL rather than NDS. Whether you use NDS or not then use bind variables and also trap errors and use AUTHID CURRENT_USER. Steve closes with a note to be careful about using NDS. This is an excellent short article and even though it doesn't mention security specifically its worth reading it and considering the points made.

Is setting trace a security risk? - part 1

I want to talk in this entry and continuing in another probably tomorrow about whether allowing your users to set SQL trace is a security risk or not. Why would setting trace be a security risk? Surely you want your people to be able to fix performance issues?

First letís clarify what trace we are talking about. I am talking here about SQL trace, the trace files that are generated by setting event 10046 or indeed other events that can be used to generate trace files in the database. Some other events can be used besides 10046 to create trace files include 10053 which traces the optimizer or 10928 which traces the PL/SQL virtual machine. There are many others that also generate trace files.

You can search inside the SANS Oracle security step-by-step guide

I was surfing tonight and ended up at Amazon looking at the page where the SANS press Oracle security step-by-step guide - A survival guide for Oracle security (The book I wrote for SANS) is listed and I was surprised to see a new link on there where you can search inside the whole of the book for any text string. I tried out utl_file_dir and got 6 results. You can then drill into the results and see the page. You need to register to see the link found in the search but it seems to be free even though you have to give credit card information. There are also a number of samples from the book, the front and back covers, the table of contents and an excerpt.

This is a great feature, not just for my book but for any book that has it. If you want to know if the book covers a certain parameter before you buy go ahead and search. Also the book has been recently re-printed and updated as version 2.0 with quite a lot of new material added. The book is also available from SANS.

check_parameter.sql : script added to my tools page

I have just added the fifth in a series of scripts promised on this web log to my tools page. This script is called check_parameter.sql and can be used to check the values of an initialisation parameter set in the database. This script can be very useful when auditing an Oracle database for security issues.

As with the other scripts in this series you can choose whether to send the output to the screen or to a file. If you choose a file then either the utl_file_dir parameter needs to be set correctly or a DIRECTORY object must exist pointing at the correct directory and also the user running the script must have been granted the correct privileges on the DIRECTORY.

new shell for Windows

I am currently writing the lab for a module based around auditing the operating system for security issues relating to an Oracle installation for the new SANS "Securing Oracle track" so late last night i found myself surfing for Windows shells. I knew about Cygwin and the MKS toolkit which are Unix like environments for Windows and I had also considered Perl for my own use. I was looking for the options for creating universal (ish) scripts to audit certain aspects of the operating system and I found a blog entry on Wesner Moise's site about a new shell for Windows called Monad. Wesner says:

"Microsoft is introducing a new command-line shell, codenamed Monad, into Longhorn. If you have a Longhorn build, you can use the new shell by launching "msh.exe."

The post was made in May this year so is a little old but it looks interesting as a possible tool for security audits. He goes on to discuss the good features:

"The Good

New-style commands (or commandlets, as they are called in Longhorn) are .NET libraries (plus associated .msh or .cmdlet file) that consist of classes driving from CmdLet. Through the magic of reflection, Monad performs all the parsing and validation of switches and maps them to properties of your new class. Attributes are used to indicate whether properties are mandatory, optional, or produce prompting if omitted. Monad also will eventually support Intellisense and help for cmdlets.

Commands can output .NET objects not just text. For example, you can enter

get/process | where "handlecount -gr 400" | sort handlecount | format/table processname,handlecountYou get:

ProcessName HandleCount
---------------- ---------------
processname handlecount
... ...
Get/Process returns a sequence of process objects, which is filtered to include only those processes with a HandleCount property exceeding 400, then sorted and formatted to show a table of two columns. These objects are treated like records, and can be output to various formats besides text, such as an Excel spreadsheet.

Other notables: In addition to the standard input, output, and error steams, there can be additional streams used by cmdlets such as for verbose mode, progress status, and debug mode. Drives can not just be mapped to the filesystem, but also to other heirarchies like the registry database. The scripting language is designed to be as powerful as Perl, as it includes typed variables, functions, property accessors and method calls, and associative arrays."

and then the bad:

"The Bad

The new shell, as it stands now, is not very usable as a routine interactive shell, so, at least in this build, you'll gravitate back to the familiarity and simplicity of cmd.exe.

Monad is not quite a superset of the XP cmd shell. You can execute traditional commands as before, but the built-in commands all behave different. For example, in the most basic of operations, listing the current directory (ie, typing "dir") produces the directory contents but in user-unfriendly manner. I sure hope that the new shell undergoes usability testing, since the new shell syntax is unfamiliar and does require learning."

The shell sounds like its a bit green and needs more testing and features but it sounds like Windows could finally be getting a "real" shell like bash, ksh, csh available on Unix / Linux. Keep an eye out on progress on monad it could be useful for oracle security auditors.

Auditing DBA's?

Is it possible to audit the DBA with certainty that the DBA cannot alter or remove the audit trail? This was the subject of a thread a couple of days ago on This subject is discussed from time to time on various newsgroups and mailing lists. Generally the talk falls into two or three camps, first suggestions to fire the DBA if you do not trust him or suggest that he has free reign if you do trust him because auditing him is pointless or suggest ways to comply and audit him whilst understanding that if he is determined he can probably get around your measures.

The need to audit the DBAís activities as well as everyone elseís has become a real requirement for companies that have to comply with new regulations that have sprung up in recent years. From 9i you can use the parameter audit_sys_operations to create trace files in the location pointed at by audit_file_dest parameter. Ideally this will write to a directory that the DBA's do not have access to. Ensuring that methods that can be used within the database to access operating system files are restricted is essential in this case so that the DBA cannot use the database engine to alter or remove the files. Fine Grained Audit is also a possibility if the SYS.FGA_LOG$ table can be protected. The best method to do this is to copy it to another database or to the file system either in real time or on a regular basis. This could be done by moving the table out of the SYSTEM tablespace and even from the SYS user (pointing back with synonyms) so that a trigger can be added to it to copy the data. This is most likely not supported by Oracle. The same techniques can be used with SYS.AUD$. Notes on metalink exist to show how to do this. Again this is not supported by Oracle but could still be done. Auditing the normal audit trail in the database for changes to it is also possible. Again this could be turned off or the contents of the audit trail changed by a DBA who wanted to do it.

I think it all comes down to compromise as do most of these very difficult problems that need to be solved. You can audit the DBA and its possible to write the audit records from audit_sys_operations to a file system the DBA cannot access but having DBA's you trust is also necessary!

some interesting comments on ORACLE-L about alert #68

I have been following alert #68 comments and news on various forums and mailing lists that I am subscribed to for a few weeks now. I found some interesting comments on ORACLE-L over at In a thread called "Security Alert #68 - Have to upgrade versions prior to" where the poster asked for a sanity check on his plan to upgrade to as he felt that this version does not need patching. This is not the case. Oracle recommends that you upgrade to a supported version in this case such as or and then apply patch 68.

A couple of interesting comments were posted to this thread, the first from Tom Mercadante who said

"Here's the best part. Oracle now has *two ways* of sending patches out to
us. One is the current (old) way where you run oracle installer. The other
(new) way is where you run Opatch. And even within Opatch, they do things
two different ways. Security Patch 68 has you run sqlplus and install new
packages, and then run patch.csh - which runs sqlplus and installs stuff."


"Seems like the bad old days are
reappearing - where the divisions are not talking to each other anymore, and
they release stuff that is not ready for prime time"

The second interesting comment was from Paul Drake who said

"use opatch, not
I find myself checking the notes on Metalink on a daily basis for this one.

if you're running on win32 - the files under the "docs" folders are invaluable."

Good advice seems to be to review the Alert 68 FAQ regularly if you have still not applied the patches or are still in the process of doing so.

More direct SGA access

I just got the book Oracle Insights - Tales of the Oak table published by Apress / Oak Table Press (ISBN 1-59059-387-1) from Amazon this morning which I ordered some time back. The book is written by a good number of well known Oracle guru's and Oak table members. Each chapter is written by a different person. The book is based around horror stories that the authors have encountered when implementing, fixing, tuning Oracle databases. I have read the intro this morning and I also have completely read the chapter by Kyle Hailey - chapter 6 - "Direct memory access" with interest. As i said previously being interested in securing Oracle should not mean just reading about security exclusively. There are other areas of interested to a security person. For me these include internals, undocumented stuff about Oracle and useful utilities like trace, dumps or debuggers.

I didn't realise that this book also covers the issue of direct memory access to the SGA when i ordered it so this is a bonus to me. The material is very similar to Kyles paper and also Miladin Modrakovic's follow up paper.

As you will remember i talked about the same subject a couple or so days ago when i read about it in the new Oracle wait interface book published on Oracle press. The difference with the tales of the Oak table book is that the chapter is written by Kyle himself. The description and explanation is very useful and interesting. The chapter also is great as it gives credit to the initial author of a DMA SGA program Roger Sanders and talks about how it came about and how Kyle wrote his version of Rogers m2 program.

Customers Gripe About Oracle's Patch Plan"">Internetnews article : "Customers Gripe About Oracle's Patch Plan"

I have just seen an article posted today October 20th 2004 by Michael Singer on website that is questioning the issue of Oracles monthly patch release plan and when the next patch in the monthly cycle will be coming out. Michael has spoken with an Oracle representative who cannot explain the delay to the planned monthly cycle announced a couple of months ago. Michael also notes from his conversation that Oracle will continue with individual releases for critical security issues. The article makes interesting reading and quotes some customers problems from various mailing list postings.

More SQL Injection: A paper on Oracle SQL Injection by Stephen Kost

I was made aware of a paper on SQL Injection specifically aimed at Oracle databases today. The paper written by Stephen Kost is an excellent description of the problem of SQL Injection in Oracle. This paper is very well written and starts by discussing what is SQL Injection, how does it work what are the types of SQL Injection - SQL manipulation, code injection, function call injection and buffer overflows.

Stephen covers injection using PL/SQL and JDBC as these are the most common API's used with Oracle based applications. He talks about the fact that there is a bigger risk to Oracle databases from injection than first thought and also covers the basic ways to protect against SQL Injection even covering where these basic ways will not work. The two ways are to use bind variables and also to filter the input strings specifically for single quotes. Stephen points out that there are cases where bind variables cannot be used such as when the dynamic SQL or PL/SQL needs to generate table names, column names or procedure/function names. He also covers the issue of error message reduction as this is a way that attackers can learn about how an application works so they can exploit it.

This is a very thorough discussion of the subject in relation to Oracle and covers how SQL Injection works and how to secure it. Even the reference section is thorough. Again the paper is here and is well worth reading.

creating read only tables

I saw a good thread yesterday morning on where the poster wanted to know if it is possible to create a read-only table. Some great ideas were suggested, from the basics of restricting privileges, triggers to capture the changes, DDL triggers even a great idea involving disable validate constraint. This stops DML from taking place. There was also a suggestion from Howard Rogers about using a read only tablespace and a read-only datafile. Some of the ideas involving triggers have issues as sqlldr can bypass the triggers. I suggested some ideas on grants and also to use Row Level Security. It seems from this very interesting thread that its almost impossible to make a table truly read only for a long period of time. It is possible to do it over shorter periods if users can be prevented from making silent changes to reverse the read-only status. This potential functionality could be a useful addition for Oracle to consider in the future, true read only tables.

An interesting SQL Injection paper

I am currently finishing off making changes from technical review comments for the new 6 day hands on SANS Securing Oracle track that I have written and also completing some of the labs. Yesterday I was working on writing a lab that demonstrates SQL Injection techniques in Oracle so I have been looking into what new papers there are out there on SQL Injection. There are not many specifically aimed at Oracle apart from the three part paper for Security focus last year. Links to the three parts can be found here. There are however quite a few papers on SQL Injection but not aimed at Oracle.

I have been looking at other papers on the subject as even though they are for different databases itís still possible to learn from them. I found a paper and more advanced SQL Injection written by Stephano Di Paola that seems quite interesting. Its written for mySQL and covers SQL Injection for Cross Site Scripting, Phishing and SQL Injection for HTTP response splitting. Even though the paper is not for Oracle i found it interesting material on the subject. The bibliography gives a good list of other SQL Injection, cross site scripting and phishing papers worth reading.

A tuning book and security?

I went to Leeds yesterday and bought the new book "Oracle wait interface: A practical guide to performance diagnostics and tuning" ISBN 0-07-222729 published by Oracle press and written by Richmond Shee, Kirtikumar Deshpande and K Gopalakrishnan. This is an excellent book, the bible on the Oracle wait interface. I like to buy most books on Oracle if they are well written or useful. Although as you know my main interest is Oracle security and security in general, I am also very interested in internals and undocumented features particularly of Oracle. This book covers some of these areas, hence my interest and also why I am writing about it here.

Listener security guide

I was asked on an email this afternoon by someone if I could recommend a white paper or document about securing the Oracle listener or if I could summarise whatís needed to secure the listener. I had no trouble immediately recommending the Integrigy paper Oracle database listener security guide which I came across for the first time last year.

This is an excellent paper covering most of the basics needed to secure an Oracle listener. I had added a link to this paper to my site last year on my white papers page. There is also a useful tool from Integrigy that can be used to check the listener. I recommended this paper and tool to the person who emailed me and thought everyone else might benefit from this paper, either by re-reading it if you have seen it before (Like I did this afternoon) or to read it for the first time. It contains solid advice on securing an Oracle listener.

computerworld have also picked up the patch quickly story

It seems that the email last week from Oracle to advise customers that there are now public exploits available for some of the bugs fixed in the alert #68 patch set is gathering momentum. This morning this story was presented to me when i searched on google for an Oracle security related subject. The story was posted yesterday (October 15) by Paul Roberts. Again the authors say they have tried to get a response from Oracle on the subject.

Again there is no excuse now to not applying these patches, the risk is real.

where is the next monthly patch?

The more interesting question not alluded to in Lisa Vaas's eweek article is where is the next monthly patch. Oracle brought out patches for alert 68 saying that this is the first of the new monthly patch release plan. This patch was out on August 31 and we have not seen the next monthly patch yet and its now half way through October. I have heard that a number of big companies have complained about the amount of work involved in a monthly patch release schedule. Possibly Oracle could compromise between a monthly schedule which could cripple large companies with lots of databases and the original more hap-hazard schedule of security releases. A quarterly release schedule would be better for companyís staff time budgets needed for installation and testing but would not deliver the advantage of security fixes being available monthly. Itís all about compromises I suspect!

eweek article on alert #68 discusses public exploit availability

An article in eweek by Lisa Vaas posted yesterday 14th October talks about the issue of public exploits now being known for the fixes available in Oracle alert #68. Lisa has picked up on the same issue that I talked about in my blog entry People are now looking for alert 68 exploits! a few days ago. Eweek indicated that they had been attempting to get more information from Oracle over the phone in this article.

who_can_access.sql : a script to find uses and roles that can access a particular object

This is the fourth script in a series of five scripts that I am presenting here that are very useful when auditing the security of an Oracle database. This script is called who_can_access.sql and is also available from my tools page. The script is used to test who has been granted privileges on specific objects in the database. This includes most objects in the database for instance TABLES, VIEWS, PL/SQL, LIBRARIES etc.

This script also does the same as the others in the series and prints out hierarchical reports of privileges granted via roles, or roles granted to roles granted to users. This allows you to see a complete picture of which users can see or access an object and from where the privilege was granted.

The report can be specified to the screen or to a file. If the report is sent to a file then either the utl_file_dir parameter needs to be configured to point at the directory to be used or a DIRECTORY object needs to exist that the user of the report has access to.

SQL Injection papers

I am currently making changes after the technical review comments on the new SANS 6 day hands on "Securing Oracle track" and I have mentioned three classic papers written by Rain Forest Puppy in one of the modules that covers the techniques used in SQL Injection. RFP wrote three papers about how SQL injection works and how it can be used to hack with. My reviewer discovered that the links on RFP's site are no longer there. There is a note on his site that he is moving servers. A quick email to the pen-test mailing list at Security Focus's site got me some new links to the same articles. I just re-read them tyhis evening. These are still excellent articles about SQL Injection even if they are a little old. They are not about Oracle but about security and SQL Injection. This phenonima can affect Oracle databases as well as many others so the techniques described are still useful to anyone interested in Oracle security. The links are How I hacked PacketStorm, - (broken link) NT Web Technology Vulnerabilities and RFPlutonium to fuel your PHP-Nuke and are well worth reading. I also wrote a three part paper on SQL Injection in Oracle for security focus, there are links to these papers on my white papers section here.

Scanning for Oracle databases on your network

This is an interesting subject that was raised some time back on one c.d.o.* newsgroups. The original post asked the question about how to find all Oracle databases on the posters company network.

This is possible to do and is worth doing for all companies. You will have a certain number of production, development and test databases but do you know about those extra databases created when testing something, those installed by power users on their PC's or those installed by technically minded employees that use the space on a company PC to try out Oracle in their lunch hour or what about the DBA's, developers, admin staff who have created databases for personal testing, development or whatever. Itís quite common to find rogue databases in your company networks.

This is an issue for security. If these databases are not patched then they can be used to exploit the whole network either through known vulnerabilities or via known configuration issues. It is important to find them and protect them or remove them.

expired passwords, ORA-01045 and password changes

There was a post to the comp.databases.oracl.server newsgroup yesterday that raised an issue about creating a new account with a expired password. Then the poster immediately connected as this new user and was prompted to change the password as the account was created expired. This password change occurred and then the user tried to connect but failed with:

ORA-01045: user UNNX lacks CREATE SESSION privilege; logon denied

This is because when the user was created no privileges were granted. I did not agree with the poster that this is a bug or error in Oracle as was suggested in his original post. I said this in my reply which is included below.

People are now looking for alert 68 exploits!

I spoke the other day about the recent email from Oracle reminding their customers to apply the patches that fix alert 68. This email also mentioned that Oracle are now aware that some exploits for some of the bugs fixed in alert 68 are now public.

I wanted to emphasise this as I have just seen this morning a post to the newsgroup where someone is asking about the availability of additional information about these bugs. This type of request is not the first I have seen on mailing lists and newsgroups. If this increases or the information becomes more readily available then some companies are going to have problems. Exploits are not just used by internet based hackers they can also be used internally by employees.

Apply the patches soon if you have not done so already.

which special characters can be used in Oracle database passwords

I saw a post entitled Special characters in Oracle password to the ORACLE-L list today. The poster was asking which special characters can be used in an Oracle password and he said he had a problem when using them in an ALTER USER statement but not when using a CREATE USER statement. The exact problem he was having was not given (so far).

The characters that can be used for a password without enclosing it in quotes are the same as any normal Oracle identifier. I wanted to share the answer i posted today to ORACLE-L here as i think its useful to those who are not sure about which characters can be used in Oracle passwords.

preventing password leakage with SQL*Loader

I received an email tonight from a colleague who helped out on the SANS Oracle security step by step book when it was first written. He asked me if I knew how to hide the password used to connect to the database with SQL*Loader. He regularly audits the Unix servers under his control for password leakage on the command line by suing simple ps -ef | grep commands. This sometimes picks up users using SQL*Plus and connecting as follows:

sqlplus system/manager@orcl

The command line then shows up in a process listing using the ps command. He informs the culprit and advises them of the dangers of this and tells them to rectify the issue by using:

sqlplus /nolog

and then supplying the username/password@sid in the SQL script being used. It had come to his attention that users of the sqlldr binary were also leaking the password by supplying it on the command line. He was stumped as to how to fix this. I supplied the answer which I thought I would share tonight with others so that they can also stop this issue.

The simple answer is to use a parfile. This is a file that contains the SQL*Loader commands and could look like this:

$ cat parfile.par

This is then invoked as follows:

$ sqlldr parfile=parfile.par

This will prevent the leakage of the password into the process list but will present a new problem. The password is then stored in a script. This is a perennial issue. The answer is really down to levels of risk. The parfile can be protected at the file system level to prevent it being read. Ideally do not make it accessible to the owner of the oracle software or the dba group as this will prevent access from many of the functions such as UTL_FILE that allow operating system files to be read or written from within the database. Also the file could be generated or hand written just before use and then destroyed immediately after use. There are many other ideas that could be explored, as I said it really comes down to levels of risk. The particular issue here was how to prevent the process list leakage. Unfortunately this sort of problem often creates a new one.

Finally although not discussed with my colleague the same issues apply with other Oracle tools such as exp and imp and can be solved in the same way.

Oracle 9i union flaw

There was a recent posting to the bugtraq mailing list about an issue with MS Access having a flaw in the way unions work. Thanks to Josh Wright of the SANS Institute for pointing this one out to me.

I should make clear first that I don't agree with this as being an issue or bug. It is the way SQL was intended to work in union statements.

The Access and Oracle issue raised is that the poster suggests it is a bug if a select such as:

select login,password from users
select password,login from users

is allowed to work. He says that the columns should not be allowed to be swopped. He suggests that then SQL Injection can be used for instance to return passwords in a screen that displays only usersnames.

Oracle remids all customers to apply Patches for alert #68

As a customer of Oracle I received an email from Oracle support last night 22:55 GMT. This email is entitled "Reminder:Oracle security alert 68" and advises those customers who have not yet done so to apply the patches noted in the Alert 68 MetaLink Document ID 281188.1. The other main point to the email is that the Oracle are now aware of public exploits available for some of the vulnerabilities fixed with the patch. Some of the issues are critical and should be patched immediately. The alert documentation was last updated on 24th September. There is also a FAQ document - ID 282108.1 that will be of use to people.

Apply the patches if you have not done so already!

who_has_priv.sql : script to find user who have been granted a system privilege

I have just updated my tools page (again) and added a new script written by myself called who_has_priv.sql that can be used to find out which users and roles and then hierarchically via those roles who has been granted a specific system privilege.

This is the third in a series of useful scripts that can be used to help audit an Oracle database for security issues in the configuration or set-up of it.

Tools page has been updated again

I have just updated my tools page again with some more additions to the commercial section. Aaron Newman has sent me an updated list of his array of security tools for the Oracle database. The most interesting for me is the new AppRadar for Oracle due to be released in the fourth quarter of 2004. This tool is a host based intrusion protection system. It provides database specific active monitoring and protection unlike other host based and network based IDS tools. Application Security Inc have also extended their range of tools to include web and application server auditing and scanning tools.

The second person to send me details was Dale Edgar who sent details of The Data Masker which is a tool aimed at mangling or obfuscating data held in test databases so that is still real and usable by applications but is false data. This will be a very useful tool for companies to comply with the various regulations.

As with my previous post on this same subject if anyone else has anymore Oracle security specific products either free or commercial then please let me know at

Hiding literal strings in PL/SQL

I just came across a posting on about restricting access to the listener that was started a couple of weeks ago. The thread is here. This thread in itself is quite interesting anyway. But on a different subject in the last post by Yong Huang he mentions the following:

"A little off-topic. 10g also solved another minor security problem:
Literal characters in wrapped PL/SQL code are shown in plain text (see
Not any more."

A link to this thread is here and it talks about the issue of hiding a literal string in PL/SQL when the PL/SQL is wrapped. I also discussed this issue in the SANS Oracle security step-by-step guide book ISBN 0-9743727-4-9. Yongs original thread in 2002 gives a good example of the problem. I also talked about this issue in a paper written for a previous employer in Nov 2001 called exploiting and protecting Oracle. In that paper I discussed how some of the shipped packages provided by Oracle which are also wrapped also contain visible strings some of which are SQL strings. These could be altered by a hacker or employee to effect password changes or similar escalations of privileges. If the files are writable or can be accessed through one of many ways from within the database so that malicious code can be added. The hacker would then wait for the DBA to re-run the scripts which is quite feasible for instance after a patch application or upgrade and gain privileges.

The issue is fixed in 10g as Yong points out as 10g now completely encodes / encrypts the wrapped PL/SQL code so no strings or indeed any text is visible.

What about before 10g? Ė Is it possible to do anything to improve the situation? - The only options really are to remove all of the Oracle shipped PL/SQL files from the installation tree and add them back as needed or access them from a write once media such as CDROM when needed so that they cannot be edited. The same would apply to any application code. This would prevent alteration of the code but would not prevent reading of literal strings as these can still be read from the data dictionary. If the strings are critical, such as an encryption key then read them in from elsewhere or obfuscate them in some way.

10g makes the problem not a problem anymore though so if you use a lot of PL/SQL containing literal strings this improvement is one further reason to consider upgrading.

Howard Rogers writes about Virtual Private databases

I was checking up on Howard's site today and came across an excellent new article about Virtual private databases. The article can be found here. This is a very well written paper that starts by showing us how the problem of segregating data from being viewed by particular users can be done badly with views. Howard then goes on to tell us why even doing it this way would be a big mistake. He then introduces us to Row Level Security or Virtual Private Databases (VPD) and shows how this Oracle functionality implemented in the server itself can be used to control access to each row of data depending on who is doing the accessing. Howard also goes on to talk about performance issues and application contexts as well as promising to write an advanced VPD article soon. The paper can be found here.

who_has_role.sql : A script to find which users and roles have been granted a role

who_has_role is the second script in this series of useful Oracle security scripts. This script can be used as part of a security audit of an Oracle database. Quite often you will want to find out which users have been granted a particular role. Good examples are the roles DBA or CONNECT and RESOURCE as all of these have powerful privileges that should not just be given out to just any users. If a role is also granted to a role ( and to a role...) then it can become quite difficult to see all of the real users that have the role. I wrote this script to aid in this task as like its counterpart find_all_privs.sql available on my tools page it also prints out the grants hierarchically. Here is a simple example where i check to see who has been granted the role SELECT_CATALOG_ROLE:

Copyright (c) 2004 Limited. All rights reserved.

OUTPUT METHOD Screen/File [S]: S

Investigating Role => SELECT_CATALOG_ROLE (PWD = NO) which is granted to =>
User => SH (ADM = NO)
Role => DBA (ADM = YES|PWD = NO) which is granted to =>
User => SYS (ADM = YES)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => ODM (ADM = NO)
User => SYS (ADM = YES)
User => ODM_MTR (ADM = NO)
Role => OLAP_DBA (ADM = NO|PWD = NO) which is granted to =>
Role => DBA (ADM = NO|PWD = NO) which is granted to =>
User => SYS (ADM = YES)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => SYS (ADM = YES)
User => OLAPSYS (ADM = NO)
Role => EXP_FULL_DATABASE (ADM = NO|PWD = NO) which is granted to =>
Role => DBA (ADM = NO|PWD = NO) which is granted to =>
User => SYS (ADM = YES)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => SYS (ADM = YES)
Role => IMP_FULL_DATABASE (ADM = NO|PWD = NO) which is granted to =>
Role => DBA (ADM = NO|PWD = NO) which is granted to =>
User => SYS (ADM = YES)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => SYS (ADM = YES)

PL/SQL procedure successfully completed.

For updates please visit /tools.htm


As you can see a complete list of users is returned even if the SELECT_CATALOG_ROLE has been grant via another role such as EXP_FULL_DATABASE. The script can be set up to print the results to the screen by passing in 'S' as the second parameter or to a file by using 'F' and then supplying a directory and file name. The directory needs to be in a location pointed at by the parameter utl_file_dir or a valid DIRECTORY object that can be used by the user running the script. Tools page updated

I have just updated the tools page on my web site. On this page i include three sections. The first section includes free scripts that I have written my self. I covered one of these scripts yesterday called find_all_privs.sql and will cover four others over the coming days. I also include an annotated list of free Oracle security tools and free tools related to Oracle security. The final section is an annotated list of commercial Oracle security tools.

My aim with this page is to provide in one place a complete list of all available free and commercial Oracle security tools and products. If anyone knows of any tools, products or scripts I have not included then please by all means let me know at

I have just added some new entries to the commercial section of the page. The first for an interesting audit logging and management tool called Entregra for Oracle and the second an application firewall aimed at protecting Oracle databases by filtering and load balancing all of the network traffic destined for the Oracle database called ZXTM from Zeus technologies.

The tools page can be found here

find_all_privs.sql : A script to find all privileges allocated to a user or role

Today i want to talk about a PL/SQL script that can be used to find all privileges allocated to a role or to a user. This script is called find_all_privs.sql and can be found here.

I wrote this script some time ago to help someone who asked for such a script in a newsgroup or mailing list - sorry I cannot remember the original posting to reference here. The person at the time wanted to know all the privileges (system privileges, object privileges and roles) that had been granted to any particular user. They also wanted to see the privileges held hierarchically. That is if a user has been allocated a role then also what privileges that role also gave the user.

I want to present this useful script here and also over the next week or so I will also introduce four more scripts that i have written that:

  • Lists out which users or roles have been allocated a specific role
  • Lists out which users or roles can access a specific object in the database and with which privilege
  • Lists out which users or roles have been allocated a specific system privilege
  • Lists out the detailed settings for a specific parameter passed in. This includes hidden parameters

I hope that these scripts will be a useful addition to any DBA or security auditorís toolkit.