How to Run DBSAT 2.2.0 on Oracle Cloud PDB using Wallet

• 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.

• 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. 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.

Roy-PC:RSA $ Roy-PC:RSA $ ssh -i /UserData/Oracle/RSA/id_rsa opc@129.213.124.96 Enter passphrase for key '/UserData/Oracle/RSA/id_rsa': Enter passphrase for key '/UserData/Oracle/RSA/id_rsa': [opc@royserver ~]$ [opc@royserver ~]$ 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 [opc@royserver ~]$ [opc@royserver ~]$ sudo su - oracle Last login: Fri Sep 13 00:51:12 UTC 2019 [oracle@royserver ~]$ [oracle@royserver ~]$ 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 [oracle@royserver ~]$ [oracle@royserver ~]$

-- 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] :

[oracle@royserver DBSAT_22]$ ls -ltr total 4576 -rw-r--r-- 1 oracle oinstall 4682601 Sep 13 04:36 DBSAT_2.2.zip [oracle@royserver DBSAT_22]$ unzip DBSAT_2.2.zip [oracle@royserver DBSAT_22]$ python -V Python 2.7.5 [oracle@royserver 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) [oracle@royserver DBSAT_22]$ echo $ORACLE_HOME /u01/app/oracle/product/19.0.0.0/dbhome_1 [oracle@royserver 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>

[oracle@royserver admin]$ vi tnsnames.ora [oracle@royserver admin]$ [oracle@royserver 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) [oracle@royserver admin]$ SQL> conn DBSAT@DISCOVER_SEC Enter password: Connected. SQL> show user USER is "DBSAT" SQL> show con_name CON_NAME ------------------------------ SECDATA SQL>

[oracle@royserver ~]$ 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: [oracle@royserver ~]$ ls /u01/app/oracle/admin/DBRSV19c/wallets cwallet.sso cwallet.sso.lck ewallet.p12 ewallet.p12.lck [oracle@royserver ~]$ [oracle@royserver ~]$ 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 [oracle@royserver ~]$

[oracle@royserver ~]$ 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>

[oracle@royserver 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. [oracle@royserver DBSAT_22]$ echo $ORACLE_HOME /u01/app/oracle/product/19.0.0.0/dbhome_1 [oracle@royserver 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:

[oracle@royserver 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. [oracle@royserver DBSAT_22]$

[oracle@royserver 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 [oracle@royserver DBSAT_22]$ [oracle@royserver 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 [oracle@royserver DBSAT_22]$

[oracle@royserver 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

[oracle@royserver 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. [oracle@royserver DBSAT_22]$ export JAVA_HOME=/usr [oracle@royserver 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. [oracle@royserver 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)