Running new Oracle DBSAT version 2.1.0 against Autonomous Database

Posted in: Cloud, Oracle, Technical Track

Recently Oracle released the newest version of the “Database Security Assessment Tool” (DBSAT 2.1.0). This version is certified to run against Oracle Cloud DBCS (PaaS) and Autonomous Databases. Also, the Discoverer option can now find sensitive and personal data in several languages (not only English as before), which is very helpful for GDPR compliance and similar data protection laws.

List of new features of DBSAT version 2.1.0:

– Certified for Oracle Database 18c, 19c and autonomous databases.
– New references to STIG Rules in reporter findings.
– The users with directly granted system privileges are now marked with (D).
– The system privileges grants to public now reported as high risk.
– For multitenant: PDB runs show only Roles and Privileges that can be acted upon for quicker remediation.
– More enhanced checks for Audit Configuration and Audit Trail Management.
– Updated remarks and recommendations.
– Added “CONF.PWDFILE” rule to report on REMOTE_LOGIN_PASSWORDFILE.
– Added “CONF.INSTNM” to report usage of database version on instance name.
– Added “CONF.BKUP” to report backup records in the last 90 days.
– Added “PRIV.AUDOBJ” to report users with privileges that can directly access and modify objects with audit data.
– Added “PRIV.AUDMGMT” to report users with execute privileges on DBMS_AUDIT_MGMT
– The Discoverer now supports Java 9 and 10.
– The Discoverer now can find sensitive data in data models in more languages: Dutch, French, German, Italian, Portuguese, and Spanish.
– The Discoverer has added/updated sensitive types, categories and subcategories.
– The Discoverer has added recommended controls per risk level category.

 

How to run DBSAT against Oracle Autonomous Database:

In my test, I have used Oracle Autonomous Database version 18.4.0.0.0. Although it is possible to run the older DBSAT 2.0.2 against this configuration, this latest version 2.1.0 is the only one certified.

It is worth noting that with a normal, on-premises database, the DBSAT Collector and Reporter are most often run on the database server itself.  However, with the Autonomous Database, we don’t have access to the host server and must instead run DBSAT in a client-server configuration.

Below shows my step-by-step testing which may be helpful. Note that this scenario was using Oracle Autonomous Database in the cloud, so I had to install an Instant Client and download the Autonomous Database wallet file from Oracle Call Interface (OCI) which includes the necessary certificate and network files required to access the DB from the client (ie from sqlplus or SQL Developer):

 

1) Download the latest version and install it (license-free for all Oracle clients):

Oracle Database Security Assessment Tool (DBSAT) (Doc ID 2138254.1)

Users Guide Document:
https://docs.oracle.com/en/database/oracle/security-assessment-tool/2.1/satug/#SATUG-GUID-C7E917BB-EDAC-4123-900A-D4F2E561BFE9

Note that previously I checked my Python and Java version as per the user guide:

Roys_Mac:DBSAT_202 myOSuser$ python -V
Python 2.7.10
Roys_Mac:DBSAT_202 myOSuser$
Roys_Mac:DBSAT_202 myOSuser$ java -fullversion
java full version "1.8.0_201-b09"
Roys_Mac:DBSAT_202 myOSuser$

Also note that I’m running DBSAT using the ADB  “admin” user, which is the main administrative user for Autonomous database (instead of sys or system).

 

2) Running The Collector:

The first attempt to run DBSAT may fail because the “dbsat.sh” (or “dbsat.bat” if on Windows) main script would be looking for ZIP, UNZIP and SQLPLUS programs on certain paths under the Database Oracle Home, which won’t match with your Instant Client paths, hence there is a need to open the “dbsat.sh” main script and edit a few lines. In my case, I changed the following:

# Default location of zip and unzip. May be modified if necessary.
ZIP=/usr/bin/zip
UNZIP=/usr/bin/unzip
#DBZIP=${ORACLE_HOME}/bin/zip
DBZIP=/usr/bin/zip

# connect to database and run the collector script.
#"${ORACLE_HOME}/bin/sqlplus" -L $CONN_STR @"$CMDDIR/$SAT_SCRIPT" $TEMP_OUTPUT_FILE
"${ORACLE_HOME}/sqlplus" -L $CONN_STR @"$CMDDIR/$SAT_SCRIPT" $TEMP_OUTPUT_FILE

After the above changes, you must export the Oracle_Home to your client home :

export ORACLE_HOME=/MacOS/Oracle/instantclient_18_1

And then run the DBSAT Collector:

/MacOS/Oracle/DBSAT_210/dbsat collect ADMIN@automdb1_tp dbsat210_Autonomous18c

Roys_Mac:instantclient_18_1 myOSuser$ /MacOS/Oracle/DBSAT_210/dbsat collect ADMIN@automdb1_tp dbsat210_Autonomous18c
Database Security Assessment Tool version 2.1 (March 2019)
This tool is intended to assist in you in securing your Oracle database
system. You are solely responsible for your system and the effect and
results of the execution of this tool (including, without limitation,
any damage or data loss). Further, the output generated by this tool may
include potentially sensitive system configuration data and information
that could be used by a skilled attacker to penetrate your system. You
are solely responsible for ensuring that the output of this tool,
including any generated reports, is handled in accordance with your
company's policies.
Connecting to the target Oracle database...
SQL*Plus: Release 18.0.0.0.0 Production on Sun Mar 17 22:00:16 2019
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Enter password:
Last Successful login time: Sun Mar 17 2019 21:35:47 -06:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Setup complete.
SQL queries complete.
OS Commands Skipped.
BEGIN
*
ERROR at line 1:
ORA-20002: Complete without OS Commands.
ORA-06512: at line 4
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
DBSAT Collector completed successfully.


Calling /usr/bin/zip to encrypt dbsat210_Autonomous18c.json...

Enter password:
Verify password:
adding: dbsat210_Autonomous18c.json (deflated 90%)
zip completed successfully.
Roys_Mac:instantclient_18_1 myOSuser$

From the above output, note error “ORA-20002: Complete without OS Commands” is expected, because the execution of the Collector was client/server instead of on the underlying DB server.

 

3) Running The Reporter:

Then execute the Reporter component on the local/client machine:

/MacOS/Oracle/DBSAT_210/dbsat report -a ./dbsat210_Autonomous18c

Roys_Mac:instantclient_18_1 myOSuser$ /MacOS/Oracle/DBSAT_210/dbsat report -a ./dbsat210_Autonomous18c
Database Security Assessment Tool version 2.1 (March 2019)
This tool is intended to assist in you in securing your Oracle database
system. You are solely responsible for your system and the effect and
results of the execution of this tool (including, without limitation,
any damage or data loss). Further, the output generated by this tool may
include potentially sensitive system configuration data and information
that could be used by a skilled attacker to penetrate your system. You
are solely responsible for ensuring that the output of this tool,
including any generated reports, is handled in accordance with your
company's policies.
Archive: dbsat210_Autonomous18c.zip
[dbsat210_Autonomous18c.zip] dbsat210_Autonomous18c.json password:
inflating: dbsat210_Autonomous18c.json
DBSAT Reporter ran successfully.
Calling /usr/bin/zip to encrypt the generated reports...
Enter password:
Verify password:
zip warning: dbsat210_Autonomous18c_report.zip not found or empty
adding: dbsat210_Autonomous18c_report.txt (deflated 78%)
adding: dbsat210_Autonomous18c_report.html (deflated 84%)
adding: dbsat210_Autonomous18c_report.xlsx (deflated 3%)
adding: dbsat210_Autonomous18c_report.json (deflated 83%)
zip completed successfully.
Roys_Mac:instantclient_18_1 myOSuser$

Note: the four files created, in particular, the html report, is easier to read, especially the HTML output:  dbsat210_Autonomous18c_report.html ” for example.

 

4) Running The Discoverer:

In my test, I had some problems with my Java environment, so I had to modify the “dbsat.sh” script to make it work (this shouldn’t be needed, but it’s worth showing in case you have similar issues). The following lines changed were required simply to force the discoverer to continue although the failed validations:

if [ -z "${JAVA_HOME}" ]; then
echo Error: Environment variable JAVA_HOME is NOT set.
#exit 1;
fi
_java=$JAVA_HOME/bin/java
if [ ! -e "$_java" ]; then
echo Error: Environment variable JAVA_HOME is NOT set to a valid JAVA installation.
#exit 1
fi
#$JAVA_HOME/bin/java $JAVA_OPTS -cp $CLASSPATH sensDataDisc $CONF_PATH $OUTPUT_FILE_LOC $LOG_LEVEL
java $JAVA_OPTS -cp $CLASSPATH sensDataDisc $CONF_PATH $OUTPUT_FILE_LOC $LOG_LEVEL

Then I ran the Discoverer. Note that the errors are still displayed, but skipped over because the code was commented out.

/MacOS/Oracle/DBSAT_210/dbsat discover -c /MacOS/Oracle/DBSAT_210/Discover/conf/autonomous_dbsat.config Autonomous_DBSAT210_Discoverer

Roys_Mac:instantclient_18_1 myOSuser$ /MacOS/Oracle/DBSAT_210/dbsat discover -c /MacOS/Oracle/DBSAT_210/Discover/conf/autonomous_dbsat.config Autonomous_DBSAT210_Discoverer
Database Security Assessment Tool version 2.1 (March 2019)
This tool is intended to assist in you in securing your Oracle database
system. You are solely responsible for your system and the effect and
results of the execution of this tool (including, without limitation,
any damage or data loss). Further, the output generated by this tool may
include potentially sensitive system configuration data and information
that could be used by a skilled attacker to penetrate your system. You
are solely responsible for ensuring that the output of this tool,
including any generated reports, is handled in accordance with your
company's policies.
Error: Environment variable JAVA_HOME is NOT set.
Error: Environment variable JAVA_HOME is NOT set to a valid JAVA installation.
Enter username: admin
Enter password:
Enter TrustStore password:
Enter KeyStore password:
DBSAT Discover ran successfully.
Calling /usr/bin/zip to encrypt the generated reports...
Enter password:
Verify password:
zip warning: Autonomous_DBSAT210_Discoverer_report.zip not found or empty
adding: Autonomous_DBSAT210_Discoverer_discover.html (deflated 72%)
adding: Autonomous_DBSAT210_Discoverer_discover.csv (deflated 30%)
Zip completed successfully.
Roys_Mac:instantclient_18_1 myOSuser$

And perhaps the most important part of all this testing, the configuration of the .config file (ie: dbsat_home/Discover/conf/sample_dbsat.config), is more complex than for on-premises databases due to connectivity requirements from your local machine to the Autonomous in the Oracle Cloud.

See the following summary of my configuration file (truncated to show only relevant changed lines), the .jks files, SSL_DN values, and others you will need to take from your Oracle Cloud Console:

Roys_Mac:conf myOSuser$ cat autonomous_dbsat.config
#Configuration File for DBSAT Discoverer Tool
# Copyright (c) 2017, 2019, Oracle and/or its affiliates. All rights reserved.
###########################################################
#Database Section: Allows the user to provide DB server details
[Database] #DB_IP is the IP address or FQDN for the DB Server
DB_HOSTNAME = adb.us-ashburn-1.oraclecloud.com
#DB_PORT is the port at which the DBSAT tool needs to connect to
DB_PORT = 1522
#DB_SERVICE_NAME is the service Name for the DB
DB_SERVICE_NAME = zxk8lzlxu6frazs_automdb1_tp.atp.oraclecloud.com
###########################################################
##SSL Parameters : Enables and Defines the SSL configuration for Database Server
SSL_ENABLED = TRUE
# If SSL_ENABLED = TRUE, then SSL_TRUSTSTORE is mandatory.
SSL_TRUSTSTORE = /MacOS/Oracle/instantclient_18_1/network/admin/truststore.jks
SSL_TRUSTSTORE_TYPE = JKS
SSL_KEYSTORE = /MacOS/Oracle/instantclient_18_1/network/admin/keystore.jks
SSL_KEYSTORE_TYPE = JKS
SSL_DN = "CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US"
Roys_Mac:conf myOSuser$

 

References:

https://blogs.oracle.com/cloudsecurity/database-security-assessment-tool-dbsat-21-is-now-available

https://www.oracle.com/a/tech/docs/dbsec/dbsat/dbsat-public-faq-mar2019.pdf

https://www.oracle.com/database/autonomous-database.html

https://cloud.oracle.com/home

https://blog.pythian.com/oracle-dbsat-discoverer-feature/

https://blog.pythian.com/oracles-database-security-assessment-tool-dbsat-version-2-2-0-1/

email

Interested in working with Roy? Schedule a tech call.

2 Comments. Leave new

Marcos Siqueira
April 23, 2019 8:12 am

Very good document, thanks for share it

Reply
Roy Salazar
May 15, 2019 10:22 pm

Very glad to help, thanks for the feedback

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *