Category Archives: 19c

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 Security EUS Snippets – Setup Proxy User Privileges

Since I’m always short of time for a longer blog post, I’ll just try a short one. Intended as a mini-series, I will show different configuration examples for Oracle Enterprise User Security. Today I’ll start with the configuration of EUS based proxy privileges. The environment I use is DOE, my Docker based Oracle Engineering environment. In particular the EUS configuration. For more information, see the corresponding GitHub repository oehrlis/doe respectively in the folder eus for the EUS specific environment.

Background

Database proxy privileges are used relatively often to give certain users rights to access a different schema. The user authenticates himself with his credentials and becomes a proxy user in the database. Below an example where the user RMAN, gets access to a different schema, specifically an other RMAN catalog schema (see also blog post about SEPS and RMAN).

CREATE USER rman IDENTIFIED BY welcome1;
CREATE USER rman19000 NO AUTHENTICATION QUOTA UNLIMITED ON rman_data;
GRANT RECOVERY_CATALOG_OWNER TO rman19000;
ALTER USER rman19000 GRANT CONNECT THROUGH rman; 
ALTER USER rman19000 DEFAULT TABLESPACE rman_data;

The following users were created

  • RMAN1900 is the schema owner for an Oracle 19c RMAN catalog stored in the tablespace RMAN_DATA. The user is created without any authentication but with a proxy privilege for the user RMAN.
  • RMAN is the user which will be used to connect to the catalog. There are other catalogs as well but not shown in this example
SQL> connect rman[RMAN19000]/welcome1@CATALOG
Connected.
SQL> show user
USER is "RMAN19000"
SQL> SELECT sys_context('userenv','PROXY_USER') PROXY_USER,
sys_context('userenv','SESSION_USER') SESSION_USER from dual;

PROXY_USER SESSION_USER
---------- ---------------
RMAN	   RMAN19000D

With pure database authentication or authorisation, the configuration of proxy users is easy. With Enterprise User Security, proxy privileges are no longer managed in the database but in the directory. Let’s take a look at that.

Database Configuration

For Enterprise User Security based proxy privileges, only ENTERPRISE USERS is specified in the database. The rest is done in the OracleContext of the directory. See also ALTER USER in Oracle® Database SQL Language Reference 19c.

ALTER USER scott GRANT CONNECT THROUGH ENTERPRISE USERS;

Enterprise User Security Configuration

The configuration can be either done via Oracle Enterprise Manager Cloud Control as documented in Oracle® Database Enterprise User Security Administrator’s Guide 19c or with the command line utility eusm. I prefer the command line utility as I often do not have an OEM by hand.

  • Create the proxy permission in the directory.
eusm createProxyPerm proxy_permission="Scott Proxy" \
domain_name="OracleDefaultDomain" \
realm_dn="dc=trivadislabs,dc=com" \
ldap_host=eusoud.trivadislabs.com ldap_port=1389 \
ldap_user_dn=cn=eusadmin,cn=oraclecontext \
ldap_user_password=$(cat /u01/common/etc/eusadmin_pwd.txt)
  • Define a target user for this proxy permission.
eusm addTargetUser proxy_permission="Scott Proxy" \
database_name="TEUS01" \
target_user="SCOTT" dbuser="system" \
dbuser_password=$(cat /u00/app/oracle/admin/TEUS01/etc/TEUS01_password.txt) \
dbconnect_string="eusdb.trivadislabs.com:1521/TEUS01.trivadislabs.com" \
domain_name="OracleDefaultDomain" \
realm_dn="dc=trivadislabs,dc=com" \
ldap_host=eusoud.trivadislabs.com ldap_port=1389 \
ldap_user_dn=cn=eusadmin,cn=oraclecontext \
ldap_user_password=$(cat /u01/common/etc/eusadmin_pwd.txt)
  • Explicit granting of proxy permission to the user KING. Can also be assigned to a group.
eusm grantProxyPerm proxy_permission="Scott Proxy" \
user_dn="cn=Ben King,ou=Senior Management,ou=People,dc=trivadislabs,dc=com" \
domain_name="OracleDefaultDomain" \
realm_dn="dc=trivadislabs,dc=com" \
ldap_host=eusoud.trivadislabs.com ldap_port=1389 \
ldap_user_dn=cn=eusadmin,cn=oraclecontext \
ldap_user_password=$(cat /u01/common/etc/eusadmin_pwd.txt)
  • Display the proxy permissions defined for the EUS default domain.
eusm listProxyPermissions domain_name="OracleDefaultDomain" \
realm_dn="dc=trivadislabs,dc=com" \
ldap_host=eusoud.trivadislabs.com ldap_port=1389 \
ldap_user_dn=cn=eusadmin,cn=oraclecontext \
ldap_user_password=$(cat /u01/common/etc/eusadmin_pwd.txt)
  • Display information for the proxy permission Scott Proxy
eusm listProxyPermissionInfo proxy_permission="Scott Proxy" \
domain_name="OracleDefaultDomain" \
realm_dn="dc=trivadislabs,dc=com" \
ldap_host=eusoud.trivadislabs.com ldap_port=1389 \
ldap_user_dn=cn=eusadmin,cn=oraclecontext \
ldap_user_password=$(cat /u01/common/etc/eusadmin_pwd.txt)
  • Display proxy permissions for the user KING.
eusm listProxyPermissionsOfUser \
user_dn="cn=Ben King,ou=Senior Management,ou=People,dc=trivadislabs,dc=com" \
realm_dn="dc=trivadislabs,dc=com" \
ldap_host=eusoud.trivadislabs.com ldap_port=1389 \
ldap_user_dn=cn=eusadmin,cn=oraclecontext \
ldap_user_password=$(cat /u01/common/etc/eusadmin_pwd.txt) 

Using the Proxy Permissions

Let’s test the permissions and connect as user KING.

  • Regular connection to the database as schema owner SCOTT.
SQL> connect SCOTT/tiger@TEUS01
Connected.
SQL> show user
USER is "SCOTT"
SQL> select sys_context('userenv','PROXY_USER') PROXY_USER,
sys_context('userenv','SESSION_USER') SESSION_USER from dual;

PROXY_USER	SESSION_USER
--------------- ---------------
                SCOTT
  • Regular connection to the database as KING.
SQL> connect king/welcome1@TEUS01
Connected.
SQL> show user
USER is "KING"
SQL> select sys_context('userenv','PROXY_USER') PROXY_USER,
sys_context('userenv','SESSION_USER') SESSION_USER from dual;

PROXY_USER	SESSION_USER
--------------- ---------------
                KING
  • Proxy connection to the database
SQL> connect king[SCOTT]/welcome1@TEUS01
Connected.
SQL> show user
USER is "SCOTT"
SQL> select sys_context('userenv','PROXY_USER') PROXY_USER,
sys_context('userenv','SESSION_USER') SESSION_USER from dual;

PROXY_USER	SESSION_USER
--------------- ---------------
KING            SCOTT

Conclusion

Configuration of proxy permissions in connection with Oracle Enterprise User Security is not as complicated as you might think. It is also useful if shared global users need access to certain schemas. For example, a power user is allowed to access the application schema.

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:

Kerberos Troubleshooting – A few approaches

It is way too long ago since my last blog post. These were or are busy weeks for me. Any way, I finally found some time to start writing a blog post about a special setup for kerberos authentication of Oracle databases. It is about configuring kerberos authentication for multiple database servers with only one active directory account and corresponding Service Priciple Names (SPN). Additionally there is an challenge, that the keytab file should only be created with ktutil directly on the DB server. Access to a Windows server and use of ktpass.exe is not possible. I did setup a nice test case on a couple of compute instances on Oracle cloud infrastructure. During the verification of the test setup I had to realise that the kerberos authentication does not work as planned. Until now it is not possible to create a keytab file with ktutil that I can use successfully with Active Directory. The same kerberos configuration with a keytab create with ktpass.exe on the AD server does work. But that’s on other story…

The aim of this blog post is to sum up a couple of troubleshooting actions I came across. Kerberos itself is around since a couple of decades. Therefore you will find various documentation, RFC, etc. But it is not always easy to recognise what is still relevant and what not. Mainly because the implementation of Kerberos at both Oracle and Microsoft is not necessarily the same or 100% MIT Kerberos compliant. The fact that there are different versions of Oracle, MS AD and Kerberos makes it even more exciting 🙂

Basics

A basic requirement for Kerberos is the network and time configuration.

  • Problem: okinit does fail with clock skew too great
  • Cause: The systems involved must be synchronous in terms of system time e.g. using a NTP service to configure date / time. If the system times differ to much you will receive this error when using okinit.
  • Solution: Configure proper system times using NTP service. Small time drifts can be covered by setting SQLNET.KERBEROS5_CLOCKSKEW=300 in sqlnet.ora
  • Problem: Miscellaneous errors due to wrong / missing network configuration.
  • Cause: Using CNAME rather A records, no DNS configuration, no revers lookkup etc
  • Solution: Configure proper DNS name resolution for database service as well MS active directory service. Each system must be able to be resolved by name or IP address. Kerberos will look for service principle names based on A records.
oracle@db:/u00/app/oracle/network/admin/ [TDB190S] cd
oracle@db:~/ [TDB190S] nslookup win2016ad.trivadislabs.com
Server:		10.0.1.4
Address:	10.0.1.4#53

Name:	win2016ad.trivadislabs.com
Address: 10.0.1.4

oracle@db:~/ [TDB190S] nslookup 10.0.1.4
4.1.0.10.in-addr.arpa	name = win2016ad.trivadislabs.com.

oracle@db:~/ [TDB190S] nslookup db
Server:		10.0.1.4
Address:	10.0.1.4#53

db.trivadislabs.com	canonical name = ol7db19.trivadislabs.com.
Name:	ol7db19.trivadislabs.com
Address: 10.0.1.6

oracle@db:~/ [TDB190S] nslookup 10.0.1.6
6.1.0.10.in-addr.arpa	name = ol7db19.trivadislabs.com.

Trace and Log Files

Kerberos Trace

As of Oracle 12c release 2 it is possible to enable kerberos tracing by setting KRB5_TRACE to a trace file. This logs the Kerberos calls in the current session.

export KRB5_TRACE=/u00/app/oracle/network/admin/kerberos.trc
oracle@db:~/ [TDB190S] okinit king

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 08-JUN-2020 20:54:16

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

Configuration file : /u00/app/oracle/network/admin/krb5.conf.
Password for king@TRIVADISLABS.COM:

A sample output of a kerberos trace file:

oracle@db:~/ [TDB190S] head -10 /u00/app/oracle/network/admin/kerberos.trc
[5645] 1591649656.590082: Getting initial credentials for king@TRIVADISLABS.COM
[5645] 1591649656.590084: Sending unauthenticated request
[5645] 1591649656.590085: Sending request (199 bytes) to TRIVADISLABS.COM
[5645] 1591649656.590086: Resolving hostname ad.trivadislabs.com
[5645] 1591649656.590087: Sending initial UDP request to dgram 10.0.1.4:88
[5645] 1591649656.590088: Received answer (196 bytes) from dgram 10.0.1.4:88
[5645] 1591649656.590089: Sending DNS URI query for _kerberos.TRIVADISLABS.COM.
[5645] 1591649656.590090: No URI records found
[5645] 1591649656.590091: Sending DNS SRV query for _kerberos-master._udp.TRIVADISLABS.COM.
[5645] 1591649656.590092: Sending DNS SRV query for _kerberos-master._tcp.TRIVADISLABS.COM.

Oracle SQLNet tracing

For Kerberos troubleshooting with Oracle SQLNet it is helpful to disable ADR tracing. Not mandatory, but makes life a bit easier. Set DIAG_ADR_ENABLED in sqlnet.ora to OFF.

DIAG_ADR_ENABLED=OFF

Before KRB5_TRACE was available, okinit calls could only be traced with sqlnet.ora and TRACE_LEVEL_OKINIT. See also MOS note 162668.1. The parameter does not make sense when you already use KRB5_TRACE.

TRACE_LEVEL_OKINIT=SUPPORT
TRACE_DIRECTORY_OKINIT=/u00/app/oracle/network/
TRACE_FILE_OKINIT=okinit.trc

For further analysis you usually have to switch on SQLNet Tracing. Don’t even thing about setting an other level than SUPPORT (16). Kerberos calls are only available with the highest level.

TRACE_LEVEL_OKINIT=SUPPORT
TRACE_DIRECTORY_OKINIT=/u00/app/oracle/network/
TRACE_FILE_OKINIT=okinit.trc

Enable tracing for SQLNet clients:

TRACE_LEVEL_CLIENT=SUPPORT
TRACE_DIRECTORY_CLIENT= /u00/app/oracle/network/trc
TRACE_FILE_CLIENT=sqlnet_client.trc

Enable tracing for SQLNet Server:

TRACE_LEVEL_SERVER=SUPPORT
TRACE_DIRECTORY_SERVER= /u00/app/oracle/network/trc
TRACE_FILE_SERVER=sqlnet_server.trc

The errors in the trace files are not always obvious. You can find a few infos and hint in MOS note 185897.1. But most of the time there is no way around searching for the corresponding error or function call in Oracle Support or the search engine of choice.

Network Tracing

The next level is to trace the network calls. Depending on the environment you can directly use Wireshark. But it is much easier to first create a network dump via command line and to analyse it later using Wireshark. I use tcpdump on my OCI environment and download the trace file to my MacBook, where I then use Wireshark.

Get the available interfaces:

sudo tcpdump -D
1.nflog (Linux netfilter log (NFLOG) interface)
2.nfqueue (Linux netfilter queue (NFQUEUE) interface)
3.usbmon1 (USB bus number 1)
4.ens3
5.any (Pseudo-device that captures on all interfaces)
6.lo [Loopback]

Start tracing for interface ens3:

sudo tcpdump -i ens3 -s 65535 -w /tmp/network_okcreate.trc

Keep it running until while testing the kerberos authentication. As soon as done copy the trace file to the client an open it using Wireshark. The following picture does show a trace dump where the kerberos protocol has been selected.

Wireshark sample output

A part of the kerberos packet is encrypted and not visible as you can see in following picture.

Wireshark enc

Kerberos does use the service’s secret key to encrypt these messages. You can import the keytab file into Wireshark to decrypt the messages. For this purpose the keytab file must be specified in Wireshark in the preferences. Click Edit > Preferences > Protocols > KRB5.

Wireshark Preferences

You now see the message content of the packet. This is in particular useful when you have to analyse issues related to ticket size, missing groups etc.

Wireshark decrypted

Conclusion

Unfortunately my Kerberos problem is still not solved. Nevertheless I did get the opportunity to practice a couple of Kerberos tracing methods. The introduction of KRB5_TRACE did simplify tracing a bit, but in most case you still have to use SQLNet or network tracing to find the root cause of you Kerberos problem. A direct solution is unfortunately not always found with tracing. At least you have all the relevant information to search My Oracle Support, open a service request or try your luck at googling for a solution.

Good luck with your Kerberos setup. 😉

References

Some links related to this blog post:

  • Kerberos Troubleshooting Guide [185897.1]
  • Master Note For Kerberos Authentication [1375853.1]
  • How to Trace Unix System Calls [110888.1]
  • Tracing Okinit [162668.1]
  • How to Enable Oracle SQL*Net Client, Server, Listener, Kerberos and External procedure Tracing from Net Manager [395525.1]
  • Requesting kerberos TGT with OKINT errors with okinit: Clock skew too great in 12.1.0.2 [2312008.1]

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.

SQL Developer 19.1 unable to use connection type ldap with OUD

Due to a tip from a work colleague, I came across a changed behaviour of the latest SQL Developer release. It affects the connection type LDAP respectively the use of an LDAP directory for the database name resolution. After specifying one or more LDAP servers it should actually be possible to select the corresponding context in the drop down list. But as of SQL Developer 19.1 this drop down list remains empty as you can see in the following images. As far as I can tell, this problem only occurs when using Oracle Unified Directory and the latest release of SQL Developer.

You can enable debugging in the SQL Developer to search for errors. But it is far easier to have a short look in the OUD access log. In the access log, you will instantly notice an error as you can see in the following excerpt of the access log.

[02/Jul/2019:04:34:51 +0000] CONNECT conn=276 from=172.20.0.1:55224 to=172.20.0.2:1389 protocol=LDAP
[02/Jul/2019:04:34:51 +0000] BIND REQ conn=276 op=0 msgID=1 type=SIMPLE dn="" version=3
[02/Jul/2019:04:34:51 +0000] BIND RES conn=276 op=0 msgID=1 result=0 authDN="" etime=0
[02/Jul/2019:04:34:51 +0000] SEARCH REQ conn=276 op=1 msgID=2 base="" scope=sub filter="(objectClass=orclContext)" attrs="cn,DN"
[02/Jul/2019:04:34:51 +0000] SEARCH RES conn=276 op=1 msgID=2 result=50 message="The request control with Object Identifier (OID) "1.2.840.113556.1.4.319" cannot be used due to insufficient access rights" nentries=0 etime=3
[02/Jul/2019:04:34:51 +0000] SEARCH REQ conn=276 op=2 msgID=3 base="" scope=one filter="(objectClass=orclContext)" attrs="cn,DN"
[02/Jul/2019:04:34:51 +0000] SEARCH RES conn=276 op=2 msgID=3 result=50 message="The request control with Object Identifier (OID) "1.2.840.113556.1.4.319" cannot be used due to insufficient access rights" nentries=0 etime=1
[02/Jul/2019:04:34:51 +0000] SEARCH REQ conn=276 op=3 msgID=4 base="" scope=base filter="(objectClass=orclContext)" attrs="cn,DN"
[02/Jul/2019:04:34:51 +0000] SEARCH RES conn=276 op=3 msgID=4 result=50 message="The request control with Object Identifier (OID) "1.2.840.113556.1.4.319" cannot be used due to insufficient access rights" nentries=0 etime=1
[02/Jul/2019:04:34:51 +0000] UNBIND REQ conn=276 op=4 msgID=5
[02/Jul/2019:04:34:51 +0000] DISCONNECT conn=276 reason="Client Disconnect"

There is an issue with an LDAP control as stated in error message: The request control with Object Identifier (OID) “1.2.840.113556.1.4.319” cannot be used due to insufficient access rights The control 1.2.840.113556.1.4.319 better known as Paged Results Control
allows the client to control the search results. In particular it is used to iterate through the search results a page at a time. And why is this of interest in connection with the SQL Developer?

When using the LDAP connection type, the SQL Developer uses an anonymous LDAP query to query the Oracle context and database name. Starting with SQL Developer 19.1, Oracle seems to use Paged Results Control, which makes sense if you have a large number of LDAP entries. By default Oracle Unified Directory does not allow the Paged Results Control for anonymous connections. Simplest solution to this problem is to allow Paged Results Control also for anonymous connections. For this you have to change the access control handler and the global act either by dsconfig or via OUDSM. The MOS Note 1932191.1 does provide the step by step solution how to modify the global act via dsconfig, OUDSM or by modifying the config.ldif. The latter is not recommended, because this requires a restart of the OUD server. Furthermore, the direct modification of config.ldif is only necessary in a few cases, e.g. when resetting the root user respectively directory manager password.

Procedure to modify the global aci via dsconfig:

  • Start dsconfig and directly modify the act of the Access Control Handler

dsconfig -h oud -p 4444 -D "cn=Directory Manager" -j $PWD_FILE -X set-access-control-handler-prop

  • Select the property global-aci
  • Select remove one or more values
  • Select the # that has Authenticated users control access which contains the 1.2.840.113556.1.4.319 OID)
  • Select use these values
  • Select finish and apply any changes to the Dsee Compat Access Control Handler
  • Start again dsconfig to add the new aci for the Access Control Handler

dsconfig -h oud -p 4444 -D "cn=Directory Manager" -j $PWD_FILE -X set-access-control-handler-prop

  • Select the property global-aci
  • Select add one or more values
  • When prompted with Enter another value for the global-aci property [continue]:, enter
    (targetcontrol="1.3.6.1.1.12|| 1.3.6.1.1.13.1 ||1.3.6.1.1.13.2 ||
    1.2.840.113556.1.4.319 ||1.2.826.0.1.3344810.2.3 ||
    2.16.840.1.113730.3.4.18 ||2.16.840.1.113730.3.4.9 ||
    1.2.840.113556.1.4.473 ||1.3.6.1.4.1.42.2.27.9.5.9")
    (version 3.0; acl "Authenticated users control access"; allow(read) userdn="ldap:///anyone";)
    
  • Select use these values
  • Select finish and apply any changes to the Dsee Compat Access Control Handler

The entire adjustment can also be done directly with two dsconfig commands.

First remove the global-aci for userdn all: (Make sure to remove the line wraps when copy/paste the aci)

dsconfig -h oud -p 4444 -D "cn=Directory Manager" -j $PWD_FILE -X -n set-access-control-handler-prop \
--remove global-aci:"(targetcontrol=\"1.3.6.1.1.12 || 1.3.6.1.1.13.1 || 1.3.6.1.1.13.2 || 1.2.840.113556.1.4.319 || 1.2.826.0.1.3344810.2.3 || 2.16.840.1.113730.3.4.18 || 2.16.840.1.113730.3.4.9 || 1.2.840.113556.1.4.473 || 1.3.6.1.4.1.42.2.27.9.5.9\") (version 3.0; acl \"Authenticated users control access\"; allow(read) userdn=\"ldap:///all\";)"

Add the new global-aci for userdn anyone:

dsconfig -h oud -p 4444 -D "cn=Directory Manager" -j $PWD_FILE -X -n set-access-control-handler-prop \
--add global-aci:"(targetcontrol=\"1.3.6.1.1.12|| 1.3.6.1.1.13.1 ||1.3.6.1.1.13.2 ||1.2.840.113556.1.4.319 ||1.2.826.0.1.3344810.2.3 ||2.16.840.1.113730.3.4.18 ||2.16.840.1.113730.3.4.9 ||1.2.840.113556.1.4.473 ||1.3.6.1.4.1.42.2.27.9.5.9\") (version 3.0; acl \"Authenticated users control access\"; allow(read) userdn=\"ldap:///anyone\";)"

Now the SQL Developer can load the LDAP context and database name.

The question now is whether this change is a bug or a feature. In any case, it would make sense if the SQL Developer could alternatively allow to enter the Oracle context directly and thus avoid the anonymous query.

Some links related to this blog post:

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.