Category Archives: 11gR2

Posts related to Oracle 11g Release 2

Oracle passwords and special characters

As commonly known passwords should have a certain complexity. Thereby it is common to use special characters, numbers, lower and uppercase characters. Depending on the type of special characters Oracle require that the password is enclosed in double quotation marks. Oracle does provide a guideline for Securing Passwords in the Oracle® Database Security Guide. So far so well, depending on the applications or Oracle clients the enclosing doesn’t really work as expected. Some Special characters like a $ or % are used to indicate an environment variable, other special characters like /, [] or @ are used to build the connect string. But a really nasty special character is the backslash respectively the \. The backslash is in general used as escape character to change subsequent literal characters into metacharacters and vice versa. The use of the backslash in an Oracle password has some very interesting side effects :-).

Ok, lets create a test user with a complex password and a backslash \ somewhere in the password.

SQL> conn / AS sysdba
Connected.
SQL> CREATE USER smith IDENTIFIED BY "KT20\dft";

USER created.

SQL> GRANT CREATE SESSION TO smith;

GRANT succeeded.

SQL> conn smith/KT20\dft
Connected.

SQL> SHOW USER
USER IS "SMITH"

SQL> conn smith/"KT20\dft"
Connected.

SQL> SHOW USER;
USER IS "SMITH"

SQL> conn smith/"KT20\dft"@TDB11
Connected.

SQL> SHOW USER;
USER IS "SMITH"

SQL> conn smith/KT20\dft@TDB11
Connected.

To create the user I’ve used the double quotation marks to enclose the password. As you can see, the attempts to log on to the database have worked in any case. In contrast to the login, alter the password definitely requires some quotation as you can see below.

SQL> ALTER USER smith IDENTIFIED BY KT20\dft;
ALTER USER smith IDENTIFIED BY KT20\dft
                                   *
ERROR at line 1:
ORA-00911: invalid CHARACTER

Ok, lets put the backslash at the end of the password and try to login again.

SQL> ALTER USER smith IDENTIFIED BY "KT20dft";

USER altered.

SQL> conn smith/KT20dft\
Connected.

SQL> SHOW USER
USER IS "SMITH"

SQL> conn smith/"KT20dft"
SP2-0306: Invalid OPTION.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=VALUE]]
WHERE <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]

SQL> conn smith/"KT20dft"@TDB11
SP2-0306: Invalid OPTION.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=VALUE]]
WHERE <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]

SQL> conn smith@TDB11
Enter password: ********
Connected.
SQL> SHOW USER
USER IS "SMITH"

SQL> conn smith/KT20dft\@TDB11
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected TO ORACLE.

As you can see above, the Login no longer works so smoothly. Using the username and password without any quotation does work. Any other combination with quotation or the connect identifier does not work any more. In these cases the backslash does behave as a escape character. Mmh, in this case it should be possible to escape the backslash with a second backslash isn’t it?

SQL> conn smith/"KT20dft\"
ERROR:
ORA-01017: invalid username/password; logon denied

No, doesn’t work. So far I have found no way to use the backslash at the end of a password, unless the password is entered interactively. For an administration tool like SQLPlus, SQL Developer etc which is anyway used interactively it isn’t a problem. But if you would like to setup batch jobs, RMAN backup’s etc it does not work.

Recovery Manager: Release 11.2.0.3.0 - Production ON Tue Jun 12 08:33:43 2014

Copyright (c) 1982, 2011, Oracle AND/OR its affiliates.  ALL rights reserved.

RMAN>
RMAN>
RMAN> CONNECT target *
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-10000: error parsing target DATABASE CONNECT string "sys/"KT20dft"@TDB11"

RMAN> run
2> {
3>
4> allocate channel ch1 TYPE disk;
5> backup CURRENT control file;
6> }
USING target DATABASE control file instead OF recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure OF allocate command at 06/12/2014 08:33:43
RMAN-06171: NOT connected TO target DATABASE

Conclusion

It is still highly recommended to use complex passwords. Although Oracle supports all types of multi-byte characters, it is useful to test some special characters before these are used in passwords. Not everything always works as you expect.

Trivadis PL/SQL & SQL CodeChecker

A couple of days ago Trivadis released the Trivadis PL/SQL & SQL CodeChecker (tvdcc) as SQL Developer Extension. TVDCC does check the editor content for compliance violations of the Trivadis PL/SQL & SQL Coding Guidelines Version 2.0.

Quote from the blog post of my work colleague:

Furthermore McCabe’s cyclomatic complexity, Halstead’s volume, the maintainability index and some other software metrics are calculated for each PL/SQL unit and aggregated on file level.

The results are presented in an additional tabbed panel. One tab shows all guideline violations to quickly navigate to the corresponding code position, the other tab contains a full HTML report, which also may be opened in your external browser.

Tvdcc sqldev report

It is a convenient tool, especially if you do not regularly developed PL / SQL code.

References

Additional information and links related to the Trivadis PL/SQL & SQL CodeChecker.

Update: Oracle and OpenSSL ‘Heartbleed’ vulnerability

While writing a post about the new Critical Patch Advisory I’ve discovered, that Oracle made the Information about the OpenSSL Vulnerability publicly available. The information in MOS Note 1645479.1 has been moved to OpenSSL Security Bug – Heartbleed CVE-2014-0160.

Until now it looks like that Oracle Databases are not affected since they do not use OpenSSL. On the other hand products like Oracle Wallet Manager and EM Base Platform are still under investigation. We’ll know more once Oracle has completed its investigations.

Oracle released CPU / PSU April 2014

As announced last week in my post Oracle CPU / PSU Pre-Release Announcement April 2014, Oracle has now released the Critical Patch Updates for April 2014. Overall this CPU contains 104 new security fixes across several Oracle products like Database Server, MySQL Server, Sun Product Suite, WebLogic Server etc. For Oracle Database it contains only 2 security fixes, whereas one of the fixes has a very high CVSS rating. But this only affects installations on Windows. On these platform it is recommended to install the patch bundle as soon as possible. Nevertheless it is certainly advisable to install the patches on Unix/Linux-based systems

As somehow expectable, Oracle did not add any information about the OpenSSL issue to it’s current Critical Patch Advisory. I assume this is because the vulnerability was announced a few days ago. As discussed in my post Oracle and OpenSSL ‘Heartbleed’ vulnerability, Oracle is tracking information about this issue in MOS Note 1645479.1 OpenSSL Security Bug-Heartbleed.

CPU Release Dates

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

  • 15 July 2014
  • 14 October 2014
  • 20 January 2015
  • 14 April 2015

References

As usual there are a bunch of links and MOS Notes around the critical patch update available:

  • Oracle Critical Patch Update Advisory – April 2014
  • Patch Set Update and Critical Patch Update April 2014 Availability Document [1618213.1]
  • Oracle Critical Patch Update April 2014 Documentation Map [1637289.1]
  • Critical Patch Update April 2014 Database Known Issues [1615881.1]
  • Critical Patch April 2014 Database Patch Security Vulnerability Molecule Mapping [1615882.1]
  • Critical Patch Update April 2014 Oracle Fusion Middleware Known Issues [1618208.1]
  • Oracle Critical Patch Updates and Security Alerts on OTN

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.

Oracle CPU / PSU Pre-Release Announcement April 2014

Today Oracle has published the Pre-Release Announcement of the CPU Advisory for April 2014. This Critical Patch Update contains 103 new security vulnerability fixes for several Oracle products. There are only a few days since the publication of the vulnerability CVE-2014-0160 known as “Heartbleed”. Therefore I assume, that this patch update does not yet address the corresponding vulnerability.

Nevertheless it seems that it contains bug fix for some major security issues. From the Oracle database point of view it is a small update. There are only two security bug fix for the Oracle Database Server and no for client-only installations. But one of the vulnerabilities does have a CVSS rating of 8.5 and affects the Core RDBMS. The vulnerabilities of Oracle Java SE are even higher and rated with a 10.0 out of 10.0.

We will see all the details next Tuesday when Oracle is officially releasing official Critical Patch Update for April 2014. 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.

Oracle CPU / PSU Pre-Release Announcement January 2014

Today Oracle has published the Pre-Release Announcement for the first CPU Patch in 2014. This Critical Patch Update contains 147 new security vulnerability fixes for several Oracle products. From the Oracle database point of view it is a small update. There are only five security fix for the Oracle Database Server and no for client-only installations.

Although the CVSS rating of these vulnerabilites are 5.0, it looks that there is no hurry to install this security fix on most of the database environments. If this is true, we’ll see next Tuesday when Oracle is officially releasing CPU / PSU January 2014. Next week I’ll have a closer look.

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

Oracle released CPU / PSU October 2013

As announced yesterday in my post Oracle CPU / PSU Pre-Release Announcement October 2013, Oracle has now released the last Critical Patch Updates for 2013. Overall this CPU contains 126 new security fixes across several Oracle products like Database Server, MySQL Server, Sun Product Suite, WebLogic Server etc. For Oracle Database it contains only 2 security fixes with a rather medium CVSS rating. Although the Core RDBMS is affected, it is probably not necessary to run a fire drill. If you have planned to patch anyway, it makes sense to consider the latest PSU or SRU. And if you plan to install Oracle 11.2.0.4.0 patch set, this critical patch update can even be skip, since there is no PSU or SPU for 11.2.0.4 available. According to the patch read-me, it seems that CVE-2013-5771 is fixed in 11.2.0.4. But I can’t confirm this, because I could not find a Bug-ID to compare.

By the way, Oracle has changed a few thing in database security patching for 12c. They will not publish any separate security patch updates (SPU) anymore but solely patch set update (PSU)

CPU Release Dates

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

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

References

Links all around Critical Patch Update:

Oracle CPU / PSU Pre-Release Announcement October 2013

Oracle has published the Pre-Release Announcement for the October CPU/SPU Patch. This Critical Patch Update contains 126 new security vulnerability fixes for several Oracle products. Despite the large amount of security fixes, it is a rather small update from the database point of view. There are only two security fix for the Oracle Database Server and no for client-only installations. But it does contain the fix for Oracle Database 12c Release 1.

The announced highest CVSS rating for databases is 5.5. Because the core RDBMS is affected, it will probably make sense to install this CPU an any database environment. But this has to be verified as soon as the CPU is officially released later this week.

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

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)