Aug 20, 2018

Oracle Database Security Assessment Tool - dbsat

About dbsat:

1) DBSAT is a light weight utility that will not impair system performance in a measurable way.
2) Oracle Database Security Assessment Tool (DBSAT) analyzes database configurations and security policies to uncover security risks and improve the security posture of Oracle Databases within your organization.
3) DBSAT reports on the state of user accounts, role and privilege grants, and policies that control the use of various security features in the database.

Benefits of Using DBSAT:
1) Quickly identify security configuration errors in your databases
2) Promote security best practices
3) Improve the security posture of your Oracle Databases
4) Reduce the attack surface and exposure to risk

How to install and use dbsat tool?

You can follow following tasks to complete installation and use.

Task 1: Install the Database Security Assessment Tool

-- download and copy dbsat software

[oracle@example01 oracle]$ cd /u01/oracle/admin/scripts/dbsat
[oracle@example01 dbsat]$ ls
dbsat.zip
[oracle@example01 dbsat]$ unzip dbsat.zip 

-- list out softwares
[oracle@example01 dbsat]$ ls
dbsat  dbsat.bat  dbsat.zip  Discover  sat_analysis.py  sat_collector.sql  sat_reporter.py  xlsxwriter

-- Verify the python version
[oracle@example01 dbsat]$ python -V
Python 2.6.6
[oracle@example01 dbsat]$ 


Task 2: Run the DBSAT Collector

$ export ORACLE_SID=tempdb
$ sqlplus / as sysdba
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ---------
TEMPDB    READ WRITE

SQL> alter user system identified by xxxx;
User altered.

SQL> 
Run the DBSAT Collector:
$ dbsat collect <connect_string> <destination>

e.g.,

dbsat collect system/xxxx@tempdb tempdb_dbsat

-- You can do like following to find help

[oracle@example01 dbsat]$ ./dbsat

Database Security Assessment Tool version 2.0.1 (December 2017)

    Usage: dbsat collect [ -n ] <database_connect_string> <output_file>
           dbsat report [ -a ] [ -n ] [ -x <section> ] <input_file>
           dbsat discover [-n] -c <config_file> <output_file>

    Options:
       -a  Report about all user accounts, including locked, 
           Oracle-supplied users
       -n  No encryption for output
       -x  Specify sections to exclude from report (may be repeated for
           multiple sections)
       -c  Configuration file for discoverer

[oracle@example01 dbsat]$

Example: To run dbsat collector, you can use custom user ( described below)

[oracle@example01 dbsat]$ ./dbsat collect dbsat_user/oracle dbsat_collection

Database Security Assessment Tool version 2.0.1 (December 2017)
...
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Setup complete.
SQL queries complete.
OS commands complete.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
DBSAT Collector completed successfully.

Calling /u01/oracle/admin/scripts/product/12.1.0/dbhome_1/bin/zip to encrypt dbsat_collection.json...

Enter password: 
Verify password: 
  adding: dbsat_collection.json (deflated 83%)
zip completed successfully.
[oracle@example01 dbsat]$ 
[oracle@example01 dbsat]$ ls -lrt
total 556
-r-xr-xr-x 1 oracle oinstall  28216 Dec 20 16:35 sat_analysis.py
-r-xr-xr-x 1 oracle oinstall  43181 Jan  8 13:43 sat_collector.sql
-r-xr-xr-x 1 oracle oinstall  12579 Jan 11 11:21 dbsat.bat
-r-xr-xr-x 1 oracle oinstall  12433 Jan 11 11:21 dbsat
-r-xr-xr-x 1 oracle oinstall 247465 Jan 16 17:47 sat_reporter.py
drwxr-xr-x 5 oracle oinstall   4096 May 10 10:51 Discover
-r-------- 1 oracle oinstall  25197 May 10 11:24 sat_analysis.pyc
drwxr-xr-x 2 oracle oinstall   4096 May 10 11:24 xlsxwriter
-rwxrwxrwx 1 oracle oinstall 143361 May 15 07:08 dbsat_col_report.html
-rw------- 1 oracle oinstall  25352 May 23 07:38 dbsat_collection.zip
[oracle@example01 dbsat]$ 


Note: Remember your password given during collection.


Task 3: Run the DBSAT Reporter

Run the DBSAT Reporter.
[oracle@example01 sat]$ dbsat report [-a] [-n] [-x <section>] <pathname>

e.g.,

[oracle@example01 dbsat]$ ./dbsat report dbsat_collection

Database Security Assessment Tool version 2.0.1 (December 2017)
....

Archive:  tempdb_sat.zip
[tempdb_sat.zip] tempdb_sat.json password: 
  inflating: tempdb_sat.json         
DBSAT Reporter ran successfully.

Calling /usr/bin/zip to encrypt the generated reports...

Enter password: 
Verify password: 
        zip warning: tempdb_sat_report.zip not found or empty
  adding: tempdb_sat_report.txt (deflated 77%)
  adding: tempdb_sat_report.html (deflated 84%)
  adding: tempdb_sat_report.xlsx (deflated 3%)
  adding: tempdb_sat_report.json (deflated 82%)
zip completed successfully.
[oracle@example01 dbsat]$ 

-- Now Unzip the Report file

[oracle@example01 dbsat]$ ls *.zip
tempdb_sat_report.zip 

$ unzip tempdb_sat_report.zip

[oracle@example01 dbsat]$ unzip tempdb_sat_report.zip
Archive:  tempdb_sat_report.zip
[tempdb_sat_report.zip] tempdb_sat_report.txt password: 
  inflating: tempdb_sat_report.txt   
  inflating: tempdb_sat_report.html  
  inflating: tempdb_sat_report.xlsx  
  inflating: tempdb_sat_report.json  
[oracle@example01 dbsat]$ 

Task 4: Analyze report now

DBSAT Reports
DBSAT produces output in multiple formats for various audiences and purposes.

The HTML report provides detailed results of the assessment in a format that is easy to navigate. The Excel format provides a high-level summary of each finding without the detailed output included in the HTML report. It also allows you to add columns for your tracking and prioritization purposes. A report in text format makes it convenient to copy portions of the output for other usage.

You can create specified user to use dbsat with following configuration

1) Creating a Database User to run DBSAT:
Example:
grant create session to dbsat identified by oracle; 
grant select on sys.registry$history to dbsat; 
grant select_catalog_role to dbsat; 
grant audit_viewer to dbsat; 
grant capture_admin to dbsat; 
grant select on sys.dba_users_with_defpwd to dbsat; 
grant select on audsys.aud$unified to dbsat;

2) Start Collector

./dbsat collect dbsat/oracle dbsat_col

[oracle@example01 dbsat]$ ./dbsat collect dbsat/oracle dbsat_collection

Database Security Assessment Tool version 2.0.1 (December 2017)
This tool is intended to assist in you in securing your Oracle database

...
Setup complete.
SQL queries complete.
/bin/cat: /u01/oracle/admin/scripts/product/12.1.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/oracle/admin/scripts/product/12.1.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 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
DBSAT Collector completed successfully.

Calling /u01/oracle/admin/scripts/product/12.1.0/dbhome_1/bin/zip to encrypt dbsat_col.json...

Enter password: 
Verify password: 
  adding: dbsat_col.json (deflated 86%)
zip completed successfully.
[oracle@example01 dbsat]$ 


3) Run DBSAT Reporter:

./dbsat report dbsat_col

[oracle@example01 dbsat]$ ./dbsat report dbsat_collection

Database Security Assessment Tool version 2.0.1 (December 2017)

This tool is intended to assist in you in securing your Oracle database
....
Archive:  dbsat_col.zip
[dbsat_col.zip] dbsat_col.json password: 
  inflating: dbsat_col.json          
DBSAT Reporter ran successfully.

Calling /usr/bin/zip to encrypt the generated reports...

Enter password: 
Verify password: 
        zip warning: dbsat_col_report.zip not found or empty
  adding: dbsat_col_report.txt (deflated 77%)
  adding: dbsat_col_report.html (deflated 84%)
  adding: dbsat_col_report.xlsx (deflated 3%)
  adding: dbsat_col_report.json (deflated 82%)
zip completed successfully.
[oracle@example01 dbsat]$ 

Identify sensitive data with DBSAT:

One big enhancement is the introduction of the new Discoverer component in DBSAT, which can be used to identify sensitive data within your database.
For this you need "discoverer" configuration to find out sensitive data. So it includes two major steps:

•Configuring the Discoverer
•Configure dbsat.config
•Configure the Pattern file
•Configure the Exclusion List file
•Running the Discoverer

What is the Discoverer?
The Discoverer component is a Java-based utility (discoverer.jar) which identifies sensitive data by executing SQL queries primarily against the Data Dictionary. Which objects (schemas, tables, columns) the Discoverer marks as sensitive, depends on its configuration files. At the end a Database Sensitive Data Assessment Report is generated as HTML and CSV file. You can run the Discoverer on the Database server or any other machine, e.g. your Laptop. To run the Discoverer you just need a Java Runtime Environment (JRE) 1.6 or higher.

Configuration:
Before you execute the Discoverer, you have to configure it using a file called dbsat.config (any name can be used). A sample configuration can be found in the subdirectory Discover/conf.

$ pwd
/u01/oracle/admin/scripts/dbsat/Discover
$ ls
bin  conf  lib
$ cd conf/
$ ls
sample_dbsat.config  sensitive_en.ini

$ chmod 775 sample_dbsat.config 
$ cp sample_dbsat.config dbsat.config
$ ls -lrt
total 24
-r--r--r-- 1 oracle oinstall 12796 Jan 16 22:58 sensitive_en.ini
-rwxrwxr-x 1 oracle oinstall  2385 Jan 16 22:58 sample_dbsat.config
-rwxr-xr-x 1 oracle oinstall  2385 May 24 07:54 dbsat.config

$ vi dbsat.config

DB_HOSTNAME = example01
DB_PORT = 1521
DB_SERVICE_NAME =  tempdb

-- To discover sensitive data

$ export JAVA_HOME=$ORACLE_HOME/jdk/jre
$ ./dbsat discover -c Discover/conf/dbsat.config tempdb_data

Example:

$ ./dbsat discover -c Discover/conf/dbsat.config tempdb_data
....
Enter username: dbsat
Enter password: 
DBSAT Discover ran successfully.
Calling /usr/bin/zip to encrypt the generated reports...

Enter password: 
Verify password: 
        zip warning: tempdb_data_report.zip not found or empty
  adding: tempdb_data_discover.html (deflated 74%)
  adding: tempdb_data_discover.csv (deflated 44%)
Zip completed successfully.
$

[oracle@example01 dbsat]$ ls -lrt
total 404
-r-xr-xr-x 1 oracle oinstall  28216 Dec 20 16:35 sat_analysis.py
-r-xr-xr-x 1 oracle oinstall  43181 Jan  8 13:43 sat_collector.sql
-r-xr-xr-x 1 oracle oinstall  12579 Jan 11 11:21 dbsat.bat
-r-xr-xr-x 1 oracle oinstall  12433 Jan 11 11:21 dbsat
-r-xr-xr-x 1 oracle oinstall 247465 Jan 16 17:47 sat_reporter.py
drwxr-xr-x 5 oracle oinstall   4096 May 10 10:51 Discover
-r-------- 1 oracle oinstall  25197 May 10 11:24 sat_analysis.pyc
drwxr-xr-x 2 oracle oinstall   4096 May 10 11:24 xlsxwriter
-rw-r--r-- 1 oracle oinstall   6596 May 24 08:17 load_data.sql
-rw------- 1 oracle oinstall     68 May 24 08:22 discoverer.log
-rw------- 1 oracle oinstall   4278 May 24 08:23 tempdb_data_report.zip
[oracle@example01 dbsat]$ 

Reference:
https://docs.oracle.com/cd/E76178_01/SATUG/toc.htm#SATUG-GUID-DB214E36-E822-4EF7-AB83-16967FFDA669
http://christian-gohmann.de/2018/01/26/identify-sensitive-data-with-dbsat/
https://docs.oracle.com/cd/E93129_01/SATUG/toc.htm#SATUG-GUID-66685BC1-1306-417B-82C4-D28EDC895FF3

v$rman_configuration - no rows selected

When you have default configurations for RMAN, then running of v$rman_configuration view will not give you any output. Once you touched any configuration item, then you able to see some outputs for this view.

Example:

My default config:

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TESTTMP are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_testtmp.f'; # default

RMAN> 


and out put for v$rman_configuration is:

SQL> select NAME,VALUE from v$rman_configuration;

no rows selected


Now I am going to change some configuration and then you can see the difference:

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> 

SQL> select NAME,VALUE from v$rman_configuration;

NAME                   VALUE
---------------------- ---------------------------------------------
RETENTION POLICY          TO REDUNDANCY 1
CONTROLFILE AUTOBACKUP    ON

SQL> 

Some times you need to reset the whole RMAN config with one shot, instead of running several clear commands like the following,
'RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;' , simply run the RESETCONFIG.

SQL> EXECUTE DBMS_BACKUP_RESTORE.RESETCONFIG;
PL/SQL procedure successfully completed.

-- After executing this command, the v$rman_configuration view is empty, which means that all
-- RMAN persistent settings are default.

SQL> select NAME,VALUE from v$rman_configuration;

no rows selected

SQL> 






Translate >>