Author Archives: Stefan

It’s a Wrap: SOUG Day in Zurich and Romandie

The past few days have been an exciting time for Oracle database enthusiasts across Switzerland. The Swiss Oracle User Group (SOUG) organized two fantastic events: the larger SOUG Day in Zurich on September 20th, 2024, and a more intimate gathering in French-speaking Switzerland on September 24th, 2024. Both events attracted Oracle professionals from across the country, who came together to discuss the latest advancements in Oracle database security. I had the privilege of presenting on two key topics: the Oracle 23c SQL Firewall and innovative ideas on using AI to enhance database security.

SOUG Day Zürich: Oracle 23c SQL Firewall, here we go

Oracle SQL Firewall is a recent addition to Oracle’s security toolkit, offering a new approach to database security. In this presentation, I introduced its rule-based data protection features, which give an extra layer of security within the database. The session included demonstrations and practical insights into how the SQL Firewall operates.

Highlights:

  • Introduction to Oracle 23c SQL Firewall and its unique rule-based protection
  • Demonstrations of SQL Firewall’s functionality in securing user data
  • Best practices for integrating SQL Firewall into broader security strategies
  • Practical examples showing how SQL Firewall enhances security beyond traditional external database firewalls

You can find the slides from the presentation here. More information about this SOUG event, including the agenda and slides, is available in the SOUG event archive.

SOUG Day Romandie: Oracle DB Security with the Use of AI

In this session, I explored how AI, specifically GenAI and Natural Query Language Prompts, could be used as potential tools for evaluating Unified Audit data and other security-related information in Oracle databases. The presentation focused on the early stages of applying AI to database security, highlighting some new and promising approaches. While these techniques are still in development, they offer a solid starting point for improving security in cloud-based databases.

Highlights:

  • Introduction to AI-driven techniques as a first test for evaluating Unified Audit data and other security-related information in Oracle databases
  • Demo of how GenAI and Natural Query Language Prompts could streamline security reporting
  • Insights into the future potential of AI in Oracle database security
  • A live prototype demonstration of AI integration for analyzing database security

You can access the slides for this session here. More information about this SOUG event, including the agenda and slides, is available in the SOUG event archive.

Event Photos

SQL Firewall Presentation in Action at SOUG Day Zürich.
Diving into AI-Enhanced Database Security at SOUG Day Romandie.

Looking Ahead

Both events provided a valuable opportunity to connect with the Oracle community, exchange ideas, and explore the future of database technology. AI and the SQL Firewall introduce innovative approaches to data protection and analysis. While AI is still in its early stages, it shows great promise. I’m looking ahead to seeing how these technologies develop in the coming years.

If you missed the events, you can access the full agenda and presentation slides via the links above.

Building Oracle 23ai Free on ARM64

Earlier this week, Oracle quietly released the RPM packages for Oracle 23ai Free Edition for ARM64 systems. This release is very interesting for developers using Macs with ARM processors as it allows them to create Oracle 23ai containers for their development and engineering environments. In this blog post, I’ll walk you through the steps of creating a Docker image for Oracle 23ai Free Edition on ARM64, customising the build process and dealing with common errors along the way.

New RPMs for Oracle Enterprise Linux 8 on ARM64

Oracle has introduced several RPM packages for ARM64, including:

  • oracle-database-preinstall-23ai-1.0-2.el8.aarch64.rpm
  • oracle-database-free-23ai-1.0-1.el8.aarch64.rpm
  • A client zip file: LINUX.ARM64_235000_client_home.zip

These packages are designed for Oracle Enterprise Linux 8 on ARM (aarch64) systems and can be downloaded from the official Oracle Database Free page

While these RPMs can be directly installed on an ARM-based Oracle Linux 8 system, my particular use case required creating a Docker image to streamline the development process.

Installation Options: Direct or Docker

For those who prefer to work directly on Oracle Linux 8 ARM64 systems, the RPMs can be installed using standard package management tools like dnf. However, since I often work on ARM-based MacBook Pros, I have opted to create a Docker image for better portability and easier management. This method allows me to run Oracle 23ai in isolated environments without changing my base system.

Using the Official Docker Build Script

I have my own build scripts for Oracle databases on github oehrlis/docker. However, these scripts are primarily intended for regular Oracle installations with the official Oracle Enterprise Edition releases, with options to include Release Updates (RUs) and Release Update Revisions (RURs). They offer flexibility and are typically used for full Oracle database installations, not for RPM-based installations like Oracle 23ai Free Edition. Since I didn’t want to switch my scripts to RPM packages first, I used the official Docker build scripts from Oracle available on GitHub oracle/docker-images. These scripts are maintained by Oracle and give container build files for various Oracle Database versions, including the Free Edition.

I attempted to build the image using the Containerfile.free Dockerfile, passing the URL for the ARM64 RPM package as a build parameter. Here’s the docker build command I used:

docker build -t oracle/database:23.5.0-free \
-f Containerfile.free \
--no-cache --build-arg \
INSTALL_FILE_1="https://download.oracle.com/otn-pub/otn_software/db-free/oracle-database-free-23ai-1.0-1.el8.aarch64.rpm" .

The build process ran successfully, and I create the image. However, when I tried to run the container, I encountered issues, which I’ll cover in the next section.

Error During Container Run

After successfully building the Docker image, I attempted to run the container using the next command:

docker run --name 23aiFree \
-p 2521:1521 \
-e ORACLE_PWD=Welcome1 \
-e ORACLE_CHARACTERSET=AL32UTF8 \
-e ENABLE_ARCHIVELOG=true \
-e ENABLE_FORCE_LOGGING=true  \
-v ${DOCKER_VOLUME_BASE}/data:/opt/oracle/oradata \
oracle/database:23.5.0-free

Unfortunately, the container not create the database, displaying the error messages below:

Copying database files
8% complete
[WARNING] ORA-00443: background process "OFSD" did not start

9% complete
[FATAL] ORA-01034: The Oracle instance is not available for use.
Start the instance.

29% complete
100% complete
[FATAL] ORA-01034: The Oracle instance is not available for use.
Start the instance.

7% complete
0% complete
Look at the log file "/opt/oracle/cfgtoollogs/dbca/FREE/FREE.log"
for further details.

When examining the alert log and trace files, I found that certain required packets were missing. For example, you can find this error:

kgslaInitCtx: skgdllOpen /opt/oracle/product/23ai/dbhomeFree/lib/libora_netlib.so
OS error: 79 Error message: Cannot access a needed shared library
OtherInfo: libgfortran.so.5: cannot open shared object file:
No such file or director

Fixing the Missing Packages Issue

I first try to manually fix the missing lib’s, but finally I dot change the setupLinuxEnv.sh script to include the necessary packages. Here’s what I changed:

Original script setupLinuxEnv.sh :

dnf install -y oraclelinux-developer-release-el8 && \
dnf -y install oracle-database-preinstall-23ai openssl hostname file expect

Updated script setupLinuxEnv.sh :

dnf install -y oraclelinux-developer-release-el8 && \
dnf -y install libgfortran && \
dnf -y install oracle-database-preinstall-23ai openssl hostname file expect

By adding the required libraries like libgfortran, I managed to successfully build and run the Docker image. Running the container with the same docker run command as before worked without issues.

Simplifying the Build Process with buildContainerImage.sh

To simplify the image creation process, Oracle provides a script called buildContainerImage.sh. This script automates many of the creation steps and simplifies the creation of container images.

But, when I tried to use this script with Oracle 23.5.0 Free Edition on ARM64, I encountered an error stating that only Oracle 19c Enterprise Edition was supported:

Currently only 19c Enterprise Edition is supported on
the ARM64 platform.

Tweaking the Build Script for Oracle 23ai Free

To work around this limitation, I had to change the buildContainerImage.sh script to include support for Oracle 23ai Free Edition. The original script only checked for Oracle 19c Enterprise Edition, so I updated the version check to allow Oracle 23.5.0 Free Edition.

Here is the relevant change:

Original:

if { [ "${VERSION}" == "19.3.0" ] && [ "${ENTERPRISE}" -eq 1 ]; }; then
  BUILD_OPTS=("--build-arg" "INSTALL_FILE_1=LINUX.ARM64_1919000_db_home.zip" "${BUILD_OPTS[@]}")
else
  echo "Currently only 19c enterprise edition is supported on ARM64 platform.";
  exit 1;
fi;

Updated:

if { [ "${VERSION}" == "19.3.0" ] && [ "${ENTERPRISE}" -eq 1 ]; }; then
  BUILD_OPTS=("--build-arg" "INSTALL_FILE_1=LINUX.ARM64_1919000_db_home.zip" "${BUILD_OPTS[@]}")
elif { [ "${VERSION}" == "23.5.0" ] && [ "${FREE}" -eq 1 ]; }; then
  BUILD_OPTS=("--build-arg" "INSTALL_FILE_1=https://download.oracle.com/otn-pub/otn_software/db-free/oracle-database-free-23ai-1.0-1.el8.aarch64.rpm" "${BUILD_OPTS[@]}")
else
  echo "Currently only 19c enterprise edition or 23ai free edition is supported on ARM64 platform.";
  exit 1;
fi;

With this change I was capable of creating the image with the script buildContainerImage.sh.

Simplifying Docker Container Use with Docker Compose

To further simplify the use of the Docker container, I have created a docker-compose.yml file. This file makes it easier to manage the container and set up its environment without having to execute lengthy docker-run commands every time.

Here is the docker-compose.yml file I created:

services:
  23aifree:
    image: ${DOCKER_USER}/${DOCKER_REPO}:23.5.0-free
    container_name: 23aifree
    hostname: 23aifree
    restart: unless-stopped
    volumes:
      - ${DOCKER_VOLUME_BASE}/data:/opt/oracle/oradata
      - ${DOCKER_VOLUME_BASE}/config/startup:/opt/oracle/scripts/startup
      - ${DOCKER_VOLUME_BASE}/config/setup:/opt/oracle/scripts/setup
    ports:
      - 2521:1521
    environment:
      ORACLE_CHARACTERSET: AL32UTF8
      ENABLE_ARCHIVELOG: true
      ENABLE_FORCE_LOGGING: true

Explanation of the docker-compose.yml File:

  • Image: The image parameter specifies the Docker image to use. In this case, it uses the custom image built earlier (oracle/database:23.5.0-free).
  • Volumes: The volumes section mounts host directories to specific paths within the container:
  • /opt/oracle/oradata is where the database data will be stored.
  • /opt/oracle/scripts/startup is where you can place scripts to be executed on container startup.
  • /opt/oracle/scripts/setup is for setup scripts that run during container creation.
  • Ports: This section exposes port 2521 on the host, mapping it to port 1521 inside the container (the default Oracle listener port).
  • Environment Variables:
  • ORACLE_CHARACTERSET=AL32UTF8 sets the character set for the database.
  • ENABLE_ARCHIVELOG=true enables archive logging.
  • ENABLE_FORCE_LOGGING=true ensures that all operations are logged, useful for recovery scenarios.

More information on GitHub oracle/docker-images

Running the Container with Docker Compose

Once the docker-compose.yml file is created, starting the Oracle container is as simple as running the

docker-compose up -d

This command starts the container in the background using the configuration defined in the docker-compose.yml file. Docker Compose makes managing the Oracle container much easier, especially when it comes to startup configurations and persistence across system restarts.

To access the container, you can either use sqlplus, SQL Developer or the command line to work with the container or database as usual. As we have not specified a password in the Docker Compose File, we have to set it explicitly in any case. After that, nothing stands in our way of using the new ARM-based Oracle 23ai container on a Mac Book Pro.

docker exec 23aifree /opt/oracle/setPassword.sh <PASSWORD>

Conclusion

In this blog post, I showed how to quickly build and run an Oracle 23ai Free Edition container on ARM64 using Docker and Docker Compose. We went through the entire process, from downloading the RPM packages and using the official build scripts, to handling bugs and missing packages, to customising the build process with docker-compose.

While this guide provides a solution to get Oracle 23ai Free running on ARM64 systems out of the box, it is important to note that Oracle plans to adapt its build scripts in the GitHub repository oracle/docker-images to officially support Oracle 23ai on ARM64. This means that in the future you can use the official scripts directly without the need for manual changes.

This guide is intended to help Oracle DBAs and developers who want to run Oracle 23ai Free on ARM64 platforms, especially on MacBook Pros. If you are working with similar configurations or have any questions, please feel free to contact me or browse my Docker resources on GitHub oehrlis/docker.

Just One more Thing…
… the ARM64 packages also run on a Raspberry Pi 5 with enough memory. I will test this as soon as I get my hands on a Pi5.

It’s a Wrap: Insights from the SOUG Espresso on Oracle Audit

In my latest SOUG Espresso session, Easy Audit Data Analysis with SQL Developer Reports, I focused on Oracle Unified Audit Analysis. This was an opportunity to share my experience and insights into database security and audit data workflow enhancement.

Event Highlights

Based on my considerable experience with Oracle databases, I emphasized the critical importance of analyzing audit events to uncover security risks and refine audit policies. In my presentation, I introduced both SQL scripts and SQL Developer Reports developed for simple ad hoc analysis of the Unified Audit Trail.

Key Takeaways

  • Audit Configuration: I discussed the significance of setting up policies, storage, and jobs correctly.
  • SQL*Plus Reporting: I showcased various scripts, which attendees can download from my GitHub, for auditing data analysis directly from the command line.
  • SQL Developer Reporting: I demonstrated using SQL Developer for more interactive and graphical analysis.
  • Best Practices: The session highlighted essential practices like least privilege, dedicated user and role concepts, and proper data retention.

Discussion and Feedback

Although the event was rich in information, the time for our discussion was unfortunately limited, indicating the need for further discussions at the next SOUG day. The session was well attended by participants from within and outside the SOUG community.

Further Resources and Next Steps

For those who want to dive deeper into Oracle Unified Audit Data Analysis, I’ve compiled a list of valuable resources:

  • My blog post about SQLPlus scripts: I have written an in-depth blog post titled SQL Toolbox for Simplified Oracle Unified Audit Data Analysis. This post provides insights into using SQLPlus to analyze audit data. Read the blog post here.
  • SQL Developer Audit Analysis: Another blog post of mine, Easy Audit Data Analysis with SQL Developer Reports, is about using SQL Developer for this purpose. It’s a guide to using the power of SQL Developer for audit analysis. Read the blog post here.
  • The slides: To get an overview of the session, you can download the slides of the event. They summarize the key points and methods that were discussed. Download the slides here.
  • GitHub repository: All the scripts that were discussed in the session are available in my GitHub repository. They are handy tools that you can use for your audit data analysis. Access the scripts on GitHub.
  • Oracle documentation: For official guidelines and deeper technical insights, I recommend the Oracle documentation on Unified Audit Data Analysis. It is an excellent resource for understanding the basics and advanced concepts. Visit the official documentation from Oracle or the whitepaper Oracle Database Unified Audit: Best Practice Guidelines.

Latest Critical Patch Updates from Oracle – January 2024

On January 18, Oracle unveiled its first quarterly Critical Patch Update Advisory of the year. This advisory, a pivotal resource for Oracle users, details an array of 389 new security patches across various Oracle product families. This update includes several high-severity vulnerabilities, notably those that can be exploited remotely over the network, with some having a CVSS rating of 9 or above. The complete advisory is accessible at CPU January 2024. In this post, I’ll delve into the updates pertinent to my current projects, offering insights on what to expect.

Oracle Database

This update contains security patches that fix 3 vulnerabilities in the Oracle database. These are not vulnerabilities that can be exploited remotely without authentication. It is important to note that these vulnerabilities do not affect client-only installations, i.e. the patches are specifically intended for the database server. The most critical of these vulnerabilities has a CVSS rating of 6.5, which classifies the update as non-urgent. Nevertheless, it is advisable to apply these patches promptly to ensure the continued security of the database.

The essential database patches and release updates:

The patches for Oracle on Linux x86-64 are available immediately. For other operating systems like Linux ARM, Windows etc. the patches will be released step by step within the estimated time frame of the next days. A detailed schedule and more detailed information can be found in the Oracle support document 2986269.1 Critical Patch Update (CPU) Program Jan 2024 Patch Availability Document (DB-only)

A side note: Oracle Database 23c will also receive a targeted patch in this cycle. It is important to note that this patch is not a full release update. Instead, it specifically addresses the security fixes from the October 2023 and January 2024 advisories and currently only applies to the cloud database version of Oracle Database 23c.

Fusion Middlerware

As far as Fusion Middleware is concerned, the situation remains unchanged compared to previous updates. The current version fixes 39 vulnerabilities, 29 of which can be exploited remotely without any form of authentication. The urgency of installing these patches cannot be overstated.

I will focus here on the security updates for WebLogic Server. There is no security update for Oracle Unified Directory included in this Critical Patch Update. The full range of patches is listed in the Oracle support document 2806740.2.

What Else?

The update is very comprehensive and covers a wide range of Oracle products. While summaries, blog posts and reports provide an overview, it is essential to read the Oracle Critical Patch Update Advisory thoroughly and evaluate the patches relevant to your specific Oracle products. This is especially important for multi-component products such as Oracle Enterprise Manager where patch updates need to be applied to the base platform, WebLogic Server, repository database, etc.

Conclusion

Patches for Linux x86-64 are now available with the latest Oracle Critical Patch Update. Other platforms such as Linux ARM and Windows will receive the updates in the next few days (details in the Oracle support document 2986269.1). My tests confirm that these patches are successfully installed and ensure reliable updates.

The urgency of the Oracle Database patches is moderate, with the highest vulnerability rated CVSS 6.5, indicating a balanced approach to the updates. However, the patches for Oracle Fusion Middleware require immediate action due to their typical severity, underlining the importance of prioritizing these updates.

In summary, while the urgency varies by Oracle product, prompt and vigilant application of patches remains critical to maintaining secure and efficient Oracle environments.

The essential Links

Dive into the Latest Enhancements of DBSat 3.1.0

Today, my initial plan was simply to finalize my article on DBSat 3.0.0 for the Oraworld Magazine. However, while checking the links to the DBSat documentation, Oracle Support Notes, and download sources, I discovered that Oracle has, almost simultaneously, released the latest version 3.1.0 of the Oracle Database Security Assessment Tool (DBSAT). Once again, this presents an opportunity to write about the tool and its newest release. I have already covered the major release of DBSAT 3.0.0 in my blog post What You Need to Know About Oracle DB SAT Release 3.0. Now, let’s explore what’s new in version 3.1.0.

Key Features in Release 3.1.0

With the major release of version 3.0.0, Oracle had already made significant improvements to DBSat. This included support for Oracle 23c and over 30 new STIG findings, to name just a few enhancements. Now, with the most recent update, Oracle has introduced several improvements and added new findings, especially for the 23c version.

The latest version focuses on the following improvements:

  • Alignment with CIS Benchmark v1.2: Included 10 new findings based on CIS recommendations for Oracle Database 19c, with updated references.
  • New Finding for Autonomous Database Serverless: Introduction of a finding related to pre-authenticated URL requests.
  • Comprehensive Security Checks: New checks for user profile limits, EXECUTE permissions on various packages to PUBLIC, and database security and administration-related permissions.
  • Auditing and Operating System User Configurations: Addition of checks for auditing actions on synonyms and operating system user configurations in pluggable databases.
  • Enhanced Existing Findings: Improved logic in user expiry checks, optimizations in application owner assessments, and updated TDE recommendations for Oracle Database 23c.

New Findings

Seven of the new findings focus on EXECUTE privilege grants to Public, assessing whether critical packages have been inappropriately granted to Public. The selection of packages for these checks is guided by recommendations from the CIS and encompasses a range of areas including network, file system, encryption, Java, job scheduling, helper functions, and credentials packages.

The following example demonstrates how the finding PRIV.NETPACKAGEPUBLIC identifies network packages that have been granted EXECUTE privileges to PUBLIC.

Information about Network Packages Granted to PUBLIC

Additional findings are detailed in the report’s ‘Privileges and Roles’ chapter. Beyond the network packages mentioned earlier, the report also examines other critical packages, such as DBMS_JAVA, DBMS_JAVA_TEST, JAVA_ADMIN, DBMS_LOB, UTL_FILE, and DBMS_ADVISOR, among others. Furthermore, it assesses other crucial permissions that may have been granted to Public, like CREATE ANY DIRECTORY and DROP ANY DIRECTORY, where relevant.

The additional new checks introduced in DBSAT cover several key aspects:

  • USER.DEFAULTPROFILE: This check details the limitations defined in the DEFAULT user profile.
  • AUDIT.SYNONYMS: It determines if actions such as creating, altering, or dropping SYNONYMs are audited.
  • CONF.DEFAULTPDBOSUSER: This evaluates the operating system user designated in the PDB_OS_CREDENTIAL.
  • CONF.PREAUTHREQUESTURL: It provides insights into pre-authenticated URLs for Autonomous Database Serverless, including which users are authorized to manage these URLs via the DBMS_DATA_ACCESS package.
  • USER.DEFAULTPROFILE: Enumerates the limits set in the DEFAULT user profile.
  • AUDIT.SYNONYMS: Verifies whether actions like create, alter, or drop SYNONYM are being audited.
  • CONF.DEFAULTPDBOSUSER: Evaluates the operating system user specified in the PDB_OS_CREDENTIAL. Particularly important if DB users are allowed to use DBMS_SCHEDULER in a multitenant environment.
  • CONF.PREAUTHREQUESTURL: Shows details of pre-authenticated URLs for Autonomous Database Serverless, including identification of users who can manage them through the DBMS_DATA_ACCESS package.

Below are more examples of these new findings and their representation in the DBSAT report.

Information about Users with DEFAULT Profile
Information about Audit Synonym Management Activities

In addition to the new findings, existing checks have also been revised and updated. These include the following three:

  • USER.APPOWNER: Optimizations have been made to enhance performance and streamline the level of detail.
  • USER.NOEXPIRE: The logic and summary of this check have been improved for better clarity.
  • ENCRYPT.TDE: The remarks have been updated to clarify the use of the TABLESPACE_ENCRYPTION parameter, providing specific recommendations for those upgrading to Oracle Database 23c and transitioning away from deprecated algorithms.

Missing Stuff

The major release 3.0.0 and its latest update 3.1.0 of DBSAT largely fulfill all expectations. DBSAT covers the latest standards and best practices and is also ready for Oracle 23c. However, there are minor issues that one might encounter during initial use. For instance, when gathering information with dbsat collect, warnings may appear if FIPS configuration files are not found. Generally, these can be safely ignored.

Additionally, DBSAT requires Java for the Report or Discover Mode. If a JAVA_HOME variable is not set, DBSAT will terminate with an error. It would be beneficial if DBSAT could default to using the JVM in ORACLE_HOME, at least on the Oracle database server. You can find more on this in my blog post What You Need to Know About Oracle DB SAT Release 3.0.

Conclusion

DBSAT 3.0.0 and its update 3.1.0 represent a significant development and improve both functionality and usability. One of the most important improvements is the independence from Python, which allows for easier deployment. The tool is now ready for Oracle Database 23c with updated security checks, STIG-V2R6 compliance and Oracle Best Practice tagging for result interpretation. The revised report format with clear explanations and guidance simplifies the identification and resolution of security issues. DBSAT also enables customized assessments by excluding specific users or areas. In addition, integration with Oracle Data Safe, Oracle Audit Vault and Database Firewall extends the standalone capabilities and strengthens the security framework of these Oracle products.

If you haven’t reviewed your database security configuration yet, now is the perfect time to begin with DBSAT 3.1.0.

Additional Resources

Some links and references related to this topic.

Easy Audit Data Analysis with SQL Developer Reports

In one of my last blog post SQL Toolbox for simplified Oracle Unified Audit Data Analysis, I introduced a set of scripts designed to streamline Oracle Unified Audit data analysis. These scripts, now available on my GitHub repository oehrlis/oradba, have received positive feedback. Building on that, I’d like to explore an alternative approach to augment your audit data analysis process with SQL Developer Reports.

SQL Plus is available on any database server, so you can quickly perform an initial analysis of audit data using the SQL scripts presented without any additional requirements. It should be noted, however, that while SQLPlus-based scripts are valuable tools, they are not always perfectly suited to all analytical requirements. In certain scenarios, more graphical analysis and the ability to drill down on specific data points are more helpful. This also includes simply getting an overview and drilling down in some cases. To fulfill these differentiated requirements, I use Oracle SQL Developer, a versatile tool that offers the ability to create custom reports. These reports provide a graphical and interactive way to analyze your audit data. In this blog post, I will provide an overview of the reports available and briefly explain how you can seamlessly integrate them into your audit data analysis workflow.

SQL Developer Reports

The Unified Audit Asessment Reprorts for SQL Developers are divided into the following categories:

  • Audit Charts A series of audit reports with diagrams and drill-downs, e.g. audit events per day and hour. These reports are ideal for an initial visual overview of the audit events. However, the runtime of the queries depends heavily on the amount of audit data. In some cases, the reports can be limited to a few days.
  • Audit Configuration Reports on audit configuration, memory usage, segments, audit policies, cleanup events, cleanup jobs and more. These reports provide a good overview of what is configured and how much audit data is already available.
  • Audit Sessions Overview of the various audit sessions with drill-down to the individual events of a session. Reports are available for the various audit types, e.g. proxy session, standard, RMAN, DataPump and more. These reports are ideal for checking what exactly was executed within a single session.
  • Generate Statements These are not really reports as such, but queries to generate the various statements for creating, deleting, enabling or disabling the audit policies. Corresponding authorization on DBMS_METADATA is required.
  • Miscellaneous All types of audit reports that have not yet been categorized. Mainly things that are still under development.
  • Top Audit Events Provide information about audit events that are selected and organized according to various criteria. This information can help you to identify audit events that occur more frequently than expected or generate more data than expected.

The queries were created to the best of our knowledge and belief. Nevertheless, the individual queries may take a little more time, depending on the amount of data. If necessary, you can copy the reports and optimize / modify them accordingly. Suggestions for improvements are of course always welcome.

Audit Reports in Detail

Audit Charts

ReportDescription
Audit Events by DayChart for the number of audit events per day with the option to drill down by audit events per hour.
Audit Events by UserChart for the number of audit events per database user with the option to drill down audit events by audit policy, action, user host and audit policy with actions.
Audit Chart Reports
Audit events by day with drill-down by hour

Audit Configuration

ReportDescription
Storage UsageThis report provides comprehensive details about the usage and configuration of the audit store, focusing especially on the DB ID and the archiving timestamp. It enables you to assess whether audit data can be deleted based on its age or if it originates from a different database. Additionally, the report offers a drill-down feature to view partition information, storage details, and purge statements. Similar to sdua_usage.sql, sdua_stostm.sql and sdua_prgstm.sql.
Clean up EventsDisplays the audit cleanup event from DBA_AUDIT_MGMT_CLEAN_EVENTS.
Clean up JobsDisplays the audit cleanup jobs from DBA_AUDIT_MGMT_CLEANUP_JOBS
ConfigurationDisplays the audit configuration from DBA_AUDIT_MGMT_CONFIG_PARAMS
Last Archive TimestampDisplays the audit archive timestamp from DBA_AUDIT_MGMT_LAST_ARCH_TS
Unified Audit PoliciesThis report offers a comprehensive overview of unified audit policies, drawing data from the views AUDIT_UNIFIED_POLICIES and AUDIT_UNIFIED_ENABLED_POLICIES. It facilitates a deeper understanding of the configuration and current status of audit policies within the system, similar to the script saua_pol.sql. The report includes extensive drill-down options, allowing for detailed analysis based on comments, actions, and statements such as AUDIT, NOAUDIT, CREATE, and DROP, as well as events and inactive policies.
Audit Configuration Reports
Audit Storage Usage

Audit Sessions

ReportDescription
by any typeDisplays audit sessions for any audit type, similar in function to the script saua_as.sql. With drill-down to session details.
by type DataPumpDisplays audit sessions for audit type DataPump, similar in function to the script saua_asdp.sql. With drill-down to session details.
by type DB VaultDisplays audit sessions for audit type Database Vault, similar in function to the script saua_asdbv.sql. With drill-down to session details.
by type Direct path APIDisplays audit sessions for audit type Direct path API based on UNIFIED_AUDIT_TRAIL. With drill-down to session details.
by type FGADisplays audit sessions for audit type FGA, similar in function to the script saua_asfga.sql. With drill-down to session details
by type ProtocolDisplays audit sessions for audit type Protocol. With drill-down to session details.
by type RMAN_AUDITDisplays audit sessions for audit type RMAN_AUDIT, similar in function to the script saua_asbck.sql. With drill-down to session details.
by type StandardDisplays audit sessions for audit type standard, similar in function to the script saua_asstd.sql. With drill-down to session details.
Proxy SessionsDisplays audit proxy sessions for audit type Standard based on UNIFIED_AUDIT_TRAIL. With drill-down to session details.
Session DetailsList detail of a particular session.
Session OverviewDisplays audit sessions for any audit type, similar in function to the script saua_as.sql.
Audit Session Reports

Generate Statements

ReportDescription
Create all audit policiesGenerates statements to recreate all audit policies as currently configured in AUDIT_UNIFIED_ENABLED_POLICIES.
Disable all audit policiesGenerates statements to disable all audit policies as currently configured in AUDIT_UNIFIED_ENABLED_POLICIES.
Drop all audit policiesGenerates statements to remove all audit policies, excluding those maintained by Oracle.
Enable all audit policiesGenerates statements to enable all audit policies as currently configured in AUDIT_UNIFIED_ENABLED_POLICIES.
Generate Statements Reports

Top Audit Events

ReportDescription
Events by UsersThis query generates a summary of audit events grouped by database username in the unified audit trail. It counts the total number of events for each user and sorts the results in descending order, showcasing the users with the highest frequency of audit events.
Top Events by ActionDisplays a ranking of Action based on the frequency of their associated events in the unified audit trail, offering insights into the most audited databases. Comparable to the script saua_teact.sql.
Top Events by Application ContextDisplays a ranking of Application Context based on the frequency of their associated events in the unified audit trail, offering insights into the most audited databases.
Top Events by Audit TypeDisplays a ranking of Audit Type based on the frequency of their associated events in the unified audit trail, offering insights into the most audited databases.
Top Events by ClientDisplays a ranking of Client Program based on the frequency of their associated events in the unified audit trail, offering insights into the most audited databases. Comparable to the script saua_tehost.sql.
Top Events by Client ProgramDisplays a ranking of Client Program based on the frequency of their associated events in the unified audit trail, offering insights into the most audited databases. Comparable to the script saua_tecli.sql.
Top Events by DBIDDisplays a ranking of database IDs (DBIDs) based on the frequency of their associated events in the unified audit trail, offering insights into the most audited databases. Comparable to the script saua_tedbid.sql.
Top Events by External User IDPresents a ranking of External User IDs by the frequency of their associated events in the unified audit trail, providing insights into the most audited user identities.
Top Events by Global User IDPresents a ranking of Global User IDs by the frequency of their associated events in the unified audit trail, providing insights into the most audited user identities.
Top Events by none Oracle Object NameShows a ranking of non-Oracle object names based on the frequency of associated audit events in the unified audit trail, highlighting the most audited non-Oracle objects. Comparable to the script saua_teobj.sql.
Top Events by Object NameDisplays a ranking of object names based on the frequency of associated events in the unified audit trail, offering insights into the most audited objects. Similar to the script saua_teobj.sql.
Top Events by Object SchemaShows a ranking of Object Schemas by event frequency, emphasizing the policies most often triggered in the unified audit trail. Comparable to the script saua_teown.sql.
Top Events by OS UserDisplays a ranking of OS User based on the number of associated events, highlighting the most frequently triggered policies in the unified audit trail. Comparable to the script saua_teosusr.sql.
Top Events by policesDisplays a ranking of audit policies based on the number of associated events, highlighting the most frequently triggered policies in the unified audit trail. Similar in function to the script saua_tepol.sql.
Top Events by SQL TextSummarizes and ranks SQL statements from the unified audit trail by their event frequency, highlighting the most frequently executed queries.
Top Audit Event Reports

Add Custom Reports in SQL Developer

The SQL Developer already offers many predefined reports. Unfortunately not many in the database security area. However, it is possible to extend the predefined reports with your own reports. See also SQL Developer Reports. The Unified Audit Assessment Reports presented here are bundled in an XML file and available via xx. This XML file can be added relatively easily in SQL Developer. You have the choice to do this as a User Defined Report or Shared Report.

User Defined Reports

Adding user defined reports is relatively simple. Just select the context menue in User Defined Reports and click Open Reports… and select your XML file.

User-defined reports can be modified, renamed, and saved after changes. However, updating these reports directly from an XML file is not straightforward. To incorporate updates from an XML file, the reports must first be removed and then reloaded.

Database / Shared Reports

Database respectivily Shared Reports have a distinct setup process. They must be added through preferences, after which they appear under the Shared Reports tree. Unlike other reports, these cannot be modified directly in SQL Developer, as it only loads the XML file during startup. However, if the XML file is updated — for instance, by downloading a new version — the reports will be updated upon the next restart. For further customization, these reports can still be copied to User Defined Reports for individual modifications.

To add a Shared Report, follow these steps:

  1. Open Preferences in SQL Developer
  2. Navigate to User Defined Extensions
  3. Click Add Row and configure the following:
    • Set Type to REPORT
    • Provide the location path to your XML file.

The screenshot below illustrates an example of this process

SQL Developer Preferences – User Defined Extenstions

Conclusion

The SQL Developer reports presented here, as well as the SQL scripts, are ideal tools for evaluating local audit data quickly and easily. Especially when it comes to getting a first insight into the collected information or when developing new audit policies. For a company-wide overview in the area of database security and auditing, however, there is no way around a central solution. This includes the following solution approaches:

  • Oracle Data Safe A cloud-based security service that automates sensitive data discovery, data masking, database security assessments, and user risk assessments. It’s designed to secure Oracle databases and enhance overall data protection.
  • Oracle Audit Vault and Database Firewall Provides a comprehensive security solution that includes monitoring, analysis, and blocking of unauthorized database activities. It consolidates audit data from multiple sources and offers real-time alerts and reports.
  • Custome Solution Involves developing a bespoke Data Warehouse (DWH) for audit data or utilizing powerful tools like Splunk or Elasticsearch. This approach allows for tailored data aggregation, reporting, and analysis, meeting specific organizational needs. Splunk and Elasticsearch offer advanced data indexing, search capabilities, and visualization tools, making them ideal for handling complex audit data.

I intend to go into more detail about centralized audit solutions in future posts. In the meantime, I hope you can use my scripts to analyze local audit data.

Additional Resources

Some links and references related to this topic.

DOAG 2023 Debrief: A Speaker’s Perspective

It’s a wrap. The DOAG database conference has come to an end. As I sit on the train, I reflect on a week full of excitement and variety. In addition to the many fascinating technical presentations, I had the opportunity to catch up with old friends and colleagues. The conference started on Monday afternoon when the Oracle ACEs met for the ACE Adventure. We got to know a different side of the city on a guided tour of Nuremberg’s rock cellars. The day ended with a beer tasting and dinner together. In addition to the ACE dinner, the SOUG evening was certainly another highlight of the event. In addition to the various social events, there were also plenty of interesting and informative technical presentations at this conference.

My Talks in a Nutshell

Oracle Database Security 23c new Feature

In my first session, I presented Oracle’s new Database Security 23c features. It was a unique opportunity to present this in the large keynote room. One of the most important new features is undoubtedly the SQL Firewall. Oracle now offers a novel approach to protecting the database from unauthorized access, SQL injection and more. In addition, Oracle Database 23c also introduces smaller improvements such as new schema privileges, read-only accounts, immediate activation of audit policies as well as updates and adaptations to new security standards.

Although not a keynote, it was an honor to present in the keynote room

Secure administration of Database users on a large scale

My second presentation, which I held together with Bettina Schaumer, Senior Principal Product Manager at Oracle, focused on Oracle Data Safe and Oracle Centrally Managed Users (CMU). Together we demonstrated how Data Safe can be used to analyze the current configuration of the database as well as the users, roles and permissions. We then presented a strategy for centrally managing users and roles with Oracle CMU and discussed its implementation.

Database security, but what about Performance 2.0?

The focus of my third presentation was “Database security and its impact on performance”. I covered various test results that included network access and the use of Transparent Data Encryption (TDE). A key aspect of my presentation was to demonstrate the considerations necessary to determine the direct impact of specific security measures on database performance. I conducted my tests in both on-premises and cloud environments. Swingbench was used for these tests, and I presented my custom scripting framework for automating Swingbench executions in various configurations. The complicated relationship between performance and database security is a challenge because every environment, application and database is different. However, this talk provides essential insights to kick-start your own investigations in this area.

DOAG Training Day: Oracle Backup & Recovery Hands-On Lab

Once again this year, I couldn’t let it go and concluded my DOAG conference with a training day. In this session, we focused on backup & recovery strategies in companies. After an introduction to Oracle Backup & Recovery and RMAN, the participants had the chance to test their knowledge in a cloud-based lab. They worked on various tasks, from simple backup and recovery scenarios to complex disaster recovery cases, both independently and in groups. The aim was to gain practical experience in database recovery. This training was an excellent opportunity for participants to develop practical skills and exchange ideas with others to learn new approaches.

Conclusion

This year’s DOAG conference was a remarkable blend of technical enrichment, professional networking and social engagement. Beginning with the Training Day, which offered hands-on experience with Oracle technologies, the event was an in-depth exploration of the latest trends and practices in the Oracle ecosystem. Highlights included the ACE Adventure and the SOUG Evening, which not only showcased the historical charm of Nuremberg, but also provided ample opportunity to catch up with old friends and catch up with colleagues. The various sessions and discussions throughout the week emphasized the importance of innovation and efficient problem solving in the database field. This event was not only a learning experience, but also a chance to strengthen relationships within the Oracle community, and it left me invigorated and looking forward to future collaborations and gatherings.

Just one more thing: During the conference, I received a lot of positive feedback about the sketches and drawings I created using Excalidraw. For those interested, my library is available via Excalidraw, and many images can also be found in my GitHub repository oehrlis/sketches. These resources reflect my commitment to enhancing our understanding and visualization of complex Oracle systems.

Tune Conference Badges: A Casual Session with Beers

What You Need to Know About Oracle DB SAT Release 3.0

DBSat

The wait is over! After a long break, Oracle has launched a major update for its Database Security Assessment Tool, DBSAT. The latest version, DBSAT 3.0, includes a number of new features and enhancements, all aimed at increasing database security and optimizing compliance processes. In this post, I’ll look at what’s new in this release and explain how it can have a positive impact on your Oracle database environment.

Key Features in Release 3.0

An overview of the improvements and new functions:

  1. STIG V2R8 compliance: This update includes 30 new STIG findings and revised STIG group IDs to ensure compliance with the latest Security Technical Implementation Guide standards.
  2. Enhanced Auditing and Security: DBSAT 3.0 introduces new auditing results, with five specific additions, and updates all existing ones. Of particular note is the focus on Sensitive Data and Transparent Sensitive Data Protection (TSDP) and the integration of the Oracle Database 23c SQL Firewall.
  3. Sensitive Data Discovery: The tool is now able to identify Indian PAN and Aadhaar numbers, expanding its scope when searching for sensitive information.
  4. Improved Clarity and Quality: Each check now comes with a one-line summary outlining the objective of the check. Results are labeled according to Oracle best practices, and there is a specific note on unsupported features in Oracle Database 23c.
  5. Operational Enhancements: DBSAT 3.0 provides a new option (-u) to exclude certain users from reports, removes dependency on Python, and provides performance optimizations for faster data collection. It is also compatible with Linux 64-bit Arm and supports Oracle Database 23c.

DBSAT 3.0 in Action

Let’s go through a simple example of using DBSAT 3.0 to evaluate a database with Oracle 19c. As a test, I’ll use my database container on my MacBook Pro with Oracle Database 19c for ARM Linux, which is now also supported by DBSat.

Download and Install DBSAT 3.0

The easiest way to find DB Sat is to access the product page Oracle Database Security Assessment Tool DBSat. From there you are directed to a corresponding Oracle support document 2138254.1 for download. However, you need a corresponding Oracle account for the download.

As mentioned above, Python is no longer required for execution. You only need an Oracle Database, corresponding credentials and a JDK. The JDK in the Oracle home directory is all you need.

Let’s unzip the package to the $ORACLE_BASE/product directory

unzip dbsat.zip -d $ORACLE_BASE/product/dbsat_3.0.0

If not yet set define the JAVA_HOME environment variable

export JAVA_HOME=$ORACLE_HOME/jdk
export PATH=$JAVA_HOME/bin:$PATH

Verify if we can run dbsat and display its new usage.

$ORACLE_BASE/product/dbsat_3.0.0/dbsat -h

If successfully you should see something linke this.

oracle@cdbua190:~/ [CDBUA190] $ORACLE_BASE/product/dbsat_3.0.0/dbsat -h

Database Security Assessment Tool version 3.0 (Nov 2023)

    Usage: dbsat collect [ -n ] <database_connect_string> <output_file>
           dbsat report [ -a ] [ -n ] [ -g ] [ -x <section> ] [ -u <user> ] <input_file>
	   dbsat discover [ -n ] -c <config_file> <output_file>

    Options:
       -a  Report with all user accounts, including locked and schema-only,
           Oracle-supplied users
       -n  No encryption for output
       -g  Show all grants including Common Grants in a Pluggable Database
       -x  Specify sections to exclude from report (may be repeated for
           multiple sections)
       -u  Specify users to exclude from report
       -c  Configuration file for discoverer

Run the Assessment

As before, dbsat can be run in three execution modes. I.e. collect, report and discover:

  1. dbsat collect: Gathers data from the specified database.
  2. dbsat report: Generates a security assessment report.
  3. dbsat discover: Identifies sensitive data within the database.

Let’s gather data from the container database CDBUA190 as user system

$ORACLE_BASE/product/dbsat_3.0.0/dbsat collect -n system@CDBUA190 $HOME/CDBUA190_v1.1

Here is an excerpt from the output of the DBsat call without the license and version information:

Setup complete.
SQL queries complete.
Warning: Exit status 256 from OS rule: dbcs_status
/bin/cat: /u00/app/oracle/product/19.0.0.0/ldap/admin/fips.ora: No such file or directory
Warning: Exit status 256 from OS rule: fips1.ora
/bin/cat: /fips.ora: No such file or directory
Warning: Exit status 256 from OS rule: fips2.ora
/bin/ls: cannot access '/u00/app/oracle/product/19.0.0.0/rdbms/log/diag': No such file or directory
Warning: Exit status 512 from OS rule: diag_dest_home
OS commands complete.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
DBSAT Collector completed successfully.

As you can see, there are a few warnings for certain OS checks, particularly regarding the FIPS configuration and a Diag directory located below $ORACLE_HOME. These warnings can typically be ignored, especially if FIPS is not being utilized in your environment.

If desired, you have the option to process the JSON document that was generated. However, creating a report from this data tends to be more straightforward. DBSAT offers the flexibility to create reports in various formats, including JSON, text, HTML, or Excel. It’s crucial to be in the DBSat directory when generating the report to ensure the tool can locate the xlsxwriter. Following the previous dbsat collect command, you can create the report as shown below.

cp $HOME/CDBUA190_v1.1.json  $ORACLE_BASE/product/dbsat_3.0.0/
cd $ORACLE_BASE/product/dbsat_3.0.0/
./dbsat report -n -a -g CDBUA190_v1.1

More variations on how to use DBSat can be found in the online documentation User Guide.

Analyze the Report

When viewing the HTML report, you can immediately see the additional information for the individual findings. Below an example for the rule checking for the latest security patch.

Example Finding

Overall, DBSat has expanded its scope to more than 120 deliverables, which include not only STIG and CIS recommendations, but also Oracle Best Practice (OBP) topics. This inclusion is particularly beneficial considering that Oracle’s feature and release cycle does not always coincide with updates to existing standards and frameworks. Of particular importance is the fact that Oracle introduces new features with each release, which require appropriate configuration and can significantly impact the security of the database.

For those who manage databases with an extensive history that includes multiple migrations, DBSAT offers a notable advantage. It provides relevant desupport information related to the latest Oracle database version, 23c. This feature is particularly valuable in ensuring that even the most complex, historically grown databases remain compatible and secure with the latest Oracle technologies.

Conclusion

DBSAT 3.0 represents a significant leap forward and offers numerous enhancements that improve not only functionality but also usability. A standout feature is the newfound independence from Python, which simplifies deployment directly on the DB server or remotely from a DBA workstation.

The security checks have been carefully enhanced and prepare the tool for the upcoming Oracle Database 23c. Updates such as STIG-V2R6 compliance and Oracle Best Practice tagging help to interpret and prioritize results. The revised format of the report, which now includes clear explanations, risk levels and best practice guidance for each finding, greatly aids understanding and remediation of security issues.

In addition, DBSAT 3.0 provides flexibility in handling large-scale findings. Users can streamline assessments and reports by excluding specific users or areas and focusing on critical areas.

Additionally, DBSAT’s integration with Oracle Data Safe, Oracle Audit Vault and Database Firewall underscores its importance. The enhancements in DBSAT 3.0 not only increase the standalone capabilities, but also enrich the security capabilities of these integrated Oracle products.

Additional Resources

Some links and references related to this topic.

  • Oracle Database Security a technical primer
  • Oracle Database Security Assessment Tool DBSat
  • Oracle Database Security Assessment Tool 3.0.0 Books
  • LiveLabs – Database Security Assessment Tool (DBSAT)
  • Oracle Support Document 2138254.1 Oracle Database Security Assessment Tool (DBSAT)
  • Oracle Support Document 2484219.1 Common Questions and Issues For Oracle Database Security Assessment Tool (DBSAT)
  • Oracle Support Document 2651827.1 Does DBSAT Scan for all of the STIG And CIS Benchmark Controls?

Easy setup of Kerberos Authentication for Oracle Databases

I have previously published a couple of blog posts related to Kerberos authentication for databases. In this post, I want to provide a simple, step-by-step tutorial for configuring Kerberos authentication. This tutorial is based on my lab setup within Oracle Cloud Infrastructure (OCI). Within this environment, I run both a database server and a corresponding Windows server configured as an Active Directory server. It should be noted that this tutorial is designed for a basic environment. The configuration must be adapted accordingly for special cases such as clusters, multiple AD forests or domains, OS configuration, etc.

Prerequisites and Requirements

Configuring Kerberos authentication for Oracle databases involves a number of tasks, each of which requires specific permissions. In my lab environment, of course, I have all the necessary permissions. In other environments, certain tasks may need to be delegated to other administrators. Essentially, the following steps need to be performed:

  • DB Server Install software component for Kerberos client tools as root user.
  • AD Server Create a service account in AD as a domain administrator.
  • DB Environment Configure the SQLNet environment as the Oracle user.
  • DB Instance Adjust the init.ora parameters and establish Kerberos accounts.

The subsequent Kerberos configuration relies on the following values:

  • AD Domain / KDC Realm: TRIVADISLABS.COM
  • AD Server / KDC: ad.trivadislabs.com (10.0.1.4)
  • Database Server (FQDN): db23.trivadislabs.com (10.0.1.23)
  • Database Server OS: Oracle Enterprise Linux 8 (Version: 8.8)
  • SamAccountName: db23
  • User Principal Name (UPN): db23.trivadislabs.com
  • Service Principle Name (SPN): oracle/db23.trivadislabs.com
  • Database SID: CDB23B with pluggable database PDB1B and PDB2B

Please note that for different environments and operating systems, the commands may need to be adjusted accordingly.

Step 1 Preparation Database Server

Command line commands to install Kerberos client utilities on OEL8/REL8

sudo dnf install krb5-workstation

Step 2 Service Account Configuration

The following steps should be performed on the AD server by a domain administrator or an administrative account with the required privileges. Essentially, the choice of the tool used for these tasks is not relevant; however, the following section describes only the relevant PowerShell commands.

If the service account already exists, we will delete it first. This step is optional. Nevertheless, it is not a bad idea to start the configuration from scratch. This will certainly make troubleshooting a bit easier.

$Hostname = "db23"
if (!(Get-ADUser -Filter "sAMAccountName -eq '$Hostname'")) {
  Write-Host "INFO : Service Account ($Hostname) does not exist."
} else  {
  Write-Host "INFO : Remove existing Service Account ($Hostname)."
  Remove-ADUser -Identity $Hostname -Confirm
} 

The PowerShell command provided below is used to create a service account with the appropriate flags set to support Kerberos encryption using both AES 128 and 256-bit methods. This guarantees that the keytab file can be generated with the necessary encryption types for AES, and authentication will function properly with such a keytab file. Update the service account name, password and UserDN accordingly.

$ServiceName = "db23"
$UserBaseDN  = "cn=Users," + (Get-ADDomain).DistinguishedName
$DNSRoot     = (Get-ADDomain).DNSRoot

# get the credentials for the kerberos service account
$credential = Get-Credential -message 'Kerberos Service Account' -UserName $ServiceName
$ServiceUserParams = @{
    Name                    =   $credential.UserName
    DisplayName             =   $ServiceName
    SamAccountName          =   $ServiceName
    UserPrincipalName       =   "oracle/$ServiceName.$DNSRoot"
    Description             =   "Kerberos Service User for $ServiceName"
    Path                    =   $UserBaseDN
    AccountPassword         =   $credential.Password
    PasswordNeverExpires    =   $true
    Enabled                 =   $true
    KerberosEncryptionType  =   "AES256"
}

# create kerberos service account
New-ADUser @ServiceUserParams

The final step on Windows involves creating a Service Principal Name (SPN) for the service user. If ktpass.exe is used to generate the keytab file, this is done automatically. However, as we are creating the keytab file on the database server using ktutil, we need to create the Service Principal Name (SPN) manually using setspn.

$ServiceName = "db23"
$DNSRoot     = (Get-ADDomain).DNSRoot
setspn $ServiceName -s oracle/$ServiceName.$DNSRoot

Example output of the command.

PS C:\Windows\system32> setspn $ServiceName -s oracle/$ServiceName.$DNSRoot@$Domain
Checking domain DC=trivadislabs,DC=com

Registering ServicePrincipalNames for CN=db23,CN=Users,DC=trivadislabs,DC=com
        oracle/db23.trivadislabs.com
Updated object
PS C:\Windows\system32>

Step 3 Oracle SQLNet Configuration

It is recommended to set up the SQLNet configuration for each database server in the $TNS_ADMIN directory. This is especially important if you are working with multiple Oracle Homes. Otherwise, multiple Kerberos configurations must be maintained for each database server.

Add the following kerberos configuration section to you sqlnet.ora file. Adjust the path to keytab and krb5.conf file accordingly.

# ----------------------------------------------------------------
# Kerberos settings
# ----------------------------------------------------------------
SQLNET.AUTHENTICATION_SERVICES=(beq,tcps,kerberos5pre,kerberos5)
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle
SQLNET.FALLBACK_AUTHENTICATION = TRUE
SQLNET.KERBEROS5_KEYTAB = /u01/app/oracle/network/admin/krb5.keytab
SQLNET.KERBEROS5_CONF = /u01/app/oracle/network/admin/krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE

Create a new Kerberos configuration file, krb5.conf, in your $TNS_ADMIN folder. Adjust the KDC realm, domain, etc., as needed.

# ----------------------------------------------------------------
# OraDBA - Oracle Database Infrastructur and Security, 5630 Muri,
# Switzerland
# ----------------------------------------------------------------
# Name.......: krb5.conf
# Author.....: Stefan Oehrli (oes) stefan.oehrli@oradba.ch
# Editor.....: Stefan Oehrli
# Date.......: 2023.05.04
# Version....: --
# Purpose....: Kerberos Configuration File
# Notes......: --
# Reference..: Oracle Database Security Guide 19c
# ----------------------------------------------------------------
[libdefaults]
forwardable = true
default_realm = TRIVADISLABS.COM
 
[realms]
  TRIVADISLABS.COM = {
    kdc = ad.trivadislabs.com
  }
 
[domain_realm]
.trivadislabs.com = TRIVADISLABS.COM
trivadislabs.com = TRIVADISLABS.COM

It is advisable to restart both the listener and the databases afterward to ensure that the new sqlnet.ora configuration is applied universally. However, this restart does not necessarily need to occur immediately.

Step 4 Create keytab File

Generate a Ticket Granting Ticket (TGT) for the service principal. To confirm the service account and streamline the subsequent steps, obtain a TGT using okinit, an Oracle tool that relies on the previously mentioned sqlnet.ora configuration.

okinit db23@TRIVADISLABS.COM

Example output of the command.

oracle@db23:~/ [CDB23B] okinit db23@TRIVADISLABS.COM

Kerberos Utilities for Linux: Version 23.0.0.0.0 - Beta on 08-NOV-2023 16:17:13

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

Configuration file : /u01/app/oracle/network/admin/krb5.conf.
Password for db23@TRIVADISLABS.COM:
oracle@db23:~/ [CDB23B] 

Obtain the Key Version Number (kvno) for the Service Principal. We need the key version number (kvno) for the service principal, which can be retrieved using the kvno utility. You also have the option to explicitly specify the ticket cache using the -c flag. The kvno is crucial for creating the keytab file.

By default, Linux Kerberos tools require a krb5.conf file in /etc. Since we do not intend to configure Kerberos for Linux authentication, we can specify the krb5.conf file from TNS_ADMIN by using the environment variable KRB5_CONFIG.

export KRB5_CONFIG=$TNS_ADMIN/krb5.conf
kvno -c /tmp/krb5cc_1000 db23@TRIVADISLABS.COM

Example output of the command.

oracle@db23:~/ [rdbms] export KRB5_CONFIG=$TNS_ADMIN/krb5.conf
oracle@db23:~/ [rdbms] kvno -c /tmp/krb5cc_1000 db23@TRIVADISLABS.COM
db23@TRIVADISLABS.COM: kvno = 2

We now create a keytab file with ktutil. The tool must be used interactively to read, create and write the keytab file. See the ktutil man page for full usage. In the following example, we use the aes256-cts-hmac-sha1-96 encryption type. Update the addent command accordingly with the correct kvno and encryption type. Optionally, you can add multiple encryption types to a keytab by running addent multiple times. The list of encryption types can be found at Kerberos Parameters. Make sure to use encryption types which are supported by your KDC.

oracle@db23:~/ [rdbms19] export KRB5_CONFIG=$TNS_ADMIN/krb5.conf
oracle@db23:~/ [rdbms19] mv $TNS_ADMIN/krb5.keytab $TNS_ADMIN/krb5.keytab.orig
oracle@db23:~/ [rdbms19] ktutil
ktutil:  addent -password -p oracle/db23.trivadislabs.com@TRIVADISLABS.COM -k 2 -e aes256-cts-hmac-sha1-96
Password for oracle/db23.trivadislabs.com@TRIVADISLABS.COM:
ktutil:  list -e
slot KVNO Principal
---- ---- ---------------------------------------------------------------------
   1    2 oracle/db23.trivadislabs.com@TRIVADISLABS.COM (aes256-cts-hmac-sha1-96)
ktutil:  wkt /u01/app/oracle/network/admin/krb5.keytab
ktutil:  q

Verify the new keytab file using oklist.

oracle@db23:~/ [rdbms] oklist -e -k

Kerberos Utilities for Linux: Version 23.0.0.0.0 - Beta on 08-NOV-2023 16:18:31

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

Configuration file : /u01/app/oracle/network/admin/krb5.conf.
Keytab name: FILE:/u01/app/oracle/network/admin/krb5.keytab
KVNO Principal
---- --------------------------------------------------------------------------
   2 oracle/db23.trivadislabs.com@TRIVADISLABS.COM (AES-256 CTS mode with 96-bit SHA-1 HMAC) 

Step 5 Database Configuration

To enable Kerberos database authentication, it is necessary to modify the init.ora parameters os_authent_prefix=” and for system older als 23c also remote_os_authent=FALSE in each database. Both parameters require a database restart.

ALTER SYSTEM SET os_authent_prefix='' SCOPE=spfile;

Furthermore, it is essential to create or modify the appropriate database users for Kerberos authentication. In the following example, we will create a user named King and grant them the necessary privileges to establish a connection to the database and select information from V$SESSION.

ALTER SESSION SET CONTAINER=pdb1b;
CREATE USER king IDENTIFIED EXTERNALLY AS 'king@TRIVADISLABS.COM';
GRANT create session TO king;
GRANT SELECT ON v_$session TO king;

Step 6 Test Kerberos Authentication

We initially create a Ticket Granting Ticket (TGT) for a specific user, such as King.

okinit king

Example output of the command.

oracle@db23:~/ [CDB23B] okinit king

Kerberos Utilities for Linux: Version 23.0.0.0.0 - Beta on 03-NOV-2023 15:47:27

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

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

Now, we can connect directly to the PDB1B database using SQL*Plus without specifying a username and password.

oracle@db23:~/ [CDB23B] sqlplus /@pdb1b

SQL*Plus: Release 23.0.0.0.0 - Beta on Wed Nov 8 16:20:43 2023
Version 23.2.0.0.0

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

Connected to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Beta
Version 23.2.0.0.0

SQL>

By querying the system context USERENV, you can find relevant information about the user, authentication method and more.

SET linesize 160 pagesize 200
COL db_user FOR A20
COL auth_method FOR A20
COL auth_id FOR A40

SELECT 
   sys_context('userenv','SESSION_USER') db_user,
   sys_context('userenv','AUTHENTICATION_METHOD') auth_method,
   sys_context('userenv','AUTHENTICATED_IDENTITY') auth_id
FROM dual;

Example output of the query.

SQL> SELECT
  2     sys_context('userenv','SESSION_USER') db_user,
  3     sys_context('userenv','AUTHENTICATION_METHOD') auth_method,
  4     sys_context('userenv','AUTHENTICATED_IDENTITY') auth_id
  5  FROM dual;

DB_USER 	     AUTH_METHOD	  AUTH_ID
-------------------- -------------------- ----------------------------------------
KING		     KERBEROS		  king@TRIVADISLABS.COM

Tips and Best Practices

Once Kerberos is up and running, the experience is generally smooth. However, it’s important to consider these best practices:

  • Start Simple: Begin with an uncomplicated setup. A multi-domain Oracle Maximum Availability Architecture (MAA) environment isn’t necessary for initial implementation.
  • Ensure Basic Infrastructure: Verify that the foundational configurations are in place, such as network access, open ports, proper name resolution, and synchronized time settings.
  • Avoid Ad-hoc Tweaks: If issues arise, resist the urge to make random changes. Certain details may be cached, complicating the troubleshooting process. Instead, systematically revert to the last working configuration and proceed cautiously.
  • Regenerate Keytab Files When Needed: Many issues can be resolved with a fresh and correctly configured keytab file. Don’t hesitate to recreate it.
  • Adopt Secure Practices Early: Begin with robust security measures, such as employing strong encryption algorithms and setting secure passwords. The mindset of ‘I’ll secure it later‘ often leads to vulnerabilities. 🤪
  • Document Troubleshooting Steps: Keeping a record of the steps and solutions can be invaluable for future reference.
  • Distinguish Between OS and DB Authentication: It’s critical to understand the differences between OS-level Kerberos authentication and Oracle database-specific Kerberos authentication. Do not confuse the two.

Common Errors and Troubleshooting

The Oracle Support Document 185897.1 Kerberos Troubleshooting Guide provides a comprehensive overview of potential issues you might encounter with database Kerberos authentication. Additionally, my blog post Kerberos Troubleshooting – A few approaches outlines practical troubleshooting examples. When addressing Kerberos authentication problems, enabling Oracle SQLNet Tracing is often indispensable; without it, you might find yourself groping in the dark.

Below a couple of common Database Kerberos Authentication issues

  • Incorrect Keytab File: The keytab file may be outdated or incorrectly configured.
  • Service Principal Name (SPN) Mismatches: The SPN registered in Active Directory doesn’t match the one the Oracle server is expecting.
  • Clock Skew: There’s too much time difference between the client and server machines, or between the server and the domain controller.
  • DNS Resolution Problems: The client or server may be unable to resolve the domain names to their IP addresses.
  • Expired Credentials: User credentials or service tickets may have expired.
  • Kerberos Realm Confusion: Incorrect configuration of the Kerberos realm can lead to failed authentication.
  • Version Mismatch: The version of Kerberos on the client does not match with what the Oracle Database expects.
  • Access Denied: Improper permissions set for the Oracle service account within Active Directory.
  • SQLNet Configuration: sqlnet.ora or krb5.conf file may have incorrect entries or lack necessary Kerberos parameters.
  • Kerberos Ticket Issues: Problems obtaining or using a valid Kerberos ticket due to cache issues or misconfigurations.
  • Network Issues: Latency or connectivity problems can prevent proper communication between the client, server, and Kerberos Key Distribution Center (KDC).
  • Case Sensitivity: Kerberos is case-sensitive; mismatches in case between configurations can cause failures.
  • Client Configuration Errors: The Kerberos client may not be configured correctly on the user’s machine, leading to authentication errors.
  • Multi-Domain Environments: Additional complexities when the database and users are in different domains or forests.
  • KVNO Mismatch: Discrepancies between the KVNO in the keytab file and the KVNO for the service principal in the KDC can result in authentication failures. This often happens after a password change for a service account where the keytab file was not simultaneously updated

Unfortunately, I keep running into a new problem every time I try to configure Kerberos. When writing this blog post, it took me a relatively long time to figure out that the User Principal Name (UPN) of my service account was not set. The error was of course an ORA-01017 and ORA-12631, although this can easily be checked with the following LDAP query.

ldapsearch -h ad.trivadislabs.com -p 389 \
-D king@TRIVADISLABS.COM -q \
-b "cn=Users,dc=trivadislabs,dc=com" \
-s sub "(sAMAccountName=db23)" \
userPrincipalName servicePrincipalName

Conclusion

As you can see, it is clear that setting up and configuring Kerberos is a straightforward process. It provides a relatively simple way to increase the security of database accounts and at the same time significantly improve the user-friendliness of single sign-on (SSO). However, the devil is in the detail. In complex Active Directory domains or Key Distribution Centers (KDCs), additional configuration, such as setting up domain trust, can involve a certain amount of complexity. Furthermore, not all tools and clients are Kerberos-capable out of the box. Therefore, it is important to understand the database users and their access methods. Even with the integration of Kerberos, a well thought-out user and role concept remains essential. However, Kerberos integrates seamlessly with Oracle’s Centrally Managed Users (CMU) and can coexist with other authentication methods, such as password-based authentication. Why don’t you start by configuring Kerberos for your DBAs and power users?

Additional Resources

Some links and references related to this topic.

Quickly run X11 Apps from an Oracle DB Container

Running an Oracle Database (DB) Container on your laptop, regardless of your device, is a common practice for professionals in lab and engineering environments. These containers offer a convenient solution for setting up and managing Oracle databases for various purposes. However, there are scenarios where you need to interact with the Oracle DB Container using graphical user interfaces, such as Oracle Database Configuration Assistant (DBCA), Oracle Net Configuration Assistant (NETCA), and other X11-based tools.

In this quick tutorial, I will guide you through the process of running X11 applications from an Oracle DB Container, simplifying your database management tasks.

Quick Configuration Steps

In a rush? Ensure that the DISPLAY variable is correctly configured and allow ‘localhost’ to connect to your X11 server.

  • To allow ‘localhost’ to connect to the X11 Server on your Docker host, run the following command:
xhost localhost
  • Additionally, set the DISPLAY to host.docker.internal:0 in your container. This resolves to the internal IP address used by the host:
export DISPLAY=host.docker.internal:0

For a deeper understanding and essential security considerations, keep reading.

Requirements

You have made it this far! 😎 In this case, you want a little more context. First, let’s take a look at what the requirements are for MacOS. For Linux or Windows based systems these have to be adapted accordingly.

  1. Docker Desktop You need Docker installed and running on your MacOS. If you don’t have it, you can download and install it from the official Docker website.
  2. XQuartz as X11 Server: XQuartz is an open-source X Window System server for macOS. You can install it from XQuartz’s official website.
  3. Oracle DB Container: You should have an Oracle DB Container up and running on your MacOS. If you haven’t set up the container yet, you have a couple of options:

And finally, the whole thing also works with any other container where you want to start an X11 application.

Configuration

Once you have the prerequisites in place, follow these steps to configure your environment for running X11 apps from your Oracle DB Container.

Add localhost to xhost

Before we start, open a terminal and add localhost to the xhost access control list. This allows the Oracle DB Container to display graphical interfaces on your local XQuartz server.

xhost +localhost

Define the Environment Variable DISPLAY

You can define the DISPLAY environment variable in multiple ways: you can set it when running a docker exec command, include it in your docker-compose configuration, or configure it as an environment variable in an interactive session. The DISPLAY variable specifies the X11 server that the container should use for display.

Using docker exec:

Run your Oracle DB Container and specify the DISPLAY environment variable:

docker exec -it -e DISPLAY=host.docker.internal:0 <container_name> <command>

Replace <container_name> with the name of your Oracle DB Container and <command> with the X11 application you want to run.

Using docker-compose

If you use docker-compose to manage your containers, you can add the DISPLAY environment variable to your docker-compose.yml file. Here’s an example:

services:
  oracle-db-container:
    environment:
      - DISPLAY=host.docker.internal:0

Make sure to replace oracle-db-container with the actual name of your Oracle DB Container.

What I left out – Additional Considerations

My configuration is indeed simple and straightforward. However, there are a few key points to consider for a more comprehensive setup:

  • Network Configuration: The network configuration for my container is uncomplicated. It operates on the same network as the host system. If your setup requires the container to run on a separate network, you may need to use the --net=host flag to ensure it uses the host network. Be aware that changes to the xhost configuration may also be necessary in this scenario 
  • X11 Configuration: In my setup, no xauth or .Xauthority file is employed. However, in some cases, you may need to share the .Xauthority file through a bind volume between the container and host for proper X11 authentication.
  • Security Considerations: It’s important to acknowledge that enabling X11 access to your container also opens the door to potential security risks. The container can intercept access to the X server, including mouse and keyboard inputs, which may introduce security vulnerabilities such as key-logging. Therefore, it’s imperative to limit this approach to applications you trust to safeguard the security of your environment.

Conclusion

With these configurations in place, you’re now equipped to effortlessly run X11 applications from your Oracle DB or any other container. This capability empowers you to efficiently manage your Oracle databases with the convenience of graphical tools, further enhancing your lab and engineering environment on MacOS

Security Notice: Have I already mentioned it? It’s crucial to exercise caution when using this method. Only run applications you trust within the container because they gain access to X11, potentially allowing for activities like mouse and keyboard interception, such as key-logging. Therefore, limit this approach to trustworthy applications to ensure the security of your environment.

References

Some links related to this topic.