Oracle Auditing Features
You design and create your database to serve a set of purposes. But how do you know exactly how the database is being used in the real world?
Your Oracle database gives you the ability to audit its operations. This auditing capability will create a history of the interactions between users and the history of the database . This can be can used for various purposes, from diagnosing the cause of performance problems to checking for potential security violations. By the time you complete this module, you should be able to:
- List the types of things that can be audited
- Enable the audit feature
- Audit the use of a SQL statement
- Generate audit trail reports
- Audit the use of a specific Oracle privilege
- Audit the use of specific database objects
- Delete old audit trail records
Why Auditing may be necessary
Although you may not need to audit your Oracle database in the course of normal operations,
exceptional circumstances may cause you to need to take advantage of this feature.
The Oracle database gives you the ability to restrict unauthorized access to your valuable data. However, your security is only as good as your implementation, and people do make mistakes. In addition, you may want to understand what type of activities (legitimate or not) are taking place with your data. The ability to audit database activity can address both of these issues.
Oracle's audit capabilities let you track actions at the statement level, privilege level, or schema object level for the entire database or particular users.
Auditing can also gather data about database activities for planning and tuning purposes. Auditing of connections with administrative privileges to an instance and audit records recording database startup and shutdown occur by default.
As a non-DBA user within an Oracle database, you cannot enable the auditing features of the database. If auditing has been enabled, there are data dictionary views that anyone can use to view the audit trail.
Many different audit trail data dictionary views are available. Most of these views are based on a single audit trail table in the database (SYS.AUD$). The most generic of the audit trail views available is named USER_AUDIT_TRAIL. Since this view shows the audit records for many different types of actions, many of the columns may be inapplicable for any given row.
The DBA version of this view, DBA_AUDIT_TRAIL, lists all entries from the audit trail table; USER_AUDIT_TRAIL lists only those that are relevant to the user. A vast number of auditing capabilities is available and each type of audit can be accessed via its own data dictionary view.
The following are the available views:
||For statements concerning objects
||For connections and disconnections
||For grant, revoke, audit, noaudit, and alter system commands issued by the user
In the next lesson, we will begin by looking at the types of things you can audit.