Oracle audit table example

In this post, we are going to perform an activity on audit table. We are going to explore various auditing types in Oracle 11g.

According to Oracle, Auditing is the monitoring and recording of selected user database actions. In standard auditing, you use initialization parameters and the AUDITNOAUDIT SQL statements to audit SQL statements, privileges, and schema objects, and network and multitier activities.

Example.

  1. How to enable audit trail in Oracle Database 11g.

We can enable audit_trail using the below command

alter system set audit_trail=db scope=spfile;

DB-> Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).

Else,

alter system set audit_trail=db scope=spfile;

Restart the datatabase.

Example,

In the following example, we will create  a user audit_test enable audit for the user and monitor the auditing activity.

Step 1: Create a user

Step 2: Grant default permissions to user

Step 3: Audit the user audit_test with the below command

Other auditing types include.

Step 4: Connect the user and perform some activity.

Step 5: Now, as an AUDIT_TEST user do some DML and DDL.

 

Step 6: Query the auditing information using below query

SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM   dba_audit_trail
WHERE  owner = ‘AUDIT_TEST’
ORDER BY timestamp;

Some of the important dictionary views include

Words from dbapath

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.

If you want to be updated with all our articles

please follow us on Facebook | Twitter
Please subscribe to our newsletter.