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!
You'll be receiving a confirmation email

Follow us on twitter :)

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.