There are three types of operations involved with database activities:

Application activities: Encompass SQL statements issued against application tables.

Administration activities: Encompass commands issued by the database administrators or operators for maintenance and administration purpose.
Database events: Events that occur when a specific activity occurs. For example, when a user logs on or logs off, when the database is started or is shut down, or when an error is generated by a command or statement.

These activities will help in design an auditing scheme based on activities that are dictated by business requirements. 

A good audit program provides an audit trail for all activities, especially those that challenge the system's security. It also creates the opportunity for using process controls, such as alerts that signal the database administrator when certain activities occur.

It is possible to track not only data changes (DML statements) through Oracle triggers, but also main database activities (DDL statement).

Example of auditing user Activities

Follow these steps to audit all activities performed by the DBSEC user:
 

1. Log on as SYSTEM or SYS and issue the following audit statement.

AUDIT ALL BY DBSEC;

DELETE SYS.AUD$;

1. Now log on as DBSEC  and create a temporary table.

 CREATE TABLE TEMP2(NUM NUMBER);

3.  go back to SYSTEM to view the contents of DBA_AUDIT_TRAIL. They are two records: one for logging on and one for creating a table

 

• Data Definition Language (DDL) statements: Including CREATE, ALTER, and DROP commands
• Data Control Language (DCL) statements: Including GRANT and REVOKE commands
• Database events: including events as AFTER LOGON and BEFORE LOGON
• SQL statement audit trail: Including the audit trail a history of all statements issued by a specific user on any table.