How to run DBSAT against Oracle RDS database

Posted in: Cloud, DBA Lounge, Oracle, Technical Track

The scope of this article is to show how to run DBSAT (Oracle Database Security Assessment Tool) against an Amazon RDS Oracle instance. This is based on a real-life execution. The DBSAT version used was 2.0.2 over an RDS database version 11.2.0.4. For normal, on-premises databases, the DBSAT Collector and Reporter are most often run on the database server itself. However, with the RDS Database, you don’t have access to the host server and must instead run DBSAT from a client-server configuration, the same server where you run SQL Plus to connect to your RDS database. For example:

1) Install DBSAT:

If you don’t have binaries already, then get DBSAT from the Oracle support website (license-free for all Oracle clients). The following is the Document ID:

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

User Guide Document:

https://docs.oracle.com/cd/E93129_01/SATUG/toc.htm#SATUG-GUID-EF4C8FC3-1EF2-45AC-BE07-26CCFA059E53

Ensure the server where you installed Oracle Client has the appropriate Python version (and if you are planning on running the Discoverer option, check Java version as well). This version of DBSAT (2.0.2) requires Python 2.6 or later to run. The Discoverer is a Java program, it will need the Java Runtime Environment (JRE) 1.8 (jdk8-u172) or later to run (only required if you need to run Discoverer option to find sensitive / private data) :


[MyOSuser@JumpServer ~]$ hostname
JumpServer.domain.net
[MyOSuser@JumpServer ~]$
[MyOSuser@JumpServer ~]$ echo $ORACLE_HOME
/domain/app/oracle/product/11.2.0/client_1
[MyOSuser@JumpServer ~]$
[MyOSuser@JumpServer ~]$ python -V
Python 2.6.6
[MyOSuser@JumpServer ~]$
[MyOSuser@JumpServer ~]$ java -fullversion
openjdk full version "1.8.0_191-b12"
[MyOSuser@JumpServer ~]$

Installation is pretty simple, just unzip the binaries. Since Oracle is releasing newer versions of DBSAT, I suggest you create a folder that includes the version in the name, ie “ /DBSAT_202 ”, so in the future, if you want to run newer version, you can create a new folder named with the next version and so on.

Once this is all done, the next step is to create a database user with the required privileges to run DBSAT. This is especially important when running on RDS because you can’t use sys or system privileged users.

Check the documentation and ensure you grant the required privileges to the DBSAT database user according to the Oracle Database version:

https://docs.oracle.com/cd/E93129_01/SATUG/toc.htm#SATUG-GUID-EF4C8FC3-1EF2-45AC-BE07-26CCFA059E53

The following is a summary of required privileges and roles:


CREATE SESSION*
READ or SELECT on SYS.REGISTRY$HISTORY
Role SELECT_CATALOG_ROLE*
Role DV_SECANALYST* (if Database Vault is enabled)
Role AUDIT_VIEWER (12c and later)
Role CAPTURE_ADMIN (12c and later)
READ or SELECT on SYS.DBA_USERS_WITH_DEFPWD (11g and later)
READ or SELECT on AUDSYS.AUD$UNIFIED (12c and later)

Full version of the RDS instance:


SQL> select * from v$version;
BANNER
---------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>

Creation of user named “DBSAT” and grant of privileges:


SQL> show user
USER is "rdsdbadmin"
SQL>
SQL> create user DBSAT identified by ******;
User created.
SQL>
SQL> grant CREATE SESSION to DBSAT;
Grant succeeded.
SQL>
SQL> grant SELECT_CATALOG_ROLE to DBSAT;
Grant succeeded.
SQL>
-- SQL> grant SELECT on SYS.REGISTRY$HISTORY to DBSAT;
=> above fails on RDS, so need following syntax:
SQL>
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'REGISTRY$HISTORY',
p_grantee => 'DBSAT',
p_privilege => 'SELECT');
end;
/
PL/SQL procedure successfully completed.
SQL>
-- SQL> grant SELECT on SYS.DBA_USERS_WITH_DEFPWD to DBSAT;
=> above fails on RDS, so need following syntax:
SQL>
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'DBA_USERS_WITH_DEFPWD',
p_grantee => 'DBSAT',
p_privilege => 'SELECT');
end;
/
PL/SQL procedure successfully completed.
SQL>

RDS Documentation for the above syntax for your further reference :

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.System.html

 

2) Run The Collector:

First, you need to export the Oracle_Home and then run the DBSAT collector. Note that error “ORA-20002: Complete without OS Commands” is expected because on RDS, there is no access to the Host where DB is actually running, hence no OS statements can be executed. DBSAT still provides recommendations for database level:


[MyOSuser@JumpServer DBSAT_202]$ pwd
/export/home/MyOSuser/DBSAT_202
[MyOSuser@JumpServer DBSAT_202]$
[MyOSuser@JumpServer DBSAT_202]$ ./dbsat collect DBSAT@rdsdbname dbsat_RDS_rdsdbname_userDBSAT
Database Security Assessment Tool version 2.0.2 (May 2018)
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 11.2.0.4.0 Production on Mon Feb 11 14:33:43 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Setup complete.
SQL queries complete.
OS Commands Skipped.
BEGIN
*
ERROR at line 1:
ORA-20002: Complete without OS Commands.
ORA-06512: at lne 4
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
DBSAT Collector completed successfully.
Calling /domain/app/oracle/product/11.2.0/client_1/bin/zip to encrypt dbsat_RDS_rdsdbname_userDBSAT.json...
Enter password:
Verify password:
adding: dbsat_RDS_rdsdbname_userDBSAT.json (deflated 88%)
zip completed successfully.
[MyOSuser@JumpServer DBSAT_202]$

 

3) Run The Reporter:

You can execute the Reporter from any server or machine, no need for connectivity to DB, just ensure you have copied the json output file from the Collector, for example: “dbsat_RDS_rdsdbname_userDBSAT.json“:


[MyOSuser@JumpServer DBSAT_202]$ ./dbsat report -a ./dbsat_RDS_rdsdbname_userDBSAT
Database Security Assessment Tool version 2.0.2 (May 2018)
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.
Archive: dbsat_RDS_rdsdbname_userDBSAT.zip
[dbsat_RDS_rdsdbname_userDBSAT.zip] dbsat_RDS_rdsdbname_userDBSAT.json password:
inflating: dbsat_RDS_rdsdbname_userDBSAT.json
DBSAT Reporter ran successfully.
Calling /usr/bin/zip to encrypt the generated reports...
Enter password:
Verify password:
zip warning: dbsat_RDS_rdsdbname_userDBSAT_report.zip not found or empty
adding: dbsat_RDS_rdsdbname_userDBSAT_report.txt (deflated 87%)
adding: dbsat_RDS_rdsdbname_userDBSAT_report.html (deflated 89%)
adding: dbsat_RDS_rdsdbname_userDBSAT_report.xlsx (deflated 4%)
adding: dbsat_RDS_rdsdbname_userDBSAT_report.json (deflated 90%)
zip completed successfully.
[MyOSuser@JumpServer DBSAT_202]$

Note: Of the four reports created, you have output with HTML, TXT, XLSX and JSON formats.

We have now two zip files generated, both encrypted with a password. One has the Collector output, which is used by the Reporter process to actually generate the reports, the other has the four reports in the mentioned output formats:


[MyOSuser@JumpServer DBSAT_202]$ ls -ltr dbsat_RDS_rdsdbname*
-rw------- 1 MyOSuser users 17935 Feb 21 14:35 dbsat_RDS_rdsdbname.zip
-rw------- 1 MyOSuser users 87094 Feb 21 14:40 dbsat_RDS_rdsdbname_report.zip
[MyOSuser@JumpServer DBSAT_202]$

 

4) Run the Discoverer:

In this particular use case, the Discoverer was not part of the scope, hence not executed, but the syntax after configuring the connectivity into .config file, would be like:


dbsat discover -c /export/home/MyOSuser/DBSAT_202/Discover/conf/RDS_dbsat.config dbsat_RDS_rdsdbname_Discoverer

The file “sample_dbsat.config” needs to be updated in order to connect to the database. In the above sample, I would copy it as “RDS_dbsat.config” and edit it. You will also see the search patterns for sensitive data into the file sensitive_en.ini, I have a full blog dedicated only on how to run the Discoverer component: https://blog.pythian.com/oracle-dbsat-discoverer-feature/

 

References:

https://blogs.oracle.com/cloudsecurity/database-security-assessment-tool-dbsat-21-is-now-available

https://www.oracle.com/a/tech/docs/dbsec/dbsat/dbsat-public-faq-mar2019.pdf

https://blog.pythian.com/oracles-database-security-assessment-tool-dbsat-version-2-2-0-1/

https://blog.pythian.com/oracle-dbsat-discoverer-feature/

https://blog.pythian.com/running-new-oracle-dbsat-version-2-1-0-against-autonomous-database/

email

Interested in working with Roy? Schedule a tech call.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *