Database Auditing
There are two distinct types of auditing
Standard
There are 3 levels of standard auditing
For all 3 levels of auditing you can choose to audit by access (audit every time you access) or by session (audit only once per access during the session), you can also auditing on if the access was successful (whenever successful) or not (whenever not successful).
When auditing you need somewhere to log the audit information, this is controlled by the audit_trail parameter which can take the following values
Remember to restart the instance when changing the audit_trail parameter.
turn off auditing | alter system set audit_trail=none scope=spfile; |
auditing written to db | alter system set audit_trail=db scope=spfile; |
auditing written to o/s | alter system set audit_file_dest='c:\oracle\auditing'; Note: if the audit_file_dest is not set then the default location is $oracle_home/rdbms/audit/ |
audit all sys operations (default false) | alter system set audit_sys_operations=true scope=spfile; Note: this will audit all sys operations regardless if audit_trail is set. |
To start auditing you can use the below (there are many more options than stated below)
session | audit session by vallep; |
table | audit table; |
table and specific user | audit table by vallep; |
table, specific user and access | audit table by vallep by access; |
privilege auditing | audit create any table; audit create any table by vallep; |
object auditing | audit select on vallep.employees by access whenever successful; audit select on vallep.employees by access whenever not successful; |
disabling audit | noaudit table; noaudit all privileges; noaudit create any table by vallep; |
turn off all auditing | noaudit all; (turn off all statement auditing) noaudit all privileges; (turn off all privilege auditing) noaudit all on default; (turn off all object auditing) |
purge audit table | delete from sys.aud$; truncate from sys.aud$; |
Useful Views |
|
DBA_STMT_AUDIT_OPTS | display any statement auditing |
DBA_PRIV_AUDIT_OPTS | display any privilege auditing |
DBA_OBJ_AUDIT_OPTS | display any object auditing |
DBA_AUDIT_TRAIL | display the captured audit information |
DBA_COMMON_AUDIT_TRAIL | displays the captured audit information for both standard and FGA |
Auditing via Triggers
It is possible to audit the system by using triggers, there are a number of system-level triggers that can be fired
example trigger auditing | create or replace trigger audit_insert create or replace trigger logon_audit_trig ## You would need to create table as below, you would also create a logoff trigger that populated the below table create table logon_audit ( |
Fine-Grain Auditing
Fine-grain auditing (FGA) allows you to audit users accessing data of a certain criteria. As per standard auditing you can audit select, insert, update and delete operations. You use the package dbms_fga to add, remove, enable and disable FGA auditing policies, it is virtually impossible to bypass these policies, one nice feature is that you can attach handlers (like a trigger) to the policies which can execute procedures which could email or page you.
There are many options that can be applied to the dbms_fga package, so best to look up the oracle man pages but here are some simple examples
Privilege | grant execute on dbms_fga to vallep; |
Creating | begin |
Creating (handler) | dbms_fga.add_policy ( create procedure vallep.log_id (schema1 varchar2, table1 varchar2, policy1 varchar2) |
Removing | begin dbms_fga.drop_policy ( object_schema=>'vallep', object_name=>'employees', policy_name=>'compensation_aud'); end; / |
Enabling | begin dbms_fga.enable_policy ( object_schema=>'vallep', object_name=>'employees', policy_name=>'compensation_aud'); end; / |
Disabling | begin |
Useful Views |
|
DBA_AUDIT_POLICIES | identify FGA audit policies |
DBA_FGA_AUDIT_TRAIL | display the captured audit information |
DBA_COMMON_AUDIT_TRAIL | displays the captured audit information for both standard and FGA |
SYS.AUD$ table
Make sure that the sys.aud$ table gets purged from time to time as connections and DML activity in the database might come to a stand still if it becomes full.
purge audit table | delete from sys.aud$; truncate from sys.aud$; |