Every query run in SQL*Plus leaves a trail. Every connection, every command, every failed login attempt is evidence of what truly happened inside your database. If you don’t track it, you’re not in control—you’re blind. Auditing SQL*Plus is how you see everything.
SQL*Plus auditing starts with knowing exactly what to capture. At a minimum, you want:
- Logon and logoff times with user IDs.
- All executed SQL statements.
- Any failed login attempts.
- Changes to sensitive tables.
In Oracle, you can enable auditing at both the database and session level. Use the AUDIT command to target specific actions, privileges, or objects. Start simple:
AUDIT SESSION;
AUDIT SELECT TABLE, INSERT TABLE, UPDATE TABLE, DELETE TABLE BY ACCESS;
Every record is stored in the database audit trail. To see them:
SELECT * FROM DBA_AUDIT_TRAIL;
For deeper visibility, enable fine-grained auditing (FGA). It tracks specific columns, conditions, or actions. Example:
BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_SALARY_ACCESS',
audit_condition => 'SALARY > 10000',
audit_column => 'SALARY'
);
END;
/
This way, SQL*Plus commands touching sensitive data trigger exact audit records. With both standard and fine-grained auditing in place, you can link every command to the person who ran it.
Auditing isn’t just about catching bad actors. It also proves compliance, supports forensic analysis, and strengthens operational discipline. Without it, performance metrics and incident timelines are built on guesswork.
Avoid storing logs only in the database. Stream them to secure storage or SIEM tools. Keep retention policies tight but aligned with your compliance needs. Always test audit configurations in a non-production environment—improper auditing can slow critical workloads or overwhelm storage.
The best teams don’t stop at logging—they automate review, alerting, and action. A raw audit trail isn’t enough. Every record needs eyes, or better yet, code watching in real time.
If you want to see a structured, practical way to set up and review your SQL*Plus auditing results without drowning in complexity, you can see it running live in minutes on hoop.dev. The less time you spend wiring up logging, the more time you’ll have for actual insight.
Do you want me also to include a section on real-world auditing queries for SQL*Plus so the post ranks for more long-tail searches? That could make it even stronger.