Securing database is inevitable. Companies will strongly focus on securing not only the database but the entire network and everything that's put inside it such as infrastructure, servers, application, web servers, client connections, etc., As we keep on performing tasks related to security, how well we can be confident that we have secured the database to the fullest?
That's where this wonderful tool DBSAT (Database Security Assessment Tool) from Oracle comes to the rescue. This tool is free of cost and can be downloaded from Oracle support site by visiting Doc ID 2138254.1
 |
| Pic courtesy: www.oracle.com |
In this post, we will see how to set up DBSAT and how to invoke the tool to collect and report security status and to identify sensitive data and improvement opportunities w.r.to security.
DBSAT consists of 3 components
Collector - Runs queries against DB and OS commands on system to be assessed and stores details in JSON format.
Reporter - Analyzes the collected data and generates the Oracle Database Security Assessment Report in HTML, Excel, JSON, and Text formats
Discoverer - Runs SQL queries and collects data from the system to be assessed, based on the settings specified in the configuration files. Collected data is then used to generate the Oracle Database Sensitive Data Assessment Report in HTML and CSV formats
Here is a small demo I have performed on my lab systems..
Prepare phase:
Step 1: Download dbsat from Oracle support note Doc ID 2138254.1 and transfer to server where you want to initiate dbsat from.. (central server)
Step 2: Install DBSAT tool (unzip the downloaded file)
-sh-4.2$ mkdir /oracle/media/dbsat
-sh-4.2$ cd /oracle/media/dbsat
-sh-4.2$ ls -lrt
total 4580
-rw-r--r--. 1 oracle oinstall 4687346 Jun 3 06:40 dbsat.zip
-sh-4.2$ unzip dbsat.zip
Archive: dbsat.zip
inflating: dbsat
inflating: dbsat.bat
inflating: sat_collector.sql
inflating: sat_reporter.py
inflating: sat_analysis.py
inflating: xlsxwriter/app.py
inflating: xlsxwriter/chart_area.py
inflating: xlsxwriter/chart_bar.py
inflating: xlsxwriter/chart_column.py
inflating: xlsxwriter/chart_doughnut.py
inflating: xlsxwriter/chart_line.py
inflating: xlsxwriter/chart_pie.py
inflating: xlsxwriter/chart.py
...
...
...
inflating: Discover/conf/sensitive_de.ini
inflating: Discover/conf/sensitive_pt.ini
inflating: Discover/conf/sensitive_it.ini
inflating: Discover/conf/sensitive_fr.ini
inflating: Discover/conf/sensitive_nl.ini
inflating: Discover/conf/sensitive_el.ini
-sh-4.2$ ls -lrt
total 5000
-r-xr-xr-x. 1 oracle oinstall 13465 May 5 2020 dbsat
-rw-rw-r--. 1 oracle oinstall 296035 May 6 2020 sat_reporter.py
-rw-rw-r--. 1 oracle oinstall 61823 May 6 2020 sat_collector.sql
-rw-rw-r--. 1 oracle oinstall 26096 May 6 2020 sat_analysis.py
-r-xr-xr-x. 1 oracle oinstall 13644 May 6 2020 dbsat.bat
-rw-r--r--. 1 oracle oinstall 4687346 Jun 3 06:40 dbsat.zip
drwxr-xr-x. 2 oracle oinstall 4096 Jun 3 06:42 xlsxwriter
drwxr-xr-x. 5 oracle oinstall 40 Jun 3 06:42 Discover
-sh-4.2$
Step 3: Make sure your tnsping works and you are able to connect to the database to be assessed without issues
-sh-4.2$ tnsping abc.world
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 03-JUN-2021 06:49:08
Copyright (c) 1997, 2020, Oracle. All rights reserved.
Used parameter files:
/oracle/grid/193/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = abcdb)(PORT = 1527)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ABC)))
OK (0 msec)
-sh-4.2$
-sh-4.2$ mkdir reports
Notice that I have created a directory by name reports under /oracle/media/dbsat and this directory will be used to store the reports generated.
Step 4: (Optional) Create a user with minimum privileges if you don't want to use user with higher privileges like sys/system
create user dbsat_user identified by dbsat_user;
--If Database Vault is enabled, connect as DV_ACCTMGR to run this command
grant create session to dbsat_user;
grant select_catalog_role to dbsat_user;
grant select on sys.registry$history to dbsat_user;
grant select on sys.dba_users_with_defpwd to dbsat_user; // 11g and 12c
grant audit_viewer to dbsat_user; // 12c
grant capture_admin to dbsat_user;// 12c covers sys.dba_priv_captures, sys.priv_capture$, sys.capture_run_log$
--If Database Vault is enabled, connect as DV_OWNER to run this command
grant DV_SECANALYST to dbsat_user;
Running the collector:
Now as the set up is completed, we can start the tool to collect the data for our assessment and reporting later.
The command format is as below
dbsat collect db_connect_string output_file_name
-sh-4.2$ ./dbsat collect system@abc.world ./reports/dbsat-report-abc
Database Security Assessment Tool version 2.2.1 (May 2020)
This tool is intended to assist 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 19.0.0.0.0 - Production on Thu Jun 3 06:51:07 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter password: --> Enter password for system user
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
Setup complete.
SQL queries complete.
OS commands complete.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
DBSAT Collector completed successfully.
Calling /oracle/ABC/193/bin/zip to encrypt dbsat-report-abc.json...
Enter password: --> Enter encryption password, will be used to decrypt later
Verify password:
adding: dbsat-report-abc.json (deflated 83%)
zip completed successfully.
-sh-4.2$
The collector has run successfully and the o/p file is store by name /oracle/media/dbsat/reports/dbsat-report-abc.json
This report will be used in the next phase for reporting the security status of the database
Running the Reporter:
Note: Reporter requires Python version is 2.6 or later is installed. Install if it's not available on your server
The command format is as below
dbsat report input-file
Options such as -a (report all DB account), -n (no encryption for o/p), -g (show all grants), -x (exclude a section such as user, privilege, etc from report) can be used with the command but I'll stick with the default for now.
-sh-4.2$ ./dbsat report ./reports/dbsat-report-abc
Database Security Assessment Tool version 2.2.1 (May 2020)
This tool is intended to assist 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: dbsat-report-abc.zip
[dbsat-report-abc.zip] dbsat-report-abc.json password: --> Enter the encryption password provided while running collector
inflating: dbsat-report-abc.json
DBSAT Reporter ran successfully.
Calling /usr/bin/zip to encrypt the generated reports...
Enter password: --> Enter password to encrypt the reports
Verify password:
zip warning: dbsat-report-abc_report.zip not found or empty
adding: dbsat-report-abc_report.txt (deflated 77%)
adding: dbsat-report-abc_report.html (deflated 83%)
adding: dbsat-report-abc_report.xlsx (deflated 3%)
adding: dbsat-report-abc_report.json (deflated 81%)
zip completed successfully.
-sh-4.2$
-sh-4.2$ cd reports/
-sh-4.2$ ls -lrt
total 176
-rw-------. 1 oracle oinstall 68997 Jun 3 06:56 dbsat-report-abc.zip
-rw-------. 1 oracle oinstall 109062 Jun 3 07:01 dbsat-report-abc_report.zip
-sh-4.2$
Note that to analyze the report we provide the input file as the file generated during the collect phase. We have to provide the password we used during the collect phase to make use of the file.
Also we have to provide password to encrypt the report files generated. We can use same password for both the encryption to remember easily.
-- dbsat-report-abc.zip will contain only the collector json file.
-- dbsat-report-abc_report.zip contains the report in multiple formats.
You can check
here for a sample report (download the file and open in browser of your choice) that I generated for my database which would give a detailed analysis on the security aspects of the database.
Discoverer:
Discoverer can be used to generate the Oracle Database Sensitive Data Assessment Report.
Uses the config file under dbsat_home/Discover/conf.
Making use of the config file, discoverer can identify the sensitive type data such as identification info (Name, gender, etc.,), biographical info (address, family data, etc.,), financial info (credit card number, bank data, etc.,), job or academic info, etc., so that we can secure the data using different techniques such as masking, encrypting, etc.,
We can also create an exclusion list file if we want to exclude schemas, tables, or columns to exclude from the scan.
For this demo purpose, I have created oracle example schemas in my test system (different from the one used above for collector and reporter, so the DB version will vary) which contains a lot of sensitive data tables/columns which will be reported in the discoverer report.
Command has the following format
dbsat discover -c config_file output_file
My config file contains the below entries. I have just copied the default sample_dbsat.config and named it as orcl_dbsat.config and edited the below connection entries alone and left all as default
[Database]
TNS_ADMIN = /u01/orcl/122/network/admin
NET_SERVICE_NAME = orcl
WALLET_LOCATION =
DB_HOSTNAME = linux75-2
DB_PORT = 1522
DB_SERVICE_NAME = orcl
[oracle@linux75-2 dbsat]$ ./dbsat discover -c ./Discover/conf/orcl_dbsat.config ./reports/orcl-discover
Database Security Assessment Tool version 2.2.1 (May 2020)
This tool is intended to assist 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.
Enter username: system
Enter password:
DBSAT Discover ran successfully.
Calling /usr/bin/zip to encrypt the generated reports...
Enter password: --> Enter encryption password here
Verify password:
zip warning: ./reports/orcl-discover_report.zip not found or empty
adding: reports/orcl-discover_discover.html (deflated 81%)
adding: reports/orcl-discover_discover.csv (deflated 75%)
Zip completed successfully.
[oracle@linux75-2 dbsat]$ cd reports
[oracle@linux75-2 reports]$ ls -lrt
total 12
-rw-------. 1 oracle oinstall 10450 Jun 13 00:07 orcl-discover_report.zip
[oracle@linux75-2 reports]$
The report will be created in both html and csv format. Pasting the summary picture of the report below and the detailed sample report can be found
in this link (download the file and open in browser of your choice)
With this detailed report handy, we can now work on securing the data by using database techniques such as encryption, data masking, etc., That's the end for today :)
References:
Happy Securing...!!!
