my-orcl@Areef DBA, my-orcl@Arif DBA: April 2018

Hai, this article explains you about introduction and implementation of

Oracle Database Security Assessment Tool (DBSAT) (Doc ID 2138254.1)

List of Topics (Linux, Database, RAC, EBS)

INTRODUCTION:

DBSAT is an Oracle-provided command line tool that evaluates how securely your database is configured. DSAT considers such things as: user roles and entitlements, security policies, and security controls. DSAT is an easy first step to help the DBA mitigate potential security risks on sensitive databases. DSAT reports identify short-term risks so that the DBA and cyber-security team can implement a comprehensive security strategy.

In this article, we discuss:

1. DBSAT & its components

2. Implementation

3. Report output

4. How to interpret the results.

1. DBSAT & ITS COMPONENTS:

DBSAT has three components: Collector, Reporter, and Discoverer. Collector and Reporter work together to discover risk areas and produce a report on those risk areas--the "Database Security Assessment Report." The Discoverer is a stand-alone module used to locate and report on sensitive data. This output is called the "Database Sensitive Data Assessment Report."

2. IMPLEMENTATION:

Linux Machine Details:

[root@rac1 ~]#

[root@rac1 ~]# lsb_release -a

LSB Version: :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch

Distributor ID: OracleServer

Description: Oracle Linux Server release 6.4

Release: 6.4

Codename: n/a

[root@rac1 ~]#

[root@rac1 ~]# arch

x86_64

[root@rac1 ~]#

[root@rac1 ~]# hostname

rac1.dell.com

[root@rac1 ~]#

[root@rac1 ~]# hostname -i

192.168.1.11

[root@rac1 ~]#

[root@rac1 ~]# python -V

Python 2.6.6

[root@rac1 ~]#

Creation of User and granting permission:

[root@rac1 ~]#

[root@rac1 ~]# su - oracle

[oracle@rac1 ~]$

[oracle@rac1 ~]$ cat dell.env

export ORACLE_SID=DELL

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export TNS_ADMIN=$ORACLE_HOME/network/admin

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib/usr/lib; export LD_LIBRARY_PATH

[oracle@rac1 ~]$

[oracle@rac1 ~]$. dell.env

[oracle@rac1 ~]$

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 12 08:26:20 2018

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

SQL> SQL> select INSTANCE_NAME, STATUS, DATABASE_STATUS, VERSION from v$instance;

INSTANCE_NAME STATUS DATABASE_STATUS VERSION

---------------- ------------ ----------------- -----------------

DELL OPEN ACTIVE 12.1.0.2.0

SQL>

SQL> grant create session to dbsat identified by oracle;

Grant succeeded.

SQL> grant select on sys.registry$history to dbsat;

Grant succeeded.

SQL> grant select_catalog_role to dbsat;

Grant succeeded.

SQL> grant audit_viewer to dbsat;

Grant succeeded.

SQL> grant capture_admin to dbsat;

Grant succeeded.

SQL> grant select on sys.dba_users_with_defpwd to dbsat;

Grant succeeded.

SQL> grant select on audsys.aud$unified to dbsat;

Grant succeeded.

SQL>

SQL> exit

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

[oracle@rac1 ~]$

Download & Unzip DBSAT TOOL: (Doc ID 2138254.1)

[oracle@rac1 ~]$

[oracle@rac1 ~]$ cd /u01/dbsat/

[oracle@rac1 dbsat]$ l1

total 2104

-rwxr-xr-x. 1 oracle oinstall 2150961 Apr 12 08:37 dbsat.zip

[oracle@rac1 dbsat]$

[oracle@rac1 dbsat]$ unzip dbsat.zip

Archive: dbsat.zip

inflating: dbsat

inflating: dbsat.bat

inflating: sat_reporter.py

inflating: sat_analysis.py

inflating: sat_collector.sql

. . . . .

[oracle@rac1 dbsat]$

[oracle@rac1 dbsat]$ ls

dbsat dbsat.zip sat_analysis.py sat_reporter.py

dbsat.bat Discover sat_collector.sql xlsxwriter

[oracle@rac1 dbsat]$

[oracle@rac1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-APR-2018 16:03:44

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.dell.com)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 11-APR-2018 16:03:27

Uptime 0 days 0 hr. 0 min. 17 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.dell.com)(PORT=1521)))

Services Summary...

Service "DELL" has 2 instance(s).

Instance "DELL", status UNKNOWN, has 1 handler(s) for this service...

Instance "DELL", status READY, has 1 handler(s) for this service...

Service "DELLXDB" has 1 instance(s).

Instance "DELL", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@rac1 ~]$

[oracle@rac1 ~]$ tnsping dell

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 11-APR-2018 16:03:52

Used parameter files:

/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.dell.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DELL)))

OK (0 msec)

[oracle@rac1 ~]$

[oracle@rac1 ~]$ cd /u01/dbsat/

[oracle@rac1 dbsat]$

Execute DBSAT Collector:

[oracle@rac1 dbsat]$

[oracle@rac1 dbsat]$ ./dbsat collect dbsat/oracle@DELL oracle_db

Database Security Assessment Tool version 2.0.1 (December 2017)

This tool is intended to assist in 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 12.1.0.2.0 Production on Thu Apr 12 08:39:00 2018

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.

/bin/cat: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora: No such file or directory

Warning: Exit status 256 from OS rule: sqlnet.ora

/bin/ls: cannot access /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora: No such file or directory

Warning: Exit status 512 from OS rule: ls_sqlnet.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/app/oracle/product/12.1.0/dbhome_1/bin/zip to encrypt oracle_db.json...

Enter password: dbsat

Verify password: dbsat

adding: oracle_db.json (deflated 86%)

zip completed successfully.

[oracle@rac1 dbsat]$

[oracle@rac1 dbsat]$ ls

dbsat dbsat.zip oracle_db.zip sat_collector.sql xlsxwriter

dbsat.bat Discover sat_analysis.py sat_reporter.py

[oracle@rac1 dbsat]$

The time it takes to complete depends on the hardware and the data that needs to be collected. A database that has thousands of users and roles might take hours to run. At the end of the process, you’ll be asked to provide a password twice. Do not forget it as you’ll need it when running dbsat report.

A file named oracle_db.zip is created in the directory (/u01/dbsat). There is no need to unzip the file. DBSAT reporter will take either the json file (if –n was used) or the zip file.

Execute DBSAT Reporter:

[oracle@rac1 dbsat]$

[oracle@rac1 dbsat]$ ./dbsat report -a oracle_db