Tag Archives: Trivadis Content

Blog posts also posted on the Trivadis Blog (TriBlog)

Secure External Password Store for RMAN

The draft version of this blog post is lying around for some time in my inbox. I’ve never found time to finish it. But due to a task in a project it’s about time to finish my notes on Oracle’s Secure External Password Store. Ludovico, a work colleague has already written a blog post about Removing passwords from Oracle scripts earlier this year. I would like to complement the topic and discuss a few points specifically in connection with RMAN Backup’s and a central RMAN catalog. The goal remains the same, getting rid of passwords with a minimal operational effort. The key element is the Oracle Wallet and the Secure External Password Store functionality.

Oracle Wallet

The Oracle Wallet is a PKCS#12 container used to store different kinds authentication and encryption keys. The wallet can thereby be used to store one or multiple of the following information:

  • Credentials for PKI authentication to the Oracle Database
  • Certificates for network encryption (SSL/TLS)
  • Oracle Advanced Security transparent data encryption (TDE) master encryption keys
  • Passwords for Oracle Database Secure External Password Store

Depending on the application there is one or more wallet. A wallet for an oracle client, a global wallet on the server, one wallet per database instance, a wallet for a database instance acting as a client or a wallet containing all information at once. It doesn’t really get easier when everyone is talking about Oracle Wallets without specifying what they are used for. For this reason, it is advisable to use different Oracle wallets for the various application cases. But more on that later. Oracle Secure External Password Store uses a client-side Oracle Wallet to store the password credentials.

The Secure External Password Store

Concept

With Secure External Password Store, Oracle stores the database credentials, ie username and password, securely in an Oracle Wallet. When initiating a database connection Oracle accesses the wallet and reads the credentials depending on the connect string. Since auto login is configured, no password is required to open the wallet and read the credentials. A password is only required to add, change, or delete credentials in the wallet.

The connect string is unique in the Wallet. Only one credential can be stored per connect string. Different credentials for the same database must be distinct by a different connect string.

Configuration

Create some directories for the SQLNet configuration and the wallet:

mkdir -p /u00/app/oracle/admin/$ORACLE_SID/network
mkdir -p /u00/app/oracle/admin/$ORACLE_SID/network/admin
mkdir -p /u00/app/oracle/admin/$ORACLE_SID/network/wallet

Define a TNS Alias in tnsnames.ora file for the catalog conection:

vi /u00/app/oracle/admin/$ORACLE_SID/network/admin/tnsnames.ora

CATALOG =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = urania1)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
    (SERVICE_NAME = TCAT01)
  )
 )

Create a wallet for Secure External Password Store:

mkstore -wrl /u00/app/oracle/admin/$ORACLE_SID/network/wallet -create
Enter password:         Manager01
Enter password again:  	Manager01

Create database connection credentials in the wallet:

mkstore -wrl /u00/app/oracle/admin/$ORACLE_SID/network/wallet -createCredential catalog rman manager
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:            
   
Create credential oracle.security.client.connect_string1

Modify the sqlnet.ora and add the WALLET_LOCATION and SQLNET.WALLET_OVERRIDE parameter to start using the Secure External Password Store:

vi /u00/app/oracle/admin/$ORACLE_SID/network/admin/sqlnet.ora

WALLET_LOCATION = 
   (SOURCE = 
      (METHOD = FILE) 
      (METHOD_DATA = (DIRECTORY = /u00/app/oracle/admin/$ORACLE_SID/network/wallet))
)
SQLNET.WALLET_OVERRIDE = TRUE

Use

Since we put the tnsnames.ora, sqlnet.ora and the wallet in an alternativ directories we have to set TNS_ADMIN before being able to use it.

export TNS_ADMIN=/u00/app/oracle/admin/$ORACLE_SID/network/admin

oracle@urania:~/ [TDB11] sqlplus /@catalog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 14 22:13:30 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "RMAN"

oracle@urania:~/ [TDB11] rman

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jul 14 22:13:09 2014

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

RMAN> connect catalog /@catalog

connected to recovery catalog database

RMAN>

Maintenance

The mkstore utility provide a bunch of commands to maintain the credentials within a wallet. Below you find some examples. More are available in MOS Note 340559.1.

Liste the contents of the external password store: 

oracle@urania:~/ [TDB11] mkstore -wrl /u00/app/oracle/admin/$ORACLE_SID/network/wallet -listCredential
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:            

List credential (index: connect_string username)
1: catalog rman

Modifying database login credentials in a wallet : 

oracle@urania:~/ [TDB11] mkstore -wrl /u00/app/oracle/admin/$ORACLE_SID/network/wallet -modifyCredential CATALOG rman manager
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:            

Modify credential 
Modify 1

A few use and special cases

There are a whole lot of cases where you can not save or share passwords. A few examples:

  • General applications where you want to authenticate “password free”, without setting up a PKI infrastructure.
  • Avoid RMAN Catalog passwords in backup scripts.
  • Avoid SYSDBA / SYSDG account passwords in an Oracle DataGuard environment.
  • Regular DBA batch jobs scheduled by cron. Remove passwords in shell and SQL scripts.
  • Simplify end-user experience. Use Secure External Password Store to save passwords instead of saving them in TOAD or SQL Developer.
  • Configure Application and Web server to use Secure External Password Store rather than save and maintain database credentials in the Application.
  • Application batch jobs scheduled by cron. Remove passwords in shell and SQL scripts.

Multiple RMAN Catalog Schemas

In my example above I did use the user RMAN to connect to the catalog. To be honest this user does only have a CREATE SESSION privilege. There are two RMAN Catalog Schema in my Catalog Database, RMAN11203 for 11.2.0.3.0 respectively RMAN12101 for 12.1.0.1.0.

SQL> select * from RMAN11203.RCVER;

VERSION
------------
11.02.00.03

SQL> select * from RMAN12101.RCVER;

VERSION
------------
12.01.00.01

Instead of adding each credential for any RMAN Catalog to the wallet one can use the proxy authentication. In this case you just have to grant the appropriate privileges to the user RMAN. Instead of distributing all RMAN catalog credentials in each Oracle Wallet, you need to maintain only one account and additionally a few proxy privileges in the RMAN catalog. This does of course also work for other applications.

SQL> alter user RMAN11203 grant connect through RMAN;

User altered.

SQL> alter user RMAN12101 grant connect through RMAN;

User altered.

As you can see below, it is now possible to specify the user or schema in the connect string. The authentication is done as user RMAN. Credentials are read from the Secure External Password Store.


export TNS_ADMIN=/u00/app/oracle/admin/$ORACLE_SID/network/admin

oracle@urania:~/ [TDB11] sqlplus [RMAN12101]/@catalog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 14 22:13:30 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "RMAN12101"

SQL> SELECT SYS_CONTEXT ('USERENV','SESSION_USER') FROM DUAL;

SYS_CONTEXT('USERENV','SESSION_USER')
------------------------------------------------
RMAN12101

SQL> SELECT SYS_CONTEXT ('USERENV','PROXY_USER') FROM DUAL;

SYS_CONTEXT('USERENV','PROXY_USER')
-------------------------------------------------
RMAN

oracle@urania:~/ [TDB11] rman

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jul 14 22:13:09 2014

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

RMAN> connect catalog ¨[RMAN12101]/@catalog

connected to recovery catalog database

RMAN>

Data dictionary view about all proxy connections.

col proxy for a6
col client for a10
col role for a5
set linesize 120
select * from DBA_PROXIES;

PROXY CLIENT    AUT AUTHORIZATION_CONSTRAINT            ROLE PROXY_AUT
----- --------- --- ----------------------------------- ---- ---------
RMAN  RMAN11203 NO  PROXY MAY ACTIVATE ALL CLIENT ROLES      DATABASE
RMAN  RMAN12101 NO  PROXY MAY ACTIVATE ALL CLIENT ROLES      DATABASE

Oracle Transparent Data Encryption

Oracle Transparent Data Encryption use as well an Oracle Wallet to store the TDE master keys. Unlike the Oracle wallet used for SSL for encryption or Secure External Password Store, this wallet is defined in the sqlnet.ora with the parameters ENCRYPTION_WALLET_LOCATION. But if the parameter ENCRYPTION_WALLET_LOCATION is not set Oracle will use WALLET_LOCATION to locate the wallet used for TDE. If both parameter are omitted Oracle will fallback to the default location. If the $ORACLE_BASE is set, this is “$ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet”, otherwise it is “$ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet”. In general it is a good practice to set both parameters in sqlnet.ora.


ENCRYPTION_WALLET_LOCATION = 
   (SOURCE = 
      (METHOD = FILE) 
      (METHOD_DATA = (DIRECTORY = /u00/app/oracle/admin/$ORACLE_SID/wallet))
)

WALLET_LOCATION = 
   (SOURCE = 
      (METHOD = FILE) 
      (METHOD_DATA = (DIRECTORY = /u00/app/oracle/local/tvdbackup/network/wallet))
)
SQLNET.WALLET_OVERRIDE = TRUE

There are the following combinations depending on how you use TDE together with the External Password Store.:

  • If TDE is not used you only have to set WALLET_LOCATION in sqlnet.ora used by RMAN.
  • If TDE is used for tablespace or column encryption, you have to set at least ENCRYPTION_WALLET_LOCATION in sqlnet.ora used by the database. The parameter WALLET_LOCATION is only required in sqlnet.ora used by RMAN. But if RMAN is used to open the database (eg. offline backup, restore etc), it will also require a correct ENCRYPTION_WALLET_LOCATION parameter. Otherwise the database respectively the encrypted tablespaces cannot be opened.
  • If TDE is used for transparent backup encryption, you will need in any case a correct setting of ENCRYPTION_WALLET_LOCATION and WALLET_LOCATION in sqlnet.ora used by RMAN.

See also MOS Note 1228046.1 Master Note For Transparent Data Encryption ( TDE ) and 1504783.1 Setting ENCRYPTION_WALLET_LOCATION For Wallets Of Multiple Instances Sharing The Same Oracle Home

Oracle SSL Authentication

A further special case is when an application uses SSL for encryption. Setting the sqlnet.ora parameter, SQLNET.AUTHENTICATION_SERVICES, specifies SSL and an SSL wallet is created. If this application wants to use secret store credentials to authenticate to databases (instead of the SSL certificate), then those credentials must be stored in the SSL wallet. After SSL authentication, if SQLNET.WALLET_OVERRIDE = TRUE, then the user names and passwords from the wallet are used to authenticate. If SQLNET.WALLET_OVERRIDE = FALSE the SSL certificate is used.

Possible solutions or workarounds:

  • Store the credentials in the SSL wallet and set SQLNET.WALLET_OVERRIDE accordingly.
  • Define a separate TNS_ADMIN and a sqlnet.ora for the user, application respectively OS environment, which wants to use secret store credentials.

See also MOS Note 340559.1 Using The Secure External Password Store.

Oracle Instant Client

Although the Oracle Instant Client does not contain tools to create or modify Oracle Wallets, it is able to access the wallet and read the required credentials. The wallet can be prepared on the database server and copied to the instant client. Thus no credential have to be stored in an application. But be aware, anybody who can access the wallet can also log into the database. The access to the wallet must be limited with the corresponding OS access privileges. See as well MOS Note 1441745.1 Using a Secure External Password Store with the JDBC Thin Driver.

Wallet deploy option

Depending on the use of the Oracle wallets, there are different ways to deploy them. In general it is a good practice to define a wallet for each database, regardless of whether TDE is used or not. The wallet location is defined with the sqlnet.ora parameter ENCRYPTION_WALLET_LOCATION. In addition, a generic wallet can be defined for Secure External Password Store with sqlnet.ora parameter WALLET_LOCATION. Alternatively you can create a specific wallet, which is only used by RMAN to lookup database credentials. This specific wallet is part of the RMAN backup scripts and will be activeted by setting an alternative TNS_ADMIN when executing the backup scripts. Such a wallet could be distributed with the backup scripts on all servers. Various RMAN catalog schema can easily be accessed by using proxy privileges.

A possible scenario with Trivadis TVD-Backup™:

  1. Configure default sqlnet.ora. eg. set ENCRYPTION_WALLET_LOCATION to /u00/app/oracle/admin/$ORACLE_SID/wallet and WALLET_LOCATION to /u00/app/oracle/network/wallet
  2. Use the instance specific wallet in /u00/app/oracle/admin/$ORACLE_SID/wallet for TDE
  3. Use the generic wallet in /u00/app/oracle/network/wallet for SSL, Password Store etc
  4. Configure your backup scripts eg. TVDBACKUP_BASE=/u00/app/oracle/local/tvdbackup
  5. Create a dedicated network and wallet directory for your backup tool eg. $TVDBACKUP_BASE/network/admin respectively $TVDBACKUP_BASE/network/wallet
  6. Set RMAN specific TNS_ADMIN before executing the backup script eg. export TNS_ADMIN=$TVDBACKUP_BASE/network; rman_exec.ksh -t TDB11 -s bck_inc0
  7. Deploy your backup script.

Licensing

The licensing of Oracle Secure External Password Store is at first sight somewhat obscure. Depending on which version of Oracle Documentation or Metalink Notes you’re reading, different kind of information are available. According to old documentation or Metalink Notes like 465460.1, 1084132.1 or 1628809.1 Oracle Secure External Password Store is limited to Oracle Enterprise Edition. This limitation has been removed from all available online Oracle documentation. Secure External Password Store can be used on all production editions. Therefore do not hesitate to use it. 🙂

Quote from MOS Note 340559.1 Using The Secure External Password Store:

The feature Secure External Password Store can be used without any restriction in all product editions, you do not require a license for the Advanced Security Option (ASO).

Conclusion

Oracle Secure External Password Store is a simple and secure solution to increase database security and to avoid clear text password in any kind of scripts or applications. Although Secure External Password Store is available for several Oracle releases, it is actually far too little used. The biggest challenge in connection with Oracle Secure external password store is the uniform distribution and configuration of sqlnet.ora. This blog post should give some ideas how Oracle Secure External Password Store could be used in conjunction with RMAN. Whether on the database server, application server or Oracle client, saving passwords is a thing of the past.

References

Although I haven’t seen many customers using Oracle Secure External Password Store, there are a lot of information, white papers and Metalink notes on this topic available.

Enterprise Manager Cloud Control 12c Release 4

A bit less than a year after Oracle Enterprise Manager Cloud Control 12c Release 3 has been released, Oracle has now released the latest version of its Enterprise Manager Cloud Control. The new release is immediately available for all supported platforms on OTN Oracle Enterprise Manager downloads or via the following direct links:

What’s New in 12.1.0.4

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

    Framework and Infrastructure

  • Updated Management Repository Page
  • Enterprise Manager Page Performance Page
  • Incident Management Actions in System Dashboard
  • BI Publisher 11.1.1.7 Integration
  • Integrated Installation of Business Information Publisher with Enterprise Manager 12c
  • Security Console
  • SSH Key Credential Support in Preferred Credentials
  • Global Preferred Credentials
  • Private Roles
  • Default and Bulk Apply Privilege Delegation Template
  • Fine Grained Aggregate Target Type Privilege Enhancement
  • Job System Enhancements
  • Enhanced Customization of Patching Procedures
  • Agent-Side Check Based Rule and Secure Technical Implementation Guide Compliance
  • Plug-in Management
    Services Management

  • New Create Wizard for Services
  • Support for REST and WADL for Web Service Beacon Tests
    Enterprise Monitoring and Incident Management Features

  • Advanced Thresholds Management
  • Metric Alert Message Customization
  • Metric Collection Schedule Enhancements
  • Time-Based Static Thresholds
  • Repository-Side Metric Extensions
  • Metrics Enhancements
  • Incident Management Updates in 12.1.0.4
  • Notification Enhancements
  • Enhanced Target Down Detection
    Fusion Application Management Features

  • Oracle Fusion Applications Plug-in 12.1.0.6 Features
    Database Management Features

  • Database Plug-in 12.1.0.6 Features
    Middleware Management Features

  • Fusion Middleware Plug-in 12.1.0.6 Features
    Exadata Features

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

  • Cloud Management Plug-in 12.1.0.8 Features
  • Chargeback and Consolidation Planner Plug-in 12.1.0.6 Features
  • Virtual Infrastructure Plug-in 12.1.0.1
  • Cloud Framework Plug-in 12.1.0.1
    Lifecycle Management

  • Scheduling Patch Preparation, Deployment, and Switchback

Resources

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

Requirements

The requirements are similar to those from release 2 and release 3. Only the hardware requirements have been adjusted slightly. The evaluation / simple installation needs something less, whereas the advanced installations requires in general more memory and disk space. 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, 6 GB RAM 8 GB RAM with ADPFoot 1 , JVMDFoot 2, 18 GB Hard Disk Space or 21 GB Hard Disk Space with ADP, JVMD

Trivadis CBO Days 2014

CBO Days 2014
The company I work for, Trivadis, organized again an exceptional event with top speakers in Zurich. This year’s focus will be on the Oracle Database query optimizer, also known as cost-based optimizer (CBO).

The query optimizer is not only one of the most complex pieces of software that constitutes the Oracle kernel; it is also one of the most unappreciated. Why? Taking efficient and sufficient advantage of the query optimizer, you will definitively need to understand how it works. This is exactly what we are aiming for at the CBO Days.

The event will take place from June 10 to 11 in Zurich. More Information on the Event including full agenda, registration, etc is available on the website of Trivadis.

Eclipse Jira integration and SSLHandshakeException

Basically I’ve solved this issue quite a while ago. Unfortunately I did not wrote any kind of documentation. That’s why I run again into SSLHandshakeException with my Eclipse installation on my new MacBook Pro, when trying to access our JIRA environment. As it now hopefully take a while until I buy again a new MacBook Pro, I thought it’s time to write a short note. 🙂

Cause

Our JIRA server is configured using a self signed certificate. The certificate is not part of the JVM certificate keystore.

Solution

The solution is simple, just add the certificate to the keystore. But which one? The eclipse installation details will help to identify the right JVM and keystore.

  1. Checkout the eclipse installation and configuration details in Eclipse > About Eclipse > Installation Details Somewhere there you find the java.home used by eclipse.
  2. Get the Certificat of your JIRA Web server. On Mac OS I’ve used Firefox to store the X.509 Certificate as PEM
  3. Set the JAVA HOME
  4. Load the certificate to the keystone using key tool
  5. Restarte Eclipse and start to configure your JIRA Repository

On my Mac OS X Mavericks I have to use the following JAVA_HOME. Password for the keystone is by the way changeit.

export JAVA_HOME=/Library/Java/JavaVirtualMachines/jdk1.7.0_45.jdk/Contents/Home/jre
sudo keytool -import -alias tvdvaps -file certificate.pem -keystore $JAVA_HOME/lib/security/cacerts

The full description can be found in FAQ JiraBuddy Eclipse Plugin for JIRA

Update agent.jar on audit vault server

As I wrote in my post Error installing Audit Vault Agent 12.1.1 on AIX, there is a Audit Vault Agent bug 17058352 on AIX. Unfortunately it hasn’t yet been fixed in the latest bundle patch for Oracle Audit Vault and Database Firewall. If you haven’t changed your default profile in /etc/profile on your AIX server, you will run into the same issues again when trying to update the agents according the patch readme. Rather than downloading and fixing the agent.jar on each AIX system, it is also possible to update the agent.jar on the Audit Vault server before updating and restarting the agents on the monitored servers.

For this just login to the Oracle Audit Vault and Database Firewall server as support and become oracle.

ssh support@melete2

su - root
su - oracle

Locate your agent.jar, backup it and unpack the agentctl

cd /var/lib/oracle/dbfw/av/jlib/
cp agent.jar agent.jar_backup_bugfix_17058352
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

You now just can proceed with updating the agent on the AIX servers.

Update: ORA-00600 [kpdbModAdminPasswdInRoot: not CDB] when changing password of default account

As discussed in my post ORA-00600 [kpdbModAdminPasswdInRoot: not CDB] when changing password of default account There is an unpublished bug 16901482 which cause an ORA-00600 when trying to set a new password for an Oracle default account like DBSNMP, DIP or OUTLN.

On september 4th Oracle released the one-off patch 16901482 for this bug. A short test showed that the issue has been fixed with this patch. Unfortunately the patch is only available for Linux86-64.

Simple offline patch installation according the patch README. 

oracle@urania:~/16901482/ [TDB12] $cdh/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.1.0.1.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u00/app/oracle/product/12.1.0.1
Central Inventory : /u00/app/oraInventory
   from           : /u00/app/oracle/product/12.1.0.1/oraInst.loc
OPatch version    : 12.1.0.1.0
OUI version       : 12.1.0.1.0
Log file location : /u00/app/oracle/product/12.1.0.1/cfgtoollogs/opatch/opatch2013-09-09_17-06-52PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
oracle@urania:~/16901482/ [TDB12] $cdh/OPatch/opatch apply
Oracle Interim Patch Installer version 12.1.0.1.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /u00/app/oracle/product/12.1.0.1
Central Inventory : /u00/app/oraInventory
   from           : /u00/app/oracle/product/12.1.0.1/oraInst.loc
OPatch version    : 12.1.0.1.0
OUI version       : 12.1.0.1.0
Log file location : /u00/app/oracle/product/12.1.0.1/cfgtoollogs/opatch/16901482_Sep_09_2013_17_07_24/apply2013-09-09_17-07-23PM_1.log

Applying interim patch '16901482' to OH '/u00/app/oracle/product/12.1.0.1'
Verifying environment and performing prerequisite checks...
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u00/app/oracle/product/12.1.0.1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.rdbms, 12.1.0.1.0...

Verifying the update...
Patch 16901482 successfully applied
Log file location: /u00/app/oracle/product/12.1.0.1/cfgtoollogs/opatch/16901482_Sep_09_2013_17_07_24/apply2013-09-09_17-07-23PM_1.log

OPatch succeeded.
oracle@urania:~/16901482/ [TDB12] $cdh/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.1.0.1.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /u00/app/oracle/product/12.1.0.1
Central Inventory : /u00/app/oraInventory
   from           : /u00/app/oracle/product/12.1.0.1/oraInst.loc
OPatch version    : 12.1.0.1.0
OUI version       : 12.1.0.1.0
Log file location : /u00/app/oracle/product/12.1.0.1/cfgtoollogs/opatch/opatch2013-09-09_17-08-09PM_1.log

Lsinventory Output file location : /u00/app/oracle/product/12.1.0.1/cfgtoollogs/opatch/lsinv/lsinventory2013-09-09_17-08-09PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2): 

Oracle Database 12c                                                  12.1.0.1.0
Oracle Database 12c Examples                                         12.1.0.1.0
There are 2 products installed in this Oracle Home.


Interim patches (1) :

Patch  16901482     : applied on Mon Sep 09 17:07:51 CEST 2013
Unique Patch ID:  16618513
   Created on 4 Sep 2013, 12:02:44 hrs PST8PDT
   Bugs fixed:
     16901482

--------------------------------------------------------------------------------

OPatch succeeded.

Simple test with DBNSMP user similar to the test in the initial post. 

SQL> col username FOR a20
SQL> SELECT username,account_status,password_versions,ORACLE_MAINTAINED FROM dba_users
  2  WHERE username='DBSNMP';

USERNAME             ACCOUNT_STATUS                   PASSWORD_VER O
-------------------- -------------------------------- ------------ -
DBSNMP               EXPIRED                          10G 11G      Y

SQL> conn dbsnmp/dbsnmp
ERROR:
ORA-28001: the password has expired

Changing password for dbsnmp
New password: 
Retype new password: 
Password changed
Connected.
SQL> exit

Oracle 11.2.0.4.0 Patchset released

Oracle has released the patchset 11.2.0.4.0 for Oracle 11g Release 2. The current patchset is as well as the other 11g R2 patchsets a full installation. This means you will have to download quite a bit from Metalink, altogether 7 files. On My Oracle Support search for patchset 13390677 or follow the link to reach the download page.

So far the patch set is only available for Linux (x86 and x86-64bit), Oracle Solaris on SPARC (32bit and 64bit) and Oracle Solaris on x86 and x86-64bit. The Metalink Note 11.2.0.4 Patch Set – Availability and Known Issues [1562139.1] or the generic ALERT for Oracle 11g Release 2 ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts [880782.1] will list the patch set for other platforms as soon as they are available. The first Metalink Note is currently still under construction 🙂 .

List of Bugfixes

In the Metalink Note 1562142.1 you find a hell of a long list with bugs fixed in this patchset. But also this document is currently under construction. I think you would be well advised to test the patchset before installing on production. According to Metalink Note 1562139.1 there are up to now only two notable fixes with potential change in behavior but no new issues introduced in this patchset. So check the Metalink Note on updates.

New Features

Compared with previous patch set this one does not include all too many new features. Below a you find a list of the new features included in this patch set.

  • Oracle Data Redaction
  • Trace File Analyzer and Collector
  • RACcheck – The Oracle RAC Configuration Audit Tool
  • Database Replay Support for Database Consolidation
  • Dynamic Statistics
  • Optimization for Flashback Data Archive History
  • New sqlnet.ora Parameter SSL_EXTENDED_KEY_USAGE
  • New PrimaryLostWriteAction Property
  • ENABLE_GOLDENGATE_REPLICATION for Oracle GoldenGate

Features like Oracle Data Redaction are backported from Oracle 12c R1 (See Oracle 12c New Security Features). Details on these new feature are available in the Oracle documentation Oracle Database 11g Release 2 (11.2.0.4) New Features.

Reference

A collection of a few important Metalink Notes, readme and other links related to Patchset 11.2.0.4.0:

  • README for 13390677
  • ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts [880782.1]
  • 11.2.0.4 Patch Set – Availability and Known Issues [1562139.1]
  • 11.2.0.4 Patch Set – List of Bug Fixes by Problem Type [1562142.1]
  • Quick Reference to Patchset Patch Numbers [753736.1]
  • Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 [1189783.1]
  • Information on installing the patch set must be taken from the documentation (Installing and Upgrading) or the Metaling Note: Complete checklist for out-of-place manual upgrade from 11.2.0.1 to 11.2.0.2 [ID 1276368.1] (old only used as reference)

ORA-00600 [kpdbModAdminPasswdInRoot: not CDB] when changing password of default account

During the test on Oracle Database 12c I run into an interesting error respectively Chris Antongini has made me aware of it. If you try to change the password of an Oracle default account on a none CDB with SQL*Plus password you run into an ORA-00600 [kpdbModAdminPasswdInRoot: not CDB] and of course the password is not changed. Let’s try it with the DBSNMP account.

SQL> show parameter pluggable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_pluggable_database            boolean     FALSE

SQL> col username for a20
SQL> select username,account_status,password_versions,ORACLE_MAINTAINED from dba_users
  2  where username='DBSNMP';

USERNAME             ACCOUNT_STATUS                   PASSWORD_VER O
-------------------- -------------------------------- ------------ -
DBSNMP               EXPIRED & LOCKED                 10G 11G      Y


SQL> conn dbsnmp/dbsnmp
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter user DBSNMP account unlock;

User altered.

SQL> conn dbsnmp/dbsnmp
ERROR:
ORA-28001: the password has expired


Changing password for dbsnmp
New password: 
Retype new password: 
ERROR:
ORA-00600: internal error code, arguments: [kpdbModAdminPasswdInRoot: not CDB],
[], [], [], [], [], [], [], [], [], [], []


Password unchanged
Warning: You are no longer connected to ORACLE.

As you can see above the account is expired and locked. After unlocking the account I’ll try to login again and will be asked to set a new password. Doing the same on a container database does work without an ORA-00600.

SQL> show parameter pluggable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_pluggable_database            boolean     TRUE
SQL> col username for a20
SQL> select username,account_status,password_versions,ORACLE_MAINTAINED from dba_users
  2  where username='DBSNMP';

USERNAME             ACCOUNT_STATUS                   PASSWORD_VER O
-------------------- -------------------------------- ------------ -
DBSNMP               EXPIRED & LOCKED                 10G 11G      Y

SQL> conn dbsnmp/dbsnmp
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter user DBSNMP account unlock;

User altered.

SQL> conn dbsnmp/dbsnmp
ERROR:
ORA-28001: the password has expired


Changing password for dbsnmp
New password: 
Retype new password: 
Password changed
Connected.

Sure as DBA you may workaround this problem and set the users new password with alter user. So far I couldn’t reproduce this problem with a custom test user. According to Oracle this problem is related to an unpublished bug 16901482 which will be fixed with a one off patch 17217733.

Oracle Database 12c New Feature: Last Login Time

As Markus Flechtner has already mentioned in his blog, Oracle has started to record the last login time. It is a small but very useful 12c security feature and operates independently of the database audit. Nevertheless, there are some restrictions. But let’s start at the beginning…

A simple example

Ok, lets try to connect as user scott.

oracle@urania:~/ [TDB12] sqlplus scott/tiger

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 12 21:59:19 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Tue Aug 06 2013 07:29:29 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

The highlighted line above show the last login information. The information itself is stored in column SPARE6 of SYS.USER$ and can be queried in the column LAST_LOGIN of DBA_USERS.

SQL> col username for a15
SQL> col last_login for a25
SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';

Session altered.

SQL> select username,last_login from dba_users where username='SCOTT';

USERNAME        LAST_LOGIN
--------------- -------------------------
SCOTT           12.08.2013 21:59:19

col name for a15
alter session set NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';

SQL> select name,spare6 from user$ where name='SCOTT';

NAME            SPARE6
--------------- -------------------
SCOTT           12.08.2013 19:59:19

The display of the last login information can also be disabled with -nologintime. 

oracle@urania:~/ [TDB12] sqlplus -nologintime scott/tiger

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 12 22:24:04 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

Nevertheless the login time is still being recorded. Visible through the identical timestamp Mon Aug 12 22:24:04 2013 above and below

SQL> col username for a15
SQL> col last_login for a25                                                                  
SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';

Session altered.

SQL> select username,last_login from dba_users where username='SCOTT';

USERNAME        LAST_LOGIN
--------------- -------------------------
SCOTT           12.08.2013 22:24:04

Ok, whats about SYSDBA and other password file users?

A little less simple example

Let’s create a test user with required privileges. That is CREATE SESSION, SYSBACKUP and SELECT on DBA_USERS

SQL> create user king identified by kong;

User created.

SQL> grant create session to king;

Grant succeeded.

SQL> grant sysbackup to king;

Grant succeeded.

SQL> grant select on dba_users to king;

Grant succeeded.

First we initiated an SQL*Plus session as SYSBACKUP

oracle@urania:~/ [TDB12] sqlplus king/kong as sysbackup

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 12 22:35:36 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> col username for a15
SQL> col last_login for a25
SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';

SQL> select username,last_login from dba_users where username='KING';

USERNAME        LAST_LOGIN
--------------- -------------------------
KING

As seen above, no logon time is recorded. This is because the user KING has not been authenticated by a password but rather by OS authentication and the corresponding OS group. The same applies if the password file is used for authentication.

SQL> show user
USER is "SYSBACKUP"

SQL> select * from v$pwfile_users where username='KING';

USERNAME        SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
--------------- ----- ----- ----- ----- ----- ----- ----------
KING            FALSE FALSE FALSE TRUE  FALSE FALSE          0

But again if we connect as regular user with password authentication the logon time will be recorded. In this case nothing will be displayed because it’s the first time the user KING is logging into the database.

oracle@urania:~/ [TDB12] sqlplus king/kong 

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 12 22:46:28 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> col username for a15
SQL> col last_login for a25
SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';

Session altered.

SQL> select username,last_login from dba_users where username='KING';

USERNAME        LAST_LOGIN
--------------- -------------------------
KING            12.08.2013 22:46:28

SQL> show user
USER is "KING"

Conclusion

As stated above, this is a small but nice security feature. It does record logon and connect from any regular user. For a DBA , it is easy to check whether a user has logged in and how long it’s been. 

SQL> select username,last_login from dba_users where last_login is not null order by last_login;

USERNAME        LAST_LOGIN
--------------- -------------------------
HR              05.08.2013 09:04:06
SYSTEM          05.08.2013 14:52:12
TEST            12.08.2013 21:14:45
SCOTT           12.08.2013 22:24:04
KING            12.08.2013 22:46:28

On the other hand, there are some small drawbacks.

  • No login time recording for administrative users respectively password file users
  • Login time is only displayed when logged in by SQL*Plus
  • -nologintime just switch of display not recording of login time

Since this feature is intended primarily for interactive use, the limitations are understandable to a certain degree. Especially for SYSDBA, SYSDG and SYSBACKUP the last login time would be flooded by a bunch of internal connections by RMAN or dataguard. To ensure the traceability, it is mandatory to use database audit including audit of SYS operation.

References

Links related to this post, password file users, security and more

  • Oracle® Database New Features Guide 12c Release 1 (12.1) Last Login Time Information
  • SQL*Plus® User’s Guide and Reference Release 12.1 No Login Time Option
  • SYSDBA and SYSOPER Privileges in Oracle [50507.1]
  • Checklist for Resolving CONNECT AS SYSDBA (INTERNAL) Issues [233223.1]
  • Why Can I Login AS SYSDBA With any Username and Password? [242258.1]
  • All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS, Database Vault, Audit Vault [207959.1]

OAV-46599 when trying to add new secure target on AVDV 12.1.1.1

It is the second time that I run into this problem. Therefore, it is time to write a quick note before I struggle a third time. At some point adding a new secure targets no longer works and breaks with an OAV-46599.

OAV 46599

Initially I was a bit confused about the error. Because there haven’t been any changes on the system since the last secure target has been added. But reading the whole error message above gives the correct indication of the root cause. It’s nothing else than an ORA-28001 the password has expired. Lets see which user has an expired password.

SQL> alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';

Session altered.

SQL> set linesize 160 pagesize 200
SQL> select username,account_status,expiry_date from dba_users where account_status='EXPIRED';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE
------------------------------ -------------------------------- -------------------
ANONYMOUS                      EXPIRED                          17.09.2011 10:21:08
AVREPORTUSER                   EXPIRED                          17.07.2013 21:25:55

It looks like the account AVREPORTUSER is expired. Because I do not know the password yet know whether it was stored somewhere, I’ll just reset the old password. For this I need both password hash’s.

SQL> col name for a15
SQL> col password for a17
SQL> col spare4 for a65
SQL> select name,password,spare4 from user$ where name='AVREPORTUSER';

NAME            PASSWORD          SPARE4
--------------- ----------------- -----------------------------------------------------------------
AVREPORTUSER    F315BBCEBB3F78E7  S:14155D035FEBAB05790EAB47CCC4ACDBD8B728C373EECDABE6EB5FAA9D03

With alter user identified by values I’m able to specify both the 10g and the 11g password hash to reset the password to the same value.

 alter user AVREPORTUSER identified by values 'S:14155D035FEBAB05790EAB47CCC4ACDBD8B728C373EECDABE6EB5FAA9D03;F315BBCEBB3F78E7';

As you can see in DBA_USERS the account has now status open again. Adding secure targets does work again.

SQL> select username,account_status,expiry_date,password_versions from dba_users where username='AVREPORTUSER';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         PASSWORD
------------------------------ -------------------------------- ------------------- --------
AVREPORTUSER                   OPEN                             11.02.2014 06:39:02 10G 11G

But how has this account become expired? The reason is quite obviously. All AV user do have the Oracle DEFAULT profile which has a limited password life time of 180 days. Therefore, the accounts expire after 180 days. And yes my AVDF test system was set up about 180 days ago. 🙂

SQL> select username,profile from dba_users where username='AVREPORTUSER';

USERNAME                       PROFILE
------------------------------ ------------------------------
AVREPORTUSER                   DEFAULT

SQL> select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180

An Oracle Bug has been opened for this issue. BUG 17078860 Relax The profile settings in the AV server’s database