A simple Container based Guacamole Setup

Another food blog post? No, of course not. Guacamole in this case does not refer to the avocado-based dip, but to the Apache client less remote desktop gateway. We use this as an alternative access to the OCI based Trivadis LAB environment. We use an automatically configured Guacamole environment with Nginx reverse proxy and Let’s encrypt SSL certificates. The detailed description of this Guacamole environment is the goal of this blog post.

For the Trivadis LAB and training environment in Oracle Cloud (OCI), we typically use a bastion host to access the various resources. In training courses, it is not always possible for all participants to use SSH clients such as Putty, MobaXterm, SSH command line and so on. This is especially true when training courses are increasingly conducted virtually, as is currently the case. Participants often use their company’s IT devices for this purpose. These devices are often protected accordingly and do not allow SSH access to public IP addresses. An HTTP-based remote access gateway such as Guacamole offers a simple alternative to allow participants to access the training environment.

Structure

Apache itself already provides Guacamole as a container. A simple setup contains two containers. The guacamole container for the remote desktop gateway and the guacd container for the server-side proxy. Guacamole uses a dedicated port and is not specially protected. For the training environment, however, access should be possible via HTTPS. The use of the default ports is also desirable. Guacamole uses Tomcat with a dedicated port and is not specially protected by default. For the training environment, however, access should be possible via HTTPS. The use of the default ports is also desirable. To meet the requirements while using existing Docker images, Guacamole is run behind an Nginx reverse proxy. The SSL certificates are generated using Let’s encrypt. The following graphic shows the schematic structure of the environment.

Guacamole Setup with Nginx Reverse Proxy

A few notes on the containers:

  • nginx configured as reverse proxy. By default this container does bind to HTTP and HTTPS to allow client access.
  • certbot simple container to create the Let’s Encrypt certificates.
  • guacamole the Apache Guacamole container
  • guacd the server-side proxy server for Guacamole
  • mysql database used to store the Guacamole connection information

Quickstart

Make sure you have docker and docker-compose installed on your environment. If not yet done you can get more information in chapter Install Docker Compose in the Docker documentation.

git clone https://github.com/oehrlis/guacamole.git
  • Review and update the .env and docker-compose.yml file. Make sure to define at least the following environment variable either with export , in the .env file or directly change the values in the compose file:
    • EMAIL – Adding a valid address for certificate renewal (default none)
    • HOSTNAME – Define a hostname for the nginx server and certificate name (default: $(hostname))
    • DOMAINNAME – Define a domain name for nginx server and certificate name (default: trivadislabs.com)
    • STAGING_ENABLE – Set STAGING to 1 if you’re testing your setup to avoid hitting request limits is certification
  • Pull the required Docker images
docker pull guacamole/guacamole
docker pull guacamole/guacd
docker pull mysql/mysql-server
docker pull nginx
docker pull certbot/certbot
  • Prepare the MySQL database
cd guacamole
./bin/prepare_initdb.sh
  • Start guacamole containers
docker-compose up -d guacamole mysql guacd
  • Perform the certificate challenge. Where Nginx is first started with a self signed certificate in order to then execute the actual certificate request. Define the variables If not yet updated in the .env file
export HOSTNAME="guacamole"
export DOMAINNAME="example.org"
export EMAIL="info@example.org"
export STAGING_ENABLE=1
./bin/prepare_certs.sh
  • Start all containers
docker-compose up -d

The different passwords for the Guacamole admin, Guacamole database and MySQL root are generated when not explicitly specified. You can find them in the logs respectively in the .env file which is updated by the prepare_initdb.sh script.

As soon as your container are up and running you can access your guacamole remote desktop gateway

Login Dialog Guacamole
Example Guacamole Home Page

Automatisation

There is even a quicker way to setup the Guacamole Docker stack by using the script setup_guacamole.sh. In particular, this method is used to set up the guacamole stack in an OCI bootstrap process. By default it will use the OS user avocado (what else 😉). But the user can be customised by the variable GUACAMOLE_USER.

git clone https://github.com/oehrlis/guacamole.git
cd guacamole
export HOSTNAME="guacamole"
export DOMAINNAME="example.org"
export EMAIL="info@example.org"
export STAGING_ENABLE=1
./bin/setup_guacamole.sh

Customised Configuration

As you could see above, the installation can be customised with a few variables. These include the following variables in particular.

VariableDefault ValueDescription
HOSTNAMEhostnameHostname of the bastion host used to create the certificate request. i.h. this name must be resolvable via DNS
DOMAINNAMEtrivadislabs.comDomain name user for the certificate request
EMAILadmin@DOMAINNAMEA valid e-Mail address used for the certificate challange
GUACAMOLE_USERavocadoGuacamole OS User
GUACAMOLE_BASE/home/${GUACAMOLE_USER}/guacamoleGuacamole base folder
GUACADMIN_USERguacadminGuacamole admin user
GUACADMIN_PASSWORDn/aGuacamole admin password. Will be generated and stored in .env
MYSQL_PASSWORDn/aMySQL database password. Will be generated and stored in .env
Environment Variables

Additionally, it is possible to create the guacamole connections directly when creating the configuration. For this purpose, the SQL script 02_connections.sql in the config/mysql directory has to be adapted. For example, with an SSH connection.

INSERT INTO guacamole_connection (connection_name, protocol) 
    VALUES ('Database Server (db - 10.0.1.6)', 'ssh');
INSERT INTO guacamole_connection_parameter VALUES (2, 'hostname', '10.0.1.6');
INSERT INTO guacamole_connection_parameter VALUES (2, 'port', '22');
INSERT INTO guacamole_connection_parameter VALUES (2, 'username', 'oracle');

Of course you can also enter passwords, SSH keys etc. directly. All SQL scripts in this directory are executed when the MySQL DB is created. So further customisations are possible. More information about the configuration of Guacamole can be found in the documentation.

OCI Bastion Host

How about using this on an OCI bastion host? Nothing simpler than that. You just have to configure you VCN and bastion host in the following manner.

  • Allow incoming traffic for port 80 and 443
  • Install docker on the bastion host
  • Register the public IP of your bastion host in a DNS zone create in OCI
  • Deploy the Guacamole stack manually as explained above.

In one of my next blog posts, I will show you how to automatically deploy your bastion host with guacamole stack by using my Terraform modules module tvdlab-bastion and tvdlab-base.

Conclusion

The container-based setup for Apache Guacamole is relatively simple and adds value to accessing the Trivadis cloud-based training and lab environment. The approach described here has also been successfully used for various test and PoC environments. To make setup in OCI even easier, Terraform module tvdlab-bastion and tvdlab-base use this approach to deploy the Guacamole stack on the Bastion host directly in the bootstrap process. But more about that in an other blog post.

References

Home office, starving and the favourite takeaway is far away

It happens that the children are at school and the wife is at work in the hospital. For lunch a light snack from the takeaway around the corner would be perfect. No problem at work in the city, but when working from home? The offer in my area is relatively modest. Hey but why not just make a quick Piadina for lunch! For once something else than OCI, DB Security or other database stuff. Not sure what a Piadina is?

Piadina or Piada is a thin Italian flatbread, typically prepared in the Romagna historical region (Forlì, Cesena, Ravenna and Rimini). It is usually made with white flour, lard or olive oil, salt and water.

Wikipedia. 2020. “Piadina.” Last modified 23. October 2020. https://en.wikipedia.org/wiki/Piadina

The dough

Unfortunately, I can not fall back on an old family recipe. Therefore, simply a variant of the dough, which has always worked well for me. In principle, the ingredients are relatively simple and available in almost every household. One takes (or borrow from the neighbour):

  • 200g white flour
  • 1 tsp salt
  • 1 tbsp olive oil
  • 1 dl sparkling mineral water

Mix everything together into a smooth dough and let it rest ideally for 20-30 minutes. If you are starving, you can skip the 30 minutes. There is no block corruption or anything…

The roasting / baking

Then divide the dough into 4 equal parts and roll each out into a thin patty. To do this, take a rolling pin. If you don’t have one, you can use an empty and clean beer, wine or whiskey bottle from the night before 🤪. You can then briefly bake the patty in a pan without adding oil.

The filling

As a filling you can take what every you find in you fridge and fits to a Piadina. Usually you take some cheese (Parmigiano, Pecorino,…) italian raw ham, arugula, salami, dried tomatos etc…

My Piadina today looks like this

Conclusion

A Piadina always goes well. It also doesn’t take much more time to prepare than deploying an Oracle 21c DB into OCI. After enjoying 1-2 Piadina, you are strengthened for a productive and successful afternoon. Enjoy

How to get an Oracle 21c Database on the Oracle Cloud

A few hours ago Oracle published a blog post about the new version Oracle 21c. See Introducing Oracle Database 21c. It is again an innovation release with a couple of interesting new features and enhancements. The online Oracle Documentation library does provide a few information on this enhancements:

The study of documentation and blog posts is always interesting. But it gets much more exciting when you can do first hands-on with the new release. Thanks to the Oracle Cloud this is easily possible. With a few clicks in OCI you can create a DB system with Oracle 21c. Then nothing else standing in the way to test new features all night long.

Requirements

Unfortunately it is not possible to get Oracle 21c as Always Free Version. At least I did not find a way to do so. My workmate Philipp Salvisberg pointed out to me that it is indeed possible to create an Allways Free 21c ATP DB. You just have to be in the right OCI region. Any way, since I do like to test a few infrastructure feature like DB Nest, Kerberos, EUS etc I any way have to setup a DB System rather than an Oracle Autonomous Database. In order to finally get started, a few prerequisites must be met:.

  • An Oracle Account and active OCI Tenant
  • A few OCI credits
  • Compartment where you plan to deploy your DB system
  • A VCN for you DB System.

VCN

Usually create my VCN’s with Terraform. That means I create my VCNs usually with a bastion host, a public and a private network. For this I use my Terraform OCI modules which are available in the Terraform registry. See tvdlab-base, tvdlab-bastion or tvdlab-vcn. A blog post is still on my todo list 😊. For setting up the Oracle 21c DB system I assume that a corresponding VCN is available.

I’ll use the following information for my setup

  • Compartment DB21C
  • VCN db21c00

DB System

To setup the 21c database go to the main page and select Bare Metal, VM, and Exadata in the menu.

Create a DB System by pressing the corresponding blue button. Do not be surprised. On the following picture you can already see a 21c DB system.

Compared to Oracle 20c preview it does not matter if you choose Bare Metal or Virtual Machine. 21c is available for both environments. This also applies to the storage management software. I do select a Virtual Machine with the Logical Volume Manager.

DB System wizard part 1

As I do only use the DB System for simple feature test and engineering I keep the size to the minimum. As Oracle software I choose the Enterprise Edition. Other editions including Standard Edition are also possible.

DB System wizard part 2

Add you public SSH key(s) to be able to access the DB system later on via SSH. Depending on your OS you can use putty or ssh-keygen for this. Since I do work on a Mac I’ll create the pair of keys with ssh-keygen.

Add SSH public keys

Finalise the system configuration by selecting your VCN and a corresponding subnet. In my case it is the VCN db21c00 and the private subnet db21c00 private.

DB System wizard part 3

After pressing next you come to the second page of the DB System wizard, where we configure the DB itself.

DB System wizard part 4

Choose a fancy name for your DB or like me just TDB21c. By the default you see, that the Database Image 19c is selected. You can change it and select 21c.

DB System wizard part 5

Finish the DB System wizard by selecting Create DB System.

DB System wizard part 6

It will take now a while until you Oracle 21c Database is ready to use. A good moment to crab a cup of coffee or write a blog post 😉. I’ll do this now as well and finish this post in a couple of minutes…

DB System Setup

My 21C DB system is finished in the meantime and can be used.

Since I did create the VM in a private subnet, it is not possible to directly access the DB from the internet. I do have to connect using the bastion host. Either via SSH or for SQL Developer using SSH port forwarding.

Enclosed an example to connect to the new DB system via bastion host using SSH proxy command. In this example I do us the following SSH parameters:

  • -A does enables forwarding of the authentication agent connection
  • -J is used to specify the proxy host used to initiate the connection. In my case the bastion host.
  • db21c is the alias hostname for the public IP of my bastion host
  • deadalus is the hostname for my DB system
  • opc is the user used to connect to the different compute instances. you can also connect directly to the oracle user, if you put the public keys in the .ssh/authorized_keys file of user oracle.
soe@gaia:~/ [ic19300] ssh -A -J opc@db21 opc@deadalus
Last login: Wed Dec  9 05:35:20 2020 from 10.0.0.2
[opc@deadalus ~]$ sudo su - oracle
Last login: Wed Dec  9 05:36:22 UTC 2020 from 10.0.0.2 on pts/0
[oracle@deadalus ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Dec 9 05:42:38 2020
Version 21.1.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.1.0.0.0

SQL> 

Alternatively, you can do SSH port forwarding for port 1521 and connect directly to the database with SQL Developer.

soe@gaia:~/ [ic19300] ssh -A opc@db21 -L 1521:deadalus:1521
Last login: Wed Dec  9 05:33:00 2020 from yyy.yyy.yyy.yyy
--------------------------------------------------------------------
-
- Welcome to the bastion / jump host for the OCI environment
- hostname          :   db21c
- Public IP         :   xxx.xxx.xxx.xxx
- Guacamole Console :   http://secret
- Guacamole Admin   :   <USER>
- Guacamole Password:   <PASSWORD>

[opc@db21c-bastion00 ~]$ 
SQL Developer connection setting
SQL Developer SQL Worksheet

If you don’t want to deal with command line and SSH commands, then with newer versions of SQL Developer you can set up a connection directly via SSH port forwarding. Select SSH from the View menu and create a new SSH connection. The following details must be added:

  • Name: Name of your SSH connection
  • Host: Public IP of your bastion host
  • Username: Usually just opc
  • Key File: The private key file matching the public key used above
  • Name: Name of you SSH port forwarding rule
  • Host: Private IP address of the DB system
  • Port: The TCP Port configured on the DB system usually just 1521
  • Local Port: Use an automatically assigned local port, then you do not have to bother if it is already in use
New SSH Host Dialog

The SSH host can now be used to configure the DB connection. You only have to select the connection type SSH.

SQL Developer Connection using SSH

Conclusion

In OCI you can create a new engineering system for Oracle 21c relatively quickly. With the Bastion host I have chosen a more complex but secure method. The separation of the subnets into private and public network does offer me the flexibility to test a few Features which requires additional infrastructure components. e.g. Centrally Managed Users CMU or Enterprise User Security EUS, which both require additional services such as Oracle Unified Directory or MS Active Directory. Running everything via public IP is rather a bad idea. Any way, following this approach you now have Oracle 21c Database. I wish you happy engineering.

References

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:

Trivadis LAB: Simple Vagrant Setup of Windows AD Server

One of my biggest problems when I started to look into Kerberos Authentication, Oracle Centrally Managed Users as well Oracle Enterprise User Security was the availability of an Active Directory to setup test cases. It is usually not the core business of an Oracle DBA to configure an Active Directory server. 🙂 Using the productive AD is generally not a good choice either. One day I set up a Virtualbox VM with Windows 2016 and Active Directory. Great, but the VM went the way that many test VMs go and got screwed up. Set up a VM from scratch is cumbersome and time consuming. In particular when you would have to do it regular. Just doing backup and snapshot does work, but does not help to share the VMs with colleagues. Besides that the disk space on my notebook is limited. Then I did started to look into Vagrant. Then I did started to look into Vagrant. Not only for Oracle Database VMs but also for my Active Directory server. I have successfully used this environment in the past for several lectures and trainings at SOUG, DOAG and AOUG.

The aim of this blog post is to introduce the Trivadis LAB environment. In particular, the vagrant based setup of the Windows Server for Active Directory. I will discuss some basic steps to create such a VM, but also a few configuration details so that you can use it in your own engineering project. I myself use the Windows VM together with DB VMs (oehrlis/trivadislabs.com) but also with my Docker based engineering environment (oehrlis/doe). So lets get ready to rumble…

Trivadis LAB Environment

As you can see in the following figure, the entire Trivadis LAB environment contains VMs for Oracle databases and Oracle Unified Directory in addition to the VM for Active Directory. However, these are not included in this blog post. We just focus on Windows.

LabEnvironment
Trivadis LAB Environment

To allow a more or less practical use of the directory, a simple structure was created for the fictitious company Trivadis LAB. The following graphic shows the organisation chart including departments and employees for Trivadis LAB. All the users listed can be used as test users. The login name corresponds to the last name in lower case. The password for all users is set to a default password (see configuration files)

Trivadislabs Company
Organisation Chart Trivadis LAB

The fictitious company has the following departments:

idDEPARTMENTDISTINGUISHED NAME (DN)
10Senior Managementou=Senior Management,ou=People,dc=trivadislabs,dc=com
20Accountingou=Accounting,ou=People,dc=trivadislabs,dc=com
30Researchou=Research,ou=People,dc=trivadislabs,dc=com
40Salesou=Sales,ou=People,dc=trivadislabs,dc=com
50Operationsou=Operations,ou=People,dc=trivadislabs,dc=com
60Information Technologyou=Information Technology,ou=People,dc=trivadislabs,dc=com
70Human Resourcesou=Human Resources,ou=People,dc=trivadislabs,dc=com
Trivadis LAB Departments

The following groups were defined:

GROUPDISTINGUISHED NAME (DN)DESCRIPTION
Trivadis LAB APP Adminsou=Trivadis LAB APP Admins,ou=Groups,dc=trivadislabs,dc=comApplication administrators
Trivadis LAB DB Adminsou=Trivadis LAB DB Admins,ou=Groups,dc=trivadislabs,dc=comDB Admins from the IT department
Trivadis LAB Developersou=Trivadis LAB Developers,ou=Groups,dc=trivadislabs,dc=comDevelopers from the research department
Trivadis LAB Managementou=Trivadis LAB Management,ou=Groups,dc=trivadislabs,dc=comManagement and managers
Trivadis LAB System Adminsou=Trivadis LAB System Admins,ou=Groups,dc=trivadislabs,dc=comSystem Admins from the IT department
Trivadis LAB Usersou=Trivadis LAB Users,ou=Groups,dc=trivadislabs,dc=comAll Users
Trivadis LAB HRou=Trivadis LAB HR,ou=Groups,dc=trivadislabs,dc=comHuman Resources
Trivadis LAB Groups

Prerequisites

The vagrant projects in oehrlis/trivadislabs.com do require Vagrant and Window Server Virtualbox.

  1. Install Oracle VM VirtualBox
  2. Install Vagrant

The first time you provision a Windows Server VM, the basis Vagrant Box is loaded from the Vagrant Cloud, which may take a while. If preferred, you can download this VM in advance with Vagrant. Enclosed the example for Windows Server 2019

vagrant box add StefanScherer/windows_2019 --provider virtualbox

Setup

Setup of the Vagrant VM is straight forward. You have to decide if you would like to setup a Windows Server 2019 (win2019ad) or Windows Server 2016 (win2019ad). The steps below are for Windows Server 2019.

  1. Clone this repository git clone https://github.com/oehrlis/trivadislabs.com
  2. Adjust configuration in trivadislabs.com/common/config/vagrant.yml
  3. Change into the trivadislabs.com/win2019ad directory
  4. Run vagrant up
    1. The first time you run this it will provision everything and may take a while (20-40min). Ensure you have a good internet connection as the scripts will download a couple of tools via Chocolatey.
    2. The installation can be customised, if desired (see below).
  5. Connect to the VM using vagrant rdp as vagrant or administrator user. Default password is either store in vagrant.yml or default_pwd_windows.txt.
  6. If necessary, run the Windows Update manually.
  7. You can shut down the VM via the usual vagrant halt and then start it up again via vagrant up

Enclosed an excerpt from the vagrant up command:

user@host:~/trivadislabs.com/win2019ad/ [ic19300] time vagrant up
Bringing machine 'win2019ad' up with 'virtualbox' provider...
==> win2019ad: Importing base box 'StefanScherer/windows_2019'...
==> win2019ad: Matching MAC address for NAT networking...
==> win2019ad: Checking if box 'StefanScherer/windows_2019' version '2020.07.17' is up to date...
==> win2019ad: Setting the name of the VM: win2019ad.trivadislabs.com
==> win2019ad: Clearing any previously set network interfaces...
==> win2019ad: Preparing network interfaces based on configuration...
    
...

    win2019ad: This Computer SID is S-1-5-21-1473420208-2468469534-
    win2019ad: =========================================================
    win2019ad:  Successfully finish setup AD VM 
    win2019ad:   Host      : win2019ad
    win2019ad:   Domain    : trivadislabs.com
    win2019ad: =========================================================

real	34m7.109s
user	1m4.814s
sys	0m32.222s

Configuration

The Vagrantfile is preconfigured for the Trivadis LAB domain. I.e. host name, domain name, user etc. are predefined. Generally there is no need to adjust the Vagrant file itself. To ensure that all VMs in Trivadis LAB always work with the same configurations, a central YAML file is used for Vagrant. The file is locate in common/config/vagrant.yml. For the Window VM you find the following configuration:

# Configuration valid for all VM's
common:
  default_password: LAB01schulung
  domain_name: trivadislabs.com
  company_name: Trivadis LAB
  gateway: 10.0.0.1
  dns: 10.0.0.4
  public_dns1: 8.8.8.8
  public_dns2: 4.4.4.4

# Configuration valid for Windows 2019 AD server
win2019ad:
  box: StefanScherer/windows_2019
  vm_name: win2019ad
  domain_mode: WinThreshold
  people_ou_name: People
  groups_ou_name: Groups
  mem_size: 2048
  cpus: 1
  public_ip: 10.0.0.4

A short explanation of the settings and possibilities:

  • common YAML common section
  • win2019ad YAML section for Windows Server 2019
  • default_password Password used to initialise the AD Server. It is recommended to change this as soon as possible. Default is LAB01schulung
  • domain_name Network domain name for the environment. Depending on this, the directory tree in AD and the AD domain itself is named. Default value is trivadislabs.com
  • company_name Company Name for the Trivadis LAB. This is used to name the AD groups.
  • network configuration for gateway (10.0.0.1), dns (10.0.0.4), public_dns1 (8.8.8.8), public_dns2 (4.4.4.4)
  • box the base Vagrant box used to setup the VM
  • vm_name Name of the VM win2019ad
  • domain_mode Active Directory domain mode
  • people_ou_name Active Directory OU for the users
  • groups_ou_name Active Directory OU for the groups
  • mem_size Memory size for the VM
  • cpus Number of CPUs for the VM
  • public_ip Public IP address of the VM

The different scripts use the settings for initialising the AD server. So it is possible to change the AD domain, host domain name etc.

Access

There are several option to access the VM. You either can directly start the Console on Virtualbox. If you run the VM in headless mode you will either run vagrant rdp or use Microsoft Remote Desktop directly. Running vagrant rdp finally starts only Microsoft Remote Desktop as well.

Remote Desktop Configuration for win2019ad
Microsoft Remote Desktop Session on win2019ad

From here the environment is ready for all kinds of engineering. Depending on what you want to do, you may need some customisation, e.g. creating service users, generating keytab files, installing the Oracle password filter etc.

Behind the scenes

Setting up ad Active Directory server cannot be done in one swing. You need several steps and server reboot. Therefore the configuration is divided into different PowerShell scripts. The following list gives an overview of the different scripts. They are executed in the listed sequence. After the script 22_install_chocolatey.ps1 and 27_config_cmu.ps1 the VM is restarted by Vagrant.

Conclusion

It took some effort to prepare the environment. Nevertheless, the effort was worth it. I was quite happy to have a Test AD at hand in some projects. Testing Oracle Enterprise User Security, Oracle Centrally Manage Users or Kerberos is not possible without an Active Directory server. In particular if you like to test the Oracle Passwort Filter for Active Directory. But this will be an other 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