Category Archives: Security

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:

Changes in database security patching with 12c

During my preparation for the tests of October Critical Patch Updates (CPU), I stumbled over an interesting Oracle Support Document. I this document Oracle announced that there will nolonger be seperate SPU (Security Patch Update) respectively CPU (Critical Patch Update) for 12.1.0.1 and newer.

Excerpt from Oracle support document 1581950.1 Database Security Patching from 12.1.0.1 onwards:

Starting with Oracle Database version 12.1.0.1, Oracle will only provide Patch Set Update (PSU) patches to meet the Critical Patch Update (CPU) program requirements for security patching. SPU (Security Patch Update) patches will no longer be available. Oracle has moved to this simplified model due to the popularity of the PSU patches. PSUs are Oracle’s preferred proactive patching vehicle since their inception in 2009 [1].

In future it will be much easier to decide on CPU or PSU patches. :-) The downside is that testing becomes more complicated, since the PSU (Patch Set Updates) include security patches as well functional bug fixes.

References

Some links related CPU, SPU and PSU

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.

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.

43
44
45
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

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]