Oracle 12c new password verify function

Even with Oracle Database 12c, the quality of the database passwords is not enforced by default. A password verify function with the corresponding password resource limits has to be developed individually. As a basis one can use the script  utlpwdmg.sql to setup the default password resource limits. The script is provided by Oracle and is used to update the default profile. It has been updated for Oracle Database 12c, but it still does not run automatically when creating a database. The 12c DBCA is missing a flag or a radio button to select something like extended standard security settings as this was known from 11g.

New Password Resource Limits

Without modification,  utlpwdmg.sql updates the profile DEFAULT, which is the default profile for all users. The following limits are the same as of Oracle Database 11g except a different password verify function.

Resource NameLimitDescription

PASSWORD_LIFE_TIME 180 Sets the number of days the user can use his current password.
PASSWORD_GRACE_TIME 7 Sets the number of days that a user has to change his password before it expires.
PASSWORD_REUSE_TIME UNLIMITED Sets the number of days before which a password cannot be reused.
PASSWORD_REUSE_MAX UNLIMITED Sets the number of password changes required before the current password can be reused.
FAILED_LOGIN_ATTEMPTS 10 Specify the number of failed attempts to log in to the user account before the account is locked.
PASSWORD_LOCK_TIME 1 Specify the number of days an account will be locked after the specified number of consecutive failed login attempts.
PASSWORD_VERIFY_FUNCTION ora12c_verify_function PL/SQL password complexity verification function to enforce password complexity.

In the comment of the script you find other password resource limits. Recommendations from Center for Internet Security (CIS Oracle 11g).

Resource NameLimit

PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 3
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 20
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION ora12c_verify_function

Recommendations from Department of Defense Database Security Technical Implementation Guide (STIG v8R1).

Resource NameLimit

PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 5
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function

New Functions

The function has been cleaned up by Oracle. As before, there are the two functions verify_function (10g) and verify_function_11G (11g). New there are four more functions for 12c, ora12c_verify_function and ora12c_strong_verify_function and two helper functions complexity_check and string_distance.

string_distance

This function calculates the Levenshtein distance between two strings ‘s’ and ‘t’ or a bit simpler how much do two strings differ from each other. The Levenshtein algorithms has already be used in the old verify_function_11G. It is now just a function for itself to be easier used in custom password verify functions.

differ := string_distance(old_password, password);

complexity_check

This function verifies the complexity of a password string. Beside the password string it accepts a few value to describe the complexity. Nothing basically new but it makes it a bit easier to define custom password verify functions.

  • chars – All characters (i.e. string length)
  • letter – Alphabetic characters A-Z and a-z
  • upper – Uppercase letters A-Z
  • lower – Lowercase letters a-z
  • digit – Numeric characters 0-9
  • special – All characters not in A-Z, a-z, 0-9 except DOUBLE QUOTE which is a password delimiter

Verify if the password has at least 8 characters, 1 letter and 1 digit.

IF NOT complexity_check(password, chars => 8, letter => 1, digit => 1) THEN
RETURN(FALSE);
END IF;

Verify if the password has at least 9 characters, 2 upper/lower case character, 2 digits and 2 special characters.

IF NOT complexity_check(password, chars => 9, upper => 2, lower => 2,
digit => 2, special => 2) THEN
RETURN(FALSE);
END IF;

ora12c_verify_function

This function is the new 12c password verify function. It enforce a similar respectively slightly stronger password complexity as verify_function_11G. verify_function_11G just checked for DB_NAME or ORACLE with 1 to 100 attached. e.g. oracle1 or oracle83. With the new function DB_NAME or ORACLE may not be part of the password at all. The following is verified

  • Password at least 8 characters
  • at least 1 letters
  • at least 1 digits
  • must not contain database name
  • must not contain user name or reverse user name
  • must not contain oracle
  • must not be too simple like welcome1
  • password must differ by at least 3 characters from the old password

ora12c_strong_verify_function

This function is provided to give stronger password complexity. It considers recommendations of the Department of Defense Database (STIG) with the following limits.

  • Password at least 9 characters
  • at least 2 capital letters
  • at least 2 small letters
  • at least 2 digits
  • at least 2 special characters
  • password must differ by at least 4 characters from the old password

References

Links all around Critical Patch Update:

Conclusion

Oracle Database 12c brings a slightly enhanced  utlpwdmg.sql script which can much easier be adapted to custom requirements. Nevertheless a DBA has to define a password verify function himself or run  utlpwdmg.sql. Oracle does not enforce passwords by default. It is recommended to define different profiles for different user groups e.g. DBA, App Users, Schema Owner etc. and to use as well a password verify function. The examples in  utlpwdmg.sql can and must be adapted to fulfill minimal security requirements.

Oracle released CPU / PSU July 2013

About a week ago Oracle has released the July Critical Patch Updates. Overall this CPU contains 89 new security fixes across several Oracle products like Database Server, MySQL Server, Sun Product Suite, WebLogic Server etc. For Oracle Database Server it does contain 6 fixes, but none of them is for client-only installation. 1 of these vulnerabilities may be remotely exploitable without authentication. According the Database risk matrix all supported versions are affected. Since the critical patch update does mainly fix vulnerabilities in the core RDBMS and Oracle executables, it is worth to have a closer look. I’ll test the critical patch update on my test systems as usual. But I do not expect problems, since MOS Note 1546428.1 does not yet list any known issues. Be aware that Critical Patch Update (CPU) are usually cumulative and do contain previous security fixes. If you do not regularly apply Critical Patch Updates, it is essential to check previous patch notes.

First Testing

The Critical Patch Update could easily be installed on Linux x86-64bit, but opatch does fail with a few warnings. None of them prevents a successful installation. According to the Known Issues section in the Patch ReadMe and the two Metalink Notes 1448337.1 and 854711.1 the output can be safely ignored.

First Findings

After installing the patch and run catbundle I could identify a few changes on the hidden parameters. The following hidden parameters have been updated on my test system

SQL> @hip _db_flash_cache_keep_limit

Parameter                  Session Instance   S I D Description
-------------------------- ------- ---------- - - - --------------------------------------------------
_db_flash_cache_keep_limit         217751120        Flash cache keep buffer upper limit in percentage

SQL> @hip _fastpin_enable

Parameter                 Session Instance   S I D Description
------------------------- ------- ---------- - - - --------------------------------------------------
_fastpin_enable                   217827585        enable reference count based fast pins

The following hidden parameter has been removed

SQL> @hip _db_flash_cache_keep_limit

Parameter                   Session Instance   S I D Description
--------------------------- ------- ---------- - - - --------------------------------------------------
_thirteenth_spare_parameter                          thirteenth spare parameter - string

But the strange thing is, the following new hidden parameters.

SQL> @hip _july2013_cpu_admin_user_fix

Parameter                    Session Instance   S I D Description
---------------------------- ------- ---------- - - - --------------------------------------------------
_july2013_cpu_admin_user_fix                          july2013 cpu admin user fix

So far I could not figure out the purpose of _july2013_cpu_admin_user_fix parameter. It look’s somehow like a temporary fix for something. I assume it will disappear in the next Critical Patch Update on october 2014.

CPU Release Dates

The next four Critical Patch Updates will be released at the following dates:

  • 15 October 2013
  • 14 January 2014
  • 15 April 2014
  • 15 July 2014

References

Links all around Critical Patch Update:

  • Oracle Critical Patch Update Advisory – July 2013
  • Patch Set Update and Critical Patch Update July 2013 Availability Document [1548709.1]
  • Critical Patch Update July 2013 Database Known Issues [1546428.1]
  • Opatch warning: overriding commands for target xxxx [1448337.1]
  • Oracle Critical Patch Update July 2013 Documentation Map [1563067.1]
  • Use of Common Vulnerability Scoring System (CVSS) by Oracle [394487.1]
  • Risk Matrix Glossary — terms and definitions for Critical Patch Update risk matrices [394486.1]
  • Oracle Critical Patch Updates and Security Alerts on OTN including links to Critical Patch Update since january 2005

Error installing Audit Vault Agent 12.1.1 on AIX

The Problem

During the setup of the current audit vault agent 12.1.1 on AIX, I’ve run into issues. Depending on the configuration of the AIX environment, the agent can not be installed at all.

avagent@host:/u00/app/avagent/ [avagent] java -jar agent.jar -d /u00/app/avagent/product/avagent
/u00/app/avagent/product/avagent/bin/agentctl[56]: LOGNAME: is read only
Error while executing command: [sh, /u00/app/avagent/product/avagent/bin/agentctl, fixperms]
avagent@host:/u00/app/avagent/ [avagent]

The problem is in the for loop on line 56 of agentctl where it tries to unset environment variables. Specifically, the environment variable LOGNAME can not be reset. On our AIX LOGNAME has been defined as read only in /etc/profile.

# Unset all env vars
#
for var in <code>{{EJS6}}</code>; do
  $ECHO $var | $EGREP "$passthru" > /dev/null

  # If no match, i.e. not a passthru then unset
  if [ $? -eq 1 ]; then
    unset $var
  fi
done

The Solutions

Change OS default profile

One solution would be to change the default profile on the OS. For this just open /etc/profile and comment out line 37. But I assume for most of us it is not an option to change the default profile.

# System wide profile.  All variables set here may be overridden by
# a user's personal .profile file in their $HOME directory.  However,
# all commands here will be executed at login regardless.

trap "" 1 2 3 
#readonly LOGNAME

Change the audit agent

The alternate solution is to update the agent.jar and fix agentctl. Get the current agent.jar from the audit vault server and extract the agentclt script.

jar -xf agent.jar bin/agentctl

Update the agentctl and add LOGNAME the the list of pass through variable on line 46.

# Passthrough env vars
# Note: we passthru any vars with "-" invalid character
#
passthru='^TZ$|^LANG$|^LC_|^JAVA_HOME$|^PATH$|^PS1$|^LOGNAME$|-'

Put the updated agentctl script back to the agent.jar and run a regular installation.

jar -uf agent.jar bin/agentctl

The Bugfix

The problem was reported to Oracle and can be tracked using the bug number 17058352.

By the way if you’re using multiline shell prompts agentctl will fail on the same code on any OS. Here you may simple workaround by setting a single line prompt.

Enterprise Manager Cloud Control 12c Release 3

Oracle just released Enterprise Manager Cloud Control 12c Release 3. (see Oracle Enterprise Manager Downloads ) for all supported platforms. Is assume this release is related to Oracle Database 12c which has been released about a week ago.

The new release can immediately be downloaded downloaded on OTN for the following platforms:

What’s New in 12.1.0.3

According the online documentation this release includes the following new features:

    Framework and Infrastructure

  • Simplified OMS Disaster Recovery
  • System Dashboard Enhancements
  • LDAP Integration Enhancements
  • Administrator Entitlement Summary Page
  • Auditing Enhancements
  • Enterprise Manager Command Line Interface With Scripting Option
  • Administrator Entitlement Summary Page
    Enterprise Monitoring and Incident Management Features

  • Flexible Editing of Administration Group Hierarchy
  • Metric Extensions Enhancements
  • All Metrics Chart Enhancements
  • Incident Manager Updates in 12.1.0.3
  • Target Down Root Cause Analysis
  • SLA Management Enhancements
  • Service Target Dashboard

Fusion Application Management Features

  • Oracle Fusion Applications Plug-in 12.1.0.4 Features

Database Management Features

  • Performance Diagnostics Enhancements

Middleware Management Features

  • Fusion Middleware Plug-in 12.1.0.4 Features
  • Application Replay Enhancements

Exadata Features

  • Exadata Plug-in

Siebel Features

  • Siebel Plug-in 12.1.0.3

Extensibility

  • Support for SQL Server 2012 (32-bit / 64-bit)

Cloud Management Features

  • Cloud Management Plug-in 12.1.0.5 Features
  • Cloud Management Plug-in 12.1.0.6 Features
  • Virtualization Management Plug-in 12.1.0.5 Features

Lifecycle Management Features

  • Change Activity Planner
  • Offline Patching – Uploading Patches to the Software Library Directly from Remote Patch Repositories

Resources

Links all around the Enterprise Manager, software, presentations and documentation:

Requirements

The requirements are still the same as for 12c release 1 and release 2. The following excerpt has been taken from Oracle® Enterprise Manager Cloud Control Basic Installation Guide.

  • OS requirements: Oracle Linux 6, Oracle Linux 5.x, Red Hat Enterprise Linux 5.x, SUSE Linux Enterprise 10, SUSE Linux Enterprise 11, Asianux Server 3
  • Hardware Requirments OMS (small) : 2 Cores, 4 GB RAM 6 GB RAM with ADPFoot 1 , JVMDFoot 2, 10 GB Hard Disk Space or 14GB Hard Disk Space with ADP, JVMD

Oracle 12c New Security Features

I’ve just uploaded the slides for my lecture Oracle 12c new security features, as I had promised this in my previous posts. (See also DOAG 2013 Datenbank or DOAG SIG Security). The slides is a consolidation of my presentations on the New Security Features in latest generation of Oracle Database and does no reflect 1:1 the slides at the different events.

Yet a short summary of new security features

  • Oracle Data Redaction, Advanced Security feature to prevent display of sensitive data.
  • Support for Secure Hash Algorithm SHA-2 for DBMS_CRYPTO and the password hash.
  • New unified auditing and audit policies.
  • Privilege Analysis, to analyse who is using which privileges and clean up authorization.
  • New administration privileges like SYSBACKUP, SYSDG and SYSKM to reduce the dependence on SYSDBA and improve separation of duty.
  • Database Vault persistent protections, DB Vault does not longer depend on executables.

There is much more just on security. The full list of new features is available in the New Features Guide 12c Release 1 (12.1). Oracle 12c is a release with so many security innovations since long time. So let’s discuss the good, the bad and the mad….

If you plan to take a training have a look at the Trivadis Oracle Database 12c Techno Circle.

Oracle Database 12c

Oracle still hasn’t officially announce the new Oracle Database 12c release. But since OTN database overview has been changed to 12c, I guess it is now somehow official. As rumors hinted, one of the main innovations are pluggable databases now named multitenant. Other important new features and products are:

  • Adaptive Execution Plans
  • Application Continuity
  • Automatic Data Optimization (ADO)
  • Data Guard Far Sync
  • Data Redaction
  • Global Data Services
  • Heat Map
  • Multitenant (Pluggable Databases)
  • Pattern Matching
  • SQL Translation Framework

The full list of new features is available in the New Features Guide 12c Release 1 (12.1)

I’ve already had the chance to look into the New Security Features. (See also DOAG 2013 Datenbank or DOAG SIG Security) Therefore I’ll post more details on New Security Features in the next day’s and weeks. If you already plan to take a training have a look at the Trivadis Oracle Database 12c Techno Circle. Or are you already planning an upgrade? Then it is worth to have a look at Mike Dietrich’s Upgrade, Migrate and Consolidate to Oracle Database 12c slides.

References and Links

Some of the links are currently just partially available. It looks like Oracle is updating different links respectively references.

Pivot query on Automatic Workload Repository

I’ve just tried to get a few information from the Automatic Workload Repository (AWR). I actually wanted to put together an overview of various system metrics from DBA_HIST_SYSMETRIC_SUMMARY to create a chart. Unfortunately the data is stored as name/value pairs and not in columns. So it’s time again to convert rows to columns. There are several more or less difficult solutions for any kind of oracle release. Enclosed I’ll show a simple variant for Oracle 11g with PIVOT.

In my query I’m interested in the metrics Current Logons Count, Process Limit Usage (%) and Session Limit Usage (%). A description of these and other metrics can be found in the EM 12c online documentation Database-Related Metrics Reference Manual. The AWR view DBA_HIST_SYSMETRIC_SUMMARY is explained in the Oracle Database Reference.

Simple query with name / value pairs.

col  METRIC_NAME for a25
alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';
select
    SNAP_ID, 
    END_TIME,
    METRIC_NAME, 
    MAXVAL 
from 
    DBA_HIST_SYSMETRIC_SUMMARY 
where 
    METRIC_NAME in ('Current Logons Count','Process Limit %','Session Limit %')
order by SNAP_ID;

   SNAP_ID END_TIME            METRIC_NAME                   MAXVAL
---------- ------------------- ------------------------- ----------
       589 03.06.2013 00:21:44 Process Limit %           31,7142857
       589 03.06.2013 00:21:44 Current Logons Count             110
       589 03.06.2013 00:21:44 Session Limit %           22,2222222
       590 03.06.2013 01:20:44 Process Limit %           14,8571429
       590 03.06.2013 01:20:44 Current Logons Count              51
       590 03.06.2013 01:20:44 Session Limit %           11,8055556
       591 03.06.2013 02:21:44 Session Limit %           11,6319444
       591 03.06.2013 02:21:44 Process Limit %           14,2857143
       591 03.06.2013 02:21:44 Current Logons Count              49
       592 03.06.2013 03:20:44 Current Logons Count              47
       592 03.06.2013 03:20:44 Process Limit %           13,7142857
       592 03.06.2013 03:20:44 Session Limit %           11,1111111
...

Lets query the same data with a PIVOT query.

select 
    *
from 
    (select 
        SNAP_ID,
        END_TIME,
        METRIC_NAME,
        MAXVAL
    from 
        DBA_HIST_SYSMETRIC_SUMMARY)
pivot
   ( max(MAXVAL) 
   for METRIC_NAME in ('Current Logons Count' ,'Process Limit %','Session Limit %'))
order by SNAP_ID;

   SNAP_ID END_TIME            'Current Logons Count' 'Process Limit %' 'Session Limit %'
---------- ------------------- ---------------------- ----------------- -----------------
       589 03.06.2013 00:21:44                    110        31,7142857        22,2222222
       590 03.06.2013 01:20:44                     51        14,8571429        11,8055556
       591 03.06.2013 02:21:44                     49        14,2857143        11,6319444
       592 03.06.2013 03:20:44                     47        13,7142857        11,1111111
       593 03.06.2013 04:21:44                     47        13,7142857           10,9375
       594 03.06.2013 05:21:44                     49        14,2857143        11,2847222
       595 03.06.2013 06:21:44                     48                14        11,2847222
       596 03.06.2013 07:21:44                     49        14,2857143        11,2847222
       597 03.06.2013 08:21:44                     48                14        11,2847222
       598 03.06.2013 09:21:45                     49        14,2857143        11,2847222
       599 03.06.2013 10:21:45                     47        13,7142857           10,9375
...

Et voilà, the result can now be used to create a fancy excel chart.

Other solutions with DECODE, CASE or SYS_CONNECT_BY_PATH are also possible. These would even run on older Oracle releases.

Fast lane to ORA-00600 service requests

There are several way’s to find information on internal oracle errors like ORA-00600 and ORA-07445. Looking up google is one of them, but not in any case the best one. If you are using Oracle Metalink you may just search for the ORA-00600 and the error code of first argument or use the ORA-600/ORA-7445/ORA-700 Error Look-up Tool [153788.1], which is available since several years. But sometimes you do not get the expected results and you have to open an Oracle service request to get more information, bug fix, workaround, justification that you really haft this issue, etc. It is then a bit cumbersome to add all kind of information and upload files to create the SR. Since a while Oracle provides simple troubleshooting tools in My Oracle Support. Rather than searching for the error you just have to upload the IPS package created with Oracle adrci. Based on the information in the IPS package, Oracle suggests possible causes / solutions. Optional one has the possibility to open an SR based directly on the IPS package. The troubleshooting tool can directly be accessed ORA-00600 or will be opened if you add ORA-00600 in the error message field when creating an SR. At the end your problem and you SR is probably not solved faster, but at least it is a bit easier to open an SR. 🙂

Similar troubleshooting tools are also available for ORA-07445, ORA-04030 and ORA-04031

Build the Package

Start ADRCI and set the proper ADR home

oracle@urania:~/[TDB01] adrci

ADRCI: Release 11.2.0.3.0 - Production on Mon Jun 3 10:54:39 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

ADR base = "/u00/app/oracle"
adrci> show home
ADR Homes:
diag/rdbms/tdb01/TDB01
diag/tnslsnr/urania/listener
adrci> set home diag/rdbms/tdb01/TDB01

List the last 5 incidents

adrci> show incident -last 5

ADR Home = /u00/app/oracle/diag/rdbms/tdb01/TDB01:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
----------- --------------------------------------- ----------------------------------------
2995358 ORA 700 [dbghmo_read_msgobj_mt_bygrpid] 2013-06-01 23:32:03.985000 +02:00
2932162 ORA 600 [3619] 2013-06-01 21:05:10.287000 +02:00
2911681 ORA 600 [3619] 2013-06-01 20:27:38.752000 +02:00
2891200 ORA 600 [3619] 2013-06-01 20:25:49.517000 +02:00
2851271 ORA 700 [dbghmo_read_msgobj_mt_bygrpid] 2013-06-01 18:23:27.147000 +02:00
First 5 rows fetched (*** more available ***)

For an Oracle SR we need a physical package of the incident. This can be generated with just a simple command.

adrci> IPS PACK INCIDENT 2932162 IN /tmp
Generated package 6 in file /tmp/ORA600361_20130603113930_COM_1.zip, mode complete

References

More information on ADR, Incidents and ORA-00600 troubleshooting can be found in the following MOS notes:

ADR

  • 11g Understanding Automatic Diagnostic Repository [ID 422893.1]
  • Database 11g: Quick Steps to Package and Send Critical Error Diagnostic Information to Support [ID 443529.1]
  • ADR Different Methods to Create IPS Package [ID 411.1]
  • 11g How to Unpack a Package in to ADR [ID 745960.1]
  • How to use the ADRCI interface to generate a SQL test case for an incident [ID 1330207.1]
  • Retention Policy for ADR [ID 564269.1]
  • 11g How To Find Out Trace Files Relevant To A Critical Error [ID 443536.1]

ORA-600/7445 Internal Errors

Use of DEFAULT_CLEANUP_INTERVAL

Following a question to the blog post Database Audit and Audit trail purging, I noticed something interesting about the DEFAULT_CLEANUP_INTERVAL parameter. On one hand, it is mandatory to initialize the audit trail and to define a DEFAULT_CLEANUP_INTERVAL, on the other hand, the parameter is not used at all. Oracle explains this in the MOS note Parameter DEFAULT_CLEANUP_INTERVAL of DBMS_AUDIT_MGMT.INIT_CLEANUP procedure [1243324.1]

Quote Oracle Support (MOS Note 1243324.1):

The dbms_audit_mgmt.init_cleanup parameter DEFAULT_CLEANUP_INTERVAL is not intended to be used to control the frequency of execution of audit management automatic cleanup. This parameter, although assigned a value during initialisation of audit infrastructure, is unused in current releases. However, in future releases it is intended to be used to control functionality which automatically partitions audit tables based on their archive frequency. This functionality already exists in the DBMS_AUDIT_MGMT package but is disabled in current releases. This is not a classified product bug, but expected behaviour.

According to the MOS Note DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL Not Clearing FGA Audit Trail When Using Last Archive Timestamp [1532676.1] it could be a no go for audit purging if DEFAULT_CLEANUP_INTERVAL has not or never been. Conclusion initialize the audit trail and define a value for the default cleanup interval but manualy setup a purge job.

I’m curious what Oracle plans for the future. Unified and self purging audit trail 🙂

Reference

A few Metalink Notes related to Audit and Audit Management.

  • Master Note For Oracle Database Auditing
  • Known Issues When Using: DBMS_AUDIT_MGMT
  • How to Truncate, Delete, or Purge Rows from the Audit Trail Table AUD$
  • DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL Not Clearing FGA Audit Trail When Using Last Archive Timestamp
  • Parameter DEFAULT_CLEANUP_INTERVAL of DBMS_AUDIT_MGMT.INIT_CLEANUP procedure