I would like to draw your attention to a particularly dangerous security vulnerability, which was recently published by David Litchfield.

How dangerous is the vulnerability?

Any database user, who has “create session” privilege, which means, who can log into the database, can use the security hole to execute any OS command in the ownership of the oracle database owner. This means, that both denial of service as well as access to all data is exposed.

Which versions are affected?

Affected are database versions 10.2.0.4 (incl. 10.2.0.4.3 containing latest security patches as of January 2010) as well as 11g (incl. 11.2.0.1).

What can I do to close this security vulernability?

You can revoke privileges from PUBLIC:

revoke execute on dbms_java from PUBLIC;
revoke execute on dbms_java_test from PUBLIC;
revoke execute on “oracle/aurora/util/Wrapper” from PUBLIC;
grant execute on sys.dbms_jvm_exp_perms to IMP_FULL_DATABASE;
grant execute on sys.dbms_jvm_exp_perms to EXP_FULL_DATABASE;
revoke execute on sys.dbms_jvm_exp_perms from PUBLIC;

If you are using a third party vendor application, you should contact your vendor to check compatibility with revoked privileges or test before implementing in production.

I just came back from the DOAG Conference 2009, the german Oracle user group conference in Nürnberg where I had a speech about RAC PreProduction Testing. I have uploaded the slides and the paper to the papers section. At this time, the presentation is available in german only.

Oracle has released the October 2009 Patch Set Update (PSU) which contains several interesting news:

• first PSU, which is available for Grid Control 10.2.0.5
• seperate PSU Patches for Clusterware (CRS)
• Patch Set Updates are now released for ALL non-Windows platform whereas previous PSUs were released for special platforms (e.g. Linux Itanium) on request only.

More info can be found in Metalink Note 854428.1.

It is not very known that you can run into serious problems if you run Linux x86-32bit with a big amount of RAM installed, if using RHEL below 5. The official name for the issue is called “Low Memory Starvation”. The best solution is to use x86-64bit to be able to address the whole amount of RAM efficiently.

However, if that is not feasible, then make sure that you at least run the hugemem kernel if you use RHEL < 5. In RHEL5-32bit, the hugemem kernel is default.

A quick demonstration about what can happen if you don´t use hugemem kernel is shown here:

We realized that RMAN backup is taking more than 24 hours. Querying v$session, we find out that
one session is in ACTION "STARTED", whereas the other sessions are FINISHED.

SQL> select program, module,action
      from v$session
      where username = 'SYS' and program like 'rman%'
/      

PROGRAM                    MODULE                       ACTION
-------------------------- ---------------------------  -------------------
rman@ora-vm1 (TNS V1-V3)    backup full datafile        0000078 FINISHED129
rman@ora-vm1 (TNS V1-V3)    backup full datafile        0000272 STARTED16
rman@ora-vm1 (TNS V1-V3)    backup full datafile        0000084 FINISHED129
rman@ora-vm1 (TNS V1-V3)    rman@ora-vm1 (TNS V1-V3)
rman@ora-vm1 (TNS V1-V3)    rman@ora-vm1 (TNS V1-V3)    0000004 FINISHED131
rman@ora-vm1 (TNS V1-V3)    backup full datafile        0000092 FINISHED129

Then we check the SID,serial# from v$session of this session and also query the UNIX PID from v$process.spid

SQL> select sid,serial# from v$session where event like 'RMAN%';

       SID    SERIAL#
---------- ----------
      4343       5837

We activate SQL Tracing for this session to determine its activity:

SQL> select spid from v$process where addr =
   (select paddr from v$session where sid = 4343);

SPID
------------
1681

SQL> begin dbms_monitor.session_trace_enable(4343,5837,true,true);
  2  end;
  3  /

However, no trace file gets created. Then we start tracing system calls with strace:

ora-vm1:# strace -fp 1681
attach: ptrace(PTRACE_ATTACH, ...): Operation not permitted

“Not permitted”? – Although I am connected as root?

ps -ef|grep 1681
oracle    1681 1582  0 Aug24 ?        00:00:09 [oracle] <defunct>

The linux command “ps” reports the server process as “defunct”.

ora-vm1:/usr/oracle/admin/labo/udump$ ps -ef|grep 1582
oracle   1582 21578  0 Aug24 ?        00:00:02 rman oracle/product/10.2.0/bin/rman nocatalog
oracle   21663 1582  0 Aug24 ?        00:00:01 oraclelabo (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   21665 1582  0 Aug24 ?        00:00:03 oraclelabo (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   1681 1582   0 Aug24 ?        00:00:09 [oracle] <defunct>
oracle   21691 1582  0 Aug24 ?        00:01:36 oraclelabo (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   21695 1582  0 Aug24 ?        00:01:41 oraclelabo (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   21793 1582  0 Aug24 ?        00:01:30 oraclelabo (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

Next, I checked logfile /var/log/messages.1 and realized that the kernel out-of-memory killer (OOM) killed this PID because of low memory starvation.

/var/log/messages.1:
Aug  24 22:32:44 ora-vm1 kernel: Out of Memory: Killed process 1681 (oracle).

I have recently encountered a problem at a customer site, where the database instances resource utilization was so high that the application did not work anymore. Version was 10.2.0.4 on Linux with Oracle Recommended Generic Patches installed.

Unfortunately, the customer decided to bounce the instance, so that there is no possibility for intensive diagnosis. However, ASH report shows that a dozen sessions either waited on Wait event “cursor: pin S” or were active (ON CPU) without any SQL_ID. An AWR Report showed:

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
cursor: pin S                   140,036,615      24,833      0 ######      Other
CPU time                                             19          72.9
log file sync                         8,767           8      1   30.2     Commit
log file parallel write               9,039           8      1   29.9 System I/O
control file parallel write           1,269           5      4   20.2 System I/O

Oracle Support confirmed that this is Bug 6904068 High CPU usage when there are “cursor: pin S” waits. We have filed a backport request for it, as there is no patch for our platform available yet.

I have come across a short post on Pete Finnigan´s Oracle Security Weblog, who informed about the release of new Metasploit modules usable for penetration testing of Oracle databases.

What is Metasploit?

Metasploit is a framework, which enables automatic utilization of all kinds of exploits to test security of a system. Among others, there is an Oracle module.

To get some idea about what is possible, watch this: Attacking Oracle with the Metasploit Framework Shmoocon Firetalk Demo Video. In a very impressive 5 minute video, the presenter demonstrates how to use Oracle Listener version identification, SID brute force, well known username/password combinations (e.g. scott/tiger), gets access to scott, privilege escalates to dba, plants a java class to exec os commands, etc… You get the idea….

This will be something to watch out for, because it will enable script-kiddies to attack badly secured databases connected to the internet, or well trained rogue internal employees to attack databases, which do not have critical patch updates for well known security vulnerabilities installed.

A reuters report about this new release can you find here.

Update 2009-08-13: The metasploit developer has uploaded new demo videos of how to hack an oracle database with metasploit.

On July 14th Oracle announced on MetaLink the release of a new patching strategy for the Oracle Database.

The new Patch Set Updates (PSU) will contain cumulative patches, which contain recommended bugfixes. They will be provided on a the same quarterly basis as the Critical Patch Update (CPU), therefore release months will be January, April, July and October. The Patch Set Update will be described in the release version. E.g. 10.2.0.4.1 will be the first Patch Set Update (PSU), 10.2.0.4.2 the second PSU etc.

As described in the release information, the 10.2.0.4.1 PSU Patch (Patch 8576156) contains all the recommended patch bundles up to July 2009 (Generic, CRS, RAC, Services, DataGuard) as well as the Critical Patch Update July 2009. Moreover 5 additional critical bugfixes are included. OPatch version 10.2.0.4.7 is required for installation of PSU 10.2.0.4.1 and the PSU is rolling installable on RAC environments without downtime.

Later PSU patches can be installed on either the base release or on top of any previous PSU. For example, PSU 10.2.0.4.3 can be installed on top of Base 10.2.0.4.0, PSU 10.2.0.4.1, PSU 10.2.4.0.2.

As already mentioned, the customer has the option to install security patches only by installing the quarterly Critical Patch Update or to install security plus non-security bugfixes by installint the Patch Set Update (PSU). As the PSU 10.2.0.4.1 already contains Critical Patch Update July 2009, the documentation states that future security patches are recommended to be installed not by CPU Patches but through PSU Patches.

Further information can be found in these MetaLink Notes:

854428.1 – Intro to Patch Set Updates (PSU)
850471.1 – Oracle Announces First Patch Set Update For Oracle Database Release 10.2
8576156.8 – Bug 8576156 10.2.0.4.1 Patch Set Update (PSU)
854473.1 – Known Issues with this Patch Set Update 10.2.0.4.1

I have added a new book review to my bookshelf: HOWTO Secure and Audit Oracle 10g and 11g – Ron Ben Natan

I have added a new book review to my bookshelf: Expert Oracle JDBC – J.R. Menon.

Today, I tried to get a nice, clean report about objects with reclaimable space from Segment Advisor. It is no problem to display the list in Enterprise Manager Grid|DB Control, but it is not so easy in SQL*Plus.

This is what i ended up with:

 SELECT
  segment_owner ,
  segment_name,
  round(allocated_space/1024/1024) ALLOC_MB ,
  round(used_space/1024/1024) USED_MB ,
  round(reclaimable_space/1024/1024) RECLAIM_MB    ,
  (1-ROUND((used_space/allocated_space),2))*100 AS reclaim_pct
   FROM TABLE(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'))
  WHERE tablespace_name IN ('TS_DATA')
AND segment_type         = 'TABLE'
AND segment_owner LIKE '%'
AND segment_name LIKE '%'
AND (reclaimable_space >= 1000000
         OR (((1-ROUND((used_space/allocated_space),2))*100)) > 30)
ORDER BY reclaimable_space DESC