Configuration & Generating database report with DBSAT in Oracle 19C
Posted by Mir Sayeed Hassan on April 6th, 2024
Configuration & Generating database report with DBSAT in Oracle 19C
Brief about DBSTAT:
– The Oracle Database Security Assessment Tool (DBSAT) is a command-line tool focused on detecting potential security vulnerabilities in database.
– This tool provides insight into the current status of users, roles, permissions, and security control policies to promote successful approaches and database security risks.
– Download this DBSTAT Tool by using yhe DOC ID: 2138254.1
– Also this DBSTAT check oracle proven Database Security best practices as per standard such as “CIS benchmark recommendations & STIG rules”
– This is the User Guide for the current release (3.1)
Create a directory to setup the dbsat
[oracle@ora19cdb ~]$ mkdir /u01/dbsat/
[oracle@ora19cdb ~]$ cd /u01/dbsat/
[oracle@ora19cdb dbsat]$ ll total 89820 -rwxrwxrwx. 1 root root 46264143 Apr 6 05:11 dbsat.zip
Unzip the download DBSTAT.ZIP file
[oracle@ora19cdb dbstat]$ unzip dbsat.zip
Create the user and provide the appropriate privileges
[oracle@ora19cdb dbstat]$ vi DBSAT_User.sql create user dbsat_user identified by dbsat_user; grant create session to dbsat_user; grant select_catalog_role to dbsat_user; grant select on sys.registry$history to dbsat_user; grant read on sys.dba_audit_mgmt_config_params to dbsat_user; grant select on sys.dba_users_with_defpwd to dbsat_user; grant read on sys.dba_credentials to dbsat_user; grant execute on sys.dbms_sql to dbsat_user; grant audit_viewer to dbsat_user; // 19c and later grant capture_admin to dbsat_user;// 19c and later covers sys.dba_priv_captures, sys.priv_capture$, sys.capture_run_log$ :wq
Verify
[oracle@ora19cdb dbstat]$ cat DBSAT_User.sql create user dbsat_user identified by dbsat_user; grant create session to dbsat_user; grant select_catalog_role to dbsat_user; grant select on sys.registry$history to dbsat_user; grant read on sys.dba_audit_mgmt_config_params to dbsat_user; grant select on sys.dba_users_with_defpwd to dbsat_user; grant read on sys.dba_credentials to dbsat_user; grant execute on sys.dbms_sql to dbsat_user; grant audit_viewer to dbsat_user; // 19c and later grant capture_admin to dbsat_user;// 19c and later covers sys.dba_priv_captures, sys.priv_capture$, sys.capture_run_log$
Check the Container and Pluggable database with db status.
SYS @ ora19cdb > show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- -------------------------------------- 2 PDB$SEED READ ONLY NO 3 PDB_TEST1 READ WRITE NO
Connect to the Pluggable database “PDB_TEST1”
SYS @ ora19cdb > alter session set container=PDB_TEST1; Session altered.
Run the above created script
SYS @ ora19cdb > @DBSAT_User.sql User created. Grant succeeded. Grant succeeded. Grant succeeded. Grant succeeded. Grant succeeded. Grant succeeded. Grant succeeded.
Now run the Report Collector.
[oracle@ora19cdb dbstat]$ ./dbsat collect dbsat_user/dbsat_user@PDB_TEST1 PDB_TEST1_DBSAT_Report Database Security Assessment Tool version 3.1 (Jan 2024) 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 Sat Apr 6 06:57:55 2024 Version 19.18.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.18.0.0.0 Setup complete. SQL queries complete. /bin/ls: cannot access /u01/app/oracle/product/19.3.0/db_1/bin/tfactl: No such file or directory Warning: Exit status 256 from OS rule: executable_permission /bin/cat: /u01/app/oracle/product/19.3.0/db_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/19.3.0/db_1/network/admin/sqlnet.ora: No such file or directory Warning: Exit status 512 from OS rule: ls_sqlnet.ora Warning: Exit status 256 from OS rule: dbcs_status /bin/cat: /u01/app/oracle/product/19.3.0/db_1/network/admin/cman.ora: No such file or directory Warning: Exit status 256 from OS rule: cman.ora /bin/cat: /u01/app/oracle/product/19.3.0/db_1/ldap/admin/fips.ora: No such file or directory Warning: Exit status 256 from OS rule: fips1.ora /bin/cat: /fips.ora: No such file or directory Warning: Exit status 256 from OS rule: fips2.ora /bin/ls: cannot access /u01/app/oracle/product/19.3.0/db_1/rdbms/log/diag: No such file or directory Warning: Exit status 512 from OS rule: diag_dest_home OS commands complete. Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.18.0.0.0 DBSAT Collector completed successfully. Calling /u01/app/oracle/product/19.3.0/db_1/bin/zip to encrypt PDB_TEST1_DBSAT_Report.json... Enter password: ******* Verify password: ******* adding: PDB_TEST1_DBSAT_Report.json (deflated 79%) zip completed successfully.
Note: Provide the password created by above DBSAT Script., Here Password: dbsat_user
To generate the Report., Check the updated Java version, set the JAVA HOME & Run the below command
Check the Java Version
[oracle@ora19cdb db_1]$ java -version openjdk version "1.8.0_242" OpenJDK Runtime Environment (build 1.8.0_242-b08) OpenJDK 64-Bit Server VM (build 25.242-b08, mixed mode)
Set the JAVA HOME
[oracle@ora19cdb dbstat]$ JAVA_HOME=/u01/app/oracle/product/19.3.0/db_1/jdk;export JAVA_HOME
[oracle@ora19cdb dbstat]$ ./dbsat report PDB_TEST1_DBSAT_Report Database Security Assessment Tool version 3.1 (Jan 2024) 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. DBSAT Reporter ran successfully. Calling /usr/bin/zip to encrypt the generated reports... Enter password: ******* Verify password: ******* zip warning: PDB_TEST1_DBSAT_Report_report.zip not found or empty adding: PDB_TEST1_DBSAT_Report_report.txt (deflated 74%) adding: PDB_TEST1_DBSAT_Report_report.html (deflated 82%) adding: PDB_TEST1_DBSAT_Report_report.xlsx (deflated 2%) adding: PDB_TEST1_DBSAT_Report_report.json (deflated 79%) zip completed successfully.
Unzip the above generated report.
[oracle@ora19cdb dbstat]$ unzip -l PDB_TEST1_DBSAT_Report_report.zip Archive: PDB_TEST1_DBSAT_Report_report.zip Length Date Time Name --------- ---------------------------------------------------------------- 141877 04-06-2024 07:07 PDB_TEST1_DBSAT_Report_report.txt 243491 04-06-2024 07:07 PDB_TEST1_DBSAT_Report_report.html 37896 04-06-2024 07:07 PDB_TEST1_DBSAT_Report_report.xlsx 179217 04-06-2024 07:07 PDB_TEST1_DBSAT_Report_report.json --------- ------- 602481 4 files
Extract the report to see the contents.
[oracle@ora19cdb dbstat]$ unzip PDB_TEST1_DBSAT_Report_report.zip PDB_TEST1_DBSAT_Report_report.txt Archive: PDB_TEST1_DBSAT_Report_report.zip [PDB_TEST1_DBSAT_Report_report.zip] PDB_TEST1_DBSAT_Report_report.txt password: inflating: PDB_TEST1_DBSAT_Report_report.txt
View the complete database report
[oracle@ora19cdb dbstat]$ more PDB_TEST1_DBSAT_Report_report.txt ### Oracle Database Security Assessment - Highly Sensitive ### * Assessment Date & Time * Date of Data Collection Date of Report Reporter Version ---------------------------------- ---------------------------------- --------------------- Sat Apr 06 2024 06:57:58 UTC-04:00 Sat Apr 06 2024 07:07:48 UTC-04:00 3.1 (Jan 2024) - b73a * Database Identity * Name Container (Type:ID) Platform Database Role Log Mode Created -------- ------------------- ---------------- ------------- ------------ ---------------------------------- ORA19CDB PDB_TEST1 (PDB:3) Linux x86 64-bit PRIMARY NOARCHIVELOG Sun Mar 24 2024 07:26:25 UTC-04:00 ### Summary ### Section Pass Evaluate Advisory Low Risk Medium Risk High Risk Total Findings --------------------------- ---- -------- -------- -------- ----------- --------- -------------- Basic Information 0 0 0 0 0 1 1 User Accounts 11 8 1 2 1 0 23 Privileges and Roles 16 13 1 0 0 0 30 Authorization Control 0 3 1 0 0 0 4 Fine-Grained Access Control 0 0 5 0 0 0 5 Auditing 0 3 11 0 0 0 14 Encryption 0 4 0 0 0 0 4 Database Configuration 10 9 0 1 1 0 21 Network Configuration 1 0 1 0 1 0 3 Operating System 2 4 0 1 2 0 9 Total 40 44 20 4 5 1 114 ### Basic Information ### * Database Version * Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.18.0.0.0 Security options used: (none) * Security Features Utilized * Feature Currently Used ------------------------------------- -------------- USER AUTHENTICATION Password Authentication Yes Global Authentication No External Authentication No AUTHORIZATION CONTROL Database Vault No Database Vault Operations Control No FINE-GRAINED ACCESS CONTROL Virtual Private Database No Real Application Security No Label Security No Data Redaction No Transparent Sensitive Data Protection No AUDITING Fine Grained Audit No Traditional Audit Yes ENCRYPTION Tablespace Encryption No Column Encryption No Network Encryption No * Patch Check * - The Oracle Database should be patched Status: High Risk Summary: Oracle Database version is supported but latest patch is missing. Latest comprehensive patch has not been applied. Details: Latest patch not applied for a supported database version. Binary Patch Inventory: Patch ID (Comprehensive): 25098466 (created January 2023) Installed SQL Patch History: Action time: Sun Mar 24 2024 07:48:14 Action: APPLY Version: 19.18.0.0.0 Description: DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) (34765931) Remarks: Unsupported commercial and database systems should not be used because fixes to newly identified bugs will not be implemented by the vendor. The lack of support can result in potential vulnerabilities. Systems at unsupported servicing levels or releases will not receive security updates for new vulnerabilities, which leaves them subject to exploitation. When maintenance updates and patches are no longer available, the database software is no longer considered supported and should be upgraded or decommissioned. It is vital to keep the database software up-to-date with security fixes as they are released. Oracle issues comprehensive patches in the form of Release Updates on a regular quarterly schedule. These updates should be applied as soon as they are available. References: Oracle Best Practice CIS Benchmark: Recommendation 1.1 DISA STIG: V-237697, V-237748, V-251802 ### User Accounts ### Note: Predefined Oracle accounts which are schema-only or locked are not included in this report. To include all user accounts, run the report with the -a option. * User Accounts * User Name Profile Status Authentication Type Default Tablespace Oracle Defined ---------- ------- ------ ------------------- ------------------ -------------- DBSAT_USER DEFAULT OPEN PASSWORD USERS No PDBADMIN DEFAULT OPEN PASSWORD USERS No SYSTEM DEFAULT OPEN PASSWORD SYSTEM Yes * Users with DEFAULT Profile * - User accounts using the DEFAULT profile Status: Evaluate Summary: Found 3 users using the DEFAULT profile. Details: Following limits are defined by the DEFAULT profile: PASSWORD LIMITS: PASSWORD_VERIFY_FUNCTION: NULL INACTIVE_ACCOUNT_TIME: UNLIMITED PASSWORD_REUSE_TIME: UNLIMITED PASSWORD_REUSE_MAX: UNLIMITED PASSWORD_LOCK_TIME: 1 FAILED_LOGIN_ATTEMPTS: 10 PASSWORD_ROLLOVER_TIME: 0 PASSWORD_LIFE_TIME: 180 PASSWORD_GRACE_TIME: 7 RESOURCE LIMITS: CPU_PER_CALL: UNLIMITED COMPOSITE_LIMIT: UNLIMITED SESSIONS_PER_USER: UNLIMITED LOGICAL_READS_PER_SESSION: UNLIMITED CONNECT_TIME: UNLIMITED IDLE_TIME: UNLIMITED PRIVATE_SGA: UNLIMITED CPU_PER_SESSION: UNLIMITED LOGICAL_READS_PER_CALL: UNLIMITED Remarks: The CIS benchmark recommends against assigning users to the DEFAULT profile as the DEFAULT profile has UNLIMITED settings for all resources, allowing the user to launch a denial-of-service attack by exhausting all resources. The Oracle best practice is to update the DEFAULT profile to match organizational standards and use custom profiles to accommodate accounts that are an exception to the normal organizational standard. New database users are assigned the DEFAULT profile unless another profile is explicitly assigned. Even though profiles can be used to limit resource consumption, such as CPU and memory, Oracle Database Resource Manager is recommended for its flexible means of managing and tracking resource use. References: CIS Benchmark: Recommendation 4.4 * Users with Default Passwords * - User accounts should not have default passwords Status: Pass Summary: No unlocked user accounts are using a default password. Remarks: Default passwords for predefined Oracle accounts are well known and provide a trivial means of entry for attackers. Database or account administrators should also change well-known passwords for locked accounts. Having default passwords can lead to unauthorized data manipulation and theft of confidential information. Note that if a script creates the database and the SYS or SYSTEM user password remains unchanged, these users are considered to possess a References: Oracle Best Practice CIS Benchmark: Recommendation 4.1 DISA STIG: V-237698 * Users with Expired Passwords * - User accounts should not be inactive Status: Pass Summary: No unlocked users found with password expired for more than 30 days. Remarks: Password expiration is used to ensure that users change their passwords regularly. Unlocked accounts with an expired password can present a security risk, especially as those accounts age. Although the password is expired, if the account is unlocked it can easily be used by anyone knowing the old password. You should investigate accounts that have been unused for an extended period to determine whether they should remain active. References: Oracle Best Practice * Inactive Users * - Infrequently used accounts should be locked or dropped Status: Evaluate Summary: Found 2 user accounts with unlimited INACTIVE_ACCOUNT_TIME. No unlocked users inactive for more than 35 days found. Details: Users with unlimited INACTIVE_ACCOUNT_TIME: DBSAT_USER, PDBADMIN Remarks: If a user account is no longer in use, it unnecessarily increases the system's attack surface while providing no corresponding benefit. Furthermore, unauthorized use is less likely to be noticed when no one uses the account regularly. Investigate accounts not used for more than 35 days to determine whether they should remain active. A solution is to set INACTIVE_ACCOUNT_TIME in the profiles assigned to users to automatically lock accounts that have not logged in to the database instance for a specified number of days, for example, 35 days. Audit infrequently-used accounts for unauthorized activities. References: Oracle Best Practice CIS Benchmark: Recommendation 3.9 DISA STIG: V-237725 * Sample Schemas * - Sample schemas should be dropped Status: Pass Summary: No sample schemas found. Remarks: Sample schemas are well-known accounts provided by Oracle to serve as simple examples for developers. They generally serve no purpose in a production database and should be removed because they unnecessarily increase the attack surface of the database. References: Oracle Best Practice CIS Benchmark: Recommendation 4.2 DISA STIG: V-220284 ................... ................... ................... Contine to review the complete report of the database
If you don’t want this user to exit in database., you can drop this user from the Pluggable database as shown below.
Login to pluggable database.
SYS @ ora19cdb > alter session set container=PDB_TEST1; Session altered.
SYS @ ora19cdb > drop user dbsat_user; User dropped.