Sunday, June 24, 2012

Assessing Oracle Critical Patch Updates (CPU) for the Database

  • Oracle Database Security Checklist - White Paper (2008) (pre-requisite read for below).

  • The second pre-requisite you should undertake before assessing any security risks is understand your environment!. This should include an understanding of all product installs, networks, firewall, user access and public facing infrastructure.

    Every 3 months Oracle release a Critical Patch Update (CPU) which usually applies to a majority of Oracle products. I haven't seen an instance where there was a Critical Patch Update that didn't apply to the Oracle Database. It is good practice to regularly review these advisories when they are released. The simplest form of assessing the Risk Matrix is to see what version and component of a product is affected, whether that product exists in their environment and then apply the CPU.

    To give you an idea of vulnerabilities and their seriousness there was recently the release of a zero-day attack for the Oracle database known as the TCP Listener Poison attack. To view the most recent advisories see the below link and select "View the most recent Critical Patch Update Advisory":

  • http://www.oracle.com/technetwork/topics/security/

    The April 2012 advisory (latest at time of this writing):

  • http://www.oracle.com/technetwork/topics/security/cpuapr2012-366314.html
  • For each product there is a Risk Matrix. This matrix is the basis for assessing risk to a component associated with a product. I'm only concentrating on the Database product however the same principles in assessing the CVSS (Common Vulnerability Scoring System) applies for other products also.

    I have provided a short summary on some of the key aspects in understanding risk matrix and meaning of the CVSS headings. Hopefully for high risks that gives people imperitive to patch and help present a case to management to undertake a patching exercise.

    Prior to the Risk Matrix Oracle provide a good summary of the vulnerabilities for a product. From the April 2012 Database advisory:

    Oracle Database Server Executive Summary

    "This Critical Patch Update contains 6 new security fixes for the Oracle Database Server. 3 of these vulnerabilities may be remotely exploitable without authentication, i.e., may be exploited over a network without the need for a username and password. 1 of these fixes is applicable to client-only installations, i.e., installations that do not have the Oracle Database Server installed.

    Below is an excerpt of the April 2012 Database Product Risk Matrix:

    Oracle Database Server Risk Matrix


    CVE#ComponentProtocolPackage and/or Privilege RequiredRemote Exploit without Auth.?CVSS VERSION 2.0 RISK (see Risk Matrix Definitions)Supported Versions AffectedNotes
    Base ScoreAccess VectorAccess ComplexityAuthen-
    tication
    Confiden-
    tiality
    IntegrityAvail-
    ability
    CVE-2012-0552Oracle SpatialOracle NETCreate session, create index, alter index, create tableNo9.0NetworkLowSingleCompleteCompleteComplete10.2.0.3, 10.2.0.4, 10.2.0.5, 11.1.0.7, 11.2.0.2, 11.2.0.3See Note 1
    CVE-2012-0519Core RDBMSOracle NETCreate library, create procedureNo7.1NetworkHighSingleCompleteCompleteComplete11.2.0.2See Note 2
    CVE-2012-0510Core RDBMSOracle NetNoneYes6.4NetworkLowNoneNonePartialPartial10.2.0.3, 10.2.0.4, 10.2.0.5, 11.1.0.7 

    source: http://www.oracle.com/technetwork/topics/security/cpuapr2012-366314.html

      Here is a quick guide to assessing the risk matrix before understanding the CVSS completely for your environment:

  • As already stated understand your environment completely! It would be an incomplete assessment if you're going to miss parts of it and a pointless exercise.
  • Assess whether the “Component” exists for any databases, if the package or privilege required exists and the base score’s value (a higher value obviously requires more attention).
  • Identify components and current products listed in the Risk matrix. As stated know your environment.
  • Are there any default usernames/passwords in any databases especially for this component? Vulnerabilities that require single or no authentication and make use of packages/privileges readily available are good candidates for patching. Of course follow the security checklist and change default username passwords and lock these accounts.
  • Base score – a base score of 10 means the product should be patched as fast as reasonably possible.
  • An Access vector of “Network” or “Adjacent Network” means the vulnerability is remotely exploitable. Assess the extent at which access is available to a database/server (number of people, other servers which can connect to the database/server).
  • Search the vulnerability # on the internet and examine the search results for more information. Vulnerabilities where exploits are available off the internet need higher consideration.
  • Even in the event of a component (say “Oracle Spatial”) has a locked MDSYS account and changed default password, there may be access to packages granted to other users or PUBLIC which are still accessible. Having locked a database account does not necessarily mean that vulnerable package is not exploitable.
  • The Risk Matrix only lists latest affected and supported versions. The vulnerability may apply for lower versions so the entire branch needs to be examined. For example, say 11.2.0.3 is listed as affected then assume the 11.2.x branch is affected however the patch is only available for 11.2.0.3 The question might then be raised on why the database is still on a lower patch set.

  • As recommended by the Security Checklist change default usernames/password (and lock if not required), and revoke privileges that are not required (eg, UTL_TCP). An example security vulnerability, a default database username/password that has EXECUTE on UTL_TCP (up to 11.x by default granted to PUBLIC) is a risk to the extent that once this is exploited all hosts accessible on the network are vulnerable to attack.
  • Understanding Risk Matrix columns

    The columns Base Score, Access Vector, Access complexity, Authentication, Confidentiality, Integrity and Availability are taken from the CVSS verion 2.0 open framework. Oracle have added the "Remote Exploit Without Authentication", and also modified the “Partial” definition to include “Partial+”.

    Oracle have also included a few of their own columns in addition to CVSS. These are Component, Protocol, Package and/or Privilege and Remote Exploit without Authentication. These are described here: Risk Matrix Glossary -- terms and definitions for Critical Patch Update risk matrices

    This document describes Oracle's use of the CVSS system: Use of Common Vulnerability Scoring System (CVSS) by Oracle

    Base Score

    “The CVSS base score defines the severity of the vulnerability and ranges between 0.0 and 10.0, where 10.0 represents the highest severity. Each risk matrix is ordered using this value, with the most severe vulnerability at the top of each risk matrix.”

    Access Vector

    The poorest score is "Network".
  • Local (L) - A vulnerability exploitable with only local access requires the attacker to have either physical access to the vulnerable system or a local (shell) account. Examples of locally exploitable vulnerabilities are peripheral attacks such as Firewire/USB DMA attacks, and local privilege escalations (e.g., sudo).
  • Adjacent Network (A) - A vulnerability exploitable with adjacent network access requires the attacker to have access to either the broadcast or collision domain of the vulnerable software.  Examples of local networks include local IP subnet, Bluetooth, IEEE 802.11, and local Ethernet segment.
  • Network (N) - A vulnerability exploitable with network access means the vulnerable software is bound to the network stack and the attacker does not require local network access or local access.  Such a vulnerability is often termed "remotely exploitable".  An example of a network attack is an RPC buffer overflow.

    Access Complexity

    The poorest score is "Low".
  • High (H) - Specialized access conditions exist. For example:
    In most configurations, the attacking party must already have elevated privileges or spoof additional systems in addition to the attacking system (e.g., DNS hijacking).
    The attack depends on social engineering methods that would be easily detected by knowledgeable people. For example, the victim must perform several suspicious or atypical actions.
    The vulnerable configuration is seen very rarely in practice.
    If a race condition exists, the window is very narrow.
  • Medium (M) - The access conditions are somewhat specialized; the following are examples:
    The attacking party is limited to a group of systems or users at some level of authorization, possibly untrusted.
    Some information must be gathered before a successful attack can be launched.
    The affected configuration is non-default, and is not commonly configured (e.g., a vulnerability present when a server performs user account authentication via a specific scheme, but not present for another authentication scheme).
    The attack requires a small amount of social engineering that might occasionally fool cautious users (e.g., phishing attacks that modify a web browser’s status bar to show a false link, having to be on someone’s “buddy” list before sending an IM exploit).
  • Low (L) - Specialized access conditions or extenuating circumstances do not exist. The following are examples:
    The affected product typically requires access to a wide range of systems and users, possibly anonymous and untrusted (e.g., Internet-facing web or mail server).
    The affected configuration is default or ubiquitous.
    The attack can be performed manually and requires little skill or additional information gathering.
    The “race condition” is a lazy one (i.e., it is technically a race but easily winnable).

    Authentication

    The poorest score is "None".
  • Multiple (M) - Exploiting the vulnerability requires that the attacker authenticate two or more times, even if the same credentials are used each time. An example is an attacker authenticating to an operating system in addition to providing credentials to access an application hosted on that system.
  • Single (S) - The vulnerability requires an attacker to be logged into the system (such as at a command line or via a desktop session or web interface).
  • None (N) - Authentication is not required to exploit the vulnerability.

    Confidentiality

    The poorest score is "Complete".
  • None (N) - There is no impact to the confidentiality of the system.
  • Partial (P) - There is considerable informational disclosure. Access to some system files is possible, but the attacker does not have control over what is obtained, or the scope of the loss is constrained. An example is a vulnerability that divulges only certain tables in a database.
  • Complete (C) - There is total information disclosure, resulting in all system files being revealed. The attacker is able to read all of the system's data (memory, files, etc.)
  • Integrity

    The poorest score is "Complete".
  • None (N) - There is no impact to the integrity of the system.
  • Partial (P) - Modification of some system files or information is possible, but the attacker does not have control over what can be modified, or the scope of what the attacker can affect is limited. For example, system or application files may be overwritten or modified, but either the attacker has no control over which files are affected or the attacker can modify files within only a limited context or scope.
  • Complete (C) - There is a total compromise of system integrity. There is a complete loss of system protection, resulting in the entire system being compromised. The attacker is able to modify any files on the target system.
  • Availability

    The poorest score is "Complete".
  • None (N) - There is no impact to the availability of the system.
  • Partial (P) - There is reduced performance or interruptions in resource availability. An example is a network-based flood attack that permits a limited number of successful connections to an Internet service.
  • Complete (C) - There is a total shutdown of the affected resource. The attacker can render the resource completely unavailable.
  • Additional Notes about CVSS terms

  • The difference between Integrity and Confidentiality: confidentiality relates to data, integrity can also relate to access controls and security, for example, an exploit that gives elevated privileges to a set of users compromises the integrity of a system.
  • Availability includes a reduction in performance of a system (denial of service for example).
  • Here is also a link to the Common Vulnerability Scoring System Version 2 Calculator. I've included it so you can see how a score is calculated. This does not include the Oracle created columns (for eg, there is no Partial+) but still worth having a look at.

    Thursday, June 7, 2012

    HTML reports using SQL*Plus

    Using SQL*Plus it is possible to create presentable HTML reports that improve readability over traditional ASCII based reports.

    A Simple report:

    set markup html on 
    set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off
    
    spool currentTime1.html
    
    select to_char(sysdate,'DD.MON.YYYY HH24:MI:SS') "Current Time" from dual
    /
    
    spool off
    


    Output:

    Current Time
    06.JUN.2012 18:56:29


    Taking a plain report and adding color to it and stylesheet:
    spool currentTime2.html
    
    set markup html on spool on entmap off -
     head 'The Current Time -
     ' -
     body 'text=black bgcolor=fffffff align=left' -
     table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'
    
    select 
    '

    ' || to_char(sysdate,'DD.MON.YYYY HH24:MI:SS') ||'

    ' "Current Time" from dual / spool off


    Output:

    Current Time

    06.JUN.2012 20:56:03



    Color highlighting columns in the output:

    set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off
    
    set markup html on spool on entmap off -
        head 'USERENV -
        ' -
        body 'text=black bgcolor=fffffff align=left' -
        table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'
    
    define COLOR_BEGIN_GREEN = '';
    define COLOR_END_GREEN = '';
    
    spool userenv.html
    
    select 
     sys_context('USERENV','SESSION_USER') "Session User",
     ''|| sys_context('USERENV','INSTANCE_NAME')|| 
     '' "Instance Name",
     '&COLOR_BEGIN_GREEN' || sys_context('USERENV','SERVER_HOST') ||
     '&COLOR_END_GREEN' "DB Hostname"
    from dual
    /
    
    spool off
    


    Output:

    Session UserInstance NameDB Hostname
    SYSlexusjupiter


    Displaying columns in different colors based on a condition. This can help highlighting an issue in a report:

    set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off
    
    set markup html on spool on entmap off -
        head 'The Current Time -
        ' -
        body 'text=black bgcolor=fffffff align=left' -
        table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'
    
    define BEGIN_WARNING = '';
    define END_WARNING = '';
    
    define BEGIN_OK = '';
    define END_OK = '';
    
    column server_host new_value server_host
    column instance_name new_value instance_name
    
    select 
     sys_context('USERENV','SERVER_HOST') server_host,
     sys_context('USERENV','INSTANCE_NAME') instance_name
    from dual
    /
    
    spool logMode.html
    
    prompt 

    &server_host..&instance_name - ARCHIVELOG MODE

    select decode(log_mode,'NOARCHIVELOG','&BEGIN_WARNING'|| log_mode||'&END_WARNING', '&BEGIN_OK' || log_mode||'&END_OK') "

    Archive Log Mode

    " from v$database / spool off


    Output:

    jupiter.lexus - ARCHIVELOG MODE


    Archive Log Mode

    NOARCHIVELOG


    Putting it all together, show init.ora prameters and comment, highlight problem areas in red otherwise in green:

    set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off
    
    define BEGIN_WARNING = '';
    define END_WARNING = '';
    
    define BEGIN_OK = '';
    define END_OK = '';
    
    column server_host new_value server_host
    column instance_name new_value instance_name
    
    select 
     sys_context('USERENV','SERVER_HOST') server_host,
     sys_context('USERENV','INSTANCE_NAME') instance_name
    from dual
    /
    
    set markup html on spool on entmap off -
        head 'INIT.ORA Parameters - &server_host..&instance_name -
        ' -
        body 'text=black bgcolor=fffffff align=left' -
        table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'
    
    spool initParameters.html
    
    prompt 

    &server_host..&instance_name - INIT.ORA Parameters

    select name "Parameter Name", value "Parameter Value", comments "Comments" from ( select name name, decode(value,null,'',value) value, case -- -- control_file_record_keep_time -- when name = 'control_file_record_keep_time' then case when value <=14 then '&BEGIN_WARNING Value of '||value|| ' potentially low for retention of RMAN backup information &END_WARNING' end -- -- destinations *dest*, dump and log destinations -- when name like '%dest%' then case when lower(value) like '%product%' then '&BEGIN_WARNING Destination possibly under the Oracle product directory '||value|| ' &END_WARNING' when lower(value) like '%temp%' or lower(value) like '%tmp%' then '&BEGIN_WARNING Destination possibly under a temporary directory!'||value|| ' &END_WARNING' end -- -- processes -- when name = 'processes' then case when value < 100 then '&BEGIN_WARNING '||name||' value '||value|| ' may be too low, see v$resource_limit max utilisation &END_WARNING' end -- -- skip_unusable_indexes -- when name = 'skip_unusable_indexes' then decode(value,'FALSE',value, '&BEGIN_WARNING '||value||' &END_WARNING') -- -- SPFILE -- when name = 'spfile' then decode(value,'?', '&BEGIN_WARNING No SPFILE set &END_WARNING', '&BEGIN_OK SPFILE in USE &END_OK') -- -- undo management -- when name = 'undo_management' then case when lower(value) != 'auto' then '&BEGIN_WARNING UNDO_MANAGEMENT is not AUTO '|| value || ' &END_WARNING' end end comments from v$parameter, v$instance ) where comments is not null order by name, value / spool off


    jupiter.lexus - INIT.ORA Parameters


    Parameter NameParameter ValueComments
    control_file_record_keep_time7Value of 7 potentially low for retention of RMAN backup info rmation
    skip_unusable_indexesTRUETRUE
    spfile/app/oracle/product/11.2.0/db_1/dbs/spfileABC.oraSPFILE in USE



    References:
    Originally inspired from:
    Creating HTML Reports with SQLPLus