Auditing an Oracle database may not be a trivial exercise, but it’s not that difficult either. Oracle includes everything you need out of the box for free. You just need to know how to put the pieces together, and this guide will show you the roadmap, explaining everything you need to know.

The Four Pillars of Database Auditing

To build an effective audit trail, you need to consider four essential types of database activity:

Activity type	What it tracks	Why it matters
Sessions	LOGON / LOGOFF	Identifies when users are connected and from where.
DDLs	Changes to the configuration, schema, users, and permissions (CREATE, ALTER, DROP).	Closes the loop on the change control process.
DMLs	Data modifications (INSERT, UPDATE, DELETE)	Primary defense against unauthorized data changes.
Queries	Data retrieval (SELECT)	Primary defense against data theft.

Note on Tracking Data Changes (Before/After Images): If you need to see the exact data values that changed (the old and new values), you have to go beyond basic auditing toward triggers or LogMiner (the better approach). For example, if you add 1 to an entire column, these mechanisms will record the values of every row in the column before and after the update. However, tracking data changes is outside the scope of this article.

To capture these four pillars natively, Oracle historically provided a few distinct frameworks: Traditional Auditing, Fine-Grained Auditing (FGA), and SYS auditing. However, they all suffer from critical deficiencies, such as traditional auditing’s inability to audit the SYS account. The best way to get complete coverage is to use Oracle’s latest mechanism: Unified Auditing, which has been standard since Oracle 12c.

Choosing What to Audit

Any native auditing comes with a significant performance overhead. Your first challenge is to figure out what exactly to record so that this overhead falls within acceptable system parameters.

There are 4 primary activities that you probably want to audit:

• Sessions: Captures all database LOGONs and LOGOFFs.
• DDLs: Tracks structural schema modifications.
• DBAs: Monitors all actions executed by accounts with the DBA role.
• Sensitive Table Access: Audits queries and modifications against critical tables (e.g., your employee or financial records).

The downloadable package includes the audit.sql script, a sample script that creates these 4 core auditing policies using Oracle Unified Auditing. You will just need to adjust the names of the sensitive tables you wish to audit, as it is pre-configured to audit the HR.EMPLOYEES table.

The amount of overhead these policies will introduce depends entirely on your database activity profile. In particular, auditing sensitive table access typically carries the highest performance impact.

Pro-Tip: It is possible to tune these policies by, for example, excluding the application user from the sensitive table access policy. Such an exclusion can significantly reduce the activity volume of audited activity and dramatically lower the performance overhead. However, verify this first, as excluding the application account may be incompatible with your compliance or security requirements.

Solving the Compliance Storage Problem

Once your policies are live, Unified Auditing begins writing records to a secure, internal table inside the database. While this is technically convenient and reasonably secure, it violates a core compliance rule: the audit data must be stored on a separate server.

Almost all regulatory frameworks dictate that audit trails cannot reside on the audited machine. The fear is simple: a rogue administrator could purge the local audit trail to cover their tracks.

The simplest solution that doesn’t require additional tools is to build a Centralized Audit Server using built-in database features.

The Architecture:

1. Create a separate, highly secured Oracle instance dedicated solely to compliance data. Access to this server and instance should be highly restricted.
2. Create a Database Link (DB Link) from this central audit server to your production database(s).
3. Schedule a periodic database job (running every 5, 10, or 60 minutes) on the central server. This job queries the UNIFIED_AUDIT_TRAIL view on the production instance via the DB Link, pulls the new records, and writes them to a local table.
4. Once the data is safely copied off-box, you can purge the production instance’s local logs using the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure.

This satisfies compliance requirements by ensuring the records are moved completely off the production server in a reasonable, near-real-time window.

Automating the Reports

Collecting data is more than half the battle, but you still have to deliver regular reports. You could use a reporting solution, or use the PHP reporting scripts in our download package. These scripts use the PHP Oracle OCI connector to run SQL against the repository and produce HTML reports.

You simply input your Audit Server’s database connection details at the top of the oracle_report.php script and, if needed, update the SQLs in the myreport.php script. When executed, the scripts query your audit tables and create a report per policy type (Sessions, DDLs, DBAs, and Sensitive Tables). The report scripts generate a clean, human-readable HTML report file that can be archived or automatically emailed to your security team.

The Limitations of DIY: When to Graduate

If you implement the blueprint above, you will have a working, compliant database auditing system without spending a dime on software licenses. For many smaller environments or databases with predictable, low-volume workloads, this DIY path is completely viable.

However, as you try to scale this architecture, you will eventually hit three hard walls that are inherent to native database auditing:

The Performance Wall (Overhead)

This is the single biggest hurdle in native auditing. Every time a user executes a SQL statement, the database engine must evaluate your audit policies and write an entry to the audit table. If you are auditing queries on highly active application tables, this overhead can severely degrade database performance.

Choosing what to audit is a constant balancing act between security and operational production requirements. Too much auditing and production becomes sluggish or completely non-responsive. Too little auditing and you miss critical indicators of a breach, potentially allowing malicious actors to silently siphon away data for years.

Sooner or later, this balancing act breaks. Minimal compliance and security demands inevitably collide with the available computing resources of an ever-growing database.

A Lack of Scalability, Forensics, Anomalies, and Blocking

The DIY approach is not the best security you can achieve. It is a Minimum Viable Product. It is simply the least you must do to satisfy auditors.

This framework lacks the power required to ingest and store massive volumes of enterprise activity over long retention periods. It also lacks the forensic capabilities required to investigate events, or proactive forensics that gives you visibility into activity profiles. It cannot identify anomalous behavior or alert you when behavior patterns suddenly shift. Finally, it lacks preventive capabilities such as blocking a DBA from touching sensitive data.

Maintenance and Script Fatigue

As your environment grows beyond a handful of databases, the operational complexity of managing this type of DIY becomes too expensive. Over time, you will also expect to get far more security value from your time investment than merely keeping auditors happy.

Why Organizations Graduate to Commercial Auditing

The significant time investment, minimal results, and constant operational friction are exactly why enterprise teams eventually transition from DIY scripts to activity control solutions like Core Audit.

A proper commercial platform completely removes these limitations by changing how data is captured and processed.

	DIY auditing	Core Audit
Capture	The database engine records the activity, which causes the performance wall.The reasons: synchronously collect all the needed information (the user, the program, etc.) and write it to disk using the transactional database engine.	A lightweight agent that captures the same activity from inside the SQL engine but without the overhead.The reason: information is copied to memory buffers and asynchronously sent off-server. Correlation, processing, and recording are performed out-of-band on the audit server.
Processing	Store the activity in Oracle tables and process it using custom PHP scripts.	Custom storage and reporting engines built to scale to billions of activities per month.Additionally, move beyond a simple compliance checkbox using anomaly detection, alerts, and deep forensic intelligence to transform database security into a proactive, strategic security initiative.
Compliance	Carefully managed auditing policies to minimize overhead, database links and jobs to copy data off-server, and PHP scripts to generate reports.	Built-in. Automatically capture and send all the activity off-server with low overhead. Leverage dedicated wizards, a reporting engine, and a scheduler that will automatically send the reports.

The Bottom Line: Take the DIY approach as far as you can. Use our deployment scripts, set up your central repository, and see if it satisfies your performance and compliance needs. But when script maintenance becomes a burden, database performance suffers, or you realize you need real security – know that the enterprise technologies to take you to the next level are ready when you are.