Tag Archives: Trivadis Content

Blog posts also posted on the Trivadis Blog (TriBlog)

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

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.

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]

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)

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:

SOUG Day 2019 – Oracle Database in Docker

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

Besides the demo the following topics were discussed:

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

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

The presentation and information related to event:

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