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.

Latest Critical Patch Updates from Oracle – October 2023

On October 17, Oracle released its quarterly Critical Patch Update Advisory. This comprehensive advisory contains details about 387 new security patches for various Oracle product families. Among them are some serious vulnerabilities that can be exploited remotely over the network, i.e. with a CVSS rating of 9 or more. The entire advisory can be found at CPU October 2023. In this blog, we will focus on the products that are relevant to my ongoing projects. Let’s take a closer look at them.

Oracle Database

For the Oracle database there are security patches for 10 vulnerabilities in the current update. Two of these vulnerabilities can be exploited remotely without authentication. None of the vulnerabilities affect the client-only installations, i.e. the security patches only affect the database server. The highest CVSS rating is 6.5, so this patch update is moderately rated. Nevertheless, it makes sense to patch the database environments promptly.

The essential database patches and release updates:

Fusion Middleware

Do I really need to mention Fusion Middleware? As always, there are relatively many and very critical security vulnerabilities. There are 46 in total, and 35 of these vulnerabilities can be exploited remotely without authentication. So better patch yesterday than tomorrow.

For me, the security updates for the Weblogic Server and Oracle Unified directory are particularly relevant in this context. The whole bouquet of patches can be found in the Oracle Support Document 2806740.2.

  • Oracle Unified Directory 12.2.1.4.0 Expected to be released on October 20. See Oracle Support Document 2640772.1
  • Oracle WebLogic Server 14.1.1.0 and 12.2.1.4 see Oracle Support Document 2806740.2

What Else?

As always, the list is very long. Despite all kinds of summaries, blog posts, reports, etc., you can’t avoid studying the Oracle Critical Patch Update Advisory and checking the patches for your specific products. Especially with products like Oracle Enterprise Manager, which combines several products, you have to be careful. You have to apply patch updates for Oracle Enterprise Manager Base Platform as well as for Weblogic Server, Repository Database etc.

Conclusion

Is it necessary to consider the Critical Patch Update and install the patches? In short, yes. As Miss Sophie used to say in Dinner for One, ‘Same procedure as every year, James.’

Cheerio, and happy patching!

The essential Links

  • Oracle Critical Patch Update Advisory – October 2023
  • Oracle Support Document 2962256.1 October 2023 Critical Patch Update – Executive Summary and Analysis
  • Oracle Support Document 2966413.1 Critical Patch Update (CPU) Program Oct 2023 Patch Availability Document (DB-only)
  • Oracle Support Document 2978467.2 Fusion Middleware Critical Patch Update (CPU) Program October 2023 Patch Availability Document (PAD)
  • Oracle Support Document 2806740.2 Critical Patch Update (CPU) Patch Advisor for Oracle Fusion Middleware – Updated for October 2023
  • Critical Patch Updates, Security Alerts and Bulletins
  • Use of Common Vulnerability Scoring System (CVSS) by Oracle
  • CVE ID’s by MITRE
  • Wikipedia Dinner for One “Same procedure as every year, James” Somehow a very popular catchphrase in Germany, Switherland,…

Simplified Keytab creation using Linux Tools #JoelKallmanDay

Today’s  #JoelKallmanDay, my topic is about simplifying the configuration of Kerberos authentication of Oracle databases using Linux tools. I have already written a few things about Kerberos in the past. The blog posts on this topic are usually tagged with Kerberos. Today I want to show you an alternative method how to create the keytab file directly on the database server without the help of a domain admin. This is especially helpful in larger environments or when troubleshooting. You have a new keytab file immediately, without having to first create an incident ticket and wait for support from your Windows administration colleagues. As a rule, they are not bored either and are glad to have one task less.

Some Basics

Kerberos is a networked authentication system that Oracle uses authenticate Oracle Database users. The following graphic shows the Kerberos authentication process at a glance. The complete configuration is described in detail in Oracle® Database Security Guide 23c – Configuring Kerberos Authentication or Oracle Support Document 1996329.1. Further links can be found in the Reference chapter. This blog post is only about an alternative creation of the keytab file.

The Kerberos authentication Process at a Glance

The keytab file plays a vital role in securely managing service keys, especially service principals, for the host’s various services, including those associated with Kerberos authentication for the Oracle database. This important file equips the Oracle database with the necessary information to interact with the Key Distribution Center (KDC) and perform user authentication.

Traditionally, the keytab file is generated on the KDC server. In the context of Windows Active Directory, the ktpass tool stands as the go-to utility for this task. Detailed guidance and examples for using ktpass can be found in official documentation.

For instance, here’s an example of how I create a keytab file using ktpass in my test lab. In this case, I’m associating the service principal with the user DB19. The resulting keytab file includes all available cryptographic keys for enhanced security.

ktpass.exe -princ oracle/db19.trivadislabs.com@TRIVADISLABS.COM
  -mapuser db19 -pass <PASSWORD> -crypto AL
  -ptype KRB5_NT_PRINCIPAL
  -out C:\stage\db19.trivadislabs.com.keytab

In a test lab you can do everything yourself. This is not possible in productive environments. You need admin access to the Active Directory or have a Windows admin at hand who can do it. Afterwards you have to copy the file partially over detours on the database server. The whole thing is cumbersome and error-prone.

Requirements

The following requirements must be met in order to use the Linux tools for configuration:

  • Tools Installing Kerberos client utilities on the database server. Whereas we don’t do Kerberos configuration for the operating system, we just use the tools. Kerberos is only used for database authentication in this example.
  • AD User Creating a service account in Active Directory and set the service principal name (SPN) for this account.
  • Configuration have the basic Kerberos configuration ready in your database environment e.g., $TNS_ADMIN/sqlnet.ora and $TNS_ADMIN/krb5.conf

Example of Tools Installation

Command line commands to install Kerberos client utilities on OEL8

sudo dnf install krb5-workstation

Command line commands to install Kerberos client utilities on OEL7

sudo yum -y install krb5-workstation

Example of AD User Creation

PowerShell commands to create service principal account with the flags set for This account supports Kerberos AES 128 bit encryption and This account supports Kerberos AES 256 bit encryption. This ensures that the keytab file can be created with the encryption type for AES respectively that an authentication then also works with such a keytab file. Additionaly we do set the SPN using setspn.

$Hostname = "db19"
$sPWD = ConvertTo-SecureString -AsPlainText "<PASSWORD>" -Force
$UsersDN  = "cn=Users," + (Get-ADDomain).DistinguishedName
$DNSRoot  = (Get-ADDomain).DNSRoot
$Domain   = (Get-ADDomainController).Domain.ToUpper()

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

Write-Host "INFO : Create service account for DB server $Hostname."
New-ADUser -SamAccountName $Hostname -Name $Hostname
  -DisplayName $Hostname
  -UserPrincipalName "oracle/$Hostname.$DNSRoot"
  -Description "Kerberos Service User for $Hostname"
  -Path $UsersDN -AccountPassword $sPWD
  -Enabled $true
  -KerberosEncryptionType "AES128, AES256"

Additionaly we do set the SPN using setspn.

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

Registering ServicePrincipalNames for CN=db19,CN=Users,DC=trivadislabs,DC=com
        oracle/db19.trivadislabs.com@TRIVADISLABS.COM
Updated object

Example Kerberos Configuration

Basic krb5.conf file in the $TNS_ADMIN folder. This example does configure ad.trivadislabs.com as KDC for the realm / domain TRIVADISLABS.COM.

# ----------------------------------------------------------------
# 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

Extract from sqlnet.ora in $TNS_ADMIN with the Kerberos configuration

# ----------------------------------------------------------------
# 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

Using the Kerberos Utilities

Lets use the different commandline utilities to create the keytab file on the database server as user oracle.

Step 1: Create a TGT for the service principal

To verify the service account and simplify the following steps we do get a ticket granting ticket (TGT) using okinit. Whereby okinit is an Oracle tool and does require the sqlnet.ora configuration mentioned before.

oracle@db19:~/ [rdbms19] okinit db19

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 11-OCT-2023 21:33:35

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

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

Step 2: Check the ticket cache

Verify the ticket cache using oklist

oracle@db19:~/ [rdbms19] oklist

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 11-OCT-2023 21:34:54

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

Configuration file : /u01/app/oracle/network/admin/krb5.conf.
Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: db19@TRIVADISLABS.COM

Valid starting     Expires            Service principal
10/11/23 21:33:39  10/12/23 07:33:39  krbtgt/TRIVADISLABS.COM@TRIVADISLABS.COM
	renew until 10/12/23 21:33:35

Step 3: Get the kvno for the Service Principle

We need the key version number (kvno) of the service principle. this can be queried using the kvno utility. Verify above which ticket cache is used. Optionally specify the ticket cache explicitly using -c. The kvno will be used when creating the keytab file.

oracle@db19:~/ [rdbms19] kvno -c /tmp/krb5cc_1000 db19@TRIVADISLABS.COM 
db19@TRIVADISLABS.COM: kvno = 2

Step 4: Create a keytab file using ktutil

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@db19:~/ [rdbms19] mv $TNS_ADMIN/krb5.keytab $TNS_ADMIN/krb5.keytab.orig
oracle@db19:~/ [rdbms19] ktutil
ktutil:  addent -password -p oracle/db19.trivadislabs.com@TRIVADISLABS.COM -k 2 -e aes256-cts-hmac-sha1-96
Password for oracle/db19.trivadislabs.com@TRIVADISLABS.COM:
ktutil:  list -e
slot KVNO Principal
---- ---- ---------------------------------------------------------------------
   1    2 oracle/db19.trivadislabs.com@TRIVADISLABS.COM (aes256-cts-hmac-sha1-96)
ktutil:  wkt /u01/app/oracle/network/admin/krb5.keytab
ktutil:  q

Step 5: Verify the new keytab File

Verify the new keytab file using oklist

oracle@db19:~/ [rdbms19] oklist -e -k

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 11-OCT-2023 22:41:00

Copyright (c) 1996, 2019 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/db19.trivadislabs.com@TRIVADISLABS.COM (AES-256 CTS mode with 96-bit SHA-1 HMAC)

Conculsion

Especially in large environments, where you sometimes have to wait several days for a service ticket to be processed, the alternative method for creating a keytab file is a relief. The keytab file is immediately where you need it. No need for cumbersome copying via SSH, fileshare, tunnels etc. You need another or an additional encryption type in the keytab file? Nothing simpler than that. One call of ktutil and addent and you have an additional entcryption type in the keytab file.

Have fun configuring Kerberos

References

Some links related to this topic.

  • Oracle® Database Security Guide 23c – Configuring Kerberos Authentication
  • Oracle Support Document 1375853.1 Primary Note For Kerberos Authentication
  • Oracle Support Document 1996329.1 How To Configure Kerberos Authentication In A 12c Database
  • Oracle Support Document 1304004.1 Configuring Kerberos Authentication with a Microsoft Windows Active Directory KDC
  • Oracle Support Document 132804.1 Enabling Kerberos Authentication
  • Oracle Support Document 185897.1 Kerberos Troubleshooting Guide
  • Oracle Support Document 1523651.1 Kerberos Authentication With Oracle JDBC Thin Driver And Microsoft Active Directory
  • Oracle Support Document 1609359.1 How To Use Kerberos Authentication to connect to a database with SQL Developer with thin JDBC
  • Oracle Support Document 294136.1 Kerberos: High Level Introduction and Flow
  • Microsoft Windows Server Documentation ktpass
  • Linux Man Pages ktutil
  • Linux Man Pages kvno
  • IANA Kerberos Encryption Type Numbers
  • OraDBA Kerberos related blog posts see Kerberos