Oracle Audit Capabilities

first, the audit classification:
Oracle's audit in general can be divided into "standard audit" and "fine-grained audit" the latter is also known as "policy-based audit", the function has been greatly enhanced after oracle10g. Standard audit can be divided into user-level audit and system-level audit. User-level auditing is an audit that can be set up by any Oracle user, primarily by users auditing the database tables or views that they create, and documenting all successful and/or unsuccessful access requirements for all users to those tables or views, as well as various types of SQL operations. System-level auditing can only be set by the DBA to monitor successful or failed login requirements, monitor grant and revoke operations, and other database-level permissions.


II. Standard Audits
Category:
each of the following three standard audit types is supported in Oracle:
statement auditing, an audit of some type of SQL statement that does not specify a structure or an object.
privilege audits, audit of the use of system privileges to perform corresponding actions.
object auditing, an audit of the specified statement on a special mode object.
these three standard audit types are audited for the following 3 areas, respectively:
successful execution of an audit statement, unsuccessful execution, or both.
each user session Audit statement executes once or every time the statement is audited.
An audit of the activity of all users or designated users.

when the audit function of the database is opened, an audit record is generated during the statement execution phase. Audit records contain information such as audited actions, actions performed by the user, date and time of the operation, and so on. Audit records can exist in a data dictionary table (called an audit record) or in an operating system audit record. The database audit record is in the aud$ table of the SYS schema.

 
third, fine-grained audit:
Fine-grained audits (FGA) (introduced through oracle9i) can be understood as "policy-based audits". In contrast to the standard auditing functionality, FGA can be used to specify the conditions that are required to generate audit records: The FGA policy binds to objects (tables, views) programmatically by using the "DBMS_FGA" package. Similar to the package used for access control through VPD ("Dbms_rls"), it allows you to create any conditions you need, such as: only if the following conditions are true:
A table was accessed between nine and six o'clock in the afternoon in the morning or in Saturday and Sunday.
an IP address that is external to the corporate network is used.
a specific column is selected or updated.
a specific value for the column is used.

This creates a more meaningful audit trail, because each person's access to the table is not recorded. Starting with Oracle database 10g, FGA supports the use of any combination of SELECT, INSERT, UPDATE, and DELETE statements in a policy. In fact, the FGA policy that binds to the table simplifies the management of audit policies because it will only have to be changed once in the database and not in every application. In addition  Regardless of how the user connects to the database (through an application, WEB interface, or through Sql*plus), the operation is recorded.
 
 
iv. differences in fine-grained audit (FGA) audits and standard audits:
1. Standard audits must be enabled at the database level with parameter audit_trail. This parameter is not dynamic; You must restart the database to make it effective. In contrast, FGA does not require any parameter modification.
2. Once set on an object, the standard audit will remain there. To dismiss it, you must remove the audit option with the Noaudit command. This may be inconvenient because the Discard auditing option on a table will also discard metadata information. However, FGA can be temporarily disabled and enabled without losing any meta-data information.
3. FGA can only handle four types of statements: SELECT, INSERT, UPDATE, and DELETE. In contrast, a regular audit can handle many other statements and permissions, even session connections and disconnects.
4. Standard audit Each session creates only one record (per session) or one record per access object (on-access) This is very important in controlling the space in the Audit trail table in a way that is very resource-intensive. FGA is not equally resource-efficient; It runs once per visit-making the clue bigger.
5. By logging clues, standard audits can be used to detect any interrupt attempts and, if unsuccessful, generate an error code. and FGA can't.
6. Standard audits can write database tables or OS files. The latter is useful when the auditor (not the database administrator) has access to the thread. Under Windows, non-database audit trails are logged in the event log and can be accessed in different ways. This option protects the integrity of the audit trail. However, the FGA log is only written to the database table fga_log$. User-defined audit handlers can be created in FGA to write OS files, but their integrity is not guaranteed.
7. Standard auditing can be set for default objects. This feature becomes extremely useful when the table is created at run time: The default audit option allows auditing without database administrator intervention. This is not possible in FGA, where the user must create a policy on an existing table, which can occur only after the table has been created.
8. In FGA, auditing is more flexible-only when certain columns are accessed, when a particular condition is true, and so on. This versatility is handy when you need to control the growth of clues.
9. In FGA, the SQL assignment variable is captured by default. In a standard audit, the initialization parameter audit_trail must be set to db_extended to enable this feature.
10. Authority differences: Standard audits require auditing of system or statement permissions; FGA requires only the run permissions on the DBMS_FGA package.
    
    
   

Oracle Audit Capabilities