How to audit access (Oracle-style) in PostgreSQL

Galaxy Glossary

How can I log every read and write on a table in Oracle?

AUDIT BY ACCESS records every successful or failed statement on a specified object, privilege, or user.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does AUDIT BY ACCESS do?

It writes a row to SYS.AUD$ (Oracle) or your pgaudit log (PostgreSQL) each time the audited action runs. Use it when you need a complete, statement-level trail for compliance or breach forensics.

How do I enable auditing at the database level?

Oracle: set AUDIT_TRAIL = DB,EXTENDED and restart. PostgreSQL: install the pgaudit extension, then ALTER SYSTEM SET pgaudit.log = 'read,write'; followed by a reload.

How can I audit access to a single table?

Oracle: AUDIT SELECT, INSERT, UPDATE, DELETE ON customers BY ACCESS; logs every DML on customers. In PostgreSQL, create a role-based policy: SET pgaudit.role = 'app'; so only statements from app users are captured.

Can I audit only failed logins?

Oracle: AUDIT SESSION WHENEVER NOT SUCCESSFUL; records failed logons. PostgreSQL: enable parameter log_connections = on and filter on FATAL in logs.

How do I review audit records?

Oracle: query SELECT username, obj_name, action_name, timestamp FROM dba_audit_trail;. PostgreSQL: read $PGDATA/log/*.log or send logs to logstash/CloudWatch.

Best practices for performance?

Audit only what you need, use BY SESSION when statement-level detail is unnecessary, and archive older audit rows to keep the repository lean.

Why How to audit access (Oracle-style) in PostgreSQL is important

How to audit access (Oracle-style) in PostgreSQL Example Usage


AUDIT SELECT, INSERT, UPDATE, DELETE ON customers BY ACCESS;
-- Now every time a developer runs:
SELECT * FROM customers WHERE id = 42;
-- Oracle writes a row to DBA_AUDIT_TRAIL showing the username, SQL text, and timestamp.

How to audit access (Oracle-style) in PostgreSQL Syntax


-- Oracle syntax
AUDIT { ALL | SELECT | INSERT | UPDATE | DELETE }
     ON schema.object
     [ BY { SESSION | ACCESS } ]
     [ WHENEVER [ NOT ] SUCCESSFUL ];

-- Examples in an ecommerce schema
AUDIT SELECT ON customers BY ACCESS;                     -- log every read of Customers
AUDIT DELETE, UPDATE ON orders BY ACCESS;                -- log all changes to Orders
AUDIT SELECT ON products WHENEVER NOT SUCCESSFUL;        -- capture failed product reads

-- Disable auditing
NOAUDIT SELECT ON customers;

Common Mistakes

Mistake: Using BY SESSION when compliance requires statement-level records. Fix: Specify BY ACCESS to log each execution instead of one entry per session.
Mistake: Auditing all tables without pruning DBA_AUDIT_TRAIL. Fix: Target only sensitive tables and regularly archive or purge audit rows.