In an Oracle Database we can mention following auditing types:
- Mandatory Auditing
- Standard Auditing
- Fine-Grained Auditing
- SYS Auditing
Mandatory Auditing causes database start-up/shut-down and SYSDBA-SYSOPER login logout information to be written into AUDIT_FILE_DEST. This auditing cannot be turned off and it's always written into operating system directory specified with AUDIT_FILE_DEST.
Standard Auditing is controlled with AUDIT_TRAIL parameter. (NONE,OS,DB,DB_EXTENDED,XML,XML_EXTENDED)
* When DB or DB_EXTENDED is used, audit records are written into database (aud$ table). (Not the mandatory auditing records, they're always on OS)
* XML options write to operating system in XML format.
* When *EXTENDED is used SQLBIND and SQLTEXT information is included in the audit trail. If not used, not included.
* By default, Standard Auditing audits SQL statements which use following privileges:
ALTER ANY PROCEDURE | CREATE ANY LIBRARY | DROP ANY TABLE | ALTER ANY TABLE | CREATE ANY PROCEDURE | DROP PROFILE | ALTER DATABASE | CREATE ANY TABLE | DROP USER | ALTER PROFILE | CREATE EXTERNAL JOB | EXEMPT ACCESS POLICY | ALTER SYSTEM | CREATE PUBLIC DATABASE LINK | GRANT ANY OBJECT PRIVILEGE | ALTER USER | CREATE SESSION | GRANT ANY PRIVILEGE | AUDIT SYSTEM | CREATE USER | GRANT ANY ROLE | CREATE ANY JOB | DROP ANY PROCEDURE
* It's possible to audit Statements, Privileges and Objects with Standard Auditing. For example:
- Privilege auditing: audit select any table;
- Statement auditing: audit select table;
- Object auditing: audit select on SCOTT.SALARY;
* Following views give information about current Standard Auditing configuration in the database:
- DBA_STMT_AUDIT_OPTS; ==> describes current statements being audited across the system
- DBA_PRIV_AUDIT_OPTS; ==> describes current system privileges being audited across the system
- DBA_OBJ_AUDIT_OPTS; ==> describes auditing options for all objects
* You can use the SQL "AUDIT" statement to set auditing options regardless of the setting of AUDIT_TRAIL parameter. However, Oracle Database does not generate audit records until you enable Standard Auditing using AUDIT_TRAIL parameter.
* Auditing to OS offers higher performance when compared with DB.
Fine-Grained Auditing is used to audit operations like:
- Accessing a table outside of normal working hours
- Logging in from a particular IP address
- Selecting or updating a particular table column
* DBMS_FGA package is used to manage Fine-Grained Auditing. DBA_AUDIT_POLICIES view describes all fine-grained auditing policies in the database.
* It's not mandatory to enable Standard Auditing in order to use Fine-Grained Auditing or SYS Auditing.
SYS Auditing: AUDIT_SYS_OPERATIONS parameter (TRUE/FALSE) enables or disables the auditing of SQL statements that directly issued by users connected with SYSDBA or SYSOPER privileges (SQL statements run from within PL/SQL procedures or functions are not audited). These audit records are written into OS.
* It's not mandatory to enable Standard Auditing in order to use SYS Auditing.