Database Auditing & Flashback Tech: Security, Recovery & Time Travel

TAKEAWAYS:

• Database auditing logs user actions (logins, DDL/DML) for security and compliance, using standard auditing (predefined actions) or fine-grained auditing (custom rules).
• Flashback technology lets DBAs undo changes via Flashback Query (time-travel queries), Flashback Drop (recover dropped tables), Flashback Database (full system rollback), and Flashback Transaction Query (undo specific transactions).
• Flashback modes (enabled via FLASHBACK ON) require undo tablespace and supplemental logging for accurate recovery.
• Real-world use: Banks audit transactions (e.g., Nabil Bank logs loan approvals), eSewa tracks user payments, and NEPSE uses flashback to correct erroneous stock trades.
• Exam focus: Compare auditing types, explain flashback steps with SQL examples, and link to startup/shutdown modes (Unit 9) and recovery (Unit 4).

1. Database Auditing: Tracking Who Did What

Database auditing records who accessed what, when, and how—critical for security, compliance (e.g., GDPR, PCI-DSS), and forensic investigations. Oracle offers two types:

erDiagram
  EMPLOYEES ||--o{ AUDIT_LOG : has
  AUDIT_LOG {
    int id PK
    string user_id FK
    string action_type
    string table_name
    timestamp event_time
    string old_value
    string new_value
  }
  EMPLOYEES {
    int id PK
    string name
    decimal salary
    string department
  }

A. Standard Auditing (Predefined Actions)

• What it tracks: Logins, DDL (CREATE/DROP), DML (INSERT/UPDATE), privilege grants, and failed login attempts.
• How it works: Uses the AUDIT command to enable/disable tracking for specific actions.
• Example:

  -- Audit all CREATE TABLE operations in the HR schema
  AUDIT CREATE TABLE BY hr;
  

  • Output: Logs appear in the audit trail (default: $ORACLE_BASE/admin/<DB_NAME>/adump/).

B. Fine-Grained Auditing (Custom Rules)

• What it tracks: Row-level operations (e.g., "audit salary updates > 50,000" in the EMPLOYEES table).
• How it works: Uses policy functions (PL/SQL) to define conditions.
• Example:

  -- Create a policy to audit high-value salary changes
  BEGIN
    DBMS_FGA.ADD_POLICY(
      object_schema => 'HR',
      object_name   => 'EMPLOYEES',
      policy_name   => 'AUDIT_HIGH_SALARY',
      audit_condition => 'NEW.SALARY > 50000',
      audit_column   => 'SALARY'
    );
  END;
  

  • Output: Logs only rows where NEW.SALARY > 50,000 is updated.

Visual: Audit Trail Flow

sequenceDiagram
    participant User as Employee (HR)
    participant DB as Oracle Database
    participant Audit as Audit Trail
    User->>DB: UPDATE EMPLOYEES SET SALARY = 60000 WHERE ID = 101;
    DB->>Audit: Logs action (User: HR, Action: UPDATE, Table: EMPLOYEES, Timestamp: 2024-05-20 14:30)
    Note over DB,Audit: Audit trail stored in $ORACLE_BASE/admin/<DB_NAME>/adump/

Real-World Example: eSewa’s Audit Trail

• Scenario: eSewa (Nepal’s digital wallet) must audit all payment transactions for fraud detection.
• Implementation:
  • Standard auditing: Logs all INSERT INTO TRANSACTIONS operations.
  • Fine-grained auditing: Flags transactions > NPR 50,000 for manual review.
• Why it matters: If a user reports a missing payment, eSewa can verify the audit log to confirm whether the transaction was processed or altered.

2. Flashback Technology: Undoing Changes

Flashback technology allows DBAs to "rewind" the database to a previous state. Oracle offers four key features:

A. Flashback Query (Time-Travel Queries)

• What it does: Lets you query data as it existed at a past point in time (without altering the current database).
• Syntax:

  -- Query the EMPLOYEES table as of 1 hour ago
  SELECT * FROM EMPLOYEES AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);
  

• Use case: NEPSE (Nepal Stock Exchange) uses flashback queries to verify stock prices before a trade was executed.

B. Flashback Drop

• What it does: Recovers a dropped table (or schema) if the recyclebin is enabled.
• Steps:
  1. Check the recyclebin:

     SELECT * FROM RECYCLEBIN;
     

  2. Restore the table:

     FLASHBACK TABLE employees TO BEFORE DROP;
     

• Limitations: Only works if the table was not purged from the recyclebin.

C. Flashback Database (Full System Rollback)

• What it does: Rolls back the entire database to a restore point or timestamp.
• Requirements:
  • Undo tablespace must be sized correctly.
  • Supplemental logging must be enabled.
• Steps:
  1. Create a restore point:

     CREATE RESTORE POINT pre_upgrade BEFORE DATABASE SHUTDOWN;
     

  2. Perform risky operations (e.g., schema changes).
  3. Roll back if needed:

     FLASHBACK DATABASE TO RESTORE POINT pre_upgrade;
     

• Real-World Example: Nabil Bank uses flashback database to undo failed core banking system upgrades.

D. Flashback Transaction Query

• What it does: Identifies which transactions modified data in a specific time range.
• Example:

  -- Find all transactions that updated the EMPLOYEES table in the last 5 minutes
  SELECT * FROM FLASHBACK_TRANSACTION_QUERY;
  

• Use case: Pathao (ride-hailing app) uses this to debug incorrect fare calculations.

3. Enabling Flashback Technology

To use flashback features, the database must be in flashback mode:

-- Enable flashback mode (requires undo tablespace)
ALTER DATABASE FLASHBACK ON;

• Key settings:
  • Undo retention: Controls how long undo data is kept (default: 900 seconds).
  • Supplemental logging: Required for flashback database (enables tracking of DML changes).

Visual: Flashback Database Architecture

4. Comparing Auditing and Flashback

5. Worked Example: Auditing and Flashback in a Bank

Scenario: Global IME Bank needs to:

1. Audit all loan approvals > NPR 10,00,000.
2. Recover from a failed interest rate update.

stateDiagram-v2
  [*] --> Idle
  Idle --> Processing: Loan Approval Request
  Processing --> Audit: Log Transaction
  Audit --> Flashback: If Fraud Detected
  Flashback --> [*]: Rollback Transaction
  Processing --> [*]: Approve Loan
  note right of Processing
    Example: Nabil Bank
    - Standard auditing logs all
    - Fine-grained audits loans > NPR 5M
  end

Step 1: Set Up Fine-Grained Auditing for Loans

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema => 'BANK',
    object_name   => 'LOANS',
    policy_name   => 'AUDIT_HIGH_LOANS',
    audit_condition => 'NEW.AMOUNT > 1000000',
    audit_column   => 'AMOUNT'
  );
END;

• Output: Logs all loan approvals > NPR 10,00,000 to the audit trail.

Step 2: Enable Flashback for Recovery

-- Enable flashback mode
ALTER DATABASE FLASHBACK ON;

-- Create a restore point before updating interest rates
CREATE RESTORE POINT pre_interest_update;

• Action: DBA runs UPDATE INTEREST_RATES SET RATE = 12 WHERE TYPE = 'HOME'.
• Problem: The update causes errors in loan calculations.
• Solution: Roll back to the restore point:

  FLASHBACK DATABASE TO RESTORE POINT pre_interest_update;
  

Step 3: Verify with Flashback Query

-- Check the correct interest rate before the failed update
SELECT * FROM INTEREST_RATES AS OF TIMESTAMP (TO_TIMESTAMP('2024-05-20 14:00:00', 'YYYY-MM-DD HH24:MI:SS'));

6. Common Pitfalls and Best Practices

A. Auditing Pitfalls

• Performance overhead: Auditing high-frequency tables (e.g., LOGIN_ATTEMPTS) can slow down the database.
  • Fix: Use fine-grained auditing sparingly and monitor with V$SESSION_EVENT.
• Storage bloat: Audit logs grow rapidly.
  • Fix: Set up automated log rotation and archive old logs.

B. Flashback Pitfalls

• Undo tablespace size: If too small, flashback operations fail.
  • Fix: Monitor UNDO_ADVICE and resize:

    ALTER DATABASE UNDO TABLESPACE undo_ts RETENTION 14400; -- 4 hours
    

• Supplemental logging: Must be enabled for flashback database.
  • Fix: Run:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
    

C. Real Picture: Oracle Database Server Rack

Exam Tip

1. Auditing Questions:

   • Always compare standard vs. fine-grained auditing in your answer.
   • Include SQL examples (e.g., AUDIT CREATE TABLE, DBMS_FGA.ADD_POLICY).
   • Link to security: Mention compliance (e.g., "auditing is required for PCI-DSS").

2. Flashback Questions:

   • Explain the prerequisites: Undo tablespace, supplemental logging, and FLASHBACK ON.
   • Show SQL steps: For flashback database, include:
     • CREATE RESTORE POINT
     • FLASHBACK DATABASE TO RESTORE POINT
   • Real-world tie-in: Relate to banking, stock exchanges, or e-commerce (e.g., "NEPSE uses flashback to correct trades").

3. Common Exam Traps:

   • Don’t confuse FLASHBACK TABLE (recyclebin) with FLASHBACK DATABASE (full rollback).
   • Don’t forget that flashback query does not modify the database—it’s read-only.
   • Always mention the UNDO tablespace when discussing flashback.

Final Note: This unit is highly practical. Expect SQL-based questions (e.g., "Write the command to audit all DROP TABLE operations") and scenario-based questions (e.g., "How would you recover a dropped table in a production bank database?"). Draw diagrams for auditing/flashback flows in exams—they earn marks!

In the real world

• Nabil Bank: Uses fine-grained auditing to track loan approvals > NPR 5 million, with flashback transaction query to investigate suspicious transactions (e.g., a loan officer approving a loan for a non-existent customer).
• eSewa: Implements standard auditing for all payment transactions and flashback query to verify disputed transactions (e.g., a user claims a NPR 10,000 payment was never processed).
• NEPSE (Nepal Stock Exchange): Relies on flashback database to correct erroneous stock trades (e.g., reversing a trade executed due to a system glitch during market hours).