How to Run DBSAT 2.2.0 on Oracle Cloud PDB using Wallet

The Oracle Database Security Assessment Tool (DBSAT) is a command-line tool focused on identifying how securely the database is configured and where sensitive data resides with the goal of promoting successful approaches to mitigating potential security risks. DBSAT requires no special installation, is easy to use, and produces clear, understandable reports. Furthermore, it’s provided to licensed Oracle customers by Oracle support (and is downloadable from My Oracle Support) for no additional charge. DBSAT has three components: Collector, Reporter, and Discoverer.

The scope of this blog post is to show how to run the new version 2.2.0 of DBSAT using an Oracle Wallet so that no database user password is needed during its execution. We’ll be using an Oracle 19c Cloud Database Instance (OCI). Also, the test will be at PDB level.

What’s new in DBSAT version 2.2.0 ?

This latest version was released in September 2019, following the previous version 2.1 which was released in March 2019. DBSAT is now certified for the latest Oracle database versions 18c and 19c, for both on-premises and cloud databases, including DBCS (PaaS) and the Oracle Autonomous Database.

The general list of new features in this release includes:

• Analysis of common and local privileges and roles granted in a pluggable database.
• Added Code Based Access Control (CBAC) roles and privileges granted to stored procedures, functions, and packages.
• Added roles with enabled unified audit policies.
• Improved remarks for all findings.

And the new features for the Discoverer component include:

• Discoverer: Retrieval of database username/password from Secure External Password Store (SEPS). For a further explanation of using SEPs (generally and outside of the DBSAT utility) see: https://blog.pythian.com/using-oracle-wallets-seps-perl-jdbc-odbc/
• Discoverer: Support for TNS_ADMIN and TNS aliases.
• Discoverer: Sample pattern file for Greek language.

1) OCI Database Instance creation

A brief reference: when creating a database within the OCI web console, you have three options:

1. A VM Server (IaaS) choosing a compute shape with only an OS included: a VM server can be created automatically from the OCI dashboard menu:
   “Core Infrastructure => Compute -> Instances”
   Once the VM is created, you could manually install the desired database version, then access it via ssh from your computer using RSA keys.
2. Autonomous DB: from the OCI Dashboard menus:
   “Database => Autonomous Data Warehouse”
   “Database => Autonomous Transaction Processing”
   There, you create either ADW or ATP Autonomous Database. Once ready, you can access it directly from your computer using an Oracle client, using SQL Plus or SQL Developer – after downloading the connectivity package.
3. DB SYSTEM (DBaaS or “DBCS”): from the OCI Dashboard menu:
   “Database => BareMetal, VM and Exadata”
   There, you can choose to create your database on a VM Server, a BareMetal Machine, or an Exadata Machine. All options will result in a server with a database already up and running, created automatically from OCI. Then access it by ssh from your computer using RSA keys.

Exadata Cloud at Customer: There is also an option to create an Exadata Cluster behind your firewall. Since this is more of a hybrid solution (and requiring on-premises Oracle hardware), I didn’t list it as one of the ways to get an Oracle Cloud Database from OCI, but it’s worth mentioning, anyway:
“Database => Exadata Cloud at Customer”
Oracle Database Exadata Cloud at Customer enables you to apply the combined power of Exadata and Oracle Cloud inside your own data center. You have full access to the features and operations available with Oracle Database, but with Oracle owning and managing the Exadata infrastructure.

General information for Oracle Cloud Database Services:

https://docs.cloud.oracle.com/en-us/iaas/Content/Database/Concepts/databaseoverview.htm

Sample connection to the DB System created for this test :


Roy-PC:RSA $
Roy-PC:RSA $ ssh -i /UserData/Oracle/RSA/id_rsa [email protected]
Enter passphrase for key '/UserData/Oracle/RSA/id_rsa':
Enter passphrase for key '/UserData/Oracle/RSA/id_rsa':
[[email protected] ~]$
[[email protected] ~]$ ps -ef | grep pmon
opc 19823 19624 0 02:54 pts/0 00:00:00 grep --color=auto pmon
oracle 53267 1 0 Aug21 ? 00:02:16 ora_pmon_DBRSV19c
grid 79423 1 0 Aug21 ? 00:02:06 asm_pmon_+ASM1
grid 94062 1 0 Aug21 ? 00:02:15 apx_pmon_+APX1
[[email protected] ~]$
[[email protected] ~]$ sudo su - oracle
Last login: Fri Sep 13 00:51:12 UTC 2019
[[email protected] ~]$
[[email protected] ~]$ df
Filesystem 1K-blocks Used Available Use% Mounted on
devtmpfs 7532884 0 7532884 0% /dev
tmpfs 7566532 1127404 6439128 15% /dev/shm
tmpfs 7566532 164672 7401860 3% /run
tmpfs 7566532 0 7566532 0% /sys/fs/cgroup
/dev/mapper/VolGroupSys-LogVolRoot 36682240 10146868 26535372 28% /
/dev/sda2 1442816 131436 1311380 10% /boot
/dev/sdj 206292968 20170984 175619840 11% /u01
/dev/asm/commonstore-427 5242880 362188 4880692 7% /opt/oracle/dcs/commonstore
tmpfs 1513308 0 1513308 0% /run/user/54322
[[email protected] ~]$
[[email protected] ~]$


-- from Sqlplus on the Server:
SQL>select * from v$version
BANNER
--------------------------------------------------------------------------------
BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
BANNER_LEGACY CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0
SQL>


2) Setting up for DBSAT execution

If you don’t have the utility already, then download DBSAT from the Oracle Support website (no additional licenses required) following the Document ID: 2138254.1:

Oracle Database Security Assessment Tool DBSAT. Doc ID 2138254.1

User Guide for reference:

https://docs.oracle.com/en/database/oracle/security-assessment-tool/index.html

A short summary from the above-referenced User Guide: in order to run the Collector you need to grant sufficient privileges to a DB User or you can run it as SYSDBA. For the Reporter, you need Python version 2.6 or later (no need to be the same server where you run the Collector).

And the Discoverer component requires Java Runtime Environment (JRE) 1.8 (jdk8-u172).

For this particular test, we want to create specific DB User at PDB level and grant the required privileges, additionally testing a new feature to connect with SEPS] :


[[email protected] DBSAT_22]$ ls -ltr
total 4576
-rw-r--r-- 1 oracle oinstall 4682601 Sep 13 04:36 DBSAT_2.2.zip
[[email protected] DBSAT_22]$ unzip DBSAT_2.2.zip
[[email protected] DBSAT_22]$ python -V
Python 2.7.5
[[email protected] DBSAT_22]$ java -version
java version "1.8.0_212"
Java(TM) SE Runtime Environment (build 1.8.0_212-b10)
Java HotSpot(TM) 64-Bit Server VM (build 25.212-b10, mixed mode)
[[email protected] DBSAT_22]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.0.0.0/dbhome_1
[[email protected] DBSAT_22]$


SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SECDATA READ WRITE NO
SQL>
SQL> ALTER SESSION SET CONTAINER=SECDATA;
Session altered.
SQL> select open_mode, log_mode, database_name from v$database;
OPEN_MODE LOG_MODE
-------------------- ------------
DATABASE_NAME
--------------------------------------------------------------------------------
READ WRITE ARCHIVELOG
SECDATA.SUB08211802510.LABNET.ORACLEVCN.COM
SQL>
SQL> create user DBSAT identified by *********************;
User created.
SQL> grant CREATE SESSION to DBSAT;
Grant succeeded.
SQL> grant SELECT_CATALOG_ROLE to DBSAT;
Grant succeeded.
SQL> grant SELECT on SYS.REGISTRY$HISTORY to DBSAT;
Grant succeeded.
SQL> grant SELECT on SYS.DBA_USERS_WITH_DEFPWD to DBSAT;
Grant succeeded.
SQL> grant AUDIT_VIEWER to DBSAT;
Grant succeeded.
SQL> grant CAPTURE_ADMIN to DBSAT;
Grant succeeded.
SQL> grant SELECT on AUDSYS.AUD$UNIFIED to DBSAT;
Grant succeeded.
SQL>


[[email protected] admin]$ vi tnsnames.ora
[[email protected] admin]$
[[email protected] admin]$ tnsping DISCOVER_SEC
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 18-SEP-2019 04:36:03
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = royserver)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = secdata.sub08211802510.labnet.oraclevcn.com)))
OK (0 msec)
[[email protected] admin]$
SQL> conn [email protected]_SEC
Enter password:
Connected.
SQL> show user
USER is "DBSAT"
SQL> show con_name
CON_NAME
------------------------------
SECDATA
SQL>


[[email protected] ~]$ mkstore -wrl /u01/app/oracle/admin/DBRSV19c/wallets -createCredential DISCOVER_SEC DBSAT
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
[[email protected] ~]$ ls /u01/app/oracle/admin/DBRSV19c/wallets
cwallet.sso cwallet.sso.lck ewallet.p12 ewallet.p12.lck
[[email protected] ~]$
[[email protected] ~]$ mkstore -wrl /u01/app/oracle/admin/DBRSV19c/wallets -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
List credential (index: connect_string username)
1: DISCOVER_SEC DBSAT
[[email protected] ~]$


[[email protected] ~]$ sqlplus /@DISCOVER_SEC
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 18 04:43:29 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Wed Sep 18 2019 04:36:48 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user
USER is "DBSAT"
SQL> show con_name
CON_NAME
------------------------------
SECDATA
SQL>

3) Run the Collector

Note: We don’t need to initiate an administrative connection (SYSDBA) or manually provide credentials since the utility will get the (purpose-built) username from the Oracle credential wallet. By using the TNS entry created, the DBSAT utility will be able to connect to the database without us having to type any username or password. And once the output json file is created, I’m adding a password to encrypt the content in a password-protected .zip file:


[[email protected] DBSAT_22]$ ./dbsat collect "/@DISCOVER_SEC" SECDATA-PDB_DBSAT22
Database Security Assessment Tool version 2.2 (September 2019)
Connecting to the target Oracle database...
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 18 05:25:18 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Wed Sep 18 2019 05:06:03 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Setup complete.
SQL queries complete.
/bin/cat: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora: No such file or directory
Warning: Exit status 256 from OS rule: listener.ora
/bin/ls: cannot access /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora: No such file or directory
Warning: Exit status 512 from OS rule: ls_listener.ora
OS commands complete.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
DBSAT Collector completed successfully.


Calling /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/zip to encrypt SECDATA-PDB_DBSAT22.json...
Enter password:
Verify password:
adding: SECDATA-PDB_DBSAT22.json (deflated 87%)
zip completed successfully.
[[email protected] DBSAT_22]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.0.0.0/dbhome_1
[[email protected] DBSAT_22]$


4) Run the Reporter

For the Reporter, no database connection is required and, hence, there is no need to use the wallet here. But still, I am showing the step to show the output it produces:


[[email protected] DBSAT_22]$ ./dbsat report ./SECDATA-PDB_DBSAT22
Database Security Assessment Tool version 2.2 (September 2019)
Archive: SECDATA-PDB_DBSAT22.zip
[SECDATA-PDB_DBSAT22.zip] SECDATA-PDB_DBSAT22.json password:
inflating: SECDATA-PDB_DBSAT22.json
DBSAT Reporter ran successfully.


Calling /usr/bin/zip to encrypt the generated reports...
Enter password:
Verify password:
zip warning: SECDATA-PDB_DBSAT22_report.zip not found or empty
adding: SECDATA-PDB_DBSAT22_report.txt (deflated 75%)
adding: SECDATA-PDB_DBSAT22_report.html (deflated 83%)
adding: SECDATA-PDB_DBSAT22_report.xlsx (deflated 3%)
adding: SECDATA-PDB_DBSAT22_report.json (deflated 80%)
zip completed successfully.
[[email protected] DBSAT_22]$


[[email protected] DBSAT_22]$ ls -ltr
total 5320
-r-xr-xr-x 1 oracle oinstall 13270 Aug 12 15:24 dbsat
-rw-rw-r-- 1 oracle oinstall 276258 Sep 11 17:33 sat_reporter.py
-rw-rw-r-- 1 oracle oinstall 58661 Sep 11 17:33 sat_collector.sql
-rw-rw-r-- 1 oracle oinstall 24935 Sep 11 17:33 sat_analysis.py
-r-xr-xr-x 1 oracle oinstall 13614 Sep 11 17:33 dbsat.bat
-rw-r--r-- 1 oracle oinstall 4682601 Sep 13 04:36 DBSAT_2.2.zip
drwxr-xr-x 5 oracle oinstall 40 Sep 13 04:36 Discover
-rw------- 1 oracle oinstall 55939 Sep 13 04:54 DBRSV19c_DBSAT22.zip
-rw------- 1 oracle oinstall 25891 Sep 13 04:55 sat_analysis.pyc
drwxr-xr-x 2 oracle oinstall 4096 Sep 13 04:55 xlsxwriter
-rw------- 1 oracle oinstall 102699 Sep 13 04:55 DBRSV19c_DBSAT22_report.zip
-rw------- 1 oracle oinstall 3741 Sep 13 05:24 DBRSV19C_DBSAT22_Discoverer_report.zip
-rw------- 1 oracle oinstall 68 Sep 18 05:05 discoverer.log
-rw------- 1 oracle oinstall 3712 Sep 18 05:06 DISCOVER_SEC_Report_report.zip
-rw------- 1 oracle oinstall 52609 Sep 18 05:26 SECDATA-PDB_DBSAT22.zip
-rw------- 1 oracle oinstall 94537 Sep 18 05:29 SECDATA-PDB_DBSAT22_report.zip
[[email protected] DBSAT_22]$
[[email protected] DBSAT_22]$


5) Run the Discoverer

The Discoverer component requires Java and also needs a bit of configuration in a couple of parameter files. For this test in particular, the WALLET_LOCATION configuration and TNS_ADMIN need to be added in the .config file in order to run without needing to type in any password (or credential details) for the database connection. And again, after the report is created, I’m adding a password to encrypt the output in a .zip:


vi /home/oracle/DBSAT_22/Discover/conf/new_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] #Use TNS_ADMIN, NET_SERVICE_NAME and WALLET_LOCATION for secured
#connection to Database by connecting without password.
#TNS_ADMIN is the location from where network service names
#needs to be read
TNS_ADMIN = /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin
#NET_SERVICE_NAME is the Network Service name to be used to make
#connection
NET_SERVICE_NAME = DISCOVER_SEC
#WALLET_LOCATION is the location of wallets for secured connections
#via SSL or SEPS(Secure External Password Store).
#WALLET_LOCATION should be set to SSL or SEPS wallet locations
WALLET_LOCATION = /u01/app/oracle/admin/DBRSV19c/wallets
###########################################################
#Use DB_HOSTNAME, DB_PORT and DB_SERVICE_NAME to connect using
#password at the prompt
#DB_IP is the IP address or FQDN for the DB Server
#default is localhost
DB_HOSTNAME = royserver
#DB_PORT is the port at which the DBSAT tool needs to connect to
#default is 1521
DB_PORT = 1521
#DB_SERVICE_NAME is the service Name for the DB
#default is empty
# DB_SERVICE_NAME =
###########################################################
#SSL Parameters : Enables and Defines the SSL configuration for Database Server
[[email protected]server DBSAT_22]$


[[email protected] DBSAT_22]$ ls /home/oracle/DBSAT_22/Discover/conf
new_dbsat.config sensitive_de.ini sensitive_en.ini sensitive_fr.ini sensitive_nl.ini
sample_dbsat.config sensitive_el.ini sensitive_es.ini sensitive_it.ini sensitive_pt.ini


[[email protected] DBSAT_22]$ ./dbsat discover -c ./Discover/conf/new_dbsat.config DISCOVER_SEC_Report
Database Security Assessment Tool version 2.2 (September 2019)
Error: Environment variable JAVA_HOME is NOT set.
[[email protected] DBSAT_22]$ export JAVA_HOME=/usr
[[email protected] DBSAT_22]$ ./dbsat discover -c ./Discover/conf/new_dbsat.config DISCOVER_SEC_Report
Database Security Assessment Tool version 2.2 (September 2019)
DBSAT Discover ran successfully.
Calling /usr/bin/zip to encrypt the generated reports...
Enter password:
Verify password:
zip warning: DISCOVER_SEC_Report_report.zip not found or empty
adding: DISCOVER_SEC_Report_discover.html (deflated 72%)
adding: DISCOVER_SEC_Report_discover.csv (deflated 30%)
Zip completed successfully.
[[email protected] DBSAT_22]$


The file “sample_dbsat.config” needs to be updated for database connectivity. In the above sample, I copied it as “new_dbsat.config” and edited it. Also, you may have to review and update the search patterns for sensitive data in the file sensitive_en.ini. For additional information on this, I have a full blog dedicated specifically on how to run the Discoverer component.

References:

https://blogs.oracle.com/cloudsecurity/announcing-oracle-database-security-assessment-tool-dbsat-22

https://cloud.oracle.com/home

https://blog.pythian.com/running-new-oracle-dbsat-version-2-1-0-against-autonomous-database/

How to configure SEPS for the pluggable databases (Doc ID 1980698.1)