Category Archives: 11gR2

Posts related to Oracle 11g Release 2

Oracle CPU / PSU Announcement January 2017

Oracle has published the first Critical Patch Update in 2017. It’s quite a huge update with not less than 270 new security vulnerability fixes across the Oracle products. For the Oracle Database itself are 5 security fixes available respectively 2 security fixes for the Oracle Database Server and 3 security fixes for Oracle Secure Backup and Oracle Big Data Graph.
Neither of the two vulnerabilities for Oracle Databases are remotely exploitable without authentication. None of these fixes are applicable to client-only installations.

The highest CVSS Base Score of vulnerabilities affecting Oracle Database Server is 9.0. The following components are affected:

  • OJVM
  • RDBMS Security / Local Logon

Over all the PSU for Oracle Database Server itself is relatively small. The tests for the Trivadis CPU-Report will show if there are any issues with this PSU respectively SPU.

It seems that a bunch of Patch’s are not yet available. Oracle list the follow Post Release Patches beside the PSU and SPU for Oracle Database Server 11.2.0.4.

Patch Number Patch Platform Availability
24968615 Database Proactive Bundle Patch 12.1.0.2.170117 HP-UX Itanium (64-Bit) & AIX (64-Bit) Expected: Wednesday 18-Jan-2017
25395111 Oracle Application Testing Suite BP 12.5.0.1 All Platforms Expected: Wednesday 18-Jan-2017
25115951 Microsoft Windows BP 12.1.0.2.170117 Windows 32-Bit and x86-64 Expected: Tuesday 24-Jan-2017
25112498 Oracle JavaVM Component Microsoft Windows Bundle Patch 12.1.0.2.170117 Windows 32-Bit and x86-64 Expected: Tuesday 24-Jan-2017
24918318 Quarterly Full Stack download for Exadata (Jan2017) BP 12.1.0.2 Linux x86-64 and Solaris x86-64 Expected: Thursday 26-Jan-2017
24918333 Quarterly Full Stack download for SuperCluster (Jan2017) BP 12.1.0.2 Solaris SPARC 64-Bit Expected: Thursday 26-Jan-2017

More details about the patch will follow soon on the Oracle Security Pages.

Losing the Oracle Wallet for Enterprise User Security

Having a reliable backup solution for your Transparent Data Encryption (TDE) or Enterprise User Security (EUS) Wallets, is beyond discussion. Nevertheless it can happen that you lose or corrupt the Oracle Wallet. With Transparent Data Encryption (TDE), this is really bad luck, because you can not access your encrypted data. Losing an EUS wallet is on the other side not really an issue. You can remove the database from your EUS LDAP directory (Oracle Unified Directory OUD or Oracle Internet Directory OID) and re-register the database. Although this is the fastest solution, it has some constraints. Un-register and re-register the database, means losing the EUS mappings. Alternatively you can manually create a new empty Oracle Wallet and reset the Database password using dbca.

Ok, first lets create a new empty wallet using mkstore:

oracle@urania:/u00/app/oracle/ [TDB11A] mkstore -wrl $ORACLE_BASE/admin/TDB11A/wallet -create
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter password:              
   
Enter password again:

Alternatively you can use orapki to create an empty wallet. orapki is easier to use in scripts and supports auto login local wallets with -auto_login_local:

oracle@urania:/u00/app/oracle/ [TDB11A] orapki wallet create -wallet $ORACLE_BASE/admin/TDB11A/wallet/ -pwd <password> -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Create an entry for the Database distinguished names (DN). This step is somehow necessary because dbca -regenerateDBPassword just creates the password entry but no new dn entry:

oracle@urania:/u00/app/oracle/ [TDB11A] mkstore -wrl $ORACLE_BASE/admin/TDB11A/wallet -createEntry ORACLE.SECURITY.DN cn=TDB11A_SITE1,cn=OracleContext,dc=postgasse,dc=org
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

Create an entry for the database password:

oracle@urania:/u00/app/oracle/ [TDB11A] mkstore -wrl $ORACLE_BASE/admin/TDB11A/wallet -createEntry ORACLE.SECURITY.PASSWORD manager
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

Recreate the database registration password using dbca:

oracle@urania:/u00/app/oracle/ [TDB11A] dbca -silent -configureDatabase -sourceDB TDB11A \
> -sysDBAUserName sys -sysDBAPassword </password><password> \
> -regenerateDBPassword true \
> -dirServiceUserName cn=orcladmin -dirServicePassword </password><password> \
> -walletPassword </password><password>
Preparing to Configure Database
6% complete
13% complete
66% complete
Completing Database Configuration
100% complete
Look at the log file "/u00/app/oracle/cfgtoollogs/dbca/TDB11A_SITE1/TDB11A11.log" for further details.

Verify the new password in the Oracle Wallet:

oracle@urania:/u00/app/oracle/ [TDB11A] mkstore -wrl $ORACLE_BASE/admin/TDB11A/wallet -viewEntry ORACLE.SECURITY.PASSWORD
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:              
   
ORACLE.SECURITY.PASSWORD = S6usUGSNb#P1

This password can now be used to verify the LDAP Bind with ldapsearch using the database DN and the password:

oracle@urania:/u00/app/oracle/ [TDB11A] ldapsearch -h localhost -p 1389  \
> -D 'cn=TDB11A_SITE1,cn=OracleContext,dc=postgasse,dc=org' -w S6usUGSNb#P1 \
> -b 'cn=OracleDBSecurity,cn=Products,cn=OracleContext,dc=postgasse,dc=org' '(objectclass=*)'
cn=OracleDBSecurity,cn=Products,cn=OracleContext,dc=postgasse,dc=org
orclVersion=102000
objectClass=top
objectClass=orclContainer
objectClass=orclDBSecConfig
objectClass=orclDBSecConfig10i
orclDBVersionCompatibility=90000
cn=OracleDBSecurity
orclDBOIDAuthentication=PASSWORD

...

Or finally check login via SQLPlus as EUS user:

oracle@urania:/u00/app/oracle/ [TDB11A] sqh

SQL*Plus: Release 11.2.0.4.0 Production ON Wed Sep 14 10:22:28 2016

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


Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
WITH the Partitioning, Oracle Label Security, OLAP, DATA Mining,
Oracle DATABASE Vault AND REAL Application Testing options

SQL> conn soe
Enter password:
Connected.
SQL> @sousrinf
DATABASE Information
--------------------
- DB_NAME       : TDB11A
- DB_DOMAIN     : postgasse.org
- INSTANCE      : 1
- INSTANCE_NAME     : TDB11A
- SERVER_HOST       : urania
-
Authentification Information
----------------------------
- SESSION_USER      : EUS_USER
- PROXY_USER        :
- AUTHENTICATION_METHOD : PASSWORD
- IDENTIFICATION_TYPE   : GLOBAL SHARED
- NETWORK_PROTOCOL  :
- OS_USER       : oracle
- AUTHENTICATED_IDENTITY: SOE
- ENTERPRISE_IDENTITY   : cn=soe,cn=People,dc=postgasse,dc=org
-
Other Information
-----------------
- ISDBA         : FALSE
- CLIENT_INFO       :
- PROGRAM       : sqlplus@urania.postgasse.org (TNS V1-V3)
- MODULE        : SQL*Plus
- IP_ADDRESS        :
- SID           : 410
- SERIAL#       : 925
- SERVER        : DEDICATED
- TERMINAL      : pts/2

PL/SQL PROCEDURE successfully completed.

Depending on your Oracle Directory it may happen, that you run into ORA-28030. This can happen, if you password profile on the directory server has Reset Password on Next Login defined. To work around this issue you have to temporarily disable Reset Password on Next Login in the password profile. This issue is also discussed in the MOS Note 558119.1 ORA-28030 After Regenerating Wallet Password Using dbca.

Oracle CPU / PSU Pre-Release Announcement July 2016

Oracle has published the Pre-Release Announcement for the July 2016 Critical Patch Update. It’s quite a huge update with not less than 276 security vulnerability fixes across the Oracle products. For the Oracle Database itself are 9 security fixes
available. Dies ist wiederum eines der größeren Critical Patch Update for databases. It does contain bug fix for some major security issues. Five of the vulnerabilities are remotely exploitable without authentication. The security bug fixes are for the Oracle Database Server as well for client-only installations. That means three of the security fixes are for client-only installations.

The highest CVSS Base Score of vulnerabilities affecting Oracle Database Server is 9.0. The following components are affected:

  • Application Express
  • Data Pump Import
  • Database Vault
  • DB Sharding
  • JDBC
  • OJVM
  • Portable Clusterware
  • RDBMS Core

We will see all the details next Tuesday when Oracle is officially releasing official Critical Patch Update for July 2016. Next week I’ll have a closer look and do some test installations.

More details about the patch will follow soon on the Oracle Security Pages.

Trivadis Schwaben Gipfel – Top 10 Database Threads and Risk Management

Just finished my second presentation at the Trivadis Schwaben-Gipfel in Stuttgart. It is about Database Threads and Risk assessment and a few considerations on Database classification. The slides are available for download  Schwaben-Gipfel Top 10 Gefahren_fuer eine Datenbank.pdf.

Using Kerberos in Oracle Standard Edition

Since the release of Oracle 12cR1 mid 2013 the network encryption and strong authentication services has been removed from the Oracle Advanced Security Option. Both feature are now available for any licensed editions. Corresponding section in the Oracle Licensing Guide for 11g R2 and 12c R1 has been updated.

Network encryption (native network encryption and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of all supported releases of the Oracle database.

Oracle Network Encryption does work quite well for Standard or Enterprise Edition even with the Instant Client. But there is no Kerberos support available Oracle 11.2.0.4 Standard Edition or any other 11g SE release. This can be verified on the command line with the command adapters:

oracle@urania:/u00/app/oracle/product/11.2.0.4se/lib/ [TDB11B] adapters

Installed Oracle Net transport protocols are:

    IPC
    BEQ
    TCP/IP
    SSL
    RAW
    SDP/IB

Installed Oracle Net naming methods are:

    Local Naming (tnsnames.ora)
    Oracle Directory Naming
    Oracle Host Naming
    Oracle Names Server Naming

Installed Oracle Advanced Security options are:

    RC4 40-bit encryption
    RC4 56-bit encryption
    RC4 128-bit encryption
    RC4 256-bit encryption
    DES40 40-bit encryption
    DES 56-bit encryption
    3DES 112-bit encryption
    3DES 168-bit encryption
    AES 128-bit encryption
    AES 192-bit encryption
    AES 256-bit encryption
    MD5 crypto-checksumming
    SHA-1 crypto-checksumming

There is a MOS Note 2145731.1 which describes how to enable the Radius adapter. The same method can be used to enable Kerberos as well, event if an other MOS Note 2028070.1 specifies that Kerberos is not available in Standard Edition.

Radius and Kerberos adapters is part of the object file nautab.o. In $ORACLE_HOME/lib are two object files. nautab.o and the nautab_ee.o.dbl:

oracle@urania:~/ [TDB11B] ls -al $ORACLE_HOME/lib/naut*
-rw-r--r--. 1 oracle users 4864 Jul 15  2013 /u00/app/oracle/product/11.2.0.4se/lib/nautab_ee.o.dbl
-rw-r--r--. 1 oracle users 4520 Jul  1 18:01 /u00/app/oracle/product/11.2.0.4se/lib/nautab.o

To get Kerberos support you just have to replace the nautab.o with nautab_ee.o.dbl…:

oracle@urania:~/ [TDB11B] cp $ORACLE_HOME/lib/nautab.o $ORACLE_HOME/lib/nautab_se.o.dbl
oracle@urania:~/ [TDB11B] cp $ORACLE_HOME/lib/nautab_ee.o.dbl $ORACLE_HOME/lib/nautab.o

oracle@urania:~/ [TDB11B] ls -al $ORACLE_HOME/lib/naut*
-rw-r--r--. 1 oracle users 4864 Jul 15  2013 /u00/app/oracle/product/11.2.0.4se/lib/nautab_ee.o.dbl
-rw-r--r--. 1 oracle users 4864 Jul  1 19:54 /u00/app/oracle/product/11.2.0.4se/lib/nautab.o
-rw-r--r--. 1 oracle users 4520 Jul  1 19:54 /u00/app/oracle/product/11.2.0.4se/lib/nautab_se.o.dbl

… and relink the binaries:

oracle@urania:~/ [TDB11B] relink all
writing relink log to: /u00/app/oracle/product/11.2.0.4se/install/relink.log

As you can see the command adapters now shows Radius and Kerberos adapters:

oracle@urania:~/ [TDB11B] adapters

Installed Oracle Net transport protocols are:

    IPC
    BEQ
    TCP/IP
    SSL
    RAW
    SDP/IB

Installed Oracle Net naming methods are:

    Local Naming (tnsnames.ora)
    Oracle Directory Naming
    Oracle Host Naming
    Oracle Names Server Naming

Installed Oracle Advanced Security options are:

    RC4 40-bit encryption
    RC4 56-bit encryption
    RC4 128-bit encryption
    RC4 256-bit encryption
    DES40 40-bit encryption
    DES 56-bit encryption
    3DES 112-bit encryption
    3DES 168-bit encryption
    AES 128-bit encryption
    AES 192-bit encryption
    AES 256-bit encryption
    MD5 crypto-checksumming
    SHA-1 crypto-checksumming
    Kerberos v5 authentication
    RADIUS authentication

As of now kerberos can be configured and used as usual. If for a reason okinit and oklist are also required, they must be copied from an existing Oracle 11g Enterprise Edition installation. By default they are not part of a Standard Edition. So let’s copy the binaries and the corresponding message files:

] cp 11.2.0.4/bin/okinit 11.2.0.4se/bin/okinit
oracle@urania:/u00/app/oracle/product/ [TDB11B] cp 11.2.0.4/bin/oklist 11.2.0.4se/bin/oklist
oracle@urania:/u00/app/oracle/product/ [TDB11B] cp 11.2.0.4/bin/okinit 11.2.0.4se/bin/okinit
oracle@urania:/u00/app/oracle/product/ [TDB11B] cp 11.2.0.4/bin/okdstry 11.2.0.4se/bin/okdstry
oracle@urania:/u00/app/oracle/product/ [TDB11B] cp 11.2.0.4/network/mesg/naukus.msb 11.2.0.4se/network/mesg/naukus.msb
oracle@urania:/u00/app/oracle/product/ [TDB11B] cp 11.2.0.4/network/mesg/naukus.msg 11.2.0.4se/network/mesg/naukus.msg

Now you can use Kerberos as you want. You just have to struggle with the usual kerberos issues and bugs 🙂 But more on them here kerberos.

References

Some links related to this topic.

  • Oracle® Database Licensing Information User Manual 12c Release 1 (12.1) Oracle Advanced Security
  • Oracle® Database Licensing Information 11g Release 2 (11.2) Oracle Advanced Security
  • How To Enable Radius Adapter In Oracle Database 11g Standard Edition [2145731.1]
  • Kerberos Authentication On Standard Edition [2028070.1]
  • Configuring ASO Kerberos Authentication with a Microsoft Windows 2008 R2 Active Directory KDC [1304004.1]

Oracle CPU / PSU Pre-Release Announcement January 2015

Oracle has published the Pre-Release Announcement for the first Critical Patch Update in 2015. This Critical Patch Update contains 167 new security vulnerability fixes across all Oracle products. It looks like that this CPU does contain a bunch of critical security fixes for Oracle databases. Actually there are 7 fixes for security vulnerabilities, but none of them is remotely exploitable nor are they for client-only installations. Nevertheless the highest CVSS rating is 9.0. I wonder which OS is affected 😉

Beside the high CVSS rating, some core components seems to be affected:

  • Core RDBMS
  • DBMS_UTILITY
  • PL/SQL
  • Recovery
  • Workspace Manager
  • XML Developer’s Kit for C

We will see all the details later today, when Oracle is officially releasing the Critical Patch Update for January 2015. Together with my colleagues at Trivadis, we’ll have a closer look and do some testing. See also Trivadis Critical Patch Updates Report

More details about the patch will follow soon on the Oracle Security Pages.

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.