By Simon Pane | Edited by April Sims

Background

From Oracle OpenWorld 2017 and subsequent Oracle announcements, it’s clear that Oracle currently has two key focus areas or messages:

1. The Autonomous Cloud Database starting with the Autonomous Cloud Data Warehouse
2. Security

There is plenty of attention on the first item as it is new and revolutionary in many ways. But the second area also deserves emphasis, as it is ubiquitous to databases.

Whether you’re an Oracle system/database administrator, an IT manager/executive or even just an end user/customer, security should be a key focus. Security breaches have happened at all levels within the industry. From the executive level to the end customer whose data is leaked, none of us want to be associated with a breach.

While you may or may not be considering a cloud migration or implementation of the Autonomous Cloud Data Warehouse via a Proof of Concept (PoC) or even a flushed-out roadmap, that activity is likely future looking or at best a work in progress. Security concerns are immediate, as well as future-facing.

So are there action items we can take right now? The answer is definitely yes!

What Did Oracle Announce?

Oracle has announced several facets to improving one’s security posture. First of all, the new Autonomous Cloud services, such as the Autonomous Cloud Data Warehouse, will include automatic patching and Machine Learning for intrusion detection and anomaly pattern detection.

But again, what about the rest of us — what can we do with our existing systems right now? Regardless of whether our systems are old, new, on-premises or in the cloud? The answer is the Oracle “Database Security Assessment Tool,” or DBSTAT.

What Exactly is DBSAT?

First of all, DBSAT isn’t new. It actually first came out somewhat quietly a couple of years ago in 2016 and has been downloadable from My Oracle Support (MOS) (Doc ID 2138254.1) ever since then. But DBSAT did get heavy promotion from Oracle at Oracle Open World 17 in keynotes and lecture sessions with a follow-up release of a major new version: release 2.0.1 in December 2017. This new version offers some key advantages.

DBSAT is designed to be a small lightweight command line tool, run by Oracle DBAs. And its focus is on the database tier: the database and related server settings. The download is a tiny 2MB zip file and installation is as simple as unzipping.

It’s provided from My Oracle Support and is not a separately licensed product. It supports most of the major operating systems, including Windows and Oracle Databases from version 10.2.0.5 onwards.

DBSAT has two major phases: data collection (using the collector option) and data analysis/reporting (using the reporter option). The data collection phase captures configuration data from the database and operating system for future analysis. Consequently, the data collection must be run on the database server from a privileged user account. Typically run by the DBA using the Oracle software owner account (oracle on Linux systems depending on your environment).

The data analysis and reporting can be done either on the same computer (i.e. the database server) or another. If there are security concerns regarding disclosure of the findings, or concerns about the very small amount of overhead used to process the collected data, then the analysis and reporting can be moved to any other machine with the utility installed.

Once the collected data is processed, DBSAT produces output in four formats:

• HTML for interactive reviews.
• TEXT in case data needs to be cut and pasted into other formats — not recommended to email sensitive security findings.
• XLS as that might be easiest to work with if adding check-lists of who will take action on specific findings, or extending the info or data in the report in some other way.
• JSON for ingestion into other tools.

The last option is new with version 2.0.1 and is a very valuable addition. If you have your own custom tool for capturing and reporting on such findings, JSON output is almost essential for programmatic loading of such data.

Consequently, the overall process can be illustrated as:

Where Can We Get the Quickest Benefit?

Ideally, we’re using security tools and performing security reviews proactively to constantly improve our security posture. Realistically, that’s not always the case. The reality is that sometimes security is reactive.

For example, what if we get a Monday morning surprise such as:

“Management wants a security report on their desk by end-of-day today.”

“We suspect there may be some internal bad actors — everything must be locked down as much as possible.”

“Application X now falls under new regulatory requirements that we must be in alignment with ASAP.”

“Auditors are coming this week and nobody told us.”

Again, let’s emphasize that security shouldn’t be reactive and done only to appease auditors, management and/or external regulations. It should be a design criteria and constantly measured to ensure that implementations match technical specifications, corporate policies, industry best practices, and required regulations.

Regardless, DBSAT can help with all of the above and can help quickly.

Running the collector and reporter can be really as simple as:

./dbsat collect -n ‘/ as sysdba’ ${ORACLE_SID} && ./dbsat report -a -n ${ORACLE_SID}

In most circumstances, it should only take a matter of minutes to complete. That’s it!

Of course, there are additional options and more complex scenarios. Including the option to use a less privileged account to connect to the database which is recommended. The above command just illustrates the most basic execution. Refer to the online documentation for full usage details.

After running the reporter and collector with a command similar to the one shown above or a more complex one with additional options, the findings report is ready to be reviewed.

Keep in mind that the reports should be considered sensitive as they likely expose weaknesses or deficiencies that a bad actor could capitalize on. For this reason, the tool includes the option to encrypt the collected data and reports though it just uses standard ZIP encryption. If more robust encryption is required, then you’ll need to encrypt separately.

The first few sections of the report are applicable to a wide variety of audiences. It starts with a nice tabular summary of the findings:

As can be seen, the summary section gives a breakdown of problematic areas with a risk ranking. The sections items are links to more detailed findings.

Next, it shows a convenient summary of what security-related features and components are being used:

Beyond these sections, it gets into the details of findings, and the audience of the subsequent section are DBAs or Developers who understand the Oracle database technologies in detail.

These technical detail sections also have some valuable intelligence behind them. To explain what I mean by that, let’s explore a common situation. If the scenario is that the executive or DBA manager wants a report of all users with the DBA role ASAP — sounds simple enough, but extracting an accurate summary of the DBA role privileged users can be harder for the Oracle DBA to obtain than the non-Oracle DBA might think.

The reason why is that in the Oracle database role, grants can be nested. In a test database, I created a new role called ROLE1 and assigned the DBA role to it. Then another called ROLE2 and granted ROLE1 to that. I repeated with ROLE3 and ROLE4 and eventually ROLE4 was granted to the Oracle sample user SCOTT. So effectively the SCOTT user has been granted the DBA role but it’s nested five levels down through four other roles.

Unwinding a grant structure like this isn’t trivial. Mapping every role grant out manually isn’t practical, timely, or repeatable. So to get the information, the DBA needs to use a more complex hierarchical query using a CONNECT BY clause. Unless they’ve already prepared that query beforehand or are experts at understanding and writing Oracle hierarchical queries, they are going to have a hard time reporting on the true DBA role grantees and providing the requested report ASAP.

Fortunately, the DBSAT report has the intelligence to provide accurate information, even in these more complex configurations:

As well as unraveling more complex situations like that, the report also has some interesting “why didn’t I think of that” sort of findings. For example:

That’s an interesting finding and maybe something we should take action on. Likely this approach can help clean-up older/inactive user accounts to minimize the attack surface and overall risk.

The above are just two examples of areas of value. The remainder of the report has many dozen similar findings related to the database, listener and Operating System.

Including where needed, there are appropriate tags indicating whether the finding relates to CIS (Center for Internet Security) recommendations, or can be of assistance with European Union General Data Protection Regulation (GDPR) compliance verification.

The New Discoverer Module

A brand new Discoverer module was introduced with DBSAT 2.0.1. Oracle could have created this as a brand new stand-alone tool but instead integrated with DBSAT. This is a good thing, as Oracle Support already provides customers with many independent tools for various purposes.

The new Discoverer module can be used to search the data dictionary for sensitive data. This may be necessary if required to identify, for example, Personally Identifiable Information (PII) and Sensitive Personally Identifiable Information (SPII). Of course, this is reverse engineering. In an ideal scenario, we’d have proper data architects and modelers involved, and follow patterns of stringent Data Governance. In that case, we’d already know where the sensitive data is. But in lieu of strict data governance, DBSAT can help.

The Discoverer module is based on a configuration file which is used to essentially define “what are we looking for.” Basically patterns in table and column names. A simple example is salary data via tables or columns with “SAL,” “SALARY,” “SALGRADE” — or similar items.

The search flexibility is actually quite impressive for the first generation/release of this module, and the results can be categorized. Oracle provides an impressive configuration file to get you started, and of course, it’s fully customizable and extensible.

The downside is that it’s only going to be as good as your physical data model implementation. If when going from a logical data model, an easy to understand attribute name such as salary becomes completely obfuscated into a physical column name such as FBN009, which seems completely unrelated and obscure, then you’re never going to find it. The tool is only looking at database metadata — not actual table row data. Regardless, this is a promising start for a new component that shows a lot of potential value for most database implementations.

Other Features

In addition, Oracle provides Companion Utilities available for download from the same MOS document.

The first companion utility feature is the ability to exact only certain portions of an assessment report. For example, if you’re only really concerned with the Users with the DBA role section, then you can extract just that. The JSON output file is what is processed to extract the specified section or sections.

The second companion utility feature is a diff utility for comparing two reports. This is similar to Linux diff output but reports on differences section-by-section vs line-by-line as the Linux would do. A diff report may be valuable for measuring improvement between security hardening iterations.

These additional features are a basic start. If you need to perform more complex custom analyses, then ingestion of the JSON output into your own reporting system and your own reporting on that data will be necessary.

Those familiar with the Oracle Health Check Utility (ORAchk) may be aware that it comes with a repository database and APEX front end application (called the Collection Manager) for storing, reporting on, and comparing results and findings. Unfortunately, Oracle is not providing anything similar for DBSAT output — at least not yet. Currently, a JSON file (and the above-mentioned companion utilities) is all you get for this purpose at this time. And as mentioned, even that’s new. However, I would hope that the integration with the same Collection Manager repository or a new and similar one is on the road-map.

Conclusion

Security should be on everyone’s mind and at the forefront of their activities. If it’s not already, it should be. And assessing security is an iterative process meaning assess, review, adjust, and repeat.

Oracle’s renewed focus on security and the updated DBSAT is a valuable tool to help us all in this area. Regardless of whether we have an old Oracle 10g database running on-premises or a brand new Oracle 18c cloud instance, DBSAT is a valuable asset. It’s reached a level of maturity where it doesn’t do absolutely everything most anyone could possibly want, but it definitely does provide enough functionality and value to make it a must-have for supporting any Oracle database.

About the Author

Simon Pane is a Principal Consultant working for Pythian and an IOUG Board Member. Simon is passionate about technology and has been working with Oracle products for over 20 years. As an Oracle ACE, he is a regular writer on the Oracle blog, an editor for IOUG SELECT Journal, and speaks many times per year at most of the major Oracle conferences throughout North America and Europe including COLLABORATE and Oracle OpenWorld.