In one of my last blog post SQL Toolbox for simplified Oracle Unified Audit Data Analysis, I introduced a set of scripts designed to streamline Oracle Unified Audit data analysis. These scripts, now available on my GitHub repository oehrlis/oradba, have received positive feedback. Building on that, I’d like to explore an alternative approach to augment your audit data analysis process with SQL Developer Reports.
SQL Plus is available on any database server, so you can quickly perform an initial analysis of audit data using the SQL scripts presented without any additional requirements. It should be noted, however, that while SQLPlus-based scripts are valuable tools, they are not always perfectly suited to all analytical requirements. In certain scenarios, more graphical analysis and the ability to drill down on specific data points are more helpful. This also includes simply getting an overview and drilling down in some cases. To fulfill these differentiated requirements, I use Oracle SQL Developer, a versatile tool that offers the ability to create custom reports. These reports provide a graphical and interactive way to analyze your audit data. In this blog post, I will provide an overview of the reports available and briefly explain how you can seamlessly integrate them into your audit data analysis workflow.
SQL Developer Reports
The Unified Audit Asessment Reprorts for SQL Developers are divided into the following categories:
- Audit Charts A series of audit reports with diagrams and drill-downs, e.g. audit events per day and hour. These reports are ideal for an initial visual overview of the audit events. However, the runtime of the queries depends heavily on the amount of audit data. In some cases, the reports can be limited to a few days.
- Audit Configuration Reports on audit configuration, memory usage, segments, audit policies, cleanup events, cleanup jobs and more. These reports provide a good overview of what is configured and how much audit data is already available.
- Audit Sessions Overview of the various audit sessions with drill-down to the individual events of a session. Reports are available for the various audit types, e.g. proxy session, standard, RMAN, DataPump and more. These reports are ideal for checking what exactly was executed within a single session.
- Generate Statements These are not really reports as such, but queries to generate the various statements for creating, deleting, enabling or disabling the audit policies. Corresponding authorization on DBMS_METADATA is required.
- Miscellaneous All types of audit reports that have not yet been categorized. Mainly things that are still under development.
- Top Audit Events Provide information about audit events that are selected and organized according to various criteria. This information can help you to identify audit events that occur more frequently than expected or generate more data than expected.
The queries were created to the best of our knowledge and belief. Nevertheless, the individual queries may take a little more time, depending on the amount of data. If necessary, you can copy the reports and optimize / modify them accordingly. Suggestions for improvements are of course always welcome.
Audit Reports in Detail
Audit Charts
| Report | Description |
|---|---|
| Audit Events by Day | Chart for the number of audit events per day with the option to drill down by audit events per hour. |
| Audit Events by User | Chart for the number of audit events per database user with the option to drill down audit events by audit policy, action, user host and audit policy with actions. |
Audit Configuration
| Report | Description |
|---|---|
| Storage Usage | This report provides comprehensive details about the usage and configuration of the audit store, focusing especially on the DB ID and the archiving timestamp. It enables you to assess whether audit data can be deleted based on its age or if it originates from a different database. Additionally, the report offers a drill-down feature to view partition information, storage details, and purge statements. Similar to sdua_usage.sql, sdua_stostm.sql and sdua_prgstm.sql. |
| Clean up Events | Displays the audit cleanup event from DBA_AUDIT_MGMT_CLEAN_EVENTS. |
| Clean up Jobs | Displays the audit cleanup jobs from DBA_AUDIT_MGMT_CLEANUP_JOBS |
| Configuration | Displays the audit configuration from DBA_AUDIT_MGMT_CONFIG_PARAMS |
| Last Archive Timestamp | Displays the audit archive timestamp from DBA_AUDIT_MGMT_LAST_ARCH_TS |
| Unified Audit Policies | This report offers a comprehensive overview of unified audit policies, drawing data from the views AUDIT_UNIFIED_POLICIES and AUDIT_UNIFIED_ENABLED_POLICIES. It facilitates a deeper understanding of the configuration and current status of audit policies within the system, similar to the script saua_pol.sql. The report includes extensive drill-down options, allowing for detailed analysis based on comments, actions, and statements such as AUDIT, NOAUDIT, CREATE, and DROP, as well as events and inactive policies. |
Audit Sessions
| Report | Description |
|---|---|
| by any type | Displays audit sessions for any audit type, similar in function to the script saua_as.sql. With drill-down to session details. |
| by type DataPump | Displays audit sessions for audit type DataPump, similar in function to the script saua_asdp.sql. With drill-down to session details. |
| by type DB Vault | Displays audit sessions for audit type Database Vault, similar in function to the script saua_asdbv.sql. With drill-down to session details. |
| by type Direct path API | Displays audit sessions for audit type Direct path API based on UNIFIED_AUDIT_TRAIL. With drill-down to session details. |
| by type FGA | Displays audit sessions for audit type FGA, similar in function to the script saua_asfga.sql. With drill-down to session details |
| by type Protocol | Displays audit sessions for audit type Protocol. With drill-down to session details. |
| by type RMAN_AUDIT | Displays audit sessions for audit type RMAN_AUDIT, similar in function to the script saua_asbck.sql. With drill-down to session details. |
| by type Standard | Displays audit sessions for audit type standard, similar in function to the script saua_asstd.sql. With drill-down to session details. |
| Proxy Sessions | Displays audit proxy sessions for audit type Standard based on UNIFIED_AUDIT_TRAIL. With drill-down to session details. |
| Session Details | List detail of a particular session. |
| Session Overview | Displays audit sessions for any audit type, similar in function to the script saua_as.sql. |
Generate Statements
| Report | Description |
|---|---|
| Create all audit policies | Generates statements to recreate all audit policies as currently configured in AUDIT_UNIFIED_ENABLED_POLICIES. |
| Disable all audit policies | Generates statements to disable all audit policies as currently configured in AUDIT_UNIFIED_ENABLED_POLICIES. |
| Drop all audit policies | Generates statements to remove all audit policies, excluding those maintained by Oracle. |
| Enable all audit policies | Generates statements to enable all audit policies as currently configured in AUDIT_UNIFIED_ENABLED_POLICIES. |
Top Audit Events
| Report | Description |
|---|---|
| Events by Users | This query generates a summary of audit events grouped by database username in the unified audit trail. It counts the total number of events for each user and sorts the results in descending order, showcasing the users with the highest frequency of audit events. |
| Top Events by Action | Displays a ranking of Action based on the frequency of their associated events in the unified audit trail, offering insights into the most audited databases. Comparable to the script saua_teact.sql. |
| Top Events by Application Context | Displays a ranking of Application Context based on the frequency of their associated events in the unified audit trail, offering insights into the most audited databases. |
| Top Events by Audit Type | Displays a ranking of Audit Type based on the frequency of their associated events in the unified audit trail, offering insights into the most audited databases. |
| Top Events by Client | Displays a ranking of Client Program based on the frequency of their associated events in the unified audit trail, offering insights into the most audited databases. Comparable to the script saua_tehost.sql. |
| Top Events by Client Program | Displays a ranking of Client Program based on the frequency of their associated events in the unified audit trail, offering insights into the most audited databases. Comparable to the script saua_tecli.sql. |
| Top Events by DBID | Displays a ranking of database IDs (DBIDs) based on the frequency of their associated events in the unified audit trail, offering insights into the most audited databases. Comparable to the script saua_tedbid.sql. |
| Top Events by External User ID | Presents a ranking of External User IDs by the frequency of their associated events in the unified audit trail, providing insights into the most audited user identities. |
| Top Events by Global User ID | Presents a ranking of Global User IDs by the frequency of their associated events in the unified audit trail, providing insights into the most audited user identities. |
| Top Events by none Oracle Object Name | Shows a ranking of non-Oracle object names based on the frequency of associated audit events in the unified audit trail, highlighting the most audited non-Oracle objects. Comparable to the script saua_teobj.sql. |
| Top Events by Object Name | Displays a ranking of object names based on the frequency of associated events in the unified audit trail, offering insights into the most audited objects. Similar to the script saua_teobj.sql. |
| Top Events by Object Schema | Shows a ranking of Object Schemas by event frequency, emphasizing the policies most often triggered in the unified audit trail. Comparable to the script saua_teown.sql. |
| Top Events by OS User | Displays a ranking of OS User based on the number of associated events, highlighting the most frequently triggered policies in the unified audit trail. Comparable to the script saua_teosusr.sql. |
| Top Events by polices | Displays a ranking of audit policies based on the number of associated events, highlighting the most frequently triggered policies in the unified audit trail. Similar in function to the script saua_tepol.sql. |
| Top Events by SQL Text | Summarizes and ranks SQL statements from the unified audit trail by their event frequency, highlighting the most frequently executed queries. |
Add Custom Reports in SQL Developer
The SQL Developer already offers many predefined reports. Unfortunately not many in the database security area. However, it is possible to extend the predefined reports with your own reports. See also SQL Developer Reports. The Unified Audit Assessment Reports presented here are bundled in an XML file and available via xx. This XML file can be added relatively easily in SQL Developer. You have the choice to do this as a User Defined Report or Shared Report.
User Defined Reports
Adding user defined reports is relatively simple. Just select the context menue in User Defined Reports and click Open Reports… and select your XML file.
User-defined reports can be modified, renamed, and saved after changes. However, updating these reports directly from an XML file is not straightforward. To incorporate updates from an XML file, the reports must first be removed and then reloaded.
Database / Shared Reports
Database respectivily Shared Reports have a distinct setup process. They must be added through preferences, after which they appear under the Shared Reports tree. Unlike other reports, these cannot be modified directly in SQL Developer, as it only loads the XML file during startup. However, if the XML file is updated — for instance, by downloading a new version — the reports will be updated upon the next restart. For further customization, these reports can still be copied to User Defined Reports for individual modifications.
To add a Shared Report, follow these steps:
- Open Preferences in SQL Developer
- Navigate to User Defined Extensions
- Click Add Row and configure the following:
- Set Type to REPORT
- Provide the location path to your XML file.
The screenshot below illustrates an example of this process
Conclusion
The SQL Developer reports presented here, as well as the SQL scripts, are ideal tools for evaluating local audit data quickly and easily. Especially when it comes to getting a first insight into the collected information or when developing new audit policies. For a company-wide overview in the area of database security and auditing, however, there is no way around a central solution. This includes the following solution approaches:
- Oracle Data Safe A cloud-based security service that automates sensitive data discovery, data masking, database security assessments, and user risk assessments. It’s designed to secure Oracle databases and enhance overall data protection.
- Oracle Audit Vault and Database Firewall Provides a comprehensive security solution that includes monitoring, analysis, and blocking of unauthorized database activities. It consolidates audit data from multiple sources and offers real-time alerts and reports.
- Custome Solution Involves developing a bespoke Data Warehouse (DWH) for audit data or utilizing powerful tools like Splunk or Elasticsearch. This approach allows for tailored data aggregation, reporting, and analysis, meeting specific organizational needs. Splunk and Elasticsearch offer advanced data indexing, search capabilities, and visualization tools, making them ideal for handling complex audit data.
I intend to go into more detail about centralized audit solutions in future posts. In the meantime, I hope you can use my scripts to analyze local audit data.
Additional Resources
Some links and references related to this topic.
- Oracle® SQL Developer User’s Guide SQL Developer Reports
- GitHub repository oehrlis/oradba
- Extend your SQL Developer Slides / Webinar by Philipp Salvisberg
