Pete Finnigan's Oracle Security Weblog

I have just had an email from Alex Kornbrust who has let me know that his Metalink hacking paper has been released today. The paper is titled "Metalink Hacking" and is available on Alex's white papers page. The paper gives some background and details on Alex's investigations into using Google hacking techniques against the Oracle metalink repository. Alex starts by telling us about the 42 security bugs he found and then gives some general background information to how metalink is used and what it is. he then details some of his searches and the results he found in some cases including security bugs. Alex finishes with some guidelines on what is a security bug in Oracles eyes and goes on to look at some denial of service issues. Finally he gives some hints for customers and Oracle employees on using metalink more safely.

This is an excellent paper that covers some unusual ways of finding security bugs in Oracle.

I just saw that Chris Cemper has also found the eweek article about Alex Kornbrust finding 42 security bugs in metalink. Chris's post on his blog is titled "42 bugs in Oracle Metalink - revealing sensitive customer data" and gives another perspective on the issue.

I have just seen that Lisa Vaas has released her news article about Alex Kornbrust finding 42 security bugs in Oracle products in the Oracle support database / website - Metalink. Lisa's article was published on Friday 27 May on eweek and is titled "Oracle Bug Database Susceptible to 'Metalink Hacking'".

The article starts with a quote from a conversation between Alex and Lisa:

"Within 42 hours I was able to find 42 bugs with security potential (e.g., denial of service, SQL Injection, �)," RDS' Alexander Kornbrust said from Germany via an e-mail conversation. "I stopped after 42 bugs."

Alex goes on to say that ten bugs are not fixed in the latest patch set - CPU April 2005. Oracle had not made a formal comment to Lisa on the reporting of the 42 bugs by Red Database Security but they did point out some inaccuracies in Alex's report. I do not believe that these inaccuracies take anything away from the seriousness of the issue though.

The technique Alex has used has been coined as Metalink hacking. This is a technique made famous by Johnny Long with his Google hacking exploits.

I have known about this research by Alex for some weeks as we have had conversations about it when Alex started to look at it. I have seen an early version of Alex's paper on the same subject and made comments so I was not surprised to hear from Lisa Vaas last week to ask for comments on this paper and Alex finding 42 previously unpublished Oracle security bugs. The fact that Alex was able to find 42 security bugs in a product support database is probably not surprising but it should not be. The technique of Google hacking has been known for a long time now as has the issues of information leakage. Oracle should be embarrassed by this revelation by Alex, to their credit they have responded quickly and blocked access to the data found by Alex. Alex let me know by email on Friday that all of the data he found has now been blocked. The problem for Oracle now is that they will need to search the whole of the Metalink database and close off any other bugs waiting to be found - or at least not make them public. Databases like Matalink are a great resource for customers but they also need to be appropriately policed for any potential information leakages on security issues.

The article on its first page gives a good example of the sort of leakage found by Alex. A customer had posted details of a bug where she was able to get logged in as SYS by running a scheduler job on 10g. The user had used an escalation of privileges exploit and given out the full source code for it, she almost certainly did not realise the seriousness of what she was reporting though. BUT someone in Oracle should not have allowed this to occur.

The first page of the article goes on to explain the search strings Alex used and also has some comments from Aaron Newman of Application Security Inc. The second page of the article goes on to talk about the most serious issue in Alex's discoveries. This is an explanation of the fact that the listener is not password protected by default before 10g. The issue Alex found is a conversation between an Oracle employee and a customer where the employee says words to the affect of "no one likes to password protect the listener, I am the first person to help customers turn off password protection", as Alex says this is a funny comment for an Oracle employee to make. The employee emphasises that listener security and password protection is very important but says that she was one of the first people to "help" customers turn it off in the past. As Aaron says "If Oracle employees are out there turning security off, it's a little bit scary.". The article then goes on to quote me! and finishes with some comments on how Metalink can be made more secure.

Of course this same issue of Metalink hacking is also a potential issue for other large companies that use similar databases. Microsoft springs to mind but this issue does not just have to apply to software vendors.

I saw a new paper on Alex's site the other day titled "Change XMLDB Ports" that explains how to change the default port numbers for HTTP and FTP in XMLDB. The paper is short and sweet and includes example PL/SQL code to change the port numbers. I have a simple paper on my site that shows a different way to disable the ports completely. This paper is called "How to Stop / shutdown the ftp and http ports (2100 and 8080) on 91R2". Alex's paper does not allude to the fact that his code can also be used to disable the ports completely as well. This can be done by setting the port numbers to 0 (zero) in each PL/SQL call. The ports are enabled by default and should be disabled if the functionality is not needed. There are exploit codes published to attack these ports. There is also a Roby Sherman paper on the same subject. There is a link to it on my Oracle security white papers page - search on Roby with CRTL-F in the page.

I have been catching up on bookmarks I have made over the last couple of weeks or so this evening and I marked a blog entry on the JHeadstart and ADF blog titled "Cool New Features in Upcoming JHeadstart Release!" on May 17. The list of new features planned for a release in June this year included some security items such as support for role based authentication. Have a look at the features list if you use JHeadstart.

I was browsing Frank Nimphius's blog last night and found quite an interesting post there titled "Java: Calling a stored database procedure from Java". This is an interesting post that, although is talking about an error that Frank resolved also talks about and gives an example for an authentication routine for a custom JAAS LoginModule. Franks gives examples and works through his problem and even mentions that has taken care of possible SQL Injection issues. He also said at the end that he intends to make his JAAS modules with OC4J and J2EE declarative security, document and publish on OTN. It will be worth watching out for that.

I just saw on Alex Kornbrust's site in the news and updates section that he will talk at the IT Underground conference October 12-13th in Warsaw and also he will talk at a DOAG regional meeting in Freiburg on 21 June 2005. Alex will talk about Oracle root kits and database security. If you are in either area it should be worth listening. I guess the DOAG talk will be in German the IT Underground, I am not sure but I guess English as the IT Underground site is in English.

I just got an email from Scarlet to let me know that her interview with Mary Ann Davidson, Oracle's chief security officer has been published. I mentioned this interview in a recent post when I discussed the questions I had passed to Scarlet. My post was titled "IDG were scheduled to interview Oracle's CSO".

Scarlet's interview was made in London and she starts by telling us that Oracle are working to lower the number of security issues in their software and also that the number of attacks against data are increasing and also hackers are becoming more creative. The article is titled "Q&A: Oracle's security head talks from the trenches". In some ways the answers to questions are a little strange, Mary Ann talks about comparisons with military scenarios and also gives advice for customers to tell their vendors to make products easier to screw down out of the box and also to ask how the software is built, do the vendors use secure coding practices. This to me is strange as she is the CSO of Oracle, is she inviting Oracle customers to ask these questions of Oracle?

Mary Ann did not give a satisfactory answer as to why there are a lot of known security bugs still outstanding after long periods of time. Yes, I can see that some bugs need to be fixed for many platforms and also that its good practice to check for the same issue throughout the code base, but two years to fix security bugs classed as high risk? is this good practice?

I can see that the security of databases in general is getting tougher to resolve and the numbers of bugs are getting bigger so Mary Ann has a lot of work to control the tide. I like her thoughts on securing the database out of the box and getting customers to insist on security out of the box. This is a good plan, she is also right about fixing the configuration first as this is inevitably the easier way in for a hacker or malicious employee. This is what we did with the SANS book Oracle security step-by-step.

I can appreciate Mary Ann's problem space and this is a good interview, worth reading. Maybe my insight into Mary Ann's problem would be for Oracle to create two installer's one for developers and general users that is basically the same as the OUI is now and a second more useful installer for the security conscious customers that tied down all of the configurations that could make a database insecure. The problem space would then be reduced greatly to vulnerabilities and also to those configurations that could not be set for business reasons or those that had changed since installation. This would be a valuable extra for Oracle customers!

I got an email from Scarlet Pruitt a few days ago to say she was scheduled to interview Oracle's Chief Security Officer (CSO) where she said that as I was interested in the area of Oracle security did I have any questions that might be relevant to her discussion. I made a suggestion to ask two questions as follows:

"o - Why is it that certain researchers (for instance Alex Kornbrust and Esteban Martínez Fayó - there are others) have lists in total of over 100 unfixed security bugs on their web sites - some of which were reported 21 months ago, also some of which are high risk to customers. Why does it take Oracle so long to fix security bugs.

o - Does she plan to release more helpful information with each quarterly patch scheduled release such as information to help customers decide whether they are at risk if they do not patch quickly. This could include detailed lists of which products are vulnerable - I.e. for CPU April 2005 - and you run version 8.1.7 you should patch only if you run OID and Oracle HTTP Server."

It will be interesting to see if she managed to do the interview and also what he answers might be.

The excellent open source project Oracle Password Repository (OPR) has been updated to a new version 1.1.6 beta today 24 May 2005. The project can be found on sourceforge and the page is called "Project: Oracle Password Repository: Summary". Quoting from the page the project can be described as follows:

"OPR (Oracle Password Repository) is a Unix based secure tool for storage & retrieval of Oracle database passwords. By replacing hard coded passwords in scripts with a call to OPR, it helps keeping your Oracle environment secure and easier to maintain."

The software is great for getting rid of passwords that are held in source code files as hard coded passwords. Instead they are moved to a repository that is the essence of this software.

The changes are listed in the change log and are not major, there are documentation changes, output cosmetics and some changes to C function calls to improve security. Jan-Marten Spit, one of the authors has emailed me today to let me know of the updates. The code is purely beta status because of a lack of a range of test platforms. The beta status will be removed after testing has proved other platforms.

This software is well worth a look if you use batch processes or even on-line processes that currently require hard coded passwords to exist.

I saw Tom's post on his blog at the end of last week titled "success" and made a note to mention it here. The post talks about a common question asked of Tom, this is "how can I become really successful or more successful in my chosen career". Tom answers this well but the part of his post that I found interesting is the list at the end of the post of useful resources for people wanting to find good Oracle information or to participate in good Oracle question and answer sessions. Tom promises that his "sorry I have a backlog" message on asktom will be replaced with this list. He also promises that the list will grow as he finds more good resources. Check out the list in Tom’s blog and also check out Asktom for a growing list.

For those readers who follow the ever growing list of blogs posted by Oracle bloggers and Oracle watchers on orablogs.com, you will be glad to note that the site is back up.

I saw a post a few minutes ago by Brian Duff titled "We're Back!" that apologises for the fact the site has been down for quite a few days. I follow this site every day as there are a lot of good Oracle watchers and writers who's blogs are aggregated there.

I checked Brian’s site earlier in the evening and it was still down but his blog post is timed at 05:25 (not sure what time zone though) so maybe the DNS propagation has not completed. The reason orablogs was down was because of an IP address change.

I got an email from an online friend of mine a couple of weeks ago asking the following question:

"What would be most easy way to determine what users have access to
dba_users? I assumed only dba's had this and of course I was wrong as eg.
the dbsnmp account can list it."

This is a good question that i come across quite a few times. The reason it is a good question is because of misconceptions. people can think that only a "DBA" can access views and the data viewable from them if they are a DBA. Another good example is the misconception that users need the role CONNECT to, well connect!. This is obviously not true as they actually need the system privilege "CREATE SESSION" to be granted as the role CONNECT has far too many system privileges.

So back to the point, how do you find out which users can actually see the view DBA_USERS? - This is what I answered to my friend:-

The easiest way is to do this is to download my script who_can_access.sql and run it.

Here is an example for my system:-

SQL> @c:\petefinnigan.com\extracts\master_site\development\who_can_access.sql

who_can_access: Release 1.0.2.0.0 - Production on Sat Apr 30 18:07:02 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 => TEMP (ADM = NO)
                        User => WKSYS (ADM = NO)
                        User => CTXSYS (ADM = NO)
                        User => HACKER (ADM = NO)
                        User => SYSTEM (ADM = YES)
                        User => TEST01 (ADM = NO)
                        User => APP_EXAMPLE (ADM = NO)
                        User => TEST01_PRIV (ADM = NO)
                        User => SCHEMA_OWNER (ADM = NO)
                        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 => TEMP (ADM = NO)
                                User => WKSYS (ADM = NO)
                                User => CTXSYS (ADM = NO)
                                User => HACKER (ADM = NO)
                                User => SYSTEM (ADM = YES)
                                User => TEST01 (ADM = NO)
                                User => APP_EXAMPLE (ADM = NO)
                                User => TEST01_PRIV (ADM = NO)
                                User => SCHEMA_OWNER (ADM = NO)
                                User => REMOTE_OS_USER (ADM = NO)
                        User => SYS (ADM = YES)
                        User => OLAPSYS (ADM = NO)
                User => ORAPROBE (ADM = NO)
                Role => ADMIN_ROLE01 (ADM = NO) which is granted to =>
                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 => TEMP (ADM = NO)
                                User => WKSYS (ADM = NO)
                                User => CTXSYS (ADM = NO)
                                User => HACKER (ADM = NO)
                                User => SYSTEM (ADM = YES)
                                User => TEST01 (ADM = NO)
                                User => APP_EXAMPLE (ADM = NO)
                                User => TEST01_PRIV (ADM = NO)
                                User => SCHEMA_OWNER (ADM = NO)
                                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 => TEMP (ADM = NO)
                                User => WKSYS (ADM = NO)
                                User => CTXSYS (ADM = NO)
                                User => HACKER (ADM = NO)
                                User => SYSTEM (ADM = YES)
                                User => TEST01 (ADM = NO)
                                User => APP_EXAMPLE (ADM = NO)
                                User => TEST01_PRIV (ADM = NO)
                                User => SCHEMA_OWNER (ADM = NO)
                                User => REMOTE_OS_USER (ADM = NO)
                        User => SYS (ADM = YES)

PL/SQL procedure successfully completed.


For updates please visit http://www.petefinnigan.com/tools.htm

SQL> 

As you can see there is a hierarchical output. You can also specify that the output be sent to a file instead.

There are two other possible ways that a user could get access to the view. The first is if they have been granted the system privilege SELECT ANY DICTIONARY. This can be checked for with who_has_priv.sql - running it for my database gives:

SQL> @c:\petefinnigan.com\extracts\master_site\development\who_has_priv.sql

who_has_priv: Release 1.0.2.0.0 - Production on Sat Apr 30 18:10:48 2005
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

PRIVILEGE TO CHECK        [SELECT ANY TABLE]: SELECT ANY DICTIONARY
OUTPUT METHOD Screen/File                [S]: S
FILE NAME FOR OUTPUT              [priv.lst]: 
OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]: 

Privilege => SELECT ANY DICTIONARY has been granted to =>
====================================================================
        Role => DBA (ADM = YES) which is granted to =>
                User => SYS (ADM = YES)
                User => HACK (ADM = NO)
                User => TEMP (ADM = NO)
                User => WKSYS (ADM = NO)
                User => CTXSYS (ADM = NO)
                User => HACKER (ADM = NO)
                User => SYSTEM (ADM = YES)
                User => TEST01 (ADM = NO)
                User => APP_EXAMPLE (ADM = NO)
                User => TEST01_PRIV (ADM = NO)
                User => SCHEMA_OWNER (ADM = NO)
                User => REMOTE_OS_USER (ADM = NO)
        User => OSCAN (ADM = NO)
        User => PETE2 (ADM = NO)
        User => DBSNMP (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 => TEMP (ADM = NO)
                        User => WKSYS (ADM = NO)
                        User => CTXSYS (ADM = NO)
                        User => HACKER (ADM = NO)
                        User => SYSTEM (ADM = YES)
                        User => TEST01 (ADM = NO)
                        User => APP_EXAMPLE (ADM = NO)
                        User => TEST01_PRIV (ADM = NO)
                        User => SCHEMA_OWNER (ADM = NO)
                        User => REMOTE_OS_USER (ADM = NO)
                User => SYS (ADM = YES)
                User => OLAPSYS (ADM = NO)
        User => SQLINJECT (ADM = NO)
        Role => OEM_MONITOR (ADM = NO) which is granted to =>
                User => SYS (ADM = YES)

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL> 

Finally if the parameter O7_dictionary_accessibility=true (default before 9i) then if a user has the
system privilege SELECT ANY TABLE then they would also be able to see the view. To check if the parameter is set use my script check_parameter.sql:-

SQL> @c:\petefinnigan.com\check_parameter.sql

check_parameter: Release 1.0.2.0.0 - Production on Sat Apr 30 18:14:46 2005
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

PARAMETER TO CHECK            [utl_file_dir]: O7_DICTIONARY_ACCESSIBILITY
CORRECT VALUE                         [null]: FALSE
OUTPUT METHOD Screen/File                [S]: S
FILE NAME FOR OUTPUT              [priv.lst]: 
OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]: 

Investigating parameter => O7_DICTIONARY_ACCESSIBILITY
====================================================================
Name                  : O7_DICTIONARY_ACCESSIBILITY
Value                 : FALSE
Type                  : BOOLEAN
Is Default            : ***SPECIFIED IN INIT.ORA
Is Session modifiable : FALSE
Is System modifiable  : FALSE
Is Modified           : FALSE
Is Adjusted           : FALSE
Description           : Version 7 Dictionary Accessibility Support
Update Comment        :
-------------------------------------------------------------------------
value is correct

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL> 

It is false on my system which it should be. If it were TRUE then i could use who_has_priv.sql to find
any users with the SELECT ANY TABLE privilege:

SQL> @c:\petefinnigan.com\who_has_priv.sql

who_has_priv: Release 1.0.2.0.0 - Production on Sat Apr 30 18:16:13 2005
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

PRIVILEGE TO CHECK        [SELECT ANY TABLE]: SELECT ANY TABLE
OUTPUT METHOD Screen/File                [S]: S
FILE NAME FOR OUTPUT              [priv.lst]: 
OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]: 

Privilege => SELECT ANY TABLE has been granted to =>
====================================================================
        Role => DBA (ADM = YES) which is granted to =>
                User => SYS (ADM = YES)
                User => HACK (ADM = NO)
                User => TEMP (ADM = NO)
                User => WKSYS (ADM = NO)
                User => CTXSYS (ADM = NO)
                User => HACKER (ADM = NO)
                User => SYSTEM (ADM = YES)
                User => TEST01 (ADM = NO)
                User => APP_EXAMPLE (ADM = NO)
                User => TEST01_PRIV (ADM = NO)
                User => SCHEMA_OWNER (ADM = NO)
                User => REMOTE_OS_USER (ADM = NO)
        User => ODM (ADM = NO)
        User => SYS (ADM = YES)
        User => XDB (ADM = NO)
        User => MDSYS (ADM = NO)
        User => OSCAN (ADM = NO)
        User => WKSYS (ADM = NO)
        User => ZULIA (ADM = NO)
        User => CTXSYS (ADM = NO)
        Role => APPTEST (ADM = NO) which is granted to =>
                User => SYSTEM (ADM = YES)
                Role => APP_ROLE (ADM = NO) which is granted to =>
                        User => SCOTT (ADM = NO)
                        User => SYSTEM (ADM = YES)
                        User => APP_ROLE_TEST (ADM = NO)
        User => OLAPSYS (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 => TEMP (ADM = NO)
                        User => WKSYS (ADM = NO)
                        User => CTXSYS (ADM = NO)
                        User => HACKER (ADM = NO)
                        User => SYSTEM (ADM = YES)
                        User => TEST01 (ADM = NO)
                        User => APP_EXAMPLE (ADM = NO)
                        User => TEST01_PRIV (ADM = NO)
                        User => SCHEMA_OWNER (ADM = NO)
                        User => REMOTE_OS_USER (ADM = NO)
                User => SYS (ADM = YES)
                User => OLAPSYS (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 => TEMP (ADM = NO)
                        User => WKSYS (ADM = NO)
                        User => CTXSYS (ADM = NO)
                        User => HACKER (ADM = NO)
                        User => SYSTEM (ADM = YES)
                        User => TEST01 (ADM = NO)
                        User => APP_EXAMPLE (ADM = NO)
                        User => TEST01_PRIV (ADM = NO)
                        User => SCHEMA_OWNER (ADM = NO)
                        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 => TEMP (ADM = NO)
                        User => WKSYS (ADM = NO)
                        User => CTXSYS (ADM = NO)
                        User => HACKER (ADM = NO)
                        User => SYSTEM (ADM = YES)
                        User => TEST01 (ADM = NO)
                        User => APP_EXAMPLE (ADM = NO)
                        User => TEST01_PRIV (ADM = NO)
                        User => SCHEMA_OWNER (ADM = NO)
                        User => REMOTE_OS_USER (ADM = NO)
                User => SYS (ADM = YES)

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL> 

This is a good example of how to audit a database for insecure access to objects that could reveal information that would be useful to a hacker. In this case if this view can be read then a hacker can take the database users password hashes and crack them with a password cracker off-line.

This discussion could be taken much further of course. There could be a few reasons to ensure that users do not have access to this view, the first is as I have just indicated would be to ensure no one can get the password hashes. Another reason may be to ensure user lists cannot be created also for the purpose of illegitimate access, this time to brute force accounts, the view can also be used to glean other information about users that may be useful to hackers. The problem does not stop here though. The view DBA_USERS reads its information from base tables in the data dictionary, in this case including SYS.USER$ for the password hashes. This table should also therefore be checked for access in the same manner as described above. For other data in available in the view DBA_USERS such as the usernames then checks need to be made to see where else this can be got from. The same base table of course SYS.USER$ and other views such as ALL_USERS can be used to create lists of user accounts.

Clearly a simple question like "how can I find which users can access the DBA_USERS view" needs to be thought about carefully in the context of auditing a database for security.

Marcel-Jan emailed me the other day about his SQLGotcha tool (Marcel-Jan has an interesting tool on his site called SQL-Gotcha) and he also mentioned a good paper by Jeff Hunter about Oracle default passwords that explains the source and use of some of these passwords. The paper is titled "Oracle Created (Default) Database Users" and, as I said was written by Jeff Hunter. This paper is excellent and lists quite a lot of the default Oracle users and shows the known default passwords and also some details of what the account is used for and the features it supports and also in some of the cases describes how the accounts are created.

I also have a detailed list of Oracle default passwords and also a tool to check for them on my site.

I found an interesting paper last week on DBA Support about Oracle's random number generator. This is a package that can find uses in security or in cryptography. The paper is written by Steve Callan and is titled "Unwrapping Oracle's DBMS Packages: Understanding Oracle's Random Number Generator". The paper starts by confirming that the random number generator package DBMS_RANDOM should not be used to generate cryptographic keys as it is unsuitable. The paper then goes on to discuss why in detail. Steve gives some great examples and analysis. Page two of the paper talks about the installation scripts and then goes on to talk about the DBMS_RANDOM.STRING function with the 'P' option to generate cryptograms and even passwords. Steve closes by saying that he hopes to have surfaced some new features of the DBMS_RANDOM package and also given some insight into the numbers produced by this package.

This is a great paper and well worth reading.

I just got an email from Marcel-Jan to let me know that his SQL Gotcha tool is also on fresheat and is open source. I talked about this tool the other day in a post titled "Marcel-Jan has an interesting tool on his site called SQL-Gotcha". Marcel-Jan has recently updated this tool (version 2.0) to be RAC compliant and he has also placed it on freshmeat as SQLGotcha - Default branch. I have now downloaded the files and will install and have a play, this looks like a very useful tool to have.

It seems like an evening for reading non Oracle security papers. I seem to do this from time to time...:-). I like to read any technical paper especially those with internals info on Oracle as it is good to learn about as much of Oracles functionality as I can. It is amazing that I can read a paper that has nothing at all to do with security and I can find myself creating parallels or finding new possible security issues. I think security pops up in anything. If you want to stay secure with your Oracle databases then you cannot simply read papers or books with Oracle security in the title! To secure Oracle or indeed any software you need to understand that software and how it functions.

I saw Doug's paper mentioned in his blog entry posted on Saturday titled "Getting it right". The paper is called "Tuning parallel execution" and is a well written paper that covers the parallel execution features in the database. The paper starts off with some history and then a discussion of the architecture. It then considers configuration via the parallel parameters and then talks about the relevant dictionary views, monitoring the parallel adaptive multi-user algorithm, monitoring the SQL executed by slaves, a good discussion of tracing and wait events, finally Doug closes with some common sense. This is a good paper for those who do not know much about the parallel features. I have got and have read the O'Reilly book "Oracle Parallel Processing" by Tushar Mahapatra and Sanjay Mishra some years ago. This book was not too bad for an overview but is dated now. This new paper by Doug is a good start to parallel features.

I found a news aggregator a couple of weeks ago called Fenng's lilina news aggregator that I made a note of and have since visited a few times. I found it because I was looking for details of a particular Oracle event and ended up on this site. The news aggregator includes posts from slashdot, orablogs, OSNews, TheServerside.com and a few other blogs that I do not normally see. Although it is not a security based aggregator it is nice to find some other Oracle blogs I did not know about and also to read some general and OS based news.

I was looking for something a couple of weeks ago on Google and found by chance an interesting paper written by Jonathan Lewis on his site. I cannot remember what I was looking for at the time now but I made a note of Jonathan’s paper to go back and read it later. This evening I had a chance to do that.

I like papers that discuss internals and details that are not easily found elsewhere. The paper is called "Dual – SYS – and the not so obvious" and has a published date of April 2005.

The paper starts of with a claim from another site where someone said that in 10g that when accessing dual using the x$dual table that logical I/O can be reduced to zero CR gets from 3 and he (the other site author) then claimed that he could show how to do it in earlier versions. Jonathan said initially that this had to be wrong and decided to test the theory.

His example shows that the claim could be true as the logical I/O's do drop to zero from three on the keep pool where he placed it. But he noticed that consistent gets on the default pool had risen. He then went on to check if dual had indeed been placed in the keep pool. Jonathan shows how to check this with some great low level details. He shows that it is in the default pool. He then tries hard to get dual into the keep pool and manages to get a header block in there but the data block is still in the default pool. Jonathan postulates that it could be a bug or a design feature intending to do something clever for RAC and suggests that it looks like tables owned by SYS do not get their data blocks into the keep pool. Jonathan then finally checks to see if the logical I/O is reduced with DUAL's segment header in the keep pool. Read this excellent short paper to find out it the butler did it. This is a very interesting paper covering some great internals details and also showing some good investigative techniques. As Jonathan points out though hacking SYS objects is not a good idea even for performance reasons.

I was looking for something on google last week and whilst searching I came across a page on Marcel-Jan Krijgsman's site. I actually found it as a cached link on GotRoot.com but I went to look directly at Marcel-Jan's site.

Marcel-Jan is the original author of the scripts on my site for checking Oracle's default passwords that are still set. My page Oracle Default Password Auditing Tool includes this PL/SQL tool. Marcel-Jan's tool that I found is called SQLGotcha 2.0 and looks quite useful. I have not had time to download the scripts yet but I will do so. A brief description of its functionality is included in the page stated. Basically Marcel-Jan wanted to be able to easily trace sessions without finding the sid and serial#. You can specify username, machine, program, Unix PID or even a table that is being accessed. The tool also has a waiting mode and can trace standard SQL Trace or event 10046 tracing. The tool can be used to keep track of traces that have been started and also to find the file name. Marcel-Jan warns that using the tool can be costly in terms of querying the dictionary. I like the idea that you can specify the tool should wait for a particular session to start-up and it will then trace it.

I am particularly interested in trace tools and tracing sessions as trace can be used to learn more about how Oracle or applications operate. This can be very useful for security investigations. I have written a paper some time back that describes many ways to set trace for your own session, others sessions and also at various different levels. It also covers all the known ways to enable and disable trace. The paper is called "How to set trace for others sessions, for your own session and at instance level".

I saw an interesting post on the Oracle-l mailing list a week or so ago and made a note to talk about it here. The thread is titled "select only user causing locks?" and the first post is here. The poster said he has a user that can only select from objects but he discovered that the user was causing a lock. He did some research and found that a user only granted select access can issue a select for update to lock a table and also can even lock a table in exclusive mode. He went on to ask if this is true and is it possible to create a truly read only account.

One of the follow ups says : "And SELECT FOR UPDATE should be a separate object privilege next to =
SELECT." - This is a good point about separation of privilege. The original poster follows with a point that power users could sit there with read only user account and lock up an entire application effectively causing a Denial of Service.

Is this a security bug or a feature? - It does not make sense that a user could issue a select for update when he has no update privilege himself. But could Oracle accommodate this in its privilege structure?

The thread carries on in the May index of the Oracle-l list on freelists.org. The original poster suggests that "set transaction read only" stops the select for update in 9i but not the lock table statement. But ensuring that set transaction read only had been issued for all power users would not be easy to prevent them issuing a select for update statement.

I saw a post on the Amis blog yesterday by Marco Gralike titled "Old undocumented stuff - Recompiling with timestamp". Of course the word undocumented grabbed my attention!

The post is quite a good one displaying the ALTER PROCEDURE {procedure name} COMPILE TIMESTAMP {time to use} in action. The TIMESTAMP clause is the key one here. Marco works through a simple example that shows the use of this statement to compile his sample procedure with a timestamp in the past. Someone posted in a comment that this functionality is needed for export and import to work. Marco has tested this on 10.1.0.4 and says he has used it back in Oracle 7.0 / 7.1 days.

This is an interesting statement that you should be aware of if you monitor the database for changes made to the dictionary in particular changes to objects. If you use a method based on checking timestamps then you should be aware that these times cannot always be trusted. The same would apply if a hacker made direct changes to the timestamps in the base object SYS.OBJ$. The only method that is acceptable to monitor for changes to dictionary objects is to checksum them and to store the checksums outside of the database and use a baseline for subsequent checks to be made against.

Whilst browsing Alex's site this evening I also noticed that he has another new paper in his Whitepaper and Presentations titled "Oracle Default Ports".

This is a useful short paper that consists of a table of a lot of default network ports used by Oracle software. The table lists the port number, the product, the service that uses the port and how to change the default port number if it is possible.

I just saw that Alex Kornbrust has added a new paper on his site in the Whitepaper and Presentations section. This paper is titled "Fact Sheet about Oracle Mod_PLSQL Passwords".

This is an excellent short paper that describes details of the MODPLSQL and MOD_PLSQL passwords including details of the encryption algorithms used, the location of the encrypted passwords, how to change a password and also importantly how to decrypt a password. Alex finishes with some examples.

I Just wrote about a useful PL/SQL function that returns an MD5 sum of a string that I found on the DBA-Village mailing list. On the same mailing list i found an interesting pointer to a paper. The page on DBA-Village's site is titled "Inner look on Oracle latches". This points at a paper titled "Inner look on Oracle latches" and is written by David Gornshtein and Boris Tamarkin of Wisdom Force technologies Inc.

This is an excellent paper that goes into some great detail on latches. There are some internal looks at X$ tables and also some examples of use of oradebug. The paper covers "what is an Oracle latch", latches and enqueues, Oracle latch internals, how processes sleep/wakeup mechanism is working for the process waiting in latch, latch contention identification and finally problems with latches.

This is a good paper and very detailed. It is not about security but I liked it because of its internals and detailed information.

I have been a subscriber on the DBA-Village newsletter for quite a few years now and every Sunday night I get an email that lists at the top some useful links, a pole and all of the threads on the forum since the last email. DBA-Village is quite a useful site but to view the content you must register. The registration is free.

There was a useful link this week to a short PL/SQL function that can be used to return the MD5 sum of a string. Running a checksum of any data held in the database that is critical can be a useful thing to do. Also check summing the data dictionary or just the PL/SQL held there can also be a useful task to ensure that the dictionary has not been altered by a hacker for his own purposes.

The page on DBA-Village is titled "Returning MD5 hash", the function is clean and simple.

I just got an email from Tom Kyte to let me know about a link he had found on a blog listed on blogs.ittoolbox.com. The entry is titled "And You Thought Your Company Photos Were Bad?" and it says that a regular securitymonkey blog reader had sent this in.

The main item is a photo published in a magazine sent out by a UK train company. A close up of the photo reveals some great information goofs by showing usernames and passwords on a white board. This is a great example of how critical information can be leaked not just by newsgroup postings or on mailing lists. The key lesson to learn here is "why were the usernames and passwords on white board in the first place". This is not something new though. I have been in companies where similar info was listed on the walls on white boards.

Great blog post though.

I saw Richard Byrom's post on his blog a few days ago and made a note to take a look at it. The post is titled "Oracle Diagnostics Support Pack and the Applications Collection Tool (ACT)". Whilst I am not an Oracle E-Business Suite expert and this post by Richard is not security related I am still interested mainly because I like to know about diagnostics tools and anything that can be used to analyse an application or database. Richard points out

"I’ve found it an invaluable tool to check applications setups, compare setups between different instances/environments and diagnose specific problems with a particular module or process"

This is an important aspect of any security set up, that is to ensure that a database and application's configuration does not change. To ensure that it does not change you need to monitor it.

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

"quirky questions, tantalizing puzzles and cheeky challenges"

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

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

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

I saw one of Tom Kytes post the other day to his blog titled "Is being anonymous bad?" and read it with interest. One of the things I have talked about here in the past "An interesting example of information leakage posted to my blog entry" (I should point out that Tom emailed me to say this leakage has been now fixed).

Information leakage can take place when employees of your company’s joining newsgroups or mailing lists and inadvertently post comments to threads that include details of critical information such as hostnames, IP Addresses, usernames, passwords and many other useful details to hackers.

Tom's post is about whether anonymous postings are good or bad on mailing lists or newsgroups. Tom says being anonymous is a good thing and he does not have a problem with anonymous postings on AskTom.

This is an interesting thread, some companies do not allow employees to post to newsgroups or mailing lists with company email accounts or to use identifications that could show that there is a connection with the employer from the posting. This can be a good idea to prevent information leakage BUT it is OK stopping people posting from work but if all they do is post anonymously or from home then you have no control over them to ensure that they do not give out details still. A poster may be anonymous but may give out real information such as an IP Address and details of a particular piece of software being run (say Forms). What happens now if someone uses google hacking techniques to locate vulnerable forms servers? - I talked about "Information leakage and goole hacking" recently - They could find your companies server still and exploit it. If you have a policy to stop information leakage you need to ensure that if employees are banned from posting from work that they understand why and do not still leak details from home. They may think its safe because they are anonymous - but may not be after all.

I noticed that Lucas mentioned Jim Czuprynski's paper on flashback in his Amis blog post. I also noticed this paper yesterday and made a note of it. Jim's paper is called "Oracle 10g Availability Enhancements, Part 3: FLASHBACK Enhancements" and is the third part of a three paper series. This is a great paper as usual from Jim that starts with a review of flashback queries with examples and then goes on to talk about flashback version query with an illustration, then the new pseudo column ORA_ROWSCN is then discussed. Flashback transaction queries are discussed again with examples before Jim finishes with a short discussion of using SCN's vs timestamps and finally a discussion of the effects of the UNDO_RETENTION settings. This is a good paper.

I was searching information about flashback yesterday for updates to the course material for the SANS 6-day hands on Securing Oracle track (SECURITY 509: Securing Oracle) and came across a post to the Amis blog about flashback titled "Right a Wrong in 10g - Undo transactions using FLASHBACK_TRANSACTION_QUERY" and written by Lucas Jellema. This is an excellent post giving some good examples of the use of the UNDO_SQL column and also the psuedo columns versions_startscn, versions_endscn, versions_operation and versions_xid available in 10g. Lucas gives some good example code and also some insight into flasback that is not the same as the usual papers on the subject.

I saw an interesting post on Tug's blog yesterday titled "Which kind of developer are you? A software terrorist?". This is a post about a recent editorial by Allen Holub, the guy famous for writing many compiler and OS books. I have read his book Compiler design in C and I subscribe to his sporadic newsletter. Alan’s editorial "The Terror of Code in the Wrong Hands" - This is an interesting read and as Tug says the description of software terrorist is great. The issue of one employee (or otherwise) staying late and "fixing" or "improving" code whilst everyone is at home and then not telling anyone is an issue that we have all heard about. But from a security perspective this is an issue we should all be concerned about. What if someone changes parameters, application code or anything else in the database either "innocently" or on purpose? Would you know it had happened? Knowing the configuration of your database because it is stored and baselined and also ensuring application source code is controlled both in source repositories and also through change control and release mechanisms is important.

This is a good article, not intended to be Oracle security specific but relevant all the same.

I posted about a week ago about direct dictionary access in a post titled "Tom talks about direct dictionary editing" and then again in a post titled "Direct dictionary access again". A few days ago Paul Drake sent me some good advice on the latter post. I wanted to repeat this here for others to benefit. Here it is:

"Pete,

One method of handling application metadata is to segregate it to its
own tablespaces, which are altered to read only except during
application maintenance. If an event trigger is configured to send an
alert when such tablespaces are altered to read write, I think that
most any auditor would be satisfied.

A lot of my employer's lookup data is in small tables anyways, so I had
moved such tables into a 2 KB block size tablespace and set it to read
only. Many of these tables were marked as candidates for single table
hash clusters - so we accomplished several objectives in the move.

Paul"

Thanks Paul for some good advice on managing application repositories.

I was searching this afternoon for something about flashback and specifically whether it is possible to restrict a user from being able to issue select statements including "AS OF SCN". I happened upon a link to something written by Tom Kyte and discovered that it was an alpha chapter or extract of Tom Kyte's Expert Oracle 10g Edition. These two alpha pages are on Apress's site on a page titled "Alpha and Beta Books". The two extracts are:

Developing Successful Oracle Applications

Architecture Overview

Whilst not security related, these are an excellent peek at Tom's latest creation.

I found an interesting news item yesterday whilst looking for something else and made a note to talk about it here later. The news item is titled "Security Threats Branch Out From Windows To Mac And Linux" and is written by Antone Gonsalves. The article investigates how security holes are being found everywhere now. Alan Paller, the director of research for the SANS Institute makes some interesting points. Oracle's products are mentioned too in the article.

I have just made some updates to my scripts who_can_access.sql, who_has_role.sql and who_has_priv.sql that are available from my Oracle security tools page. The changes were to add two new parameters to the scripts. The first asks if you wish to exclude a user(s) from the report output, the second asks for the username to exclude. Here is an example for who_has_role.sql to check for users granted the DBA role and also to exclude the user SYSTEM.

SQL> @c:\petefinnigan.com\who_has_role.sql

who_has_priv: Release 1.0.3.0.0 - Production on Wed May 04 20:29:42 2005
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

ROLE TO CHECK [DBA]: DBA
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
EXCLUDE CERTAIN USERS [N]: Y
USER TO SKIP [TEST%]: SYSTEM

Investigating Role => DBA (PWD = NO) which is granted to =>
====================================================================
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => TEMP (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
Role => HACKER (ADM = NO|PWD = ) which is granted to =>
User => TEST01 (ADM = NO)
User => APP_EXAMPLE (ADM = NO)
User => TEST01_PRIV (ADM = NO)
User => SCHEMA_OWNER (ADM = NO)
User => REMOTE_OS_USER (ADM = NO)

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

The user SYSTEM has been removed from the output report. It is also possible to use % in the name specification so that I could for instance remove SYS and SYSTEM from the output as follows:

SQL> @c:\petefinnigan.com\who_has_role.sql

who_has_priv: Release 1.0.3.0.0 - Production on Wed May 04 20:31:57 2005
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

ROLE TO CHECK [DBA]: DBA
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
EXCLUDE CERTAIN USERS [N]: Y
USER TO SKIP [TEST%]: SYS%

Investigating Role => DBA (PWD = NO) which is granted to =>
====================================================================
User => HACK (ADM = NO)
User => TEMP (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
Role => HACKER (ADM = NO|PWD = ) which is granted to =>
User => TEST01 (ADM = NO)
User => APP_EXAMPLE (ADM = NO)
User => TEST01_PRIV (ADM = NO)
User => SCHEMA_OWNER (ADM = NO)
User => REMOTE_OS_USER (ADM = NO)

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

This new feature was added for use in the SANS 6 day hands on securing Oracle track that I wrote in case you are wondering why I added these changes.

If you have downloaded these scripts before please feel free to get them again, they are now all at version 1.0.3.0.0

I just noticed this evening that Alex has updated his Oracle Security exploits page to add 5 new exploits. These are:

"Buffer Overflow in DBMS_REPCAT_INSTANTIATE"

"Become DBA via DBMS_SYS_SQL"

"Stop remote Listener via lsnrctl added"

"Switch username to SYS after executing a database job via dbms_scheduler added"

"SQL Injection in Oracle Portal WWV_LOV"

Be aware of these issues, if you are not patched then you are vulnerable.

Today Alex Kornbrust has issued two new Oracle security advisories. These, like the last three issues on 26 May 2005 are not fixed as part of any Oracle released security advisory. Alex has found these two bugs in Metalink as part of his research for his "hacking metalink" article that is soon to be published. Alex has decided to publish these two advisories as the information for these bugs has been public for months. His Published Security Alerts page has been updated today 02-May-2005.

The first advisory "Fine Grained Auditing issue in Oracle 9i / 10g". The issue is where a SELECT is performed as SYS. There are two problems, the first is that the select statement as SYS is not audited and the second is that subsequent selects by any other user are also not audited. Alex goes on to show example code to demonstrate the issue. He also gives two workarounds, the first, do not run SQL on FGA objects as SYS and also flush the shared pool to activate auditing again. The second could give disadvantages to performance on the database.

The second advisory is "DBMS_SCHEDULER 10g SELECT user issue in Oracle 10g". This issue is that a user with CREATE JOB can run any job and after he has done so he has had the session_user switched to SYS. Alex gives example exploit code based on that available from metalink.

The big question is why were security advisories not made available from Oracle for these issues when they were fixed?

Alex has just added a new page to his site - Oracle Security Scripts - that looks like it will contain a few scripts in the future but for now just contains the one script. The first thing the page does is remind the reader of what a safe SQL script is. In simple terms this is one that uses base tables and absolute paths to objects. The reason to do this is to avoid being tricked by Oracle root kits installed or even just a few views altered by hackers to avoid detection. Alex wrote about Oracle root kits recently. Alex's SQL script is fairly simple and looks for alterations to the views DBA_USERS and the view ALL_USERS. These views may have been altered to hide hackers users.

I have been testing root kits in my database and when I run Alex's script as follows:

SQL> @a
SQL Script: Check for hidden database user

Version: 0.01
Autor: Alexander Kornbrust of Red Database-Security GmbH

These SELECT statements should never return anything
Keep in mind that an empty result does not guarantee that your
data dictionary is not modified. You can check the integrity of your data dictionary with Repscan



Invisible User in DBA_USERS
------------------------------
HACKER


no rows selected

SQL>

I find that my DBA_USERS view has been altered to hide the user HACKER. I saved the script to a local file called a.sql that I ran above.