Fundamental Security Part Five – Observability

July 2023 – Oracle 19C

Now you know what permissions have been granted in your system and to whom, as shown in the previous post. Maybe you have remediated them down to least privilege. Now, how do you ensure the system stays like that? How do you know if you have succeeded in stripping the permissions and users back successfully?

We are going to need to monitor the system and see if anything changes.

Options

You are going to need a consistent and repeatable report and/or dashboard to show you the current state of your estate. There are a number of options, each with pros and cons. Lets talk about DBSAT, Oracle Data Safe, and briefly; Splunk, and doing it yourself.

DBSAT

A good starting point is to use DBSAT. This is a free, semi-supported tool supplied by Oracle which you can run against each database [note: for multitenant, you need to do this against the CDB and every PDB individually] to get a report showing where you have/failed to comply with CIS or STIG rules. The home page is here: https://www.oracle.com/uk/database/technologies/security/dbsat.html [MOS: 2138254.1]

DBSAT can be used in a number of different ways. The simple way is to generate the output from each database and then convert it into a HTML document which you can analyse. It will report pass and failure states, plus indicate where you need to perform more analysis on items as there is no clear “correct” way to implement some elements of security. For example, DBMS_SQL is regarded as a potentially insecure package as badly coded input can allow SQL Injection attacks. As a result, this will be flagged for analysis rather than be an outright failure.

If you have a database called UTF8, with a PDB called UTF8PDB1, you will need to run the collect twice. Once for the CDB and once for each PDB. I am using “/ as sysdba” but you may want to have a dedicated account if you are doing this regularly. I am using “-n” so no password is required for the zip. Given the extremely sensitive nature of the output, which would be very useful to hackers, you may want to ensure the output is password protected.

For the CDB (called UTF8):
$ export ORACLE_SID=UTF8
$ unset ORACLE_PDB_SID
$ ./dbsat collect -n “/ as sysdba” UTF8
this produces a file “UTF8.json”

$ ./dbsat report -a -n UTF8
this produces output based upon the “UTF8.json file”

For the PDB (called UTF8PDB1):
$ export ORACLE_SID=UTF8
$ export ORACLE_PDB_SID=UTF8PDB1
$ ./dbsat collect -n “/ as sysdba” UTF8PDB1
$ ./dbsat report -a -n UTF8PDB1

Typical output

$ ls -1 UTF8PDB1*
UTF8PDB1.json
UTF8PDB1_report.html
UTF8PDB1_report.json
UTF8PDB1_report.txt
UTF8PDB1_report.xlsx

CDB Output Header (HTML version) – note the Container identifying this as a CDB.

PDB Output header (txt version). Note the Container showing the PDB name and ID.

We can see that there’s a high risk on the User Accounts. Looking at that section of the report tells us the problem

We can also see a number with the status of Advisory and Evaluate, which need to be manually read, understood and (maybe) resolved. Changing the below Advisory without due diligence may cause your application to stop functioning as it may be using an older client. However, it indicates you need to upgrade the client and secure the database further:

There are two of DBSAT Companion utilities which are OK, but could be more useful:

dbsat_diff, which shows the difference between 2 report files. This should be useful for monitoring change, but as it shows the different in run times and ages it kind-of falls down a bit. Here there’s no fundamental change to CRYPT.TDE, but it’s still reported.

$ ./dbsat_diff UTF8PDB1-run1_report.json UTF8PDB1-run2_report.json

[this bit is useful showing that the SCOTT user has been fixed]
USER.DEFPWD: Users with Default Passwords
< Status: High Risk
< Summary:
<     Found 1 unlocked user account with default password.
< Details:
<     Users with default password: SCOTT
---
> Status: Pass
> Summary:
>     No unlocked user accounts are using default password.


[this is less useful - as nothing has changed except the run date...]
CRYPT.TDE: Transparent Data Encryption
< Status: Evaluate
< Summary:
<     Found 2 encrypted tablespaces. Found 7 unencrypted tablespaces. Found 1
<         encrypted column. Examined 1 initialization parameter.
< Details:
<     Unencrypted tablespaces: DATA, GONE, INDEXES, SYSAUX, SYSTEM, TEMP,
<         UNDOTBS1
<     Encrypted tablespaces: ENC_TS (AES256), USERS (AES256)
<
<     Encrypted columns: Column C2 of NEIL.T_ENC (AES 192 bits key)
<
<     ENCRYPT_NEW_TABLESPACES = CLOUD_ONLY. Recommended value is ALWAYS for
<         databases running on-premises.
<     It has been 471 days since master encryption key was last rotated.
---
> Status: Evaluate
> Summary:
>     Found 2 encrypted tablespaces. Found 7 unencrypted tablespaces. Found 1
>         encrypted column. Examined 1 initialization parameter.
> Details:
>     Unencrypted tablespaces: DATA, GONE, INDEXES, SYSAUX, SYSTEM, TEMP,
>         UNDOTBS1
>     Encrypted tablespaces: ENC_TS (AES256), USERS (AES256)
>
>     Encrypted columns: Column C2 of NEIL.T_ENC (AES 192 bits key)
>
>     ENCRYPT_NEW_TABLESPACES = CLOUD_ONLY. Recommended value is ALWAYS for
>         databases running on-premises.
>     It has been 472 days since master encryption key was last rotated.

dbsat_extract, which is helpful looking for specific sections and pulling only specified output.

Extracting 2 sections. Adding -v would give the Details as well as the summary.

$ ./dbsat_extract -i CRYPT.TDE -i USER.DEFPWD UTF8PDB1-2_report.json
=== UTF8PDB1-2_report.json: UTF8 UTF8PDB1 (PDB:3) Wed May 10 2023 19:20:49 UTC+01:00

CRYPT.TDE: Transparent Data Encryption
| Status: Evaluate
| Summary:
|     Found 2 encrypted tablespaces. Found 7 unencrypted tablespaces. Found 1
|         encrypted column. Examined 1 initialization parameter.

USER.DEFPWD: Users with Default Passwords
| Status: Pass
| Summary:
|     No unlocked user accounts are using default password.

DATA SAFE

Oracle offer a (relatively cheap) cloud product to purchase called Data Safe. It is basically the same as DBSAT but has an automated dashboard which will allow you so see exactly how you are performing against CIS/STIG benchmarks with minimal internal effort. It doesn’t fix anything, but is a easier implementation route. Note that it is a cloud product, so if you intend to use it monitor on-premises databases you will need to provide a route from Oracle Cloud Infrastructure to your database to allow it to be monitored

SPLUNK

Another alternative would be to use something like Splunk/Grafana, but feed it the JSON output from the DBSAT collection to allow you to have your own dashboard/graphing showing risk and advisory without having to live with the extended output from DBSAT.

DIY

If you are a bit old-school and very much like creating this sort of thing yourself, its just a matter of understanding the SQL’s needed to run to collect the information in whatever format you like. The SQL code ran by DBSAT is held within a file called “sat_collector.sql”, which is a very good place to start. There is also SQL within the CIS Benchmarks, which can help. There are advantages to this route, as it will also allow application and company specific information to be gathered (e.g. if you are extracting audit records regularly, ensure that the audit extraction account is actually connecting and extracting them!) However, you will need to maintain the code yourself, as security standards change and databases are upgraded.

OK, so much for monitoring and identifying issues, what about fixing a few of them. Lets start with: Fundamental Security Part Six – Network Encryption

Other Considerations

However, I suspect your system is monitoring more than just Oracle. There is a reasonable chance that you will be monitoring other databases systems, such as Postgres, SQL Server, MySQL, MongoDB and a whole host more. You need to consider the operating system for most database implementations too. You need to decide what the appropriate monitoring solution is for such a configuration, as the solutions above are Oracle-centric. Now, the best way to monitor Oracle overall is using Oracle Enterprise Manager. It has far more rules and knowledge about Oracle than any other monitoring solution, but does not present a security dashboard. There is a reasonable chance that you will want a “single pane of glass” solution to present to management detailing the overall security configuration across your entire estate. As such, a tailored solution using Splunk and Grafana might be appropriate. You may also want to consider 3rd party solutions such as DataDog or dedicated security software like Tenable Nessus. All 3rd party software will present a challenge, having to deal with the ingestion of disparate data formats and coalescing it into a single dashboard. It can be challenging but knowing when configuration changes and vulnerabilities appear is critical to helping secure your infrastructure.