EU GDPR, MS SQL Server 2016 and Oracle Security

I’ve just updated the list of my public appearances and planned events. For once, no just Oracle Events 🙂 I’ll speak about the new EU GDPR and its impact on databases in a Trivadis regional customer event together with my colleague Stephan Hurni. Beside this two events I’ll hold a webinar on Oracle 12c Release 2 new security features. This webinar is organised by DOAG.

Unfortunately all these events are in german. No matter, I’m about to register the one or other topic at upcoming Call For Papers. If the speeches get approved I’ll update my list of public appearance.

Oracle CPU / PSU Announcement April 2017

Last night Oracle released there new Critical Patch Update. From the DB perspective it is a rather small patch update. It just includes 2 fixes for security vulnerabilities on Oracle database 11.2.0.4 and 12.1.0.2. None of the vulnerabilities are remote exploitable without authentication but one fix is also for client only installations. The highest CVSS Base Score of vulnerabilities affecting Oracle Database Server 11.2.0.4 on Windows is 7.2 The following components are affected:

  • OJVM
  • SQL*Plus / Local Logon

According to MOS Note 2228898.1 Patch Set Update and Critical Patch Update April 2017 Availability Document, there should also be a OJVM PSU for Oracle 12.2.0.1. But the Patch 25811364 is not yet available.

For Oracle Fusion Middleware the situation looks somehow different. The Critical Patch Update includes not less than 31 fixes for vulnerabilities. Some of the vulnerabilities where some are remote exploitable without authentication and are rated with the highest CVSS rating of 10.0.

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

Oracle 12.2.0.1 On-Premises soon available

It seems that Oracle brings us the new release with the first “spring rays”. Tonight Oracle has Updates the MOS Note 742060.1 Release Schedule of Current Database Releases. It now includes as well sections for Oracle public cloud releases, on-premises engineered systems as well on-premises server releases. In particular the section on-premises server release has now a release date for Oracle 12.2.0.1. According to this, Oracle 12.2.0.1 will be available for Linux x86-64, Solaris SPARC and Solaris x86-64 by mid of march. For the other platforms like Windows, AIX etc we have to wait until Q2. As posted earlier the documentation for the new release is available since a couple of weeks. There is no reason not to start with the engineering work for the new release.

By the way, there are some other changes as well on this MOS Note. The attentive reader has seen, that Oracle has again extended their Free Extended Support for 11.2.0.4 until Dec 31, 2018. Unfortunately there are some contradictions with other MOS Notes like 161818.1 and 1067455.1. On these notes the Free Extended Supports ends earlier. You probabely should clarify your support status before planing to keep your 11.2.0.4 production database until end of 2018.

Some references and links to MOS Notes:

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.

Using TouchID for sudo on macOS Sierra

A couple of days ago, I’ve received my new 15″ Mac Book Pro. So far I’m quite happy. Ok the the circumstance, that I have to carry around a bunch of adapters. I’m waiting for the first projector at customers with USB-C connection. But thats an other story. Initially I thought, that I will not use the new Touch Bar that much. But I must admit that it’s quite handy from time to time. In particular the Touch ID to unlock the Mac Book Pro.

During my day to day work, I’m using the terminal quite a lot. This also includes the use of sudo. Why not using the Touch ID, to run a privileged command with sudo rather than typing the password. Good idea, unfortunately this is not possible out of the box in macOS Sierra. A Google search has revealed two possible solutions respectively projects on GitHub.

  • Replace the sudo with a customised version of sudo, which does support Touch ID (see sudo-touchid
  • Add a customised PAM module, which does support the Touch ID (see pam_touchid

I have decided to test the custom PAM module, because it seems, that this alternative has less impact on the operating system. The configuration is straight forward and includes the following steps:

  • Build the project using Xcode
  • Copy the PAM module to a custom location
  • Update the sudo configuration

As mentioned in a comments on GitHub, sudo over ssh does not work with this PAM module (see pam_touchid appears to break sudo over SSH) pam_touchid.m requires a small modification. In particular the following if statement has to be added at the top of the method pam_sm_authenticate.

if (getenv("SSH_TTY"))
return PAM_IGNORE;

In case of a sudo authentication request over SSH the module will do nothing. Sudo will fall back to the regular PAM modules. So lets start Xcode to adjust pam_touchid.m and build pam_touchid.so.2.
Build PAM Module
Create a custom directory for the PAM module, copy pam_touchid.so.2 and adjust the owner and privileges.

sudo mkdir -p /usr/local/lib/pam/
sudo cp pam_touchid.so.2 /usr/local/lib/pam/
sudo chown root:wheel /usr/local/lib/pam/pam_touchid.so.2
sudo chmod 444 /usr/local/lib/pam/pam_touchid.so.2

Update the sudo configuration and add auth sufficient pam_touchid.so reason="execute a command as another user" to the top of the file.

sudo vi /etc/pam.d/sudo

cat /etc/pam.d/sudo
# sudo: auth account password session
auth sufficient pam_touchid.so reason="execute a command as another user"
auth required pam_opendirectory.so
account required pam_permit.so
password required pam_deny.so
session required pam_permit.so

As soon as you start a new terminal session, you can use your Touch ID to authenticate sudo. Below you see an example of sudo hostname to get the current hostname.
TouchID
As mentioned in the realm of the PAM Touch ID project, you have to be sure what your doing. If it is the first time you use Xcode and Terminal, it is probably better to not change your sudo authentication.

Thanks to Hamza Sood for this PAM module.

Environment Scripts for OUD

At Trivadis we do have the TVD-BasEnv™ to standardizes and simplifies the handling of environments for Oracle database and application server landscapes. This inspired me to create something similar for Oracle Unified Directory environments. Although current versions of TVD-BasEnv™ already support OUD and OID environment. I’ve had the situation, where I need some small and slimmed down environment scripts for dedicated OUD test servers. TVD-BasEnv™ is rather complex and brings a lot of nice features for Oracle Database environments with ASM, RAC, DataGuard and more stuff which is in general not required on a simple OUD server.

My OUD Base is basically just the oudenv.sh script, some configuration files and a bunch of aliases. The directory structure for the OUD binaries, scripts and configuration files is similar to what we use in TVD-BasEnv™ and based on OFA. It is written in bash and tested on my Oracle Linux VM’s and Raspberry Pi’s with Raspbian Jessy. It should also run on any other bash environment. Um, well OUD and Raspberry Pi? Yes I’ll explain this soon in an other blog post.

Setup the Environment

In general I do use a dedicated OS user for my Oracle installations. To keep it simple and clear I name it oracle. The following commands are run on my Raspberry Pi and therefore as OS user pi. Please adjust it accordingly. Create the user and the corresponding OS groups as pi user with sudo.

pi@oud2go:~ $ sudo adduser oracle
Adding user oracle ...
Adding new group oracle (1001) ...
Adding new user oracle (1001) with group oracle ...
Creating home directory /home/oracle ...
Copying files from /etc/skel ...
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully
Changing the user information for oracle
Enter the new value, or press ENTER for the default
    Full Name []: oracle
    Room Number []:
    Work Phone []:
    Home Phone []:
    Other []:
Is the information correct? [Y/n] y
pi@oud2go:~ $ sudo addgroup oinstall
Adding group oinstall (GID 1002) ...
Done.
pi@oud2go:~ $ sudo addgroup osdba
Adding group osdba (GID 1003) ...
Done.
pi@oud2go:~ $ sudo adduser oracle oinstall
Adding user oracle to group oinstall ...
Adding user oracle to group oinstall
Done.
pi@oud2go:~ $ sudo adduser oracle osdba
Adding user oracle to group osdba ...
Adding user oracle to group osdba
Done.

Create an ORACLE_BASE directory which is used for OUD and provide access to OS user oracle.

pi@pi2go:~ $ sudo mkdir -p /u00/app/oracle
pi@pi2go:~ $ sudo chown -R oracle:oinstall /u00/app/oracle

My OUD Base is available as Bash Install script with an embedded TAR ( oudbase_install.sh) or as plain TAR file ( oudbase_install.tgz). If you use the TAR file a few manuell configuration steps are required.

Install using oudbase_install.sh

This installation is straightforward as you can see in the usage.

2016-10-15_11:41:58  START: Start of oudbase_install.sh (Version 0.1) with
2016-10-15_11:41:58  INFO : Usage, oudbase_install.sh [-hv] [-b <oracle_base>]
2016-10-15_11:41:58  INFO :   [-i <oracle_instance_base>] [-m <oracle_home_base>] [-B <oud_backup_base>]
2016-10-15_11:41:58  INFO :
2016-10-15_11:41:58  INFO :   -h                          Usage (this message)
2016-10-15_11:41:58  INFO :   -v                          enable verbose mode
2016-10-15_11:41:58  INFO :   -b <oracle_base>            ORACLE_BASE Directory. Mandatory argument.
2016-10-15_11:41:58  INFO :   -i <oracle_instance_base>   Base directory for OUD instances (default $ORACLE_BASE/instances)
2016-10-15_11:41:58  INFO :   -m <oracle_home_base>       Base directory for OUD binaries (default $ORACLE_BASE/middleware)
2016-10-15_11:41:58  INFO :   -B <oud_backup_base>        Base directory for OUD backups (default $ORACLE_BASE/backup)
2016-10-15_11:41:58  INFO :
2016-10-15_11:41:58  INFO : Logfile : /u00/app/oracle/local/log/oudbase_install.log
2016-10-15_11:41:58  ERR  : Exit Code 1. Wrong amount of arguments. See usage for correct one.

We will just provide the ORACLE_BASE and use the default values for all other settings.

oracle@pi2go:~ $ ./oudbase_install.sh -v -b /u00/app/oracle
2016-10-15_11:44:03  START: Start of oudbase_install.sh (Version 0.1) with -v -b /u00/app/oracle
2016-10-15_11:44:03  INFO : processing commandline parameter
2016-10-15_11:44:03  Installing OUD Environment
2016-10-15_11:44:03  Create required directories in ORACLE_BASE=/u00/app/oracle
2016-10-15_11:44:03  Create Directory /u00/app/oracle/etc
2016-10-15_11:44:03  Create Directory /u00/app/oracle/local
2016-10-15_11:44:03  Create Directory /u00/app/oracle/backup
2016-10-15_11:44:03  Create Directory /u00/app/oracle/middleware
2016-10-15_11:44:03  Create Directory /u00/app/oracle/instances
2016-10-15_11:44:03  Extracting file into /u00/app/oracle/local
bin/
bin/oud_export.sh
bin/oud_backup.sh
bin/oudenv.sh
bin/oudbase_install.sh
bin/oud_status.sh
config/
certificates/
doc/
etc/
etc/oudtab
etc/oudenv.conf
etc/oud._DEFAULT_.conf
lib/
log/
log/oud_status.log
log/oud_export.log
log/oud_backup.log
log/oudbase_install.log
templates/
templates/cron.d/
templates/cron.d/oud
templates/.bash_profile
templates/ldif/
templates/ldif/oud_pi_init.ldif
templates/logrotate.d/
templates/logrotate.d/oud
2016-10-15_11:44:03  Please manual adjust your .profile to load / source your OUD Environment
2016-10-15_11:44:03  END  : of oudbase_install.sh

You have to change your bash profile to make sure that the environment is loaded. Just add the following lines.

oracle@pi2go:~ $ vi .profile
# Check OUD_BASE and load if necessary
if [ "${OUD_BASE}" = "" ]
  then
    if [ -f "${HOME}/.OUD_BASE" ]
      then
        . "${HOME}/.OUD_BASE"
      else
        echo "ERROR: Could not load ${HOME}/.OUD_BASE"
    fi
fi

# define an oudenv alias
alias oud='. ${OUD_BASE}/bin/oudenv.sh'

# source oud environment
. ${OUD_BASE}/bin/oudenv.sh

During the next logon you have the OUD Base available

Manual installation using oudbase_install.tgz

Ok, it is not really more complex just un-tar the file in a directory. Normally it is $ORACLE_BASE/local. Other directory probably have to be specified in the config file.

oracle@pi2go:~ $ cd /u00/app/oracle/
oracle@pi2go:~ $ mkdir local
oracle@pi2go:~ $ cd local
oracle@pi2go:~ $ tar zxvf oudbase_install.tgz

You also have to change your bash profile as mentioned above.

Examples

A few example how to use OUD Base to simplify OUD management.

Change environment to OUD instance oud_pi.

oracle@pi2go:~/ [oud_pi] oud_pi
Source environment for OUD Instance oud_pi
--------------------------------------------------------------
 Instance Name   : oud_pi
 Instance Home   : /u00/app/oracle/instances/oud_pi
 Oracle Home     : /u00/app/oracle/middleware/oud_11.1.2.3
 Instance Status : up
 LDAP Port       : 1389
 LDAPS Port      : 1636
 Admin Port      : 4444
 Replication Port: 8989
--------------------------------------------------------------

List available / running OUD instances using oudup or via alias u.

oracle@pi2go:~/ [oud_pi] oudup
TYPE INSTANCE   STATUS PORT HOME
---- ---------- ------ ---- ----------------------------------
OUD  oud_pi     up     4444 /u00/app/oracle/instances/oud_pi

Configuration and Architecture

Config Files

The OUD Base does have the following configuration files.

File Description
.OUD_BASE This is a simple file in the user home directory. It includes the pointer to the OUD Base directory. This file is used to initiate $OUD_BASE.
oudtab oudtab is a simple file which includes all OUD instance and there ports eg. default LDAP port, admin port, SSL port and replication port.
oudenv.conf This is the main configuration file for environment variables and aliases. It is loaded when an environment is set or changed. Location of oudenv.conf is $ETC_BASE.
oud._DEFAULT_.conf This configuration file for custom environment variables. Location of oud._DEFAULT_.conf is $ETC_BASE.
oud._INSTANCE_.conf This configuration file for custom environment variables for a dedicated OUD instance eg. oud_pi Location of oud._oud_pi_.conf is $ETC_BASE.

Directories and its variables

The following directory, environment variables and aliases are defined and used in OUD Base. Most of them are inspired by OFA (Oracle Flexible Architecture) and TVD-BasEnv™.

ENV Variable Alias Path Description
$ORACLE_BASE, $cdob cdob /u00/app/oracle Base directory for the oracle binaries
$OUD_BASE, $cdl cdl $ORACLE_BASE/local OUD Base directory with the scripts, config etc
cdl.bin $ORACLE_BASE/bin Scripts directory in OUD_BASE
$ETC_BASE, $etc etc, cdl.etc $ORACLE_BASE/etc OUD Base configuration directory
$LOG_BASE, $log log, cdl.log $ORACLE_BASE/log OUD Base log directory
$ORACLE_BASE/doc OUD Base documentation directory
$ORACLE_BASE/config Local directory for configuration files, LDIF etc to build an OUD instance
$ORACLE_BASE/certificates Local directory for certificates
$ORACLE_HOME, $cdh cdh $ORACLE_BASE/middleware/oud_11.1.2.3 Oracle Unified Directory binaries eg. 11.1.2.3
$JAVA_HOME /usr/lib/jvm/jre-1.7.0-oracle-1.7.0.101-1jpp.1.el7.x86_64 Java used for OUD
$OUD_INSTANCE_BASE, $cdib cdib $ORACLE_BASE/instances Base directory for the instance homes
oud_pi Alias to set environment for OUD instance oud_pi
$OUD_INSTANCE_HOME, $cdih cdih $ORACLE_BASE/instances/oud_pi OUD Instance Home directory for Instance oud_pi
$cdic cdic $OUD_INSTANCE_HOME/OUD/config Config directory for OUD instance oud_pi
$cdil cdil $OUD_INSTANCE_HOME/OUD/logs Log directory for OUD instance oud_pi

Variables

Variable besides the ones mentioned above.

Variable Description
$OUD_INSTANCE Name of the current OUD instance
$OUD_INST_LIST List of OUD instances taken from $OUDTAB
$PWD_FILE Password file for the OUD instance eg. ${ETC_BASE}/$OUD_INSTANCE_pwd.txt or ${ETC_BASE}/pwd.txt
$PORT OUD instance port taken from oudtab file
$PORT_ADMIN OUD instance admin port taken from oudtab file
$PORT_REP OUD instance replication port taken from oudtab file
$PORT_SSL OUD instance SSL port taken from oudtab file
$OUDTAB oudtab config file eg. ${ETC_BASE}/oudtab

Aliases

Alias Description
dsc dsconfig including hostname, $PORT_ADMIN and $PWD_FILE
dsrs dsreplication status
oud_pi OUD Base does generate an alias for each OUD instance based on its name. This allows to easily change the environment from one to an other OUD instance.
oud INSTANCE Use oud INSTANCE name to change the environment to a particular OUD instance
taa tea will do a tail -f on the OUD instance access log
tae tea will do a tail -f on the OUD instance error log
tas tea will do a tail -f on the OUD instance server.out log
tarep tea will do a tail -f on the OUD instance replication log
task task does run a manage-tasks with hostname, port etc parameter
u u runs oudup to display the current OUD Instances
vio vio opens the oudtab file eg. ${ETC_BASE}/oudtab

Conclusion

Although there is the possibility to use property files for OUD I’m still happy, that I have a bunch of aliases to set or change a few directories. Eg. jump to the log directory, view config files etc. Feel free to use the OUD Base as it is on your OUD environments at your own risk. It simplifies a few settings in particular if you have multiple OUD instance on one system. You may change, modify the scripts as you like. I can not guarantee, that the scripts do not have any errors or bugs. Please test before you start using them on a production environment.

Files and References

Below you find a few references related to Raspberry Pi, USB OTG or Oracle Unified Directory:

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.