How to use and run Oracle Database Security Assessment Tool (2.2.2) on Oracle PDB with Wallet

December 6, 2022 admin Oracle database 21c, 0

Context
How to use and run Oracle Database Security Assessment Tool (2.2.2) on Oracle PDB with Wallet
for more details, please refer to oracle documentation oracle dbsat – documentation
Environment
Operating System: Oracle Linux Server 8.7 Architecture: x86-64
Oracle db: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Only demo schema tables will be scanned by Oracle Database Sensitive Data Assessment
We will run dbsat without providing the credentials in plain – text, we will configure SEPS ( Secure External Password Store) to connect to a pluggable database

Introduction

With data breaches growing every day along with the evolving set of data protection and privacy regulations, protecting business sensitive and regulated data is mission critical The Oracle Database Security Assessment Tool (DBSAT) helps identify areas where your database configuration, operation, or implementation introduces risks and recommends changes and controls to mitigate those risks. DBSAT is mainly used for the following three use cases. Assess the security of the database configuration, determine who are the users and their rights, and identify where sensitive data resides in the database.

One interesting approach from oracle, THINK LIKE A HACKER : To understand their target, attackers can spend a lot of time and use various tools that automate the discovery of databases, open ports, known vulnerabilities and privileged user accounts.
They can then launch various attacks, including password theft, brute-force password cracking, privilege escalations, and SQL injection attacks.
Once they gather the information, they identify the weakest links, then determine their next steps. Typically, attackers first assess the current
security state to find the easiest way to access sensitive data without getting caught.
For example, if the data is encrypted, they probably need to access the database as an authorized user. Are there users using default passwords?
Can I increase privileges? Is auditing enabled? Who has DBA privileges? What are the known vulnerabilities in this database version? Have these been patched?
What packaged applications are running? Are they running with powerful system privileges? What type of sensitive data do they process?
All of these questions and many more are inside the hacker’s mind, and these answers help him come up with a plan to break into the database and steal your data.

With Oracle DBSAT, you can:

Quickly and easily assess the current security status and identify sensitive data within the Oracle Database.

Reduce risk exposure using proven Oracle Database Security best practices, CIS benchmark recommendations and STIG rules.

Leverage security findings to accelerate compliance with EU GDPR and other regulations.

Improve the security posture of your Oracle Databases and promote security best practices.

DBSAT is a lightweight utility that does not measurably impact your system’s performance while running.

You can use Oracle DBSAT report findings to:

Fix immediate short-term risks

Implement a comprehensive security strategy

Support your regulatory compliance program

Promote security best practices

Oracle DBSAT Components, Sources, and Reports

Collector:

The Collector runs SQL queries and operating system commands to collect data from the system to be assessed. The collected data is written to a JSON file that is used by the DBSAT Reporter in the analysis phase.

Reporter:

Once the collector ran, the reporter analyzes the collected data and generates the dbsat Report in HTML, Excel, JSON, and text formats.

Discoverer:

According to the parameters specified in the configuration files, The Discoverer executes SQL queries and collects data from the system to be evaluated by mainly querying the dictionary views of the database.

The collected data is then used to generate the Oracle Database Sensitive Data Assessment Report in HTML and CSV formats.

Prerequisites

The following sections outline the prerequisites for the Oracle Database Security Assessment Tool:

Supported Operating Systems

The database configuration collection queries run on most supported Oracle Database platforms. However, currently the OS data collection will be skipped on Windows platforms.

Oracle DBSAT runs on:

Solaris x64 and Solaris SPARC64

Linux x86-64

Windows x64

HP-UX IA (64-bit)

IBM AIX (64-bit) & Linux on zSeries (64-bit)

Security Requirements Oracle DBSAT output files are sensitive because they may reveal weaknesses in the security posture of your database. To prevent unauthorized access to these files, you must implement the following security guidelines:

Ensure that the directories holding these files are secured with the appropriate permissions.

Delete the files securely after you implement the recommendations they contain.

Share them with others in their (by default) encrypted form.

Grant user permissions to the Oracle DBSAT user on a short-term basis and revoke these when no longer necessary.

For more information about Oracle DBSAT user privileges, see Collector Prerequisites.

The reports generated by dbsat may include potentially sensitive system configuration data and information that could be used by a skilled attacker to break into your system. It is very important to ensure and validate that the output of this tool, including the reports generated, is processed according to your company policies.

Let’s begin the demo

Download dbsat – download

Environment

Operating System: Oracle Linux Server 8.7 Architecture: x86-64

Oracle db: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production

Collector Prerequisites

DBSAT Collector must be run on the server that contains the database because it executes some operating system commands to collect process and file system information that cannot be obtained from the database.
In addition, the Oracle DBSAT Collector must be run as an OS user with read permissions on files and directories under ORACLE_HOME in order to collect and process file system data using OS commands.

Reporter Prerequisites

The Reporter is a platform-independent Python program and requires Python 2.6 or later to run.

Discoverer Prerequisites

The Discoverer is a Java program and requires the Java Runtime Environment (JRE) 1.8 (jdk8-u172) or later to run.

Oracle Database Security Assessment Tool installation

Downloads

-rw-r–r–. 1 oracle oinstall 4699765 Oct 30 14:51 dbsat.zip

To install the Oracle DBSAT:

Check the files in dbsat- directory

The Collector:

The Collector queries the database to collect data that will be analyzed by the Reporter.
Ensure that the target database and listener are running before running the Collector.
cr_dbsat_user.sql

— run the creation dbsat_user.sql

@/home/oracle/PROJECT/cr_dbsat_user.sql

User created.

Grant succeeded.

SQL>

Let’s run the collector without encrypting the output file

#! /bin/bash

#

# sample script to run dbsat collector

# scriptname : dbsatCollect.sh

# Created by: Salem Houali

# Date : 2022-12-11

# This sample script uses :

# oracleWallet

# does not encrypt the generated file

# runs on a single pluggable database

#

DIREXE=/home/oracle/dbsat

LOGS=${DIREXE}/logs

OUTPUT_FILE=${LOGS}/DBSAT_COLLECT__PDB1

cd ${DIREXE}

./dbsat collect -n /@pdb1_connect ${OUTPUT_FILE}

— check the generated output [oracle@oel8 logs]$ ls -altr
-rw-r–r–. 1 oracle oinstall 109 Dec 11 11:16 DBSAT_COLLECT__PDB1
-rw——-. 1 oracle oinstall 501429 Dec 11 11:16 DBSAT_COLLECT__PDB1.json
[oracle@oel8 logs]$

Let’s take a brief look at DBSAT_COLLECT__PDB1.json file

Some of the entries are date_and_release, instance_name, db_identity, db_pdbs, db_file_directory, dbs_file_permission, executable_permission with the complete information in the output json-file

“Run dbsat Collector“ Sun Dec 11 11:16:07 EST 2022

“==================“ Sun Dec 11 11:16:07 EST 2022

[oracle@oel8 logs]$ cat DBSAT_COLLECT__PDB1.json

{

“date_and_release”: {“version”: 1,

“columns”: [“collection_date”, “release”, “timezone”],

“data”: [[“11-12-2022 11:16:07″,”21.0.0.0.0″,”-05:00″]

]},

“date_and_release”: {“version”: 2,

“columns”: [“collection_date”, “release”, “timezone”],

“data”: [[“11-12-2022 11:16:07″,”21.3.0.0.0″,”-05:00″]

]},

“instance_name”: {“version”: 1,

“columns”: [“inst_name”],

“data”: [[“ORA21C”]

]},

“db_identity”: {“version”: 1,

“columns”: [“name”, “log_mode”, “platform”, “dg_role”, “dg_broker”, “flashback”, “controlfile”, “switchover_status”, “created”],

“data”: [[“ORA21C”,”NOARCHIVELOG”,”Linux x86 64-bit”,”PRIMARY”,”DISABLED”,”NO”,”CURRENT”,”NOT ALLOWED”,”10-07-2022 21:12:49″]

]},

“db_pdbs”: {“version”: 1,

“columns”: [“con_id”, “name”],

“data”: [[3,”PDB1″]

]},

“db_version”: {“version”: 1,

“columns”: [“banner”],

“data”: [[“Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production”]

]},

“db_file_directory”: {“version”: 1,

“columns”: [“value”],

“data”: [[“/u01/app/oracle/oradata”]

]},

Let’s run the Reporter

The Reporter analyzes the data collected by the Collector and makes recommendations to improve the security of the database.

First, let’s check if python is installed

[oracle@oel8 PROJECT]$ python -V
Python 3.9.13
[oracle@oel8 PROJECT]$

# sample script to run dbsat Reporter

#! /bin/bash

#

# sample script to run dbsat Reporter

# scriptname : dbsatReport.sh

# Created by: Salem Houali

# Date : 2022-12-11

# This sample script uses :

# runs on a single pluggable database

# for all the database accounts

# Shows all grants including common grants in a pluggable database

# Specifies no encryption for output

# Note:For security reasons, this is not recommended.

# DBSAT_COLLECT__PDB1 : dbsatCollector’s output

DIREXE=/home/oracle/dbsat

LOGS=${DIREXE}/logs

INPUT_FILE=${LOGS}/DBSAT_COLLECT__PDB1

cd ${DIREXE}

./dbsat report -a -n -g ${INPUT_FILE}

Generated files

[oracle@oel8 logs]$ ls -altr

total 1472

drwxr-xr-x. 2 oracle oinstall 132 Dec 11 12:52 tmp

-rw-r–r–. 1 oracle oinstall 109 Dec 11 12:53 dbsat_collect__2022-12-11-12-53-14.log

-rw——-. 1 oracle oinstall 503827 Dec 11 12:53 DBSAT_COLLECT__PDB1.json

drwxr-xr-x. 6 oracle oinstall 172 Dec 11 12:53 ..

-rw——-. 1 oracle oinstall 177681 Dec 11 12:54 DBSAT_COLLECT__PDB1_report.txt

-rw——-. 1 oracle oinstall 240939 Dec 11 12:54 DBSAT_COLLECT__PDB1_report.html

drwxr-xr-x. 3 oracle oinstall 250 Dec 11 12:54 .

-rw——-. 1 oracle oinstall 26456 Dec 11 12:54 DBSAT_COLLECT__PDB1_report.xlsx

-rw——-. 1 oracle oinstall 198729 Dec 11 12:54 DBSAT_COLLECT__PDB1_report.json

The HTML report provides detailed results of the assessment in a format that is easy to navigate.

Oracle Database Security Assessment Report — Summary section

Basic Information section

The Excel format provides a high-level summary of each finding without the detailed output included in the HTML report.

Finally, let’s go through the discoverer part

The Discoverer component lets you generate the Oracle Database Sensitive Data Assessment Report.

Discoverer Components and Architecture

Based on the settings specified in the configuration and pattern files, the Discoverer executes SQL queries and collects data from the system to be assessed.

Configure the Discoverer.

Access the directory where Oracle DBSAT is installed.
Navigate to the Discover/conf directory.
Make a copy of the sample_dbsat.config file

Open the copy of the dbsat.config file.

Modify the content of the file with the information relevant to your environment

The [Database], [Discovery Parameters], and [Sensitive Categories] entries for the sections must be kept, if removed, the execution of the tool will issue an error.

custom_sensitive_en.ini is the configuration file which contains sensitive types and their related regular expressions and categories. This file is comprised of multiple Sensitive Types sections and each Sensitive Type section contains 4 lines: [<sensitive_type_name>] – Name of the Sensitive Type COL_NAME_PATTERN = <regex> – REGEX used to search Column Names COL_COMMENT_PATTERN = <regex> – REGEX used to search Column Comments SENSITIVE_CATEGORY = <sens_category> – Sensitive Category to which the sensitive type belongs to

Let’s look at the custom_sensitive_en.ini configuration file: The following section in the file identifies columns with sensitive data # Identification Info – Public IDs

[FIRST NAME] COL_NAME_PATTERN = (^|[_-])(FNAME|(FIRST|GIVEN).*(NAME|NM)|FORE.?(NAME|NM))($|[_-]) COL_COMMENT_PATTERN = (FIRST|GIVEN) NAME|FORENAME SENSITIVE_CATEGORY = Identification Info – Public IDs

[LAST NAME] COL_NAME_PATTERN = (^|[_-])LNAME$|(LAST|FAMILY|PATERNAL).*(NAME|NM)|SUR.?(NAME|NM) COL_COMMENT_PATTERN = (LAST|FAMILY|PATERNAL) NAME|SUR.?NAME SENSITIVE_CATEGORY = Identification Info – Public IDs

Let’s check if our demo schemas contain any sensitive information that matches the previous patterns.

Let’s modify the [Discovery Parameters] section. SENSITIVE_PATTERN_FILES=custom_sensitive_en.ini (The pattern files to be used) SCHEMA_SCOPE=HR,BI,OE,PM,SH,IX (schemas to be scanned)

Note: The Discover/conf directory also contains the following language-specific .ini files to help discover sensitive data in data dictionaries in major European languages (filename – country, language): sensitive_de.ini – German, Germany. sensitive_el.ini – Greek, Greece. sensitive_es.ini – Spanish, Spain. sensitive_fr.ini – French, France. sensitive_it.ini – Italian, Italy. sensitive_nl.ini – Dutch, Netherlands. sensitive_pt.ini – Portuguese, Portugal.

Let’s modify some entries of the two following sections for the demo to run successfully

Database section

Discovery section

#! /bin/bash
#
# sample script to run dbsat discoverer
#
# scriptname : dbsatDiscover.sh
# Created by: Salem Houali
# Date : 2022-12-10
# This sample script uses the following parameters:
# -n : no encryption for output.
# -c : name of the configuration file used for discoverer.
# OUTPUT_FILE : full or relative path name to create the .zip file. Do not add an extension.
#
#
# /home/oracle/dbsat/Discover/bin

DIREXE=/home/oracle/dbsat
CUSTOM_DBSAT_CONF=/home/oracle/dbsat/Discover/conf/new_dbsat.config
LOGS=${DIREXE}/logs
OUTPUT_FILE=${LOGS}/DISC_PDB1
cd ${DIREXE}

./dbsat discover -n -c ${CUSTOM_DBSAT_CONF} ${OUTPUT_FILE}