Category Archives: New Features

Oracle New Features

Oracle 12 Release 2 Documentation available

Oracle just released the documentation for Oracle 12c Release 2. It seems that most of the new security features are available as discussed in my presentation at DOAG SIG Security in Düsseldorf on the 18th of october. See docs.oracle.com for the documentation bookshelf.

Yet a short summary of new security features

Encryption

  • TDE Tablespace Live Conversion
  • Fully Encrypted Database
  • Support for ARIA, SEED, and GOST Encryption Algorithms in TDE
  • TDE Tablespace Offline Conversion

Enforcing Application Security in the Database

  • RAS Session Privilege Scoping
  • RAS Column Privilege Enhancements
  • RAS Schema Level Policy Administration
  • RAS Integration with OLS

Improving Security Manageability, Administration, and Integration

  • Oracle Virtual Private Database Predicate Audit
  • Oracle Database Vault Policy
  • Oracle Database Vault Simulation Mode Protection
  • Oracle Database Vault Common Realms and Command Rules for Oracle Multitenant
  • Privilege Analysis Enhancements
  • Privilege Analysis Results Comparison
  • Redaction: Different Data Redaction Policy Expressions
  • Redaction: New Functions Allowed in Data Redaction Policy Expressions
  • Redaction: Additional Data Redaction Transformations
  • Automatic KDC Discovery When Configuring OCI Clients
  • Automatic Provisioning of Kerberos Keytab for Oracle Databases
  • Role-Based Conditional Auditing
  • Inherit Remote Privileges

Improving Security Posture of the Database

  • SYSRAC – Separation of Duty for Administering Real Application Clusters
  • Transparent Sensitive Data Protection Feature Integration
  • Requiring Strong Password Verifiers by Default

Improving User Authentication and Management

  • Automatic Locking of Inactive User Accounts

Modernizing Network Authentication and Encryption

  • Kerberos-Based Authentication for Direct NFS

There is much more just on security. The full list of new features is available in the New Features Guide 12c Release 2 (12.2). In particular the new features for TDE are worth, having a closer look. So let’s discuss the good, the bad and the mad….

If you plan to take a training have a look at the Trivadis Training. We will announce a Trivadis Oracle Database 12c Release 2 Techno Circle as soon as the software for 12c Release 2 is officially released.

Oracle 12.1.0.2.0 Patchset released

About a week ago Oracle has released the first patchset 12.1.0.2.0 for Oracle 12c Release 1. So far the patch set is only available for Linux x86-64bit, Oracle Solaris SPARC 64bit and Oracle Solaris x86-64bit. You may download the Patchset on Oracle Software Delivery Cloud eDelivery.oracle.com, on Oracle Technology Network Oracle Database 12c Release 1 or on My Oracle Support as Patchset 17694377.

To download the full patch set you will have to download altogether 8 files. But in most cases you are interested in the first two ZIP files, which you need for the oracle Database. The other files are for Oracle Grid Infrastructure, Oracle Database Client, Oracle Gateways, Oracle Examples or Oracle GSM.

A bit special for this Patchset is, that it is only available as Oracle Enterprise Edition. Oracle Standard Edition and Standard Edition One will be available at a later stage. More information on this topic on MOS Note 1905806.1.

I will post more information on other platforms or editions as soon as it is available.

New Features

The first patch set for Oracle 12c Release 1 does mainly include a bunch of new feature for Oracle Pluggable Database, Caching and In-memory. Below a you find a list of the new features included in this patch set. Ludovico, a work colleague has already written a blog post about Oracle Database 12c in-memory option, a quick overview a few days ago.

  • Advanced Index Compression
  • Approximate Count Distinct
  • Attribute Clustering
  • Automatic Big Table Caching
  • FDA Support for CDBs
  • Full Database Caching
  • In-Memory Aggregation
  • In-Memory Column Store
  • JSON Support
  • New FIPS 140 Parameter for Encryption
  • PDB CONTAINERS Clause
  • PDB File Placement in OMF
  • PDB Logging Clause
  • PDB Metadata Clone
  • PDB Remote Clone
  • PDB Snapshot Cloning Additional Platform Support
  • PDB STANDBYS Clause
  • PDB State Management Across CDB Restart
  • PDB Subset Cloning
  • Rapid Home Provisioning
  • Zone Maps

Reference

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

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]

Oracle 12c new password verify function

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

New Password Resource Limits

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

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

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

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

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

Resource Name Limit
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 5
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function

New Functions

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

string_distance

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

differ := string_distance(old_password, password);

complexity_check

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

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

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

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

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

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

ora12c_verify_function

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

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

ora12c_strong_verify_function

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

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

References

Links all around Critical Patch Update:

Conclusion

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