Oracle Auditing & Compliance: Security, Tracking & Legal Guardrails

TAKEAWAYS:

• Oracle auditing records who did what, when, and where in the database, using Standard, Fine-Grained, and Unified Auditing methods.
• Audit trails are critical for compliance (e.g., SOX, GDPR) and forensic investigations, stored in the DBA_AUDIT_TRAIL or UNIFIED_AUDIT_TRAIL views.
• Fine-Grained Auditing (FGA) lets you audit specific rows/columns (e.g., tracking salary changes in HR tables).
• Unified Auditing (12c+) consolidates all audit data into one table, simplifying analysis and reducing overhead.
• Audit policies can be set at the database, schema, or object level, with options to audit by access, by session, or by statement.
• Compliance requires auditing DML (INSERT/UPDATE/DELETE), DDL (CREATE/DROP), and login/logoff events, with retention policies for legal holds.

1. Why Auditing Matters: Real-World Scenarios

Oracle auditing isn’t just theory—it’s how banks, e-commerce platforms, and government systems prevent fraud and meet laws. Here’s how Nepali and global companies use it:

🔹 Example 1: Nabil Bank’s Loan Fraud Prevention

• Problem: Fraudsters tried to alter loan records (e.g., changing interest rates or principal amounts).
• Solution: Nabil Bank enabled Fine-Grained Auditing (FGA) on the LOAN_ACCOUNTS table to track:
  • Who modified loan details.
  • What exact SQL statement was used (e.g., UPDATE LOAN_ACCOUNTS SET INTEREST_RATE = 5).
  • Timestamp and client IP address.
• Outcome: When an employee was caught altering a loan for personal gain, the audit trail proved their actions, leading to termination and legal action.

🔹 Example 2: eSewa’s Payment Dispute Resolution

• Problem: Users reported unauthorized transactions (e.g., someone else using their eSewa account).
• Solution: eSewa uses Unified Auditing to log:
  • Every PAYMENT_TRANSACTION record change.
  • Failed login attempts (e.g., brute-force attacks).
  • Admin overrides (e.g., refunds or chargebacks).
• Outcome: When a user claimed their Rs. 50,000 was stolen, eSewa’s audit logs showed the transaction was initiated from a different device/IP, proving it was the user’s fault.

🔹 Example 3: Google Cloud’s GDPR Compliance

• Problem: Under GDPR, users can request their data be deleted ("right to erasure").
• Solution: Google’s BigQuery (built on Oracle-like principles) uses database auditing to:
  • Log all DELETE operations on user data.
  • Track who requested deletions (e.g., a user via their Gmail account).
  • Retain logs for 7 years (GDPR requirement).
• Outcome: When a European user requested deletion of their search history, Google could prove compliance by showing the audit trail.

2. Oracle Auditing Architecture: How It Works

Oracle auditing is built into the database engine. Here’s the flow:

flowchart TD
    A["User Action\n(e.g., UPDATE salary)"] -->|"SQL Statement"| B["Oracle Database Engine"]
    B --> C["Audit Policy Check\n(Is this action audited?)"]
    C -->|"Yes"| D["Audit Record Created\n(Stored in audit trail)"]
    C -->|"No"| E["Action Executed\n(No log)"]
    D --> F["DBA_AUDIT_TRAIL\nor\nUNIFIED_AUDIT_TRAIL"]
    F --> G["Query Views\n(DBA_AUDIT_OBJECT\nDBA_FGA_AUDIT_TRAIL)"]
    G --> H["Analyze with SQL\nor Export for Forensics"]

Key Components:

3. Types of Auditing in Oracle

Oracle offers three main auditing methods, each with pros and cons:

classDiagram
  class StandardAudit {
    +audits: schema-level actions
    -overhead: high
    +command: AUDIT UPDATE ON table BY USER
  }
  class FineGrainedAudit {
    +audits: specific rows/columns
    -condition: NEW.SALARY > 50000
    +command: DBMS_FGA.ADD_POLICY()
  }
  class UnifiedAudit {
    +audits: all actions in one table
    -version: 12c+
    +command: AUDIT DELETE ON orders BY SESSION
  }
  StandardAudit --> UnifiedAudit : "Replaced by"
  FineGrainedAudit --> UnifiedAudit : "Consolidated into"

📌 1. Standard Auditing (Legacy)

• How it works: Uses the AUDIT SQL command to log actions.
• Example:

  AUDIT UPDATE ON hr.employees BY USER;
  

• Limitations:
  • Only audits schema-level actions (not fine-grained).
  • High performance overhead (logs to multiple tables).
• When to use: Legacy systems or simple tracking.

📌 2. Fine-Grained Auditing (FGA)

• How it works: Audits specific rows/columns in a table (e.g., only audit salary changes > Rs. 50,000).
• Example: Track who changes an employee’s salary beyond a threshold.

  BEGIN
    DBMS_FGA.ADD_POLICY(
      object_schema => 'HR',
      object_name   => 'EMPLOYEES',
      policy_name    => 'SALARY_AUDIT',
      audit_condition => 'NEW.SALARY > 50000',
      audit_column   => 'SALARY'
    );
  END;
  

• Advantages:
  • Granular control (audit only critical data).
  • Reduces overhead (no unnecessary logs).
• Disadvantages:
  • Complex to manage (multiple policies).
  • Not available in all Oracle editions.

📌 3. Unified Auditing (12c and later)

• How it works: Single table (UNIFIED_AUDIT_TRAIL) for all audit data (replaces Standard and FGA).
• Example: Audit all DELETE operations on ORDERS table.

  AUDIT DELETE ON orders BY SESSION;
  

• Advantages:
  • Simplified management (one place for all logs).
  • Better performance (optimized storage).
  • Supports GDPR/SOX requirements.
• Disadvantages:
  • Requires Oracle 12c+.
  • Initial setup effort for migration.

4. Audit Trail: What Gets Logged?

The audit trail captures who, what, when, and where of database actions. Here’s a breakdown:

5. Worked Example: Auditing a Daraz Order System

Scenario: Daraz wants to track who cancels orders and why. They use Unified Auditing to:

1. Audit all UPDATE operations on the ORDERS table where STATUS = 'CANCELLED'.
2. Log the order ID, customer ID, and reason (from a comment field).

Step 1: Enable Unified Auditing

-- Enable unified auditing
AUDIT UNIFIED BY SESSION;

Step 2: Create an Audit Policy for Order Cancellations

BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL(
    audit_trail => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_level => DBMS_AUDIT_MGMT.AUDIT_TRAIL_COMPLETE
  );
END;
/

Step 3: Add a Fine-Grained Policy for Cancellations

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema => 'DARAZ',
    object_name   => 'ORDERS',
    policy_name    => 'ORDER_CANCELLATION_AUDIT',
    audit_condition => 'NEW.STATUS = ''CANCELLED''',
    handler_schema => 'DARAZ',
    handler_module => 'ORDER_CANCELLATION_HANDLER'
  );
END;
/

Step 4: Query the Audit Trail

-- Check who cancelled orders in the last 7 days
SELECT timestamp, username, sql_text, obj_name, action_name
FROM unified_audit_trail
WHERE action_name = 'UPDATE'
  AND obj_name = 'ORDERS'
  AND timestamp >= SYSDATE - 7;

Output Example:

Real-World Tie-In: If a customer complains that Daraz cancelled their order unfairly, the audit trail proves:

• Who executed the cancellation (CUSTOMER_123).
• When it happened (15:15:22).
• Whether it was an admin or the customer themselves.

6. Compliance Requirements: GDPR, SOX, and Nepali Laws

Oracle auditing helps meet global and local regulations:

Example for Nepali Banks:

• Nepal Rastra Bank (NRB) requires banks to audit:
  • All loan disbursements (INSERT into LOANS).
  • Changes to interest rates (UPDATE on LOAN_TERMS).
  • Admin access to sensitive tables.
• Solution: Use Unified Auditing with retention set to 7 years.

7. Managing Audit Data: Retention and Cleanup

Audit data can bloat the database if not managed. Oracle provides tools to control it:

📌 Setting Retention Period

-- Keep audit data for 1 year
EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL(
  audit_trail => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
  audit_trail_level => DBMS_AUDIT_MGMT.AUDIT_TRAIL_COMPLETE,
  retention_days => 365
);

📌 Archiving Old Audit Data

-- Archive audit data older than 6 months to a table
BEGIN
  DBMS_AUDIT_MGMT.ARCHIVE_AUDIT_TRAIL(
    user_name => NULL,
    os_user => NULL,
    action_name => NULL,
    return_rows => 10000,
    archive_table_name => 'AUDIT_ARCHIVE'
  );
END;
/

📌 Deleting Expired Audit Data

-- Purge audit data older than 1 year
BEGIN
  DBMS_AUDIT_MGMT.DELETE_AUDIT_TRAIL(
    audit_trail => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    start_time => SYSDATE - 365
  );
END;
/

8. Performance Impact of Auditing

Auditing slows down the database because every action must be logged. Here’s how to mitigate it:

Best Practice:

• Audit only what’s necessary (e.g., don’t audit SELECT queries unless required).
• Use Unified Auditing (12c+) for better performance.
• Schedule cleanup jobs (e.g., purge data older than 1 year).

9. Common Audit Scenarios and SQL Commands

Here are real-world audit use cases with SQL:

📌 Scenario 1: Track Who Drops Tables

-- Audit all DROP TABLE operations
AUDIT DROP ANY TABLE BY USER;

📌 Scenario 2: Monitor Failed Login Attempts

-- Audit failed logins (brute-force attacks)
AUDIT SESSION WHENEVER NOT SUCCESSFUL;

📌 Scenario 3: Audit Specific Column Changes (FGA)

-- Audit only changes to employee email addresses
BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    policy_name    => 'EMAIL_AUDIT',
    audit_condition => 'NEW.EMAIL IS NOT NULL',
    audit_column   => 'EMAIL'
  );
END;
/

📌 Scenario 4: Query Audit Data for Compliance

-- Find all salary changes in the last month
SELECT username, timestamp, sql_text
FROM unified_audit_trail
WHERE action_name = 'UPDATE'
  AND obj_name = 'EMPLOYEES'
  AND sql_text LIKE '%SALARY%'
  AND timestamp >= SYSDATE - 30;

10. Troubleshooting Audit Issues

Example Fix: If auditing fails silently, check:

-- Verify audit policies
SELECT * FROM DBA_AUDIT_POLICIES;

-- Check if auditing is enabled
SELECT value FROM v$parameter WHERE name = 'AUDIT_TRAIL';

Exam Tip: How to Score Full Marks

This unit is highly practical—examiners love SQL examples, real-world scenarios, and comparisons. Here’s how to ace it:

✅ What to Include in Answers

1. Define the concept clearly (e.g., "Unified Auditing consolidates all audit data into a single table in Oracle 12c+").
2. Give SQL examples (e.g., AUDIT UPDATE ON hr.employees BY USER;).
3. Compare methods (e.g., "Standard Auditing vs. Unified Auditing: Standard is legacy, Unified is optimized").
4. Relate to compliance (e.g., "GDPR requires auditing data deletions, so we use AUDIT DELETE ON users BY SESSION").
5. Use a real-world example (e.g., "Nabil Bank uses FGA to track loan modifications").

❌ Common Mistakes to Avoid

• Vague answers: Don’t just say "auditing is important"—explain how it’s implemented.
• Missing SQL: Always include at least one SQL command in your answer.
• Ignoring versions: Specify if a feature is Oracle 12c+ (e.g., Unified Auditing).
• No examples: Examiners want to see real scenarios (e.g., Daraz orders, Nabil Bank loans).

📝 Model Answer Structure (for 10-mark questions)

Question: Explain Oracle auditing with examples of its use in Nepali banks.

Answer:

1. Definition (1 mark): Oracle auditing tracks database actions (e.g., INSERT, UPDATE, DELETE) for security and compliance.

2. Types of Auditing (3 marks):

   • Standard Auditing: Legacy method using AUDIT command (e.g., AUDIT UPDATE ON hr.employees BY USER;).
   • Fine-Grained Auditing (FGA): Audits specific rows/columns (e.g., tracking salary changes > Rs. 50,000).
   • Unified Auditing (12c+): Single table for all audit data (e.g., UNIFIED_AUDIT_TRAIL).

3. Real-World Example (3 marks): Nabil Bank uses FGA to audit loan modifications:

   BEGIN
     DBMS_FGA.ADD_POLICY(
       object_schema => 'BANK',
       object_name   => 'LOAN_ACCOUNTS',
       policy_name    => 'LOAN_AUDIT',
       audit_condition => 'NEW.PRINCIPAL > 1000000'
     );
   END;
   

   This helps detect fraud (e.g., an employee altering a loan for personal gain).

4. Compliance Link (2 marks): Under Nepal Rastra Bank (NRB) regulations, banks must audit:

   • All loan disbursements (INSERT into LOANS).
   • Changes to interest rates (UPDATE on LOAN_TERMS).
   • Admin access to sensitive tables.

5. SQL Query Example (1 mark):

   -- Check who modified loans in the last week
   SELECT username, timestamp, sql_text
   FROM unified_audit_trail
   WHERE obj_name = 'LOAN_ACCOUNTS'
     AND timestamp >= SYSDATE - 7;
   

Summary Table: Auditing Methods Compared

Final Checklist Before the Exam

• Can you define Standard, FGA, and Unified Auditing?
• Can you write SQL to enable auditing for a real scenario (e.g., Daraz orders)?
• Do you know how to query audit data (e.g., SELECT FROM unified_audit_trail)?
• Can you explain compliance (GDPR, SOX, NRB) and link it to auditing?
• Do you have a real-world example (e.g., Nabil Bank loans, eSewa payments)?

Good luck! 🚀