- Mandatory auditing
- DBA Auditing
- Standard database audit
- Fine-Grained Auditing ( FGA)
- Value base auditing
Mandatory auditing
Mandatory audit or called auditing by default .
- Database startup/shutdown
- Connect as SYSDBA
Audit trace is store at AUDIT_FILE_DEST. Below is the sample output of the audit file.
DBA auditing :AUDIT_SYS_OPERATIONS
When We change the AUDIT_SYS_OPERATIONS to true, the database would audit all the trace for the SYSDBA. The parameter require the database bounce to make it effective.
alter system set AUDIT_SYS_OPERATIONS=true scope=spfile;
After the database bounce, in the audit file, we can see all the SQL that has execute run by SYSDBA.
Standard audit
Audit_trail can be set as below levels.
none/false | Disable the auditing | |
db | Write the audit log to database. aud$ table | |
db,extended | Write the audit log to database with SQL. aud$ table | |
os | write to OS file | audit_file_dest |
xml | write to os file with XML format | audit_file_dest |
xml,extended | write to os file with XML format + SQL | audit_file_dest |
- Audit target can be privileges ( SELECT ANY TABLE ..etc) , Objects ( table, index ..etc) or SQL ( select , insert .. etc)
- SELECT TABLE would record the select statement for the tables belong to the schema owner. SELECT ANY TABLE would not record the select statement for the table belong to the schema owner.
- Change the audit setting only effective after user re connect. It does not apply to the current connect session.
- The different between BY ACCESS and BY SESSION would show differently in the ACTION_NAME column within the DBA_AUDIT_TRAIL table. BY SESSION only shows SESSION REC for privileges. It wont distinguish between INSERT, DELETE, SELECT …etc.
- DBA_AUDIT_TRIAL is the view base on AUD$
AUDIT SELECT TABLE BY HR BY ACCESS;
AUDIT SELECT TABLE BY HR BY SESSION;
AUDIT SELECT ANY TABLE BY HR BY ACCESS;
AUDIT INSERT TABLE BY HR BY SESSION;
NOAUDIT SELECT TABLE BY HR;
NOAUDIT SELECT TABLE BY HR;
NOAUDIT SELECT ANY TABLE BY HR;
NOAUDIT INSERT TABLE BY HR;
Query the trail table
select username,userhost,owner,obj_name,action_name,extended_timestamp from dba_audit_trail where owner= 'HR';
The audit trail 18 and 19, I insert 2 records to the TEST_TAB, but because the audit is set at session, therefore the ACTION_NAME column record “SESSION REC” hence we don’t know whether it is insert or select.
I change the audit level to BY ACCESS and reconnect as HR user to insert the record. The trail 20 shows the INSERT in the ACTION_NAME.
Fine-Grained Auditing (FGA)
FGA audit can provide more control on the audit condition.
- DBA_AUDIT_POLICIES –> List the current FGA policy. A view base on FGA$
- DBA_FGA_AUDIT_TRAIL –> store the audit information , A view base on FGA_LOG$
- DBMS_FGA.ADD_POLICY -> Create the new FGA policy
- DBMS_FGA.DROP_POLIC -> Drop the FGA policy
- DBMS_FGA.DISABLE_POLICY –> Disable the given FGA policy
- DBMS_FGA.ENABLE_POLICY –> Enable the given FGA policy
DBMS_FGA.ADD_POLICY
exec DBMS_FGA.ADD_POLICY ( object_schema => 'HR',object_name =>'employees',policy_name => 'FGA_TEST', audit_column=>'SALARY', audit_condition =>'SALARY> 10000',STATEMENT_TYPES=>'SELECT,UPDATE')
DBA_AUDIT_POLICIES
select object_schema,object_name,policy_name,policy_column,policy_text,enabled from dba_audit_policies;
DBA_FGA_AUDIT_TRAIL
select DB_USER,sql_text,extended_timestamp from DBA_FGA_AUDIT_TRAIL
DBMS_FGA.DROP_POLIC
exec DBMS_FGA.DROP_POLICY( object_schema => 'HR',object_name =>'employees',policy_name => 'FGA_TEST')
DBMS_FGA.DISABLE_POLICY
exec DBMS_FGA.DISABLE_POLICY( object_schema => 'HR',object_name =>'employees',policy_name => 'FGA_TEST')
DBMS_FGA.ENABLE_POLICY
exec DBMS_FGA.ENABLE_POLICY( object_schema => 'HR',object_name =>'employees',policy_name => 'FGA_TEST')
KNOWN ISSUE:
If I try to create the audit_condition for the string comparison, like audit_condition =>'JOB_ID = "SH_CLERK"'. The policy would create successfully , but I would get the ORA-28112: failed to execute policy function when execute the query.
Value bases auditing with System trigger
We can create the system trigger that audit the system event such as startup, shutdown, log on ..etc
Before demo the logon trigger and logoff trigger audit.
Create table to store the audit data
CREATE TABLE logon_audit
(
EVENT VARCHAR2(10),
username VARCHAR2(30),
sid NUMBER,
SESSIONID NUMBER,
logon_time DATE,
logoff_time DATE,
host VARCHAR2(20),
program VARCHAR2(100)
);
Create logon trigger
CREATE OR REPLACE TRIGGER logon_audit_trig AFTER LOGON ON DATABASE
DECLARE
var_program VARCHAR2(100);
BEGIN
SELECT program
INTO var_program
FROM v$session
WHERE sid = sys_context('userenv', 'SID');
INSERT
INTO logon_audit VALUES
(
'LOGIN',
USER,
sys_context('userenv', 'SID'),
sys_context('userenv', 'sessionid'),
sysdate,
NULL,
sys_context('userenv', 'host'),
var_program
);
END;
Create logoff trigger
CREATE OR REPLACE TRIGGER logoff_audit_trig BEFORE LOGOFF ON DATABASE
DECLARE
var_program VARCHAR2(100);
BEGIN
SELECT program
INTO var_program
FROM v$session
WHERE sid = sys_context('userenv', 'SID');
INSERT
INTO logon_audit VALUES
(
'LOGOFF',
USER,
sys_context('userenv', 'SID'),
sys_context('userenv', 'sessionid'),
NULL,
sysdate,
sys_context('userenv', 'host'),
var_program
);
END;
Query the logon_audit table
select * from logon_audit
Clean the env
drop trigger logon_audit_trig;
drop trigger logoff_audit_trig;
drop table logon_audit;
Resource
- Standard audit: http://www.oracle.com/technetwork/database/security/index-085803.html
- Audit syntax document http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm#BCGIDBFI
- http://www.oracle-base.com/articles/11g/auditing-enhancements-11gr2.php
- DBA auditing: AUDIT_SYS_OPERATIONS http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams014.htm
- http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm
- http://oracledba.ezpowell.com/oracle/databaseAuditing.html