Category Archives: 18c

Security Best Practice: Oracle passwords, but secure!

Beach view in Brighton at the UKOUG Techfest 2019

Today I held my presentation about Oracle security best practice “Oracle passwords, but secure!” at the virtual UKOUG event. Unfortunately, this year the beautiful view of Brighton beach and the active exchange with colleagues was missing. Ok, on the other hand I was able to enjoy the first snow in Switzerland with my children. 😊

The following blog post is a summary of my presentation with some examples, notes, references and slides.

Oracle Password Hashes

The different Oracle Database releases do provide various password verifiers. Although the older password verifiers are no longer state of the art, they are still used relatively frequently. It is therefore essential to take the appropriate measures to make password-based authentication secure. Oracle currently offers the following password hash functions:

  • Oracle 10g Hash Function based on DES and an Oracle specific algorithm. It is case insensitive and does use a weak password salt i.e. the username is used as salt.
  • MD5 based Hash Function used for digest authentication in XDB
  • Oracle 11g Hash Function based on the SHA1 hash algorithm. But since 2005 SHA1 is no longer considered as safe. The hash function does supports case sensitive and multibyte character passwords.
  • Oracle 12c Hash Function based on a de-optimised algorithm involving PBKDF2 and SHA-512. It supports case sensitive and multibyte character passwords.

The different password verifiers can be controlled by SQLNET.ALLOWED_LOGON_VERSION_SERVER respectively SQLNET.ALLOWED_LOGON_VERSION_CLIENT or by setting the passwords explicitly using ALTER USER ... IDENTIFIED BY VALUES.

Create different users with different password verifiers

CREATE USER test_10g IDENTIFIED BY VALUES 'AF310E4D20D06950';
CREATE USER test_11g IDENTIFIED BY VALUES 'S:6702B83E88D277BFC378AD6B22DD1AE01895A254470F8124A9D3C5347056';
CREATE USER test_12c IDENTIFIED BY VALUES 'T:45738A7B75C9E31ED0C533BCF4931084658A143FD7CF826B980A88EA6C4F0BE66C28DA7085BCAE386723029BA967DC4F45E9C146F6FA7C22E44BA2C1BD2F56F8C22291D417E26D4B810003F3F055EDFF';
CREATE USER test_all IDENTIFIED BY Welcome1;

In DBA_USERS you will see the different password versions

SET LINESIZE 160 PAGESIZE 200
COL username FOR a10
COL password_versions FOR a20
SELECT username, password_versions 
FROM dba_users WHERE username LIKE 'TEST_%';

USERNAME    PASSWORD_VERSIONS
----------- --------------------
TEST_10G    10G
TEST_11G    11G
TEST_ALL    10G 11G 12C
TEST_12C    12C

Or in USER$ you can find the corresponding hashes:

SET LINESIZE 160 PAGESIZE 200
COL name FOR a10
COL password FOR a16
COL spare4 FOR a64
SELECT name,password,spare4 
FROM user$ WHERE name LIKE 'TEST_%' ORDER BY 1;

NAME       PASSWORD         SPARE4
---------- ---------------- ----------------------------------------------------------------
TEST_10G   AF310E4D20D06950
TEST_11G                    S:6702B83E88D277BFC378AD6B22DD1AE01895A254470F8124A9D3C5347056
TEST_12C                    T:45738A7B75C9E31ED0C533BCF4931084658A143FD7CF826B980A88EA6C4F0B
                            E66C28DA7085BCAE386723029BA967DC4F45E9C146F6FA7C22E44BA2C1BD2F56
                            F8C22291D417E26D4B810003F3F055EDFF

TEST_ALL   4932A1B4C59EC3D0 S:ABF25107166264C8EAFE72BF02152DE17000F359CB5BAF21A6AF41477633;T
                            :62FEE108652A56D940813F54EC72D1494ACAD99F2BBDD0A578BF1F97FAB4A7E
                            B468A98B6B553E460DE21E57F6C35A930DEE027D20B33ED13D56EA0ECACB1CEA
                            94EEC8AC389561346052BB0BFF2C06647

Manually create a Oracle 10g password verifier:

SQL> @create_password_hash.sql system ieShae0

Username : system
Password : ieShae0
Hash	 : 0AD56CF5F1CB8D2A
SQL	 : alter user system identified by values '0AD56CF5F1CB8D2A';

PL/SQL procedure successfully completed.

Testing the Password Verifier

There are a couple of possibilities and tools to “verify” password hashes. Among the best known are the tools Hashcat and John the Ripper. These tools doe support a wide range of hashes as well attack methods. Below you find an example of a brute force attack for the Oracle hash we created above.

  • --increment will start to brute force with shorter length e.g 4 characters
  • -custom-charset1 to define numbers and characters
  • -hash-type Oracle 7+ respectively password verifier 10g
  • --show show the password
echo "0AD56CF5F1CB8D2A" >demo.hash
hashcat --attack-mode 3 --increment --increment-min 4 \
--custom-charset1 ?l?d --hash-type 3100 ./demo.hash ?1?1?1?1?1?1?1
hashcat --hash-type 3100 ./demo.hash --show

Good Practice

Here are a few good practices on Oracle passwords.

  • Keep your Oracle Clients and Server up to date. Stay updated by following Critical Patch Updates, Security Alerts and Bulletins. Install security fixes in a reasonable time frame
  • Consider using strong Authentication like Kerberos and SSL based authentication.
  • Don’t use legacy password verifier
    • Use Oracle password file version 12.2
    • Explicitly configure ALLOWED_LOGON_VERSION_SERVER to 12a and exclusively use 12c hash values
    • Start using PBKDF2 SHA-512 for directory-based password authentication with EUS and CMU
  • Revise your password policies
    • NIST, CIS, STIG and other standards are continuously adjusted.
    • Does the complexity rule still make sense or does it just reduce the amount of possibilities.
  • User awareness training. Make sure your user know the principle of good and bad Use of phase phrase rather than password

Slides of the UKOUG Presentation

UKOUG Presentation Security Best Practice: Oracle passwords, but secure!

References

Links and references related to this blog post

Oracle Password Filter for AD, a few exciting insights

When it comes to the conception and implementation of a central user administration of Oracle databases, authentication is one of the central topics. Often there is a need for integration with an existing directory service or IAM solution. Whereby usually MS Active Directory is involved. But Oracle Databases and MS Active Directories are not yet best friends. In this blog post we will explain why this is so, with a focus on authentication.

A few Basics

Oracle Databases does provide a couple of authentication methods. This includes among others the following:

  • Password authentication
  • OS authentication
  • Kerberos authentication
  • SSL authentication

All methods have their advantages and disadvantages and thus their justification. But now let’s speak about password authentication. This works basically always the same way, no matter if database or directory based. The picture below shows the schematic diagram of the password authentication process.

Schematic flow of password authentication
  • The user does send the logon request with its username to the database.
  • The database generates a session key, to encrypt communication.
  • The client generates the password hash and sends it encrypted to the DB server.
  • The database now compares the password hashes.
    • either the hash from USER$
    • or the hash from the directory server

The key aspect is that the database always verifies the password hashes. Either with the hash in the database or, in case of directory-based authentication, with the hash from the directory. The process is used when using Oracle Centrally Managed Users (CMU) but also with Oracle Enterprise User Security in combination with an Oracle Directory e.g. Oracle Unified Directory EUS AD Proxy. In case of a regular LDAP directory, the hash is read from userPassword or another attribute. However, this is not possible in MS Active Directory, where passwords are stored internally in the Security Account Manager (SAM) and cannot be read directly. This is one of the reasons why Active Directory is not fully LDAP v3 compliant. But that is an other story 😉

This is now the moment where the Oracle password filter comes into play. Microsoft does provide a functionality within Windows called password filter. These filters provide a way to implement password policies and change notification. When a password change request is made, the Local Security Authority (LSA) calls the password filters registered on the system. Each password filter is called twice: first to validate the new password and then, after all filters have validated the new password, to notify the filters that the change has been made. The following illustration shows this process.

Password filter and change notification

Oracle Password Filter

The Oracle password filter solves relatively simply the problem that the hash cannot be read. The filter uses the password change notification and stores the password accordingly in an additional LDAP attribute. The database or directory server on the other hand is then able to read the user password hash. Oracle Databases and Active Directory starts to like each other 🤓. But usually Windows or Security Admins are not so happy any more. The fact that a foreign DLL has to be installed on the domain controller sometimes causes headaches or just endless discussions…

The latest version of the password filter is delivered as EXE file opwdintg.exe. It is part of any Oracle Database binaries as of release 18c. Older version of Oracle Database, Oracle Internet Directory and Oracle Unified Directory do also include the password filter in an other form e.g. setup.exe or a jar file. Nevertheless it is crucial, that you get the latest version which is right now part of Oracle Database 19.8.0.0. This is also the valid version when you use OUD or OID, see MOS Note 2640135.1 How to Get the Latest oidpwdcn.dll (New Name orapwdfltr.dll). Alternatively you can also download a generic patch 23191994 for fusion middleware.

But what exactly happens when you install the Oracle password filter? Oracle performs the following steps during installation:

  • Add an Active Directory schema extension for an additional user attribute orcleCommonAttribute. Once installed a schema extension can not be removed any more.
  • Create some generic groups to control the password filter plugin. The filter will only update the orcleCommonAttribute attribute for users which are part directly or indirectly of one of the group.
    • ORA_VFR_MD5 is required when the Oracle Database WebDAV client is used
    • ORA_VFR_11G enables the use of the Oracle Database 11G password verifier
    • ORA_VFR_12C enables the use of the Oracle Database 12C password verifier
  • Install the Oracle password filter DLL orapwdfltr.dll. This requires a reboot of the domain controller.

The following screenshots show the installation of the Oracle password filer.

Schema Extension done by the Oracle Password Filter
DLL Installation by the Oracle Password Filter

After a reboot the installation of the Oracle password filter is finished. Now let’s see what’s new there. First we review the AD schema change. This can be done by starting the Microsoft Management Console (MMC) and open the Active Directory Schema Snap-In. See the old documentation install the Schema Snap-In if the snap-in is not available. The following screenshot does show the details about the new attribute.

New orcleCommonAttribute

In the registry we see under LSA an additional entry for the notification packages. orapwdfltr the name of the DLL installed on the domain server.

New LSA notification packages

And finally the new groups and the new attribute orcleCommonAttribute.

New generic Oracle groups

Although the attribute orcleCommonAttribute in the picture below does only get propagated after a password reset. The user KING is part of the group Trivadis LAB Users. This group itself is member of ORA_VFR_11G.

Attributes of user KING

Yeah, but it’s an Oracle tool…

In one of my many conversations with customers about these password filters I was asked if they could examine the source code. Mmm, no! It is quite common that neither Oracle nor Microsoft publish their source code. In this case Oracle uses an API or functionality defined and documented by Microsoft. But this does not convince everyone. That’s why I have tried to investigate this in detail. One of my first attempts was a test if I can decompile the DLL. This would be possible if it is written in .net or something similar, but not with C or C++. You can use an online disassembler, but the result will not help you.

Analysis of the executable installation file opwdintg.exe with exiftool, reveal that it is only a self extracting cabinet.

exiftool opwdintg.exe 
ExifTool Version Number         : 12.00
File Name                       : opwdintg.exe
Directory                       : .
File Size                       : 193 kB
File Modification Date/Time     : 2020:09:04 06:17:15+02:00
File Access Date/Time           : 2020:09:04 06:18:43+02:00
File Inode Change Date/Time     : 2020:09:04 06:17:15+02:00
File Permissions                : rw-r--r--
File Type                       : Win64 EXE
File Type Extension             : exe
MIME Type                       : application/octet-stream
Machine Type                    : AMD AMD64
Time Stamp                      : 2013:10:14 08:48:22+02:00
Image File Characteristics      : Executable, Large address aware
PE Type                         : PE32+
Linker Version                  : 11.0
Code Size                       : 32768
Initialized Data Size           : 163840
Uninitialized Data Size         : 0
Entry Point                     : 0x7f1c
OS Version                      : 6.3
Image Version                   : 6.3
Subsystem Version               : 5.2
Subsystem                       : Windows GUI
File Version Number             : 11.0.9600.16428
Product Version Number          : 11.0.9600.16428
File Flags Mask                 : 0x003f
File Flags                      : (none)
File OS                         : Windows NT 32-bit
Object File Type                : Executable application
File Subtype                    : 0
Language Code                   : English (U.S.)
Character Set                   : Unicode
Company Name                    : Microsoft Corporation
File Description                : Win32 Cabinet Self-Extractor
File Version                    : 11.00.9600.16428 (winblue_gdr.131013-1700)
Internal Name                   : Wextract
Legal Copyright                 : © Microsoft Corporation. All rights reserved.
Original File Name              : WEXTRACT.EXE            .MUI
Product Name                    : Internet Explorer
Product Version                 : 11.00.9600.16428

You can invoke the executable with two additional parameters C and T to extract the content into the directory specified with T.

c:\vagrant>opwdintg.exe /C /T:c:\vagrant\opwdintg

In the directory you will find three files:

  • instpflt.bat Batch file used to install the password filter.
  • etadschm.bat Batch file used to do the schema extension for orcleCommonAttribute and create the 3 AD groups.
  • orapwdfltr.dll the Oracle password filter dll itself.

Even if you cannot decompile orapwdfltr.dll, you can still examine the batch files. As expected, the batch files do exactly what we have already verified graphically above. Schema extension, create groups and register Oracle password filter.

With pev, a PE file analysis toolkit, we can check other stuff like the functions exported by the DLL. As you can see in the output below, the functions correspond to Microsoft’s specifications for password filters. An indication that the DLL does what it should. However, pev provides other tools to analyse the DLL, hashes, import functions etc. But we will skip that at this point.

readpe --exports orapwdfltr.dll 
Exported functions
    Library
        Name:                            orapwdfltr.dll
        Functions
            Function
                Ordinal:                         1
                Address:                         0x1080
                Name:                            InitializeChangeNotify
            Function
                Ordinal:                         2
                Address:                         0x2ea0
                Name:                            PasswordChangeNotify
            Function
                Ordinal:                         3
                Address:                         0x1080
                Name:                            PasswordFilter

A few words about Security

But what about security? There are basically two aspects. First, the fact that the DLL is a rather critical component. There are known malware that exploit exactly this method to get the passwords. It is therefore a best practice to configure LSA security to allow only signed DLLs for LSA. Ok, besides that you should also know which DLL you have installed and why. But here we are at the point where it gets a bit difficult. Oracle has forgotten to sign orapwdfltr.dll in the past. Therefore, if LSA security is enabled the password filter will not work. See also MOS note 2612535.1 or 2616566.1. Among other things, Oracle has proposed to turn off the LSA security. Certainly not the way to go. But luckily there is already a bug 31134430 and patch 23191994 available for this issue. The fix does include a signed version of the orapwdfltr.dll, as you can see in the following code block.

signtool.exe verify /pa /v orapwdfltr.dll

Verifying: orapwdfltr.dll

Signature Index: 0 (Primary Signature)
Hash of file (sha256): 2A14712107D424FF5577EF5C3D111CF66DB40F6226047ADC4F31389D69F437EB

Signing Certificate Chain:
 Issued to: VeriSign Class 3 Public Primary Certification Authority - G5
 Issued by: VeriSign Class 3 Public Primary Certification Authority - G5
    Expires:   Wed Jul 16 16:59:59 2036
    SHA1 hash: 4EB6D578499B1CCF5F581EAD56BE3D9B6744A5E5

 Issued to: Symantec Class 3 Extended Validation Code Signing CA - G2
 Issued by: VeriSign Class 3 Public Primary Certification Authority - G5
        Expires:   Sun Mar 03 16:59:59 2024
        SHA1 hash: 5B8F88C80A73D35F76CD412A9E74E916594DFA67

    Issued to: Oracle America Inc.
    Issued by: Symantec Class 3 Extended Validation Code Signing CA - G2
            Expires:   Wed Jan 27 16:59:59 2021
            SHA1 hash: 1CB08E9B70B917E64407A4F2665799D58B171F89

The signature is timestamped: Wed Apr 22 18:33:05 2020
Timestamp Verified by:
    Issued to: DigiCert Assured ID Root CA
    Issued by: DigiCert Assured ID Root CA
    Expires:   Sun Nov 09 17:00:00 2031
    SHA1 hash: 0563B8630D62D75ABBC8AB1E4BDFB5A899B24D43

        Issued to: DigiCert SHA2 Assured ID Timestamping CA
        Issued by: DigiCert Assured ID Root CA
        Expires:   Tue Jan 07 05:00:00 2031
        SHA1 hash: 3BA63A6E4841355772DEBEF9CDCF4D5AF353A297

            Issued to: TIMESTAMP-SHA256-2019-10-15
            Issued by: DigiCert SHA2 Assured ID Timestamping CA
            Expires:   Wed Oct 16 17:00:00 2030
            SHA1 hash: 0325BD505EDA96302DC22F4FA01E4C28BE2834C5

Successfully verified: orapwdfltr.dll

Number of files successfully Verified: 1
Number of warnings: 0
Number of errors: 0

Alternatively you can also check the windows property of orapwdfltr.dll.

Properties of orapwdfltr.dll

The other security challenge is the password hash itself. In a regular LDAP, ACIs are usually defined to restrict access to password attributes. However, no ACIs are defined when installing the Oracle password filter. It is therefore strongly recommended to restrict access to this attribute. Generally only the Oracle service accounts, which are used to setup Oracle AD integration, has to read it.

Conclusion

When my workmate Martin Berger published his blog post about the issue with LSA and the password filter, there was no official solution beside disabling LSA security. Fortunately it is a bit better in the meantime. The bug fix did found its way in the latest release of Oracle Database 19c (19.8.0.0) and in the generic fusion middleware patch 23191994. This official signed version of the password filter can be used for either Oracle Centrally Managed Users (CMU), Oracle Enterprise User Security (EUS) or Oracle Unified Directory DIP. It is a fact that this password filter means a change on the domain server. Every change represents a potential risk. Nevertheless, this change is comprehensible and is, according to Microsoft, a documented procedure. By carefully assigning the Oracle groups (ORA_VFR_11g, ORA_VFR_11C, etc), you can ensure that only those users who need the hash in orcleCommonAttribute have set it. It is also recommended to define ACIs to limit access to orcleCommonAttribute restrictively.

SSL and Kerberos authentication are basically secure methods. Additionally these authentication methods allow Single Sign On. Unfortunately, practice shows that many tools cannot handle this. Password authentication on the other hand offers greater flexibility. The Oracle password filter is not bad nor dangerous. In my humble opinion it is worth to consider this solution.

References

A few links related to this blog post:

PDB Isolation and Security

Brighton see side

Today I did have my first presentation at the UKOUG TechFest 2019 in Brighton. Looking back it was a great day with many interesting lectures and good conversations with colleagues and partners. After a long and exhausting day I’ll take a few minutes to sum up my presentation about the PDB isolation and Security.

When you start using Oracle Multitenant, it makes sense to consider a few specific security topics. In general, the same security principles apply to Oracle Multitenant databases as to regular single tenant databases. But depending on the purpose of PDBs, it will be relatively important to implement a few security measures which are beyond of the scope of general database security.

PDB Isolation and Security at a glance

In operation, Oracle container databases use shared resources. These include a backup LAN, management LAN and other system resources. On the other hand, individual Oracle features do access system resources as user oracle. In a common environment, this is basically a security risk. In a DBAAS environment, these can be the following risks.

  • PDB admin use privilege escalation.
  • Excessive use of shared resources.
  • Access sensitive data via shared resources e.g. backup or management LAN.
  • Break out of PDB and get OS access as oracle.
  • Gain access to the root container (cdb$root)
  • Gain access to other PDBs.
  • Gain access to the network.
  • Use of critical features like.
  • Administration features
  • Oracle JVM
  • DBMS_SCHEDULER
  • External table pre-processor

In principle, the owner of each PDB has DBA privileges or equivalent privileges on his PDB. Various measures are taken to ensure security and separation from the operating system and other PDBs. This includes the following measures:

  • Provide DBA role respectively customized DBA role to PDB_ADMIN
  • Managing OS Access using PDB_OS_CREDENTIAL.
  • Manage File Access using PDB PATH_PREFIX and CREATE_FILE_DEST.
  • Restrict user operation in PDBs in an Oracle multitenant container database using lockdown profiles.

The following figure shows a diagram of the container database and security measures.

Demo and Engineering Environment

The easiest way to verify the various measures for PDB security, is to use a couple of examples. The demo and test environment for PDB isolation and security presented here is based on Oracle database in Docker containers. The Docker images are build according to the build script in the GitHub repository https://github.com/oehrlis/docker. With a few adjustments the scripts can be run in any Oracle container database. If you follow the Docker-based approach, the following basic images are required:

  • Oracle Database 12 Release 2 RU April 2019
  • Oracle Database 19c Release Update October 2019 (19.5.0.0)

See https://github.com/oehrlis/docker/tree/master/OracleDatabase for more Information about how to build the Docker images.

Setup Docker Container

If the appropriate docker images are available, an adequate lab environment can be set up within a very short time. Just make sure you update and adjust the docker-compose.yml file to fit your environment, before you setup the docker container. The compose file does contain two database services. One for Oracle 12c Release 2 and on for Oracle 19c (19.5.0.0).

Run docker-compose to create the Docker containers.

docker-compose up -d

Check the progress of you container and database creation

docker-compose logs -f

As soon as you the the following message your database is ready to use.

    tvd122    | ---------------------------------------------------------------
    tvd122    |  - DATABASE TTVD122 IS READY TO USE!
    tvd122    | ---------------------------------------------------------------
    ...
    tvd190    | ---------------------------------------------------------------
    tvd190    |  - DATABASE TTVD190 IS READY TO USE!
    tvd190    | ---------------------------------------------------------------

Test which PDB OS credential require some OS user. To create these user log into either of the container as root and run 01_add_pdb_os_user.sh.

    docker exec -it -u root tvd190 bash --login
    bash-4.2# /u01/config/scripts/01_add_pdb_os_user.sh 
    Found passwd utility
    Skip, group orapdb exists.
    Skip, user orapdb exists.
    Changing password for user orapdb.
    passwd: all authentication tokens updated successfully.
    Add PDB OS user orapdbsec:
    Changing password for user orapdbsec.
    passwd: all authentication tokens updated successfully.
    Add PDB OS user orapdb1:
    Changing password for user orapdb1.
    passwd: all authentication tokens updated successfully.
    Add PDB OS user orapdb2:
    Changing password for user orapdb2.
    passwd: all authentication tokens updated successfully.
    Add PDB OS user orapdb3:
    Changing password for user orapdb3.
    passwd: all authentication tokens updated successfully.

Your now ready to use you Docker base PDB isolation and security environment.

Setup regular Database Environment

Of course, the scripts can also be tested in a regular database environment. You only have to make the following adjustments.

  • Create a tnsnames.ora entry for you PDB. e.g. PDBSEC.
  • Create the run_id.sh scripts and folders according to 00_prepare_pdb_env.sh Script.
  • Create dedicated OS user for the PDB_OS_CREDENTIAL test using the scripts 01_add_pdb_os_user.sh.
  • Optional install the patch 25820082
  • Adjust the demo script to match your environment e.g. directory path, pdb name etc.

Demo and Test Scripts

The demo scripts are located in the script folder. In general, they work for both 12.2 and 19c, except the lockdown profile create script.

  • 00_prepare_pdb_env.sh Script to add a tnsname.ora entry and other stuff for the PDB PDBSEC.
  • 01_add_pdb_os_user.sh Script to add a PDB OS user.
  • 10_create_pdb.sql Create a PDB (pdbsec) used for PDB security engineering.
  • 20_create_directories.sql Script to create directories.
  • 30_create_datafile.sql Script to create datafiles.
  • 40_create_PDB_OS_CREDENTIAL.sql Script to configure PDB_OS_CREDENTIAL.
  • 41_create_ext_table.sql Script to configure table pre-processors.
  • 42_create_scheduler_job.sql Script to configure external OS jobs.
  • 50_create_lockdown_profiles_12.2.sql Script to create lockdown profiles for 12.2.
  • 50_create_lockdown_profiles.sql Script to create lockdown profiles.
  • 51_lockdown_trace_view.sql Script to create lockdown profiles for trace files.
  • 53_lockdown_external_table.sql Script to create lockdown profiles external table.
  • 60_dbms_sys_sql_test.sql Script to verify DBMS_SYS_SQL
  • 90_drop_pdb.sql Drop PDB (pdbsec) used for PDB security engineering
  • ld_profiles.sql Displays information about lockdown profiles.
  • ld_rules.sql Displays information about lockdown rules in the current container.
  • lpdb.sql List PDBS

Slides of the lecture

The slides for the lecture have been uploaded to slideshare. They do provide a few information about the idea and the concept behind the PDB isolation and security.

Oracle Enterprise User Security with multiple ldap.ora

Recently I came across the situation where I have to configure Enterpriser User Security for a database server with multiple databases for different directories. This is quite tricky when using a shared Oracle Home and a central TNS_ADMIN directory for SQLNet configuration. A common TNS_ADMIN also implies the use of only one ldap.ora file. Several ldap servers can be registered in one ldap.ora, but this is primarily used for failover configuration in a high-availability LDAP server architecture. The use of multiple EUS contexts in different LDAP servers is not supported. At least not in one single file. But there are some workarounds.

Oracle does look for the ldap.ora file in a few different places. The following sequence is maintained:

  1. $LDAP_ADMIN environment variable setting
  2. $ORACLE_HOME/ldap/admin directory
  3. $TNS_ADMIN environment variable setting
  4. $ORACLE_HOME/network/admin directory

Notes on this order can be found at different places in the Oracle documentation e.g Oracle Database Database Net Services Reference 19c Overview of Directory Server Usage File, Oracle Database Security Guide 19c About Using a dsi.ora File or in the Oracle Support Note 363283.1 What Is The Search Order For The LDAP.ORA File?

If you don’t trust the documentation, you can also verify the search order with strace. First define a few values for TNS_ADMIN and LDAP_ADMIN to be sure we do not use the default values.

export LDAP_ADMIN="/u01/config"
export TNS_ADMIN="/u00/app/oracle/network/admin"

If we make sure, that LDAP is the first names resolution in sqlnet.ora we could use strace with tsnping.

oracle@eusdb:~/ [TEUS01] grep -i NAMES.DIRECTORY_PATH $TNS_ADMIN/sqlnet.ora
NAMES.DIRECTORY_PATH=(LDAP, TNSNAMES, EZCONNECT )

oracle@eusdb:~/ [TEUS01] strace -o /u01/config/tnsping.txt tnsping TEUS01

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-NOV-2019 20:47:33

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u00/app/oracle/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = eusdb)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEUS01)))
OK (30 msec)

Checking the output does show the different directories

oracle@eusdb:~/ [TEUS01] grep -i ldap.ora /u01/config/tnsping.txt
stat("/u01/config/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.0.0.0/ldap/admin/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/network/admin/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.0.0.0/network/admin/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.0.0.0/network/admin/ldap.ora", 0x7ffd149f6be0) = -1 ENOENT (No such file or directory)

As you can see the search order does match the documented search order. But how does that help us? It’s actually relatively simple. To use different LDAP server configuration per database, we do have to make sure, that each database has an individual ldap.ora file. This can be ensured by one the following points:

  • Have a dedicated Oracle Home for each database with an individual ldap.ora file in each Oracle Home
  • Define an environment variable for LDAP_ADMIN for each database.

Both methods have their advantages and disadvantages, but aren’t optimal. As often, several paths lead to the goal. The third option uses a static listener configuration for the database with an ENVS parameter. Unfortunately, the information about ENVS has disappeared in the latest version of the Oracle documentation. At least in the Oracle Database Database Net Services Reference 19c. But you can search for ENVS in the Oracle Database Bookshelf. The parameter ENVS can be used to specify environment variables for the listener to set prior to executing (as a child process) a dedicated server program or an executable specified with the PROGRAM parameter. This allows a static listener entry to be defined for each database, where LDAP_ADMIN or TNS_ADMIN is explicitly set. Below you see an excerpt of listener.ora for ORACLE_SID TEUS01.

SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = TEUS01 )
       (ORACLE_HOME = /u00/app/oracle/product/19.0.0.0)
       (SID_NAME = TEUS01)
       (ENVS="LDAP_ADMIN=/u00/app/oracle/network/TEUS01")
   )))

The database TEUS01 does use a dedicated ldap.ora. If you add the listener entry just for this DB, you can keep the default ldap.ora in the regular TNS_ADMIN directory. Thus one can apply the following principle:

  • Use a generic ldap.ora configuration in the TNS_ADMIN directory. e.g which is valid for most of the database on this server
  • Add a static listener configuration for each database which does have to use a dedicate Oracle Enterprise User Security configuration and therefor a dedicated ldap.ora.

This method is not only helpful when configuring LDAP server or Oracle Enterprise User Security, but also in other SQLNet use cases. In particular the following:

  • Define TNS_ADMIN and use a dedicate sqlnet.ora configuration for Kerberos authentication. e.g. when you want to use or engineer Kerberos authentication for just one database in an shared environment.
  • Define TNS_ADMIN and use dedicate sqlnet.ora configuration for network encryption.
  • Define LDAP_ADMIN for dedicated ldap.ora or dsi.ora files for engineering centrally managed users.
  • And a couple more…

References

A few links and references related to this blog post

  • Oracle Support Document 728043.1 Use of DIRECTORY_SERVERS in LDAP.ORA & Known issues
  • Oracle Support Document 363283.1 What Is The Search Order For The LDAP.ORA File?
  • Search for ENVS in the Oracle Database Bookshelf
  • Oracle® Database Net Services Reference 11g Release 1 (11.1) ENVS

Audit Trail cleanup in Oracle Multitenant environments

A crucial aspect of any database audit concept is the management and maintenance of audit trails. Depending on the defined audit policies and the database activity, an audit trail can grow relatively quickly. Oracle Multitenant environments increase the operational effort because the root container and each PDB uses their own audit trail. Ok, for a simple CDB with 2-3 PDB this effort is manageable, but what about CDB’s with 40 or more PDB’s?

Let’s look into the different possibilities.

Common Purge Job for all PDB’s

Oracle allow’s to initiate an audit trail clean up for all PDB’s using dbms_audit_mgmt.clean_audit_trail procedure by specifying dbms_audit_mgmt.container_all. The following example initiate a clean up in the root container for all PDB’s without considering the last archive timestamp.

ALTER SESSION SET CONTAINER=cdb$root;
BEGIN
   dbms_audit_mgmt.clean_audit_trail(
      audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
      container => dbms_audit_mgmt.container_all,
      use_last_arch_timestamp => false);
END;
/

The problem with this method are the closed PDB’s. First let’s check how much audit records we do have per PDB.

COLUMN con_id FORMAT 999999
COLUMN count FORMAT 99999
COLUMN name FORMAT a8
SELECT
   v.con_id, v.name, v.open_mode, COUNT(u.event_timestamp) count
FROM
   cdb_unified_audit_trail u
FULL OUTER JOIN v$containers v ON u.con_id = v.con_id
GROUP BY
   v.con_id, v.name, v.open_mode
ORDER BY
   v.con_id;

CON_ID NAME     OPEN_MODE   COUNT
====== ======== =========== =====
     1 CDB$ROOT READ WRITE    644
     2 PDB$SEED READ ONLY       0
     3 PDB1     MOUNTED         0
     4 PDB2     READ WRITE     36
     5 PDB3     MOUNTED         0
     6 PDBSEC   READ WRITE     27

Running the clean up on this container database will end with an ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB.

BEGIN
   dbms_audit_mgmt.clean_audit_trail(
      audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
      container => dbms_audit_mgmt.container_all,
      use_last_arch_timestamp => false);
END;
/

BEGIN
*
ERROR at line 1:
ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 204
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 2137
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 1409
ORA-06512: at line 2

The error ORA-46273 is confusing. When we check the audit records, we see that the entries have been effectively be deleted. Here it would be more understandable if Oracle simply issue a warning rather than an error.

COLUMN con_id FORMAT 999999
COLUMN count FORMAT 99999
COLUMN name FORMAT a8
SELECT
   v.con_id, v.name, v.open_mode, COUNT(u.event_timestamp) count
FROM
   cdb_unified_audit_trail u
FULL OUTER JOIN v$containers v ON u.con_id = v.con_id
GROUP BY
   v.con_id, v.name, v.open_mode
ORDER BY
   v.con_id;

CON_ID NAME     OPEN_MODE   COUNT
====== ======== =========== =====
     1 CDB$ROOT READ WRITE    5
     2 PDB$SEED READ ONLY     0
     3 PDB1     MOUNTED       0
     4 PDB2     READ WRITE    1
     5 PDB3     MOUNTED       0
     6 PDBSEC   READ WRITE    1

The same does apply when we run the clean up task as a job. The job will always fail if one PDB is in MOUNT stat. This is annoying when monitoring scheduler jobs.

SQL> BEGIN
2 DBMS_SCHEDULER.RUN_JOB(job_name => '"AUDSYS"."TVD_TEST"');
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 204
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 2137
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 1409
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_SCHEDULER", line 566
ORA-06512: at line 2

There is an other issue related to dbms_audit_mgmt.clean_audit_trail in Oracle 12.1 to 18c. The procedure does create a wrong scheduler job. The container_all is not set for the scheduler job, which results in the job running only in the root container. The issue is addressed in Oracle bug 27527173 – ISSUE WITH CREATE PURGE JOB FOR UNIFIED_AUDIT_TRAIL WITH CONTAINER_ALL. A bugfix is available for Oracle 12.1.0.2, 12.2.0.1 and 18.0.0.0. Alternatively you can workaround this issue by manually create a scheduler job to purge the audit trail rather than using dbms_audit_mgmt.clean_audit_trail. The issue is permanently fixed in Oracle 19.0.0.0.

dbms_audit_mgmt or Scheduler Job

Considering the above problems, it seems better to directly create a regular scheduler job for deleting audit trails. All right, this does work as expected, but these kind of jobs will never be visible in cdb_audit_mgmt_cleanup_jobs view. Technically this is not a problem, but it is good practice when audit-related clean up jobs are visible where they should.

The following example creates a regular scheduler job.

BEGIN
   dbms_scheduler.create_job(
      job_name => 'TVD_AUDIT_PURGE_JOB',
      job_type => 'PLSQL_BLOCK',
      job_action => 'BEGIN dbms_audit_mgmt.clean_audit_trail(
         audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
         container => dbms_audit_mgmt.container_current,
         use_last_arch_timestamp => false); END;',
      start_date => trunc(sysdate)+(2/24),
      repeat_interval => 'FREQ=HOURLY;INTERVAL=12',
      enabled => true,
      comments => 'Create an audit purge job for unified audit');
END;
/

Insights when creating audit purge job via dbms_scheduler:

  • Job is not visible in cdb_audit_mgmt_cleanup_jobs
  • Start date can be defined when the job is created
  • Job can be created in any schema, which has the appropriate rights.

The following example does create an audit trail clean up job for unified audit trail using dbms_audit_mgmt and adjusting the start time to 01:00 with dbms_scheduler.

BEGIN
   -- Create regular purge job
   dbms_audit_mgmt.create_purge_job(
      audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
      audit_trail_purge_interval => 12,
      audit_trail_purge_name=> 'tvd_unified_audit_purge_job',
      container => dbms_audit_mgmt.container_current,
      use_last_arch_timestamp => true);
   -- Adapt start date of dbms_audit_mgmt purge job
   dbms_scheduler.set_attribute (
      name => 'tvd_unified_audit_purge_job',
      attribute => 'start_date',
      value => trunc(sysdate) + ( 1 / 24 ) );
END;
/

Insights when creating audit purge job via dbms_audit_mgmt:

  • Job is visible in cdb_audit_mgmt_cleanup_jobs
  • Start date can not be defined when the job is created with dbms_audit_mgmt. The start date has to be changed later using dbms_scheduler.set_attribute. This also applies to other adjustments to the job.
  • For Oracle 18c and 19c the job will be created in the AUDSYS schema. In Oracle 12c and earlier it will be created in the SYS schema.

Audit Window

In the above examples I did set use_last_arch_timestamp=>true. This means that only audit trails older than the archive timestamp will be deleted. The archive timestamp is usually set by the Oracle audit vault agent, when it reads the audit records. But what happens a third party tool like splunk collects the audit data? These tools will generally just read the data without setting a corresponding archive timestamp. Oracle will then never delete old audit records. Therefor it is crucial, that the archive timestamp is set by the tool as soon as the data as been collected. Additionally to this, it is good practice to define an audit window. This is nothing else than set up a job which regularly set an archive timestamp e.g. set the timestamp to sysdate-30. This way all audit records older than 30 days will be removed. The database is thus always nicely tidied up. :-The following example creates such an archive timestamp job. Every 12 hours the archive timestamp is set to sysdate-30.

BEGIN
   dbms_scheduler.create_job(
   job_name =>'tvd_unified_audit_timestamp_job',
   job_type => 'PLSQL_BLOCK',
   job_action => 'BEGIN dbms_audit_mgmt.set_last_archive_timestamp(
      audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
      container => dbms_audit_mgmt.container_current,
      last_archive_time => sysdate - 30); END;',
   start_date => trunc(sysdate)+(2/24),
   repeat_interval => 'FREQ=HOURLY;INTERVAL=12',
   enabled => true,
   comments => 'Create an regularly archive timestamp for unified audit');
END;
/

Conclusion

The administration of audit records works well in Oracle Multitenant environments as well. dbms_audit_mgmt helps with the automatic and manual management of audit trails. Nevertheless, there are a few issues and best practices to consider:

  • Audit data should always be stored in a separate tablespace. However, depending on the Oracle version, there may be issues related to the relocation of the audi trail. For example, not all partitions are moved correctly, LOB and index partitions will continue to be created in the old tablespace. See also bug 27576342dbms_audit_mgmt.set_audit_trail_location does not move lob and index partitions or MOS note 2428624.1 Dbms_Audit_Mgmt.Set_Audit_Trail_Location Does Not Move Lob And Index Partitions
  • Adjusting the partition size or the interval when a new audit partition has to be created, has be done according to the audit quantity and database activity..
  • The different jobs for the clean up as well as the setting of the archive timestamp should run in the same scheme. This increases the transparency. As of Oracle 18c < dbms_audit_mgmt creates the jobs under the user AUDSYS. Older versions usually use the SYS user
  • In Oracle Multitenant environments a central clean up job is the easiest, but makes no sense, depending on the state of the PDB’s it always finishes with an error. Here it is recommended to define one job for each CDB / PDB. In this way, the frequency of the deletion can be individually adapted to the database activity and compliance requirements of the particular PDB.
  • So far we just discussed unified audit. If the database does run in pure unified mode there is no need to define a housekeeping for the legacy audit trails (AUD$, FGA$ etc). Although it is a good practice to define a minimal housekeeping for the legacy audit trail.
  • To define a time window in which audit records are keep in the database is usually a easy method to hold a certain level of audit information in the database. Third party tools like splunk do have enough time to collect the relevant information.
  • For successful database audit concept it is crucial to define the different users and roles who can access or maintain audit information.

References

Some links related to this blog post:

  • Oracle® Database PL/SQL Packages and Types Reference 19c DBMS_AUDIT_MGMT
  • Master Note For Oracle Database Auditing [1299033.1]
  • Dbms_Audit_Mgmt.Set_Audit_Trail_Location Does Not Move Lob And Index Partitions [2428624.1]
  • Bug 27576342 – dbms_audit_mgmt.set_audit_trail_location does not move lob and index partitions
  • Bug 22859443 – CONTAINER_ALL IS IGNORED IN CREATE_PURGE_JOB
  • Patch 27527173 – ISSUE WITH CREATE PURGE JOB FOR UNIFIED_AUDIT_TRAIL WITH CONTAINER_ALL (Patch)

Oracle CPU / PSU Advisory July 2019

Recently, just in the middle of the summer holidays, Oracle has released the third Critical Patch Advisory for its products. It seems there’s a lot of work going on in Redwood Shore. Oracle has fixed about 319 security vulnerabilities across their products. The Oracle database is relatively prominently represented with 9 security vulnerabilities and a maximal CVSS rating of 9.8. The problem CVE-2018-11058 with such a high CVSS rating is related to Core RDBMS and affects all Oracle releases on various platforms. In addition this vulnerability can also be exploited remotely over the network. 3 of the security bug fixes are for client-only installations. So you have to patch your database servers as well the clients.

Oracle Unified Directory itself is not mentioned in the Oracle Critical Patch Update Advisory. But the MOS note 2385785.1 Information And Bug Listing of Oracle Unified Directory Bundle Patches: 12.2.1.3.x (12cR2PS3) Version does provide information on the latest bundle patch for OUD. Beside this patch, There are updates for Oracle WebLogic and Oracle Java as well (see links below).

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

  • Oracle 11.2 Core RDBMS, Java VM, Oracle Text
  • Oracle 12.1 Core RDBMS, Java VM, Oracle Text
  • Oracle 12.2 Core RDBMS, Java VM, Oracle Text, Spatial
  • Oracle 18c Core RDBMS, Java VM, Oracle Text, Spatial
  • Oracle 19c Core RDBMS, Java VM

Oracle Java VM is not installed by default. It is therefore recommended that you check your database environment to see if it is necessary to apply this critical patch update.

For Oracle Fusion Middleware the situation looks somehow different. The Critical Patch Update includes not less than 33 fixes for vulnerabilities. Several of the vulnerabilities may be remotely exploitable without authentication and are rated with the highest CVSS rating of 9.8.

By the way, I’ve just update my Docker build scripts for Oracle Databases as well Oracle Unified Directory on GitHub to use the latest release updates. Ok, I still haven’t improved the documentation, but at least the build scripts are up to date. 🙂

A few links related to this Critical Patch Update.

PDB_OS_CREDENTIAL with external table pre-processor

As part of a customer project I am currently enhancing PDB security and isolation. Since OS interaction is necessary, I can not just use lockdown profile to block OS access. The idea is to isolate the PDB with lockdown profiles and allow dedicated OS access. The idea is to isolate the PDB with lockdown profiles and allow dedicated OS access. However, the OS access should be performed by another user than Oracle. This is where PDB_OS_CREDENTIAL comes in. According to Oracle documentation PDB_OS_CREDENTIAL allows to specify the credentials for dedicated operating system user, which is used for OS interaction of the PDB. See PDB_OS_CREDENTIAL in Oracle® Database Reference 19c or Managing Security for a Multitenant Environment in Oracle® Multitenant Administrator’s Guide 19c.

The following OS Interaction are covered by PDB_OS_CREDENTIAL:

  • External table pre-processors
  • PL/SQL library executions
  • External jobs that do not already have an operating system credential specified

OS Configuration

First a dedicated operating system user is required. I use one OS user per PDB and one generic OS user for the CDB. Let’s create them using useradd. Create a dedicated OS group, the OS user for the CDB and set its password to manager. Which in fact is not a secure password, but sufficient for this simple test. 🙂

groupadd restricted
useradd --create-home --gid restricted \
--shell /bin/bash oracdb

echo "manager" | passwd oracdb --stdin

Create OS users for PDB1 and PDB2 and set the passwords to manager.

useradd --create-home --gid restricted \
--shell /bin/bash orapdb1

useradd --create-home --gid restricted \
--shell /bin/bash orapdb2

echo "manager" | passwd orapdb1 --stdin
echo "manager" | passwd orapdb2 --stdin

In addition to the user, we need a test script, which we can be executed as an external table pre-processor. I’ll create a dummy script executing the OS command id. This will show the user as which the script is executed.

echo "/bin/id" >/u01/eng/run_id.sh
chmod 755 /u01/eng/run_id.sh

Create Credentials (DBMS_CREDENTIAL)

Create the credentials for the different OS users using DBMS_CREDENTIAL. The credentials will be created in the root container (cdb$root).

ALTER SESSION SET CONTAINER=cdb$root;

BEGIN
dbms_credential.create_credential(
credential_name =>'GENERIC_PDB_OS_USER',
username => 'oracdb',
password => 'manager');
END;
/

BEGIN
dbms_credential.create_credential(
credential_name => 'PDB1_OS_USER',
username => 'orapdb1',
password => 'manager');
END;
/

Show the credentials:

COLUMN con_id FORMAT 999999
COLUMN owner FORMAT A10
COLUMN credential_name FORMAT A20

SELECT con_id, owner, credential_name
FROM cdb_credentials;

CON_ID OWNER CREDENTIAL_NAME
====== ===== ===============
     1 SYS   PDB1_OS_USER
     1 SYS   GENERIC_PDB_OS_USER

Assign Credentials (PDB_OS_CREDENTIAL)

The init.ora parameter PDB_OS_CREDENTIAL is now assigned to the credentials created above. By setting PDB_OS_CREDENTIAL in the CDB we define a default credential for all PDBs. Although the documentation shows, that the parameter can be set directly with an ALTER SYSTEM, this does not work. (See Multitenant : Pluggable Database (PDB) Operating System (OS) Credentials in Oracle Database 12c Release 2 (12.2) by Tim Hall)

ALTER SYSTEM SET PDB_OS_CREDENTIAL=generic_pdb_os_user SCOPE=SPFILE;

Error by the ALTER SYSTEM command.

ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-65046: operation not allowed from outside a pluggable database

Setting PDB_OS_CREDENTIAL via parameter file does work.

CONNECT / AS SYSDBA
SHOW PARAMETER PDB_OS_CREDENTIAL
SHUTDOWN IMMEDIATE;
CREATE PFILE='/tmp/pfile.txt' FROM SPFILE;
HOST echo "*.pdb_os_credential=GENERIC_PDB_OS_USER" >> /tmp/pfile.txt
CREATE SPFILE FROM PFILE='/tmp/pfile.txt';
STARTUP;
SHOW PARAMETER PDB_OS_CREDENTIAL

NAME              TYPE   VALUE
================= ====== ===================
pdb_os_credential string GENERIC_PDB_OS_USER

Setting a PDB specific credential for the PDB1.

-- set the container to PDB1
ALTER SESSION SET CONTAINER=pdb1;
ALTER SYSTEM SET PDB_OS_CREDENTIAL=PDB1_OS_USER SCOPE=SPFILE;
STARTUP FORCE;
SHOW PARAMETER PDB_OS_CREDENTIAL

NAME              TYPE   VALUE
================= ====== ===================
pdb_os_credential string PDB1_OS_USER

Test the credential

To test the credential we create an external table using the simple script created above as table pre-processor.

Create the directory:

CONNECT tvd_hr/tvd_hr@ol7db18.trivadislabs.com:1521/PDB1
CREATE OR REPLACE DIRECTORY exec_dir AS '/u01/eng';

Create the external table:

CREATE TABLE id (id VARCHAR2(2000))
ORGANIZATION EXTERNAL(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY exec_dir
ACCESS PARAMETERS(
RECORDS DELIMITED BY NEWLINE
PREPROCESSOR exec_dir:'run_id.sh')
LOCATION(exec_dir:'run_id.sh')
);

Select from the external table:

SELECT * FROM id;

ID
========================================================= =
uid=1001(oracle) gid=1010(oinstall) groups=1010(oinstall)

As you can see the user returned from the external table pre-processor is still oracle. Also the log file of the external table pre-processor is created as user oracle.

host ls -al /u01/eng
total 8
drwxr-xr-x. 2 oracle oinstall 43 Jun 17 04:47 .
drwxrwxr-x. 5 oracle oinstall 47 Jun 14 12:20 ..
-rw-r--r--. 1 oracle oinstall 582 Jun 17 04:47 ID_30158.log
-rwxr-xr-x. 1 oracle oinstall 8 Jun 17 04:24 run_id.sh

Conclusion

The parameter PDB_OS_CREDENTIAL is a promising feature to enhance PDB Isolation and security. It seems to be broken for this particular use case. I have opened corresponding service requests with Oracle. But the issue is still investigated. So far there are several bugs assigned to this issue, but unfortunately none of them is public.

  • Bug 18814778 CDB: Tracking bug: PDB_OS_CREDENTIAL does not influence os environment
  • Bug 25820082 PDB_OS_CREDENTIAL parameter not working
  • Bug 29791472 12.2 Lockdown Profile not working as desired
  • Bug 29791380 12.2 Lockdown Profile not working as desired

I’ll provide an update as soon there is a workaround or solution to this issue.

SOUG Day 2019 – Oracle Database in Docker

Today I did have the opportunity to give a presentation on Oracle Database in Docker at the SOUG day in Olten. It was a great opportunity to discuss how Oracle database engineering can be simplified using Docker.

Besides the demo the following topics were discussed:

  • Docker images, container and volumes
  • Requirements to setup Oracle database image
  • Build an Oracle database image
  • Discuss the Dockerfile and build scripts
  • Create database containers
  • Use cases for Oracle database in Docker
  • Demo setup Oracle database with Enterprise User Security and Oracle Unified Directory

Combining docker-compose and custom initialisation scripts allow a various number of use cases for Oracle database in Docker. Rapid deployment of Oracle databases for engineering and testing. But is it also suitable for production environments?

The presentation and information related to event:

Some references and links related to this blog post and the presentation:

Configure Oracle EUSM to use LDAPS

With the introduction of Oracle 18c, eusm is officially designated as an Enterprise User Security Utility. It is now officially documented of the Enterprise User Security Administrator’s Guide. Before we had to be content with the somewhat sparse MOS note 1085065.1 EUSM, Command Line Tool For EUS Administration and Some EUS Good to Knows. In addition, the tool was improved with the latest release. Up to and including Oracle 12c Release 2 it was not possible to establish a secure connection with the LDAP using eusm. The tool does use SASL authentication but still required always an unencrypted LDAP connection to the directory server. For sensitiv environments with enhanced security requirements like Banks, incurrence companies etc. is the use of unencrypted network traffic a nogo. But the new documentation for eusm starts with a short paragraph “About SSL Port Connectivity through EUSM to OID”, which made me confidence.

So there are additional parameters to support SSL:

  • ldap_ssl_port ssl port of the directory server.
  • keystore path to PKCS12 format of keystore. A file path parameter takes the path to the PKCS12 format of the keystore (for example, ewallet.p12 file)
  • key_pass to control the behavior of the keystore password eg. interactive or via commandline

Initial I did get confused by the example. A file named ewallet.p12 is usually an Oracle wallet. Thats why I did start to use an Oracle wallet as keystone for eusm. But this was complete rubbish. Leaning back and thinking again helped. eusm is written in java and the parameter is named keystone. Java and keystore results in a java kestore, doesn’t it? So I was a bit more successful with my second attempt.

Configure the keystore

As soon as one realised that the required keystore file is a java keystore of type PKCS12 it is straight forward. eusm just requires the root certificate to validate the OUD certificate during the initialisation of the LDAPS connection. In an enterprise environment this certificate can be obtained from the internal certification authority. Alternatively this may also be exported from an other keystore. In my EUS test environment I do not have an enterprise CA. Therefor I have to get the corresponding certificate directly from Oracle directory server.

Login to directory server to export the certificate.

keytool -export -noprompt -rfc \
-alias server-cert \
-keystore ${OUD_INSTANCE_HOME}/OUD/config/keystore \
-storepass $(cat ${OUD_INSTANCE_HOME}/OUD/config/keystore.pin) \
-file /u01/config/oud_trusted_cert.txt

Certificate stored in file

Copy the file to the database server and import it into a java keystore. The java keytool will create a new java keystore, if you specify a keystore file which does not yet exist. Do not to specify PKCS12 as the store type. You an either specify the keystore password interactively or use -storepass to provide the password via command line. I do use the password from the keystore pin file $ORACLE_BASE/network/admin/keystore.pin.

$ORACLE_HOME/jdk/bin/keytool -import -trustcacerts \
-alias oud_root_certificate \
-storetype pkcs12 \
-keystore $ORACLE_BASE/network/admin/keystore.jks \
-storepass $(cat $ORACLE_BASE/network/admin/keystore.pin) \
-import -file /u01/oud/oud_trusted_cert.txt

Owner: CN=oud, O=Oracle Unified Directory Self-Signed Certificate
Issuer: CN=oud, O=Oracle Unified Directory Self-Signed Certificate
Serial number: c8cff33
Valid from: Thu Feb 28 06:39:40 UTC 2019 until: Sat Feb 27 06:39:40 UTC 2021
Certificate fingerprints:
MD5: E2:C2:43:8B:CD:EB:95:9E:F1:FC:D8:C3:FF:A7:91:AF
SHA1: 80:0D:9E:89:1B:BC:69:99:02:6A:E7:B5:A6:D2:63:E9:59:5A:C3:BF
SHA256: C7:14:54:1A:C3:FE:28:72:6E:B0:16:82:42:C9:6E:3B:43:BE:D6:C7:3A:31:60:1B:
60:1D:8D:5E:7F:66:D9:7B
Signature algorithm name: SHA1withRSA
Subject Public Key Algorithm: 1024-bit RSA key
Version: 3
Trust this certificate? [no]: yes
Certificate was added to keystore

List the content of your java keystore file.

$ORACLE_HOME/jdk/bin/keytool -list \
-keystore $ORACLE_BASE/network/admin/keystore.jks \
-storepass $(cat $ORACLE_BASE/network/admin/keystore.pin)

Keystore type: JKS
Keystore provider: SUN

Your keystore contains 1 entry

oud_root_certificate, Mar 1, 2019, trustedCertEntry,
Certificate fingerprint (SHA1): 80:0D:9E:89:1B:BC:69:99:02:6A:E7:B5:A6:D2:63:E9:
59:5A:C3:BF

The method is the same if you use an enterprise certificate. You just have to use the root certificate provided by the CA.

Usage of eusm

To establish a connection via SSL, you have to enter the java keystore and the keystore password or -K when invoking eusm. The following command does list the EUS Domain. The password is omitted and has to be specified via command line.

eusm listDomains realm_dn="dc=trivadislabs,dc=com" \
ldap_host=oud \
ldap_ssl_port=1636 \
ldap_user_dn="cn=eusadmin,cn=oraclecontext" \
ldap_user_password=eusadmin \
keystore=$ORACLE_BASE/network/admin/keystore.jks -K

Enter keystore password (key_pass):
LIST OF DOMAINS IN REALM: dc=trivadislabs,dc=com

OracleDefaultDomain

This command does list all the domain mappings. The password is now specified via parameter key_pass.

eusm listMappings domain_name="OracleDefaultDomain" \
realm_dn="dc=trivadislabs,dc=com" \
ldap_host=oud \
ldap_ssl_port=1636 \
ldap_user_dn="cn=eusadmin,cn=oraclecontext" \
ldap_user_password=eusadmin \
keystore=/u00/app/oracle/network/admin/keystore.jks \
key_pass=$(cat $ORACLE_BASE/network/admin/keystore.pin)

LIST OF DATABASE SCHEMA MAPPINGS::

Mapping Name: MAPPING0
Mapping Type: SUBTREE
Mapping DN: ou=People,dc=trivadislabs,dc=com
Mapping schema:EUS_USERS
Mapping Level :DOMAIN

Below you see an excerpt of the OUD access log file. The log entry for the CONNECT command does show the LDAPS protocol.

[01/Mar/2019:14:49:12 +0000] CONNECT conn=1111 from=172.18.0.3:34126 to=172.18.0.2:1636 protocol=LDAPS
[01/Mar/2019:14:49:13 +0000] BIND REQ conn=1111 op=0 msgID=1 type=SIMPLE dn="cn=eusadmin,cn=oraclecontext" version=3
[01/Mar/2019:14:49:13 +0000] BIND RES conn=1111 op=0 msgID=1 result=0 authDN="cn=eusadmin,cn=oraclecontext" etime=0
[01/Mar/2019:14:49:13 +0000] SEARCH REQ conn=1111 op=1 msgID=2 base="dc=trivadislabs,dc=com" scope=base filter="(objectclass=*)" attrs="orclversion"
[01/Mar/2019:14:49:13 +0000] SEARCH RES conn=1111 op=1 msgID=2 result=0 nentries=1 etime=1
[01/Mar/2019:14:49:13 +0000] SEARCH REQ conn=1111 op=2 msgID=3 base="cn=OracleDefaultDomain,cn=OracleDBSecurity,cn=Products,cn=OracleContext,dc=trivadislabs,dc=com" scope=one filter="(|(objectClass=orclDBEntryLevelMapping)(objectclass=orclDBSubtreeLevelMapping))" attrs="cn,orcldbdistinguishedname,orcldbnativeuser,objectclass"
[01/Mar/2019:14:49:13 +0000] SEARCH RES conn=1111 op=2 msgID=3 result=0 nentries=1 etime=1
[01/Mar/2019:14:49:13 +0000] DISCONNECT conn=1111 reason="Client Disconnect"

Conclusion

It took way to long until eusm becomes officially available. Since it was part of the binaries already since Oracle 11c. The fact that LDAPS is finally also supported is a significant step towards general improvement of the security of databases as well directory servers. It does getting much easier to harden directory servers and limit access on the LDAPS protocol. A little unfortunate in my opinion is the Oracle documentation regarding the configuration of the java keystore. A simple example would have simplified the setup.

Some links related to this blog post:

OUD 12c – SSLHandshakeException with “no cipher suites in common”

Recently I’ve update the java installation of my Oracle Unified Directory (OUD) 12.2.1.0.3 to the latest release. Java 1.8.0 update 202 to be exact (p28916775_180202_Linux-x86-64.zip). Actually a piece of cake, I’ve done this a few times in the past. My Enterprise User Security (EUS) test environment is running in Docker. A container for the database and an other one for the directory server. Updates are usually straight forward. Stop the containers, rebuild the images with the latest software / patches and recreate the containers. But not this time. After restarting OUD, my EUS authentication seems to be broken. When trying to log in, I did get a friendly ORA-01017 error.

 SQL> connect blofeld/******** ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. 
The control of the OUD access log file did show a cipher error.
 [21/Feb/2019:06:21:27] CONNECT conn=5 from=172.20.0.3:50376 to=172.20.0.2:1636 protocol=LDAPS [21/Feb/2019:06:21:27] DISCONNECT conn=5 reason="I/O Error" msg="no cipher suites in common" 

Groundhog Day? Endless loop? I knew I did fix this before. So I’ve checked again the solution in MOS Note 2397791.1 and 2304757.1. According to my understanding the java.security file did look ok. The required legacy ciphers has been enabled by removing 3DES_EDE_CBC from the list of jdk.tls.disabledAlgorithms.
I finally did several tests with different Java versions (1.8.0 update 192 and 1.8.0 update 202) and different java.security files. In the third attempt, database authentication with EUS and OUD in combination with Java 1.8.0 Update 202 also worked. The solution was rather simple. I did use the java.security file from java 1.8.0 update 192 rather than using the new version and enable 3DES_EDE_CBC. Running diff on both files has uncovered the culprits.

 diff java.security java.security_202_default 645c645 < EC keySize < 224 --- > EC keySize < 224, 3DES_EDE_CBC, anon, NULL 700c700,701 < RC4_128, RC4_40, DES_CBC, DES40_CBC --- > RC4_128, RC4_40, DES_CBC, DES40_CBC, \ > 3DES_EDE_CBC 
Or just the lines with jdk.tls.disabledAlgorithms.
 jdk.tls.disabledAlgorithms=SSLv3, RC4, DES, MD5withRSA, DH keySize < 1024, \ EC keySize < 224, 3DES_EDE_CBC, anon, NULL 
A difference due to 3DES_EDE_CBC was to be expected, since I made the comparison to the standard file java.security and there this algorithm was not yet removed. But anon, NULL

is new. The list of disabled algorithms jdk.tls.disabledAlgorithms has been altered in Java 1.8.0 update 202. I could have seen this myself if I had looked through the release notes before installing the software 🙂 . There is a java bug related to this, see JDK-8211883 Disable anon and NULL cipher suites. The problem is now that my EUS is working again, but it will use unsecure and legacy algorithms. A proper fix of this issue has to be implemented in the LDAP / EUS stack of the Oracle database binaries.

Conclusion

First of all do read the release notes before updating production environments 🙂 . As always in IT, do a little change on one side can unexpectedly break something on the other side. The solution presented here can only be a workaround, because we endanger security with legacy algorithms. Oracle should soon update the LDAP / EUS stack in the Oracle binaries.

  • Fix for Java 1.8.0 update 192 and older: Use the solution described in MOS note 2304757.1 update java.security and remove 3DES_EDE_CBC from the jdk.tls.disabledAlgorithms
  • Fix for Java 1.8.0 update 201 and newer: Use either an old java.security which does work for you EUS environment or remove 3DES_EDE_CBC, anon and NULL from the jdk.tls.disabledAlgorithms in your java.security

Links

A few links related to this post:

  • OUD 12c – EUS Integration Failing with Message “no cipher suites in common”[2397791.1]
  • OUD 11g – EUS Authentication Fails with Error Message “no cipher suites in common”[2304757.1]
  • Java 1.8.0 update 201 release notes
  • Java bug JDK-8211883 Disable anon and NULL cipher suites
  • Preview of my Docker compose files to setup an Oracle Enterprise User Security Environment on Docker GitHub oehrlis/docker