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

Happy birthday to orablogs.com

I just saw Brian Duff's blog entry http://www.orablogs.com/duffblog/archives/000845.html - (broken link) Orablogs is One Year Old! this evening on http://www.orablogs.com - (broken link) Orablogs. I didn't realise that Orablogs had been going one year already. Thanks to Brian for putting together a great site. I visit Orablogs most days amongst many of my other web haunts to check out what is new in the Oracle world and also in hope of learning something new about Oracle and about Oracle security in particular. There are a number of the Oracle bloggers who do write from time to time about Oracle security issues - and sometimes I mention their posts here.

Orablogs is a great site for anyone wanting to learn more about Oracle and to know what a lot of Oracle watchers are watching themselves.

As Brian mentions, his site was inspired by VS Babu's Oracle feed of feeds site. This is another good Oracle resource site, not particularly covering Oracle security (apart on my posts that get displayed there of course) but worth a visit all the same in the search for new Oracle Security information.

Congratulations to Brian on making it to one year!

A script to call SQL*Plus without hardcoding passwords

I receive each week the DBA-Village newsletter and this week there was a link in the tips section of the newsletter for a script written by Wayne Martin that stores usernames and passwords in a central setuid file that only root can access. The script and a short description can be downloaded from the DBA-Village site. To do so though you will need to register for a free login - Brian's post http://www.orablogs.com/duffblog/archives/000838.html - (broken link) Why do I have to register? - comes to mind..:-)

Go to the DBA-Village home page for registration. Then the script description can be found on a tip titled http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=1641 - (broken link) Executes SQL without hard coding of passwords. The actual script source code can be downloaded from the same page by hitting the download button.

Whilst this is an admirable solution to the age old problem of passing a username and password to SQL*Plus on the command line there is a second problem now introduced. This is that the username is still hard coded but in one file that is probably better protected than hard coding the password and username in an SQL file. This is an age old problem in Oracle and other software where usernames and passwords need to be used but not leaked. The problem still exists with the Unix ps command potentially leaking the username and password on the command line. This has to be dealt with correctly by invoking SQL*Plus so that they are not displayed. The author of the script does deal with this.

The problem then is hard code passwords in SQL scripts, or a single file, or even an environment variable (probably still in a file, the .profile) and also the issue of leakage on the command line. I have written about this issue a few times. There are many solutions and each should be considered in respect to sever access, file security, access to ps listings, the level of the user account being used and many more.

Always obey the least privilege principle for users used to run scripts. If possible use DBMS_JOB or its 10g successor DBMS_SCHEDULER as then the issue goes away. If possible use a server based external account to avoid the need for a password. This needs to be done carefully so that this user can only execute the packages needed by the process or system commands needed. If possible avoid running scripts, do it from within the database. If not consider an external user (not remote!), lastly consider solutions like this script. As I said its horses for courses though. Interesting problem!

Andrej Koelewijn talks about google stopping comment spam

I was browsing the web yesterday and was reading Andrej Koelewijn's Oracle blog and found his two posts on google comment spam very interesting. I posted about comments to my blog in a post called "Comments have been disabled from my weblog" back in early December last year. I find it quite annoying to have a blog and ask questions in it that should prompt some response from readers that would hopefully result in some comment posts to my entries. This is one of the great strengths of blogs - to get some feedback and constructive criticism and comments on what I write and report. I cannot afford the time at the moment to filter spam being posted manually which seems to be necessary when the stuff I had posted was coming from a different IP Address for each comment entry. The only sensible option was to turn comments off for now.

So I read with interest Andrej's two posts. The first is titled "Google: Preventing comment spam" and points at a google blog entry about this issue. The second is titled "The register on google’s rel="nofollow”" which discusses The Registers comments on the new google tag designed to prevent comment spam. This is an interesting post as is The Registers post Google’s No-Google tag blesses the Balkanized web.

Some interesting comments about CPU - Jan 2005 on c.d.o.s

I came across a couple of posts to a thread on comp.databases.oracle.server last night about the recent quarterly patch from Oracle. The comments made interesting reading. The thread is titled http://groups-beta.google.com/group/comp.databases.oracle.server/browse_frm/thread/1bf0c40ad9feeb4d/9cae38a77fd66910?q=%22How+are+you+handling+the+quarterly+Maintenance%2FSecurity+Patches%3F%22&_done=%2Fgroups%3Fq%3D%22How+are+you+handling+the+quarterly+Maintenance%2FSecurity+Patches%3F%22%26hl%3Den%26lr%3D%26sa%3DN%26tab%3Dwg%26&_doneTitle=Back+to+Search&&d#9cae38a77fd66910 - (broken link) How are you handling the quarterly Maintenance/Security Patches?. The OP asks how others are handling the first regular patch. His manager had asked him to look into the patch and decide on action. He made a good suggestion that if the patches are regular then there is no reason why he shouldn't plan to have the patch in test in one week and in production 2 to 3 weeks later. Good plan! He also asks if people are taking the time to decide on which databases are vulnerable and hence need patching or whether people are planning to simply apply the patches wholesale.

In a reply to the same thread a poster said

"Instead of supplying oneoff's it would seem safer for oracle­ customers
if oracle supplied patchsets that had included regression te­sting etc.
The current approach just doesn't seem optimal to me."


and he followed with:

"I wouldn't be surprised if oracle changes their direction af­ter getting
some real world feedback from the current approach."


Some interesting thoughts in this thread, are people planning ahead? - after all we know when the patches ar coming, are people able to plan downtime and testing in advance, are people actually using the risk matrix information available in the advisory to assess whether each of their systems are vulnerable and need patching. Is anyone finding the risk matrix useful? - I wonder..:-)

Interesting thread on Oracle-l about ftp'ing data into the database

I just saw an interesting thread on Oracle-l titled http://www.freelists.org/archives/oracle-l/01-2005/msg01060.html (broken link) - Goofy Late Night Idea? where the poster discusses the possibility of using ftp to connect to the database and to insert data directly into a table using ftp. So a user could add a file into the database without an Oracle client simply using a command line and normal ftp commands. Nice idea. As others point out they had seen demo's of this with XMLDB. Ethan then comes back with some pseudo code of what he intended and then Christian supplies a demo - scroll down a bit. As I said quite an interesting idea - the thread is worth reading.

The security aspects of an idea like this need to be considered as well though. Allowing ftp access directly from the net or even from the Intranet to a production database is a security risk. The use of ftp is usually controlled for security reasons on normal servers let alone directly into the database where your valuable production data resides. Whilst this is a great idea to stretch the technology there are security risks to be considered. If files need to be uploaded to an Oracle database it would be more prudent to create an interface to do this task where the user commands and also the files to be uploaded can be more finely controlled.

A bad way to migrate a database or a good way to retrieve crashed data

I saw Edward Stanglers post titled "http://www.edhanced.com/ask-mred/?q=node/view/83&PHPSESSID=6474daf73d1b3fc3f51bcd940dc496af" ealier this week on http://www.orablogs.com - (broken link) orablogs and made a note of it to take a closer look and I have just realised that I had also made a note about the same Oracle-l thread Ed had found. Ed found it on orafaq but i read the thread in my mail reader as i subscribe to Oracle-l.

The thread probably made Ed sit up because of its apparent foolishness. It made me take notice for another reason. I am always interested in internals and undocumented info as I said yesterday but I am also interested in inventive use of the Oracle software. The thread is certainly inventive! The original poster started a thread called "(NEW) very interesting thing about migrate db from different OS(oracle 9.2.0.4,not 10G)" - this is URL to the thread index. Basically what he is saying is that he took a Windows 2000 data file and copied it to Linux and then dd'd a Linux datafile header onto it created a control file and the database opened correctly and he was able to query tables. The version was 9.2.0.4 not 10g. A few posters replied that Oracle has been working on this and it is now in 10g, the original poster alluded to this in his insistence of the version of his test being 9.2.0.4. Someone else suggested that there had been other threads elsewhere discussing the same issue and that the reason it will work on some platforms is endian. As Lex pointed out the trick may work on 9i but it is highly unsupported - but interesting all the same.

The original poster then went on to show some examples of copying files and testing some of his theories.

This thread was interesting to me because of the internals and interesting knowledge but also because of an old thread that immediately came to mind from oracle-l when it was over at FatCity. I couldn't remember the thread title or when it was posted but after 30 minutes of googling I managed to find it. The thread was called "Re: Anyone have a copy of DUL ??" and was by Mogens and i remembered it because of the technique Mogens suggested to open a crashed database. This thread discussed the DUL (Data UnLoader) tool that was written by some Dutch guy in Oracle support that can be used to extract data from a completely dead database. Mogens recapped an episode of the first use of DUL to retrieve some technical drawings from a crashed database and then gave an alternative suggestion to get a crashed database open. Here is a quote from this thread that i remembered when i saw the current thread:

"I never tried the other suggestion Andre had (and which he had used many times himself): Create a dummy database that has the same datafiles as the problem database. Then take the file headers from the dummy database and patch on top of the real database. Then you can start up, since the information in the file headers match.

Andre was one cool guy. He's enjoying early retirement, he claims."


He is talking about an Oracle VP called Andre Bakker. Clearly the OP from the current Oracle-l thread was not the first person to try this idea but for different reasons.

Interesting idea, not to be used to migration of databases from one platform to another - well not before 10g but it could be considered as a useful tool to get at crashed data. Also remember that these techniques could be used to get at your data if a hacker were able to simply steal datafiles..:-(, internals and security always come hand in hand!

Frank has a great blog entry about web application security

I came across Frank Nimphius's blog entry http://www.orablogs.com/fnimphius/archives/000827.html - (broken link) J2EE Security: Good source of information to get started with Web application security this evening and found it a great read. Frank starts off with a great list of "If cars were built like applications…."

He then recommends “Open Web Application Security Project" for anyone interested in web application security and also he recommends a document "A Guide to Building Secure Web Applications". http://www.orablogs.com/fnimphius/archives/000827.html - (broken link) A very entertaining and interesting post by Frank.

Steve talked about an undocumented page on his site

I was reading Steve Muench's Oracle blog about a week ago and noticed that he mentioned that he had an undocumented examples page. The word undocumented or internals or indeed anything that indicates hard to find knowledge or details that may be useful in the future always attracts my attention. I made a note at the time in my notebook to come back and look at this page.

Steve's blog entry was called Updated Dynamic JDBC Credentials Example and it told about Steve updating his example 14 for dynamic JDBC credentials for Model 1 and Model 2. Steves undocumented examples page is a great collection of snippets and examples and information.

What has this to do with Oracle security? - Nothing specifically. For me hard to find undocumented or internals knowledge is always worth reading for anyone interested in security. To be knowledgeable about security also means that you need to understand the products and the technology that you are trying to secure - how else can you secure them? Hard to find or undocumented information just gives that extra level of knowledge so it’s always worth collecting and reading it. For me it’s a learning thing.

default passwords and Oracle default passwords

I just saw on orablogs Tug's post to his Oracle blog titled http://www.grallandco.com/blog/archives/2005/01/do_not_remember.html - (brokenk link) Do not remember the default password... here a list... and went over to comment but couldn't find how to comment. I wanted to concur that the http://www.cirt.net/cgi-bin/passwd.pl - (broken link) cirt default password page is excellent for covering many types of software. The page says it has passwords for 271 vendors and 1386 default passwords.

I just want to also mention here that I have probably the biggest list of Oracle default passwords on the net. There is also a simple tool for checking your databases for existence of any of these Oracle default username / passwords.

http://www.grallandco.com/blog/archives/2005/01/do_not_remember.html - (broken link) Thanks for the tip Tug on the Cirt site.

Brian talks about site registration

I was browsing http://www.orablogs.com - (broken link) orablogs as one of my usual ports of call and saw Brian Duff's post titled http://www.orablogs.com/duffblog/archives/000838.html - (broken link) Why do I have to register? posted a couple of days ago.

I read this post with interest as I found empathy with Brian's views on registering with sites. I agree that in this day and age its hard work to keep up with a huge pile of usernames and passwords that we all tend to keep on top of reading what we want. Whilst not getting into the views / arguments about why sites collect user’s names and email addresses, Brian points out that it’s obvious why, I wanted to talk about Brian’s post here because it got me thinking about an aspect of Security that is not often discussed.

Brian mentioned that the site he was discussing has broken a major tenet of usability by including actions that the user has to d that help the site owner and not the user. This is of course security. This is an interesting thought, security getting in the way of usability. Whilst it is of the utmost importance to secure websites, applications and of course Oracle databases we should always consider usability and accessibility when implementing security or defining security policies that have to be implemented by someone. This is a key issue I come across all the time but I usually get involved after the design stage because I am auditing an existing live production Oracle database for security issues. I find problems, settings and configurations, design choices etc that can make an Oracle based application vulnerable. I suggest solutions to fix the holes but quite often there are discussions based around usability and cost effectiveness of applying security after the fact.

This is why security should be designed in from the start not just added at a later date, perhaps as a result of an Oracle security audit I have performed or perhaps because the site or database was hacked or abused by someone.

We should always consider usability when recommending security policies and solutions. Don't skimp on security but if there is a way to keep the application or site still fast and usable so it. This also benefits us security people as if users and admin staff are not inconvenienced by security they are more likely to think about security themselves and embrace it.

Just quickly back to Brian's post. I liked the idea of a single sign on for websites around the world where authentication is necessary. This would be a useful move forwards but could we trust the single sign on sites (who would collect huge databases of details on us)?

Also remember that all of these websites demand usernames and passwords. How many of use the same username and password again and again. I try to avoid doing this, although to be honest I am like Brian and do my best to avoid sites that require me to register.

Think about this, a DBA holds passwords for all your production databases. These passwords are for privileged accounts. He might register for maybe 5, 10, 15 websites for various reasons, support, news, simply entertainment. Does he reuse the same passwords he has used in your organisation?

What if a hacker were attack the DBA's home PC and steal his website passwords, would the hacker then be able to attack your company’s servers? - There would be no tell tale signatures on the companies network that indicated stealing passwords. Maybe wide acceptance of sites that require registration are also a security risk to the private users employers. Maybe companies should include in their site password policies that employees should not re-use passwords in their own time. Is this overkill or paranoia? - Maybe, maybe not. It never hurts to be over cautious when security is thought about, hang on though are getting into he usability issue again..:-)

One last thought. I posted a couple of weeks or so here about a security tool call http://www.oxid.it/cain.html - (broken link) Cain and Abel in a post titled "Great tool for security checking a PC" that is a useful read (and tool) if you have forgotten a password for a website you have subscribed to and saved it. Also it is worth checking out a tool like this to see what passwords are easily available from your own PC.

Updated internals and Oracle applications security page

I have an undocumented Oracle page on my site that also includes information and links for Oracle and Baan, Oracle and SAP, Oracle and PeopleSoft, Oracle Rdb and Oracle Applications.

I have just made two updates to this page and added first the excellent CPU - Jan 2005 analysis from Steve Kost for Oracle Applications and Oracle E-Business Suite and also I have just added a link to some free software for Baan that allows password ageing to be implemented in Baan as its required for Sarbanes Oxley compliance in the states.

The free software is from Mr. Paradox and has versions for quite a few Unix variants and also includes documentation.

Amis blog talks about LOG4PLSQL

I found the Amis blog posting titled "Addition to LOG4PLSQL to make log level adjustments at runtime" very interesting the other day and worth some comment here.

I came across the LOG4PLSQL software quite a long time ago for the first time when I was adding some instrumentation to a PL/SQL program that generated a large set of PL/SQL packages and triggers that when run generated SQL INSERT and UPDATE statements. I do like programs that generate programs and even better when you can generate a program that generates a program that generates a program..:-).

Anyways I needed to log what was generated in the triggers so I was looking around at what was available and came across LOG4PLSQL. I ended up instead using a simple trace package that I had originally written around 1998 instead but I was quite impressed with LOG4PLSQL. My own package was much simpler but offered the ability to generate log lines to the screen or file and also it had levels. e.g. I could add log statements to various parts of the programs and assign a level, such as 0 - 9, FLOW to ERROR logging. These levels could then be manipulated at run time by telling the session to log levels 0 - 4 for instance or 0 - 7 or whatever.

I will tidy up my code sometime and add it to my site tools page if it would be useful to readers. I have used it many times and although it’s simple it’s useful.

What has this got to do with security? - Well I always like the idea of using instrumentation in programs I write that can be turned on or off at run time and compiled out if possible
(I have C #define versions as well that can be compiled out if necessary - again I will add them to my site at some point). Logging is useful, very useful for run time analysis of why a program is misbehaving. It is great to be able to tell a client to turn on the trace and to be able to tell them why there is an error due to application misuse or data corruption. Logging is also useful for auditing and for detecting application misuse and privilege misuse. Logging to a file is more secure than writing logs to database tables as its likely to be harder for a hacker to erase a log file if he only has database access.

Back to the Amis blog entry. In his entry Dick is talking about changing the logging levels at runtime and an issue he had whereby he could not make code changes to change the DEFAULT_LEVEL parameter. Dick describes code changes he made to LOG4PLSQL to change the DEFAULT_LEVEL parameter to a function that returns the level which is stored in the database. Also there is a periodic read of the variable and only sessions started after a change take on the change. This is a useful change to this already useful and rich software.

Tom talks about proxy users

I was browsing Vattekkat Satheesh Babu's Oracle feed of feeds and found an interesting post by Tom Kyte on his Ask Tom site where someone asked a question about the difference between a normal database user and a proxy user and also about FGA might work with proxy users.

Tom’s answer which also includes some input by David Knox is a very good discussion on the subject of proxy users and how they work. There is a little example code and some well thought out and written explanations. The post about proxy users and FGA can be found on Toms site and is well worth reading.

Integrigy releases a useful impact analysis paper on CPU - Jan 2005

Integrigy have released a very useful paper that shows some analysis of the recent CPU - Jan 2005 patch release. The paper is titled
Oracle Critical Patch Update - January 2005 - E-Business Suite Impact. The analysis provides more information on the recent patches and bugs for the Oracle E-Business Suite. The paper is broken down into sections as follows:


  • Assessment of vulnerabilities


    • Oracle database vulnerabilities (DB01 - DB17)

    • Oracle Application Server Vulnerabilities (AS01 - AS03)

    • Oracle E-Business Suite vulnerabilities (APPS01 - APPS02)


  • Oracle patch analysis


    • Oracle database patches

    • Oracle application server patches

    • Oracle developer 6i patches

    • Oracle E-Business Suite patches


  • Patching strategy


    • High Risk and secure environment strategy

    • Non High risk environment strategy




This is an excellent paper, well written and very thorough. Anyone looking to patch Oracle applications or the E-Business suite should read this paper. This is quite a unique paper with insight, risks and analysis not seen elsewhere.

There are also two other papers on the Integrigy site that relate to CPU - Jan 2005 and its analysis. These are https://support.integrigy.com/analysis/oraclepatchlist.pdf - (broken link) Integrigy Recommended Oracle Patch List - January 18, 2005 and https://support.integrigy.com/analysis/oracleanalysis.pdf - (broken link) Integrigy Oracle Security Alert Analysis - January 18, 2005 which are locked and only available for customers.

Steve Kost has released an Integrigy advisory for CPU - January 2005

I got an email from Steve a couple of days ago, thanks Steve! for letting me know about his advisory for the CPU - Jan 2005 patch set. The Integrigy advisory is called High Risk Security Issues in the Oracle Database and Oracle Applications - Oracle Critical Patch Update – January 2005 and details the security bugs found by Stephen Kost of Integrigy and fixed in Oracles CPU - Jan 2005 patch set. The bugs found include multiple issues in the Oracle Spatial MDSYS.MD2 package, a denial of service in the Oracle Forms Server and a password leakage issue in the Oracle Reports Server and two SQL Injection issues in the Oracle E-Business Suite.

I have also updated my Oracle security alerts page to complete the annotated details I know of for CPU - Jan 2005 advisories.

In the news page updated

I added a page to my site some time back when I added all my new menu items and pages to record when Pete Finnigan or PeteFinnigan.com Ltd are quoted in news articles. I talked about adding all the new pages in an entry here called "A lot of new pages on my site".

I have just updated this page and added links to all of the recent news articles I could find where Pete Finnigan and PeteFinnigan.com Ltd were quoted. These have so far been primarily on SearchSecurity.com, eweek.com and Internetnews.com

I will update this page as and when I get asked to contribute to news articles again or of I am quoted from my weblog or other work.

Translation of www.Heise.de German news article

I posted about a German article a few entries ago. The post was titled "Another critical patch update news article - In German". Alex Kornbrust has kindly translated the article into English for me. He has also spoken to the original author Daniel Bachfeld who has very kindly agreed to let me publish his work in English here.

This article was originally published on the German security portal heise Security. The text in English is as follows:

Oracle close down security holes

Database manufacturer Oracle has published his quarterly CPU, which
close down 23 security holes. Affected products are the database server
(17), application server (3), collab suite (1) and the e-business
suite(2). The errors cover different versions of these products. More
detailed information is contained in the Oracle advisory for this
update.

Different from previously advisories the manufacturer describes in his
advisory additional details concerning the vulnerabilities. He explains
what module contains what error and what additional requirements must
be fulfilled, to exploit these holes -- e.g. if a previous
authorisation is required. Some of the holes are based on buffer
overflows which allows to inject code via the network. Other holes are
based on SQL-Injection and directory traversal, the break out from a
given directory.

The patches are available on the web pages of Oracle for registered
customers. These errors were discovered among others by the specialists
for database security David Litchfield from NGSSoftware, Pete Finnigan
and Alexander Kornbrust, which have released own advisories. According
to the advisory of Kornbrust, Oracle did not patch a buffer overflow
which could crash a database server, for nearly 2 years.

See also:

* Critical Patch Update January 2005 from Oracle
* Vulnerabilities in the Oracle Database Server from NGSSoftware
* Directory Traversal from Peter Finnigan
* Buffer Overflow in Create Database Link in Oracle8i - 9i from
Alexander Kornbrust


Thanks again to Daniel and Alex.

Search Oracle talks about the Critical Patch Update

I just found Shawna McAlearney's article on SearchSecurity.com written on 18 Jan and titled "Multiple high-risk Oracle flaws". This is quite a good article not just because it quotes me and my blog..:-) but because it analyses the bugs found and fixed and also gives some good commentary about the patch set and the researchers comments.

Alexander Korbrusts upcoming Oracle security bugs

I was browsing Alex's newly updated site and found that he has added quite a bit of content compared to its previous state (simply a logo and three or four text advisories). Alex is a bug hunter extraordinaire as he has found a large amount of security bugs in Oracle over the last few years.

One of the most interesting pages on Alex's site is his page Upcoming Security Alerts which lists 27 bugs Alex has found in Oracle products and one in BEA. The Oracle bugs are not part of a patch release yet by Oracle. Some are classed as high or medium severity but most are classed as low severity. The table also includes a reference number allocated by Alex and also the date the bug was reported to Oracle. Most were reported in 2003, the earliest in July 2003 some 18 months ago. Almost all of them have been reported for at least one year. Whilst Oracle have made great progress with the type of information included in the newest advisory released on 18 January they clearly need to make a lot of progress on the timescales taken from someone reporting a security bug to them to the point that it is fixed. The contents of these bug reports are known to Alex and the Oracle team but that doesn't mean to say that others, malicious or not have not found the same bugs in this timescale.


I also note that Alex's numbering scheme means that he has found at least 42 security bugs, assuming there are no gaps in the numbering scheme. This is also a substantial number of security bugs for one person to have found over an 18 month period.

Out of interest the same page on Alex's site also includes a link to the NGS site that lists bugs that they have found that are not fixed yet. This page also includes 16 security bugs found in Oracle mostly classed as high severity that also have not been patched yet. This gives a total of 43 security bugs in Oracle that are known by researchers and Oracle that are not yet patched. The NGS bugs are listed as being reported on 9/9/2004 so most are more recent than Alex's bugs.

I hope Oracle are planning to fix these bugs quickly. I can understand that fixing bugs in software written for a lot of platforms can take a lot of time but 18 months does sound far too long. I hope that Oracle can make great progress on this front just as they have done on the information in their new advisories.

Alexander Kornbrust has an advisory for CPU - January 2005

My good friend Alex Kornbrust has added an advisory for Critical Patch Update (CPU) - January 2005 to his website. The advisory is titled "Buffer Overflow in Create Database Link in Oracle8i - 9i" and details a bug Alex found in April 2003. Alex has found that any user with the ability to create a DATABASE LINK can crash the database. The workaround Alex suggests is to revoke the CREATE DATABASE LINK system privilege from the CONNECT role.

I would suggest a better solution is to revoke the CONNECT ROLE from all users that have been granted it and to then create a more realistic connect role for general users and grant that instead. You can find which users have CREATE DATABASE LINK system privileges with my script who_has_priv.sql and you can also find out who has been granted the CONNECT ROLE with my script who_has_role.sql.

More about Alex's site later.

Another critical patch update news article - In German

For any readers from Germany, I was just asked by a German speaking reader of my blog to post a link to a news article written in the German language titled "Oracle schließt kritische Sicherheitslücken" - I cannot paraphrase or analyse its contents much as my German is very basic. I can see that it mentions my name, Alex Kornbrusts and David Litchfields and also contains links to the advisories. I hope the link is of use to other German readers.

Eweek talks about the Critical Patch Update - January 2005 release

I just found a news article on eweek titled "Oracle Patch Fixes 23 Vulnerabilities" that discusses the latest Oracle security patch and advisory. The article is written by Ryan Naraine. It follows much the same pattern as the other two articles I found but also mentions a security research firm Secunia that suggests that the flaws are "moderately critical" and that SQL Injection exploits are possible. The article goes on to talk about two possible exploits suggested by Secunia and also talks about the previous Oracle advisories and the improvements in this new one.

Two news items about Oracles new security advisory

I have just found two news articles about Oracles new quarterly scheduled security advisory. The first is on news.com and is written by Robert Lemos. The article is titled http://news.com.com/Oracle+urges+customers+to+apply+critical+update/2100-1002_3-5540642.html - (broken link) Oracle urges customers to apply critical update and simply reports the fact that the alert has been released. The article was released yesterday just after Oracles security advisory.

The second news article is on InfoWorld and is written by Robert McMillan. This article is titled "Oracle releases critical security patch set" and again gives a basic overview of the release. This article was written today.

I hope we will see some news articles with some comments on the standard of the advisory and also its contents soon.

Critical patch update - January 2005 is out

I have just noticed that Oracle have released their advisory for the first quarterly security patch update. This is the first of the scheduled patches announced last year and talked about here and on news sites around the world.

Oracle's advisory titled "Critical patch update - January 2005" is also a change from the previous naming convention with alert 68 being the last of the original naming convention.

The advisory is a comprehensive document and contains much better information than previous advisories from Oracle. The patch also contains all the fixes included in alert #68. It also contains some non security fixes that are necessary because of interdependencies.

The key addition in this advisory over previous advisories is the new risk matrix that details each bug to some degree and also the risk. Each bug is numbered and the component identified such as Database core, networking, package name etc. Then the access required is listed. Then the privileges necessary for the bug to be exploited, then the risk matrix for confidentiality, integrity and availability. Finally earliest and latest versions are listed as well as whether a workaround is possible.

This is excellent, well done to Mary Ann Davidson and her team for doing this and improving the information available with the security advisory as compared to previous advisories. I hope that in particular the risk matrix will really help customers make decisions about applying the patches quickly and confidently. Also well done for supporting some of the older releases where it’s relevant. Excellent!

I also see that there are patches for older versions and even de-supported versions which are supported for particular products only. Again a big move forwards.

Links for each patch set are included in the alert. The alert also credits the researchers who have brought bugs to the attention of Oracle. This included Pete Finnigan (me), Alex Kornbrust, Stephen Kost and David Litchfield.

I will release an advisory later this evening now that Oracles advisory is out.

The first Oracle security alert for Jan 18th - First quarterly scheduled security patch

I just saw the first post about new vulnerabilities in the Oracle database on the bugtraq mailing list at Security Focus. The post was made about one hour ago by NGS. The post is titled "Multiple high risk vulnerabilities in Oracle RDBMS 10g/9i" and discusses multiple bugs that have been found and fixed in the first of the new quarterly patch schedule fixes. They also have announced as they did with alert 68 that they will withold details of the actual bugs they have found until April 18 - 3 months later so that customers can get patched first. The post also suggests customers should go to Metalink for the patches.

As of now there are no announcments on Metalink or on OTN or on the Oracle security alerts page with respect to availability of the new patch set but there is a note on the Oracle alerts page to say that its scheduled to be released today.

More on Sarbanes Oxley and Oracle

I talked about Sarbanes Oxley and Oracle the other day here. The issue was raised as a question on the oracle-l list originally. I have been following further posts with interest as this is a very current subject. There seems to be a major underlying feeling to the thread that the actions required by organisations are subject to the individual interpretations of the auditors performing the audit. One poster even suggested that he has seen differences between auditors from the same audit firm when they have audited his company at different levels. Jared has made some good points including suggestions on password storage and also about use of shared accounts. Also the issue of personal accountability has been discussed in detail. The final post (so far) in the thread by Kip talks about the need for audit-ability where emails have been used as the line of communication in change control procedures. He said auditors are requiring access to all emails from 8 months ago. This sort of level of accountability could become an issue in some companies with large workforces and millions of emails. The complete thread Sorbanes Oxley for dummies? can be found here.

HTML Kit

I saw with interest Steve Muench's post titled "Standalone HTML Editor Based on Firefox/Thunderbird HTML Editor" the other day. This is because I buy the UK magazine Web Designer from time to time and I was reading issue 101 that same day. One of the cover stories of this issue is about web design on the cheap, well cheaper than buying the professional tools such as those from Abode and Macromedia. I read with particular interest the lead article "Web Design on a budget" as it covers loads of replacement tools for the professional ones that can be had free or much cheaper than the professional ones. I saw the same editor Steve mentioned, Nvu but the one that caught my eye most was an HTML editor called HTML Kit. I downloaded this the other day and have had a play with it. This is a superb editor and I will be putting it to use on my site code.

As readers will know I have been giving a lot of thought to content management and production of my site recently. I have been a bit busy with work to make much further progress. I have had time to purchase a good book about PostNuke, called "PostNuke content management" written by Kevin Hatch. I will be giving serious thought to the use of a tool such as PostNuke in the near future, well after getting my newsletter re-launched and also a few other additions up and running.

In the meantime I will be playing with HTML Kit and seeing if it can help my site content productivity for now. I have been a big user of http://www.textpad.com - (broken link) textpad in the past though and Ultraedit32, so they will take some beating. :)

Penetration testing research and cost effective security

I made a note a couple of weeks back to talk about this subject here when I got a chance. Well that time has arrived. I saw a post on the pen-test list on security focus titled "Research on penetration testing?" where the poster told us that he had the chance to take a masters degree in Norway and was looking for research ideas in the area of penetration testing, unanswered questions, areas that should be researched further academically or ideas that would be of public interest.

A few posters suggested ideas, the complete thread index is available here. One poster suggested looking at the business aspects of costing and benefit to the customer of penetration testing. I thought that this was a great idea. I added a detailed post with my thoughts at the time. I will repeat my ideas here as posted:

"I would agree with this idea. When I read this my thoughts went
immediately to the book "Optimizing Oracle performance" written by Cary
Millsap and Jeff Holt - Published by O'Reilly. This book, quite
obviously by the title is about tuning Oracle and not about penetration
testing - bear with me..:-)

The book describes the new (ish) method of using the Oracle wait
interface (instrumentation in the Oracle kernel) for tuning. But the big
idea in the book is that fact that using this method the tuning effort
is repeatable and calculable in advance in terms of effort and cost
benefits.

Cary and Jeff describe how its possible to analyse the issue and then
identify the key business processes that are a problem and finally also
identify the time saving that is possible with solutions (This is
possible because what they are doing is analysing lost time in the
processing of data - so that time is highlighted in detailed steps in
the kernel source) and hence the cost benefit to the organisation.

This is mind blowing when you consider previous efforts were based on
trial and error. e.g. change this parameter and see if the program is
faster... no... now change it back and then try another parameter
instead... and so on.... with "method R" as described by the authors the
tuning effort is acutely focused based on cost benefit to the business
and the cost benefits of the possible solutions are known.

Now if this breakthrough in tuning could be applied to penetration
testing then the cost benefits for customers would be great. I would say
also that anyone who could offer this service would be in a commanding
position. Managers like to see costs and benefits..:-)

It is like the difference between alchemy and science.

Whether its possible to apply these ideas to penetration testing or not
is difficult to know. Also I feel the solution would probably be
technical as well as business related. In the Oracle book, the authors
have developed perl scripts to apply the ideas and also utilize queuing
theory in the solutions. I think ideas along this line would make a
great project.

Hope this helps
"

I think this is a very good point in relation to general penetration testing. How would you define the cost benefits of a penetration test and how does that relate to an audit of to internally running a commercial or free tool?

Great tool for security checking a PC

I found a great tool mentioned on the pen-test list on security focus. The thread is titled "RE: Password Audit tools" and this particular item mentions a tool called http://www.oxid.it/cain.html - (broken link) Cain and Abel. I didn't recognise that the tool was Cain and Abel to start with as just the URL was given. I actually came across this tool probably more than a year ago when it was recommended to me by a good friend in Germany.

I downloaded at that time and tried it. I was amazed at the details this tool can find on a PC that it is run on. The tool according to the website is a password recovery tool for MS Windows. It goes on to say that the tool allows easy recovery of passwords by sniffing the network, cracking encrypted passwords using various techniques such as dictionary attacks, brute force and cryptanalysis techniques. It can decode scrambled passwords, password boxes, cached passwords and analyze routing protocols. The program does not exploit bugs but uses weaknesses in protocols, standards etc.

The authors warn that the tool could cause damage and that they are not responsible if damage occurs. I ran it a few times with no issues. I was even able to recover a forgotten dial up password! Version 2.5 has been enhanced to allow analysis of encrypted protocols such as SHA-1 and HTTPS and can capture passwords from various authentication methods / protocols. It also contains various hash crackers, decoders and lots of other utilities.

This is a great tool. Anyone who is interested in the security of their Oracle installations should download and run this program and see for themselves the types of information and passwords that can be revealed. It is frightening when you realise that hackers or your employees can also do this.

Adam Martins Oracle password cracker seems to not be available

I was emailed last week by someone who made me aware that the link I have on my Oracle security tools page to Adam Martins Oracle Password Cracker - version 1.6 is dead and the many other pages on shareware sites on the net that link to this page also have dead links.

I checked this out myself this evening and found that I cannot not download Adam's tool. I have also sent an email to Adam to ask about it using an email address I had from a couple of years ago but this was returned as undeliverable. In the meantime if anyone knows of a working link for this tool or how to get hold of Adam to ask about it can they please let me know via email - pete_at_petefinnigan_dot_com

Searching metalink from the MS search bar

I saw a very interesting post on the oracle-l list earlier in the week and made a note to talk about it here. The post is titled "Metalink search from the MS Deskbar" and it caught my eye as it sounds like the same security concerns could surface with this search bar as with google.

The poster of the thread let's everyone know that a new http://beta.toolbar.msn.com/ - (broken link) beta desktop search bar has been released by MS. He goes on to say that he has no familiarity with the competition but he points out how useful this desktop search bar looks. he then gives a good example shortcut that can be used to search metalink. He also suggests that this search bar is faster than searching through outlook or the Windows Explorer.

As I said the story caught my eye as it could be possible that some issues occur such as with the google desktop search. I talked about this recently in a post called "Bruce Schneier talks about google desktop search security" in my own blog. Of course there may not be any issues at all, I have not tested the MS desktop bar.

Sarbanes Oxley and Oracle

There is an interesting thread today on the Oracle-l list about Sarbanes Oxley and Oracle and the issues and expense caused by adhering to Sarbanes Oxley in an Oracle shop. The thread is titled "Sorbanes Oxley for dummies?" where the poster asks if anyone has any good papers that relate to Sarbanes Oxley and what it means for the DBA. He clearly wants to understand the true position he is in relation to this complex legislation.

A great answer is posted next by Mark who makes some great observations including the fact that the actual requirements seem to be little understood and that auditors themselves are requiring things to be done that are not required as part of the legislation and that the cost of this fact will outweigh that of the true requirements. Mark also gives us a link to a Yahoo Group dedicated to Sarbanes Oxley and Oracle. The link is actually wrong in the Oracle-l thread. Mark also makes a good point about getting the DBA signed up to the "functional Team".

http://www.freelists.org/archives/oracle-l/01-2005/msg00486.html - (broken link) Paul as always makes a good contribution by giving us a link to Arups book about HIPAA and also to a paper by Arup about FGA and he makes a valuable point about SOX being so open to interpretation that its best to get advice from the auditors. Very interesting thread on this legislation.

Security ethics in vulnerability disclosure

I was browsing the net and found a very interesting news article on zdnet UK this evening titled "Court case shines light on security ethics" written by Dan Ilett.

The paper concentrates on the fact that some leading security researchers believe that more responsible actions need to be taken by security researchers in revealing bugs in software. The item is sparked by a legal action taken by a French company Tegam taken against a researcher called Guillaume Tena.

Quite an interesting read and also a controversial topic both for security researchers and for software companies.

Amis blog has an entry all about OpenVPN

I saw a good entry in the Amis Blog tonight when i was trawling http://www.orablogs.com - (broken link) orablogs. The entry is called "OpenVPN: How to safely access a database behind a firewall" and is written by Andre.

The post describes OpenVPN which is available from http://www.openvpn.net. Andre goes on to discuss the detailed features and uses of OpenVPN. Andre then discusses the platforms supported and let's us know that only version 2 will work on Windows XP. he then steps through an installation on Fedora Core 3 and then details the setup and use of OpenVPN.

This is an excellent short article and of use to anyone interested in protecting remote access to an Oracle server or database. There are two links on my Oracle security white papers page that discuss tunnelling SQL*Plus through an ssh tunnel. Search with CTRL-F for SSH on the page. These articles may also be of relevant interest to the same readers.

Nice paper on checking Oracle password strength and enforcing it

I was searching google for information about the INDENTIFIED BY VALUES clause of the ALTER USER statement and found quite a nice article about Oracle password strength, cracking and protection. The paper is called Connecting with Oracle: The Password Game by Steve Callan.

The paper is quite concise but says the right things with some good examples. I have seen all of this before in various places but this article sums up the issue of password complexity quite well. Steve starts by showing how passwords are stored and created and how trace will not reveal the password as it’s created. This is not quite true up to 9.2.0.3 and depending on the trace used. I wrote a short paper a long time ago that discussed how to extract passwords from the library cache. It was called "Revealing clear text passwords from the SGA". Also recently I have shown how Oracle passwords can be extracted from the network in a short paper called "Passwords transmitted in clear text on SQL*Net". Anyway that was a digression. Steve then goes on to talk about password crackers and recommends Bear Dangs tool which is SQL based. There is a link to it on my Oracle security tools page. Steve completes the paper with a discussion on using the Oracle password complexity function that can be attached to a user’s profile. He also gives some examples of some invalid passwords and makes a very good point about comparing your idea of password complexity with that of your users. Steve also says that this is part one of a two part paper, I will watch out for the second part.

Howard Rogers has a good article about database links

I was browsing Howards website for a link to his read only tables article and came across a paper on his site I had not seen before titled "Database Links". This is written in Howard's now familiar question and answer style and is an excellent short paper.

He starts by asking what is a database link, how is it used should it be used etc and goes on to discuss how to set them up in detail including detailed examples of how and also to test all the components are up and running. Howard goes on to test the initialisation parameters db_domain and db_name and explains what they mean and what they should be set to. He concludes by creating example links and testing them with all sorts of SQL including DDL against remote databases. He also talks about auditing and creation of links into your database and also of their use and also discusses the issues of modifying links that already exist.

This is a superb paper explaining links in detail. They should be of great interest to anyone who is interested in the security of their Oracle databases. Any connection that allows remote access to your data should be reviewed and understood. Again Howards paper "Database Links" is here.

Daily, weekly, monthly checklists

I saw Haris Ali's post last night titled http://www.cheblogs.com/roller/page/nimzobenoni/20050110#dba_checklist - (broken link) DBA Checklist and thought about the idea of having a daily, weekly, monthly even yearly checklist to be followed when monitoring and administering an Oracle database or group of Oracle databases is a good one. Whilst I don't agree totally with Haris's list, the act of sitting down a creating a list is fundamentally a good one.

Creating a checklist is the first step on the road to creating policies or procedures for maintaining and monitoring Oracle. This is one of the first actions that should be taken in a security conscious organisation. If you do not plan what to look for, monitor, check and take action then how can you possibly know what to check for and when and more importantly how would you know when there is a problem. This is planning 101.

Creating a checklist in general is useful as Haris points out for general admin but it can also be very useful for security. For instance, you should audit users accounts for weak passwords regularly and also check for default accounts that still have default passwords set. You should in conjunction with this check as part of a checklist consider the rules for managing passwords in the database, for instance minimum times between password changes. rules for password complexity etc.

Write down the key items that need to be monitored. There are two very good Oracle security checklists in existence. These are the SANS S.C.O.R.E. document that is based on the book I wrote for SANS - Oracle Security step-by-step a survival guide for Oracle security. The second is the CIS checklist which is again in part developed based on the same book. Links to both of these lists can be found on my Oracle security white papers page. There is also a few other smaller checklists for Oracle Security in existence. The Oracle 9i and 9iR2 lists are basic but not bad - again links to these are on my Oracle security papers page. There is also a tool available with the CIS paper.

Remember also that checklists are very much site specific as are policies and procedures. There are so many variables that a standard list that suits everyone is hard to define.

Automate as much as possible, the checklist from CIS has a tool. There are also other free tools available such as Patrik Karlssons tools and metacortex as well as a few commercial tools. Links can be found on my Oracle security tools page.

Also be aware that checklists and policies are a moving target. That is they need to be reviewed regularly and updated as necessary to take in new knowledge and techniques that have come available since the last review.

I like the idea in Haris's blog entry of reviewing sites of major suppliers regularly and reading DBA manuals for one hour a day - I think this is excessive but reading the manuals is important and should be done regularly to keep current with the technology. On the same subject subscribe to the relevant security mailing lists such as Bugtraq and vulndev. See the securityfocus site for details.

A nice simple DBMS_OBFUSCATION_TOOLKIT example by Nimzo Benoni

I saw a simple example of the use of the DBMS_OBFUSCATION_TOOLKIT on Haris Ali's blog today. The entry is titled http://www.cheblogs.com/roller/page/nimzobenoni/20050110#dbms_obfuscation_toolkit - (broken link) DBMS_OBFUSCATION_TOOLKIT and gives a very simple encrypt and decrypt PL/SQL procedure examples. He even shows how to ensure that the data being encrypted is aligned to an 8 byte boundary. The two procedures do not allude to the perennial problem of encryption and decryption - which is how to hide the key so that it is not available to users or hackers. In these examples the key is simply hard coded in the examples.

Haris's example shows use of the procedures without output. I think he could have made the example a little more useful by showing it actually working.

10g improves the encryption and decryption possibilities in the database by providing the DBMS_CRYPTO package that supports more algorithms including AES and also manages the padding better. This new package will replace DBMS_OBFUSCATION_TOOLKIT. There are commercial alternatives as well as free ones to the Oracle provided packages. Details of which can be found on my tools page. Haris's example is worth looking at if you are contemplating use of this package as it shows a simple example.

Becoming another user

I saw a post somewhere last week, maybe Oracle-l, dba-village, lazydba or c.d.o.* - I don't remember which where someone posted a version of the Oracle su script where a database user can connect as another user temporarily without knowing their password. This is old hat to most people but I thought it worth mentioning here anyway. The trick is accomplished by using the IDENTIFIED BY VALUES version (undocumented) of the ALTER USER command. This allows a DBA - usually - actually a user that has the ALTER USER privilege can do this. The user selects the existing password from the SYS.USER$ table (This can be via the DBA_USERS view). So the user using this technique also needs access to the existing users password hash via one of these. Usually this is not forthcoming to most users. Using my who_can_access.sql script you can check who has access to these, first SYS.USER$:


who_can_access: Release 1.0.1.0.0 - Production on Sun Jan 09 21:58:03 2005
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF OBJECT TO CHECK [USER_OBJECTS]: USER$
OWNER OF THE OBJECT TO CHECK [USER]: SYS
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Checking object => SYS.USER$
====================================================================


Object type is => TABLE (TAB)
Privilege => SELECT is granted to =>
User => CTXSYS (ADM = NO)
User => MDSYS (ADM = NO)
User => OLAPSYS (ADM = NO)
User => WKSYS (ADM = NO)
User => XDB (ADM = NO)

PL/SQL procedure successfully completed.


For updates please visit /tools.htm

SQL>


Quite a few default users have access to this table. Let's check DBA_USERS:


who_can_access: Release 1.0.1.0.0 - Production on Sun Jan 09 21:59:43 2005
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF OBJECT TO CHECK [USER_OBJECTS]: DBA_USERS
OWNER OF THE OBJECT TO CHECK [USER]: SYS
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Checking object => SYS.DBA_USERS
====================================================================


Object type is => VIEW (TAB)
Privilege => SELECT is granted to =>
User => CTXSYS (ADM = NO)
User => ORAPROBE (ADM = NO)
Role => PWD_ROLE (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => ROLE_TEST (ADM = NO)
Role => NON_PWD_ROLE (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => ROLE_TEST (ADM = NO)
Role => SELECT_CATALOG_ROLE (ADM = NO) which is granted to =>
User => SH (ADM = NO)
Role => DBA (ADM = YES) which is granted to =>
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => REMOTE_OS_USER (ADM = NO)
User => ODM (ADM = NO)
User => SYS (ADM = YES)
User => ODM_MTR (ADM = NO)
Role => OLAP_DBA (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => REMOTE_OS_USER (ADM = NO)
User => SYS (ADM = YES)
User => OLAPSYS (ADM = NO)
User => ORAPROBE (ADM = NO)
Role => EXP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => REMOTE_OS_USER (ADM = NO)
User => SYS (ADM = YES)
Role => IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => REMOTE_OS_USER (ADM = NO)
User => SYS (ADM = YES)
User => TEST01 (ADM = NO)

PL/SQL procedure successfully completed.


For updates please visit /tools.htm

SQL>


On my 9.2.0.1 database quite a lot of users and roles have access to this view.

Access to both of these and indeed any method of viewing password hashes should be blocked as far as possible by revoking the access privileges granted.

There are two other possibilities to access these tables or views. The first is if the initialisation parameter O7_dictionary_accessibility is set to TRUE then any user with SELECT ANY TABLE system privileges will also be able to access this data. The second possibility from 9i is the SELECT ANY DICTIONARY system privilege. Users and roles that have been granted these privileges can be checked for with my script who_has_priv.sql

OK, back to the subject of how to change users without a password. The trick is achieved by selecting the current hashed password from SYS.USER$ and then storing it locally. The password of the user to be connected as is changed to something known with an ALTER USER command and then the person logs in, then can then re-set the original password with the ALTER USER {blah} IDENTIFIED BY VALUES '{original password hash}'.

This can be wrapped up into a script so that the final command is spooled to a file that can be run later to restore the password. Finally Tom has a good example script and article on his site.

CREATE SCHEMA - does it do what it says on the tin?

I saw an interesting post, I think last week on the oracle-l list and also the same original post was made on comp.databases.oracle.server if I remember. The thread on Oracle-l was titled "[Q] How to create second schema under a user account?" and the poster asked, or rather suggested that it is possible under Oracle to have multiple schemas under a user account and he asked if anyone could give examples of how to do it. He asked how to create a second schema under an account, how to create objects under that schema and then how to switch schemas.

Dick replied to say that there is no way to create a second schema that he knows of and he suggested that the poster is confused as users and schemas are one and the same under Oracle. He also suggested proxy accounts although the actual problem was never stated.

Then came the bit that is interesting for me in the last post in the thread by Lex de Haan. He says that Oracle supports the ANSI/ISO standard CREATE SCHEMA command, but that it fails unless the schema name corresponds to an existing database user.

I did a quick search for a useful explanation of the CREATE SCHEMA command and came across a good post on Experts Exchange titled "Solution Title: How to Create a Schema". This post does a good job of explaining the difference between a user and a schema. Basically a schema is created automatically when a user is created. The last entry on the page gives a good example of the CREATE SCHEMA command. If a user account is created objects such as TABLES or VIEWS can be added with explicit CREATE TABLE or CREATE VIEW commands. Each object would then be added to the schema with separate DDL commands. With the CREATE SCHEMA command its possible to add multiple tables, views and grant privileges on those objects in one statement. The poster gives a good example command like the following:

CREATE SCHEMA AUTHORIZATION pete
CREATE TABLE employee
(empno NUMBER(10) PRIMARY KEY, first_name VARCHAR2(30),last_name VARCHAR2(30))
CREATE VIEW empview
AS SELECT empno, first_name, last_name FROM employee WHERE empno>30
GRANT select ON empview TO zulia;

This looks like a useful command but as Lex says it can only be used against an existing user. It also does not create a second schema under an existing user account as the original poster on oracle-l hoped.

Schema difference tool

I came across a post on the Oracle-l list by Jared titled "Schema Difference Tool" where Jared talks about Chris O'Sullivan's excellent tool T.O.Y.S. which can be used to test for differences in two database schemas. Jared http://www.impacttoys.com/index.htm - (broken link) points us to the tool and also to the fact that there is a free time limited download for testing but that you can download again.

I found out about Chris's tool quite a long time back and have always been very impressed with its abilities. I added a link to the tool on my links page quite a long time ago. This is what I said about it at the time:

"This is an excellent tool that allows you to manage your schema details. The tool can be used to capture schemas from more than one database and then from those schemas generate DDL to recreate the schema and also to compare two schemas. The tool is very fast and enjoys a lot of configuration options. The key to the success of this tool is the ability to compare a working production database for instance with a reference database and to generate DDL to alter the production database structure without loss of data so that it matches the reference database.
TOYS is in Beta test but seems solid and fast. A free time limited download is available. The web site contains a useful FAQ and comprehensive help is available with the software.

From a security perspective this tool is excellent for quickly capturing a schema definition including grants to be able to get a good picture of the structure off line. TOYS also offers a good online point and click interface to browse the captured schema data."


One thing I would add now to my security related points above is that T.O.Y.S. can also be used to check for differences between schemas if unauthorised changes are suspected and also if hacking attempts are also suspected.

This is an excellent, well written piece of software that is well worth looking at. http://www.impacttoys.com/download.htm - (broken link) Again TOYS can be found on Chris's website.

Does January 18th have special significance for Oracle?

I saw an interesting post in the Oracle-l list tonight, titled "Jan 18th". Patrice the poster asks if January 18th is a special day for Oracle as this is the day of the first of the quarterly scheduled patch releases and now it seems it is also he day that the new bigger merged Oracle / Peoplesoft company will be launched. Patrice points us to a news article that has been written by James Niccolai and is called "Oracle plans Jan. 18 'launch' for merged company".

Interesting reading for those interested in the merger, is there significance with the first patch release on the same day? - Who knows? - Maybe, maybe not!

We have moved

Just a quick note to say that we have moved yesterday from a small village in North Yorkshire to the Roman and Viking city of York also in the county of Yorkshire. My office is based at home so my company has also naturally moved to the city of York. The contact details page has been updated for my new address details.

Frank has an interesting post about the movie Troy

I was just browsing the net this evening on the hunt for anything remotely interesting and also about Oracle security and came across Frank Nimphius's post about his thoughts on Oracle security and how they relate to the movie Troy directed by Wolfgang Petersen.

You can find Franks post titled http://www.orablogs.com/fnimphius/archives/000773.html - (broken link) J2EE Security: What application security can learn from the movie Troy in his blog page.

This is great insight from Frank who has grouped his thoughts into a few groups of thoughts, "don't underestimate the attacker", "attacks come by surprise", "multiple layers of security is a better defence", "Good defence organizes itself long time before the attack occurs", "Attacking isn’t the privilege of the bad guys ", "Tradition doesn’t protect your assets", "Social engineering helped the Greek into Troy" and "There is no end".

Frank talks through the movie relating sequences of events in it with hacking and security. This is a great post and relates a non security item - a movie - to the security world of today’s computers and in our case the security of Oracle data and applications.

Great ideas! think like a hacker always, Frank does, this is good if you want to try and protect your data. A hacker will think like a hacker and use every effort to defeat you, security is not new as we can see from Franks post here, and the ideas are also not new as we can see from the ancient world example. Great post, again it is here.

Frank has a review of Bruce Schneier book "Beyond Fear"

I just saw a post from Frank Nimphius on the orablogs site about his reading of Bruce Schneier's book "Beyond Fear" over the Christmas period. Franks post is titled "Security: "Beyond Fear" by Bruce Schneier".

Frank has given a good insight into security in this short review - he talks about the need to plan for security not to just use the features of the software or language being used at the time. This is a key concept and should be obvious. You should always design security solutions based on the need to secure and what needs to be secured. I guess its like saying - I have added a great password policy to the database and audited all of he passwords, they are all strong and now no one can break in and steal the data. But the server is not secured and anyone can get access to the file system and read the raw data files. OK, it’s not a very good example but the point is a security solution needs to be designed from he ground up and ideally from day one of a project design not tacked on after project completion. The book talks about threat and counter measure, another angle that most implementers of security do not always consider.

Frank gives some great quotes from the book that he wrote down as he read it. His post again is here.

Nice article on SQL Injection

I found a nice introductory article on SQL Injection on the pen-test mailing list tonight. The paper was written to document the experiences of the author in penetrating a customers web based application.

As the author says there are no ground breaking discoveries in the paper but it does talk about real examples and experiences and does cover the subject quite well as an overview and introduction. The author says he gave this paper as a presentation to the customer involved and their reaction was well worth it.

The paper is based around IIS, ASP.NET and MS SQL Server but that doesn't really matter in this case as the paper is worth reading for the background and the techniques employed. The paper is called "SQL Injection Attacks by Example".

This paper is well written and gives a good example of the thought processes involved in compromising a system using SQL Injection, Steve then goes on to suggest how an attack could be taken further and also talks about how to protect against SQL Injection attacks. As I said this is a very good introduction paper to the technique and well worth reading.

Some updates to the Oracle default password list

I have just updated the Oracle default user list for two reasons. First is that I had added 21 default users with a trailing space in the name and password in all of the lists, sql, html, csv, xls and sxc. The second is that I have added two new default users. Thanks to Mohan for pointing out the space character issue.

The default users affected were CS, CN, RG, CE, WH, CZ, JA, JE, JG, FV, JL, ME, OE, EC, FA, GR, PA, PN, PV, QA, QP.

I have also added two new default users and their passwords that I found recently, VPD_ADMIN and DATA_SCHEMA.

All of the lists types available on the Oracle default users page have been updated as have the spreadsheet and the data installation script in the default password check script.

I would advise anyone who has used this script or the lists to download an updated version and run the checks again. Thanks again to Mohan for pointing these fixes to me.

Oracle security and content management

I have spent quite a bit of time recently thinking about content management systems that I could use to better manage my website. This has come to be an issue as the site gets larger, blog not included of course as this is already a type of content management system but only for the blog part of my site. Perhaps I have only become more aware of the issue because I have started a blog and have got used to the benefits of being able to change a template and then rebuild all pages that are part of the blog. This means I can have a standard template for all the pages and change it once and then re-generate all the pages. This is of course very useful and is of course used by most big sites already.

I began to think about the issues of adding new pages and new content to the rest of my site a while ago when I added a lot of new pages and menu items. I talked about this is an entry called "A lot of new pages on my site". My menu code is repeated in each page as the site uses tables and not frames therefore any new menu items means an edit to all pages. This is why I added a lot of new pages and menu items in one go..:-(, what I really need is a similar system to weblog software whereby I can use templates for the HTML structure and separate the content out and thereby make changes without needing to manually edit each individual page.

I had an interesting conversation with someone on email a few days before Christmas about this very subject, I thought I would talk about it here as it might be useful to others creating technical sites and also it might give some background to my own site and why it takes me a while to update it. Here is what I said to my friend:

"Yes, this is the key issue for me at present with the site as it’s
growing. I also have a huge amount of data and stuff to add to the site
- the main problem is finding time to do it all and do it properly. I
had a look at a few content managers and also thought about blog
software. The blog software is not really suitable though although could
possibly be used although I am not happy to go this way. I have been
planning to write some perl and templates to help me to make adding
content to the main part of the site easier. The blog is of course
already template based so no problem there. I started a perl admin
script recently to do this template based editing but I have not had
time to progress it far. It will do the following basic functions:



  • provide centralised menu for all admin functions


    • backups above and beyond the ISP backups

    • mail list admin

    • forum admin

    • sitemap generation

    • many more....


  • template maintenance and creation

  • define template structure / hierarchy

  • create new web pages

  • edit existing web pages

  • regenerate the site if templates have changed

  • more....




of course this is a simple content management system..:-) - the other
main problems involved is parsing the existing content and extracting
the text in the same format necessary to re-generate pages from
templates. This will also dictate the design of the template mechanism.

This will all take time."

It was suggested to look at mason, which I have done and I have also looked into postnuke, which looks quite good. But with both of these the problem is the learning curve is probably greater than the creation of my own template / perl scripts.

As I said in my above email, it all takes time and it’s probably not something that readers of my site will necessarily notice but as its the holiday period and its not really Oracle security specific I thought I might share these thoughts as they might give some insight into my sites workings.