AUDIT Database Activity

Valido anche per Oracle 11g ma testato su 12c.

Nel parameter file è definito il parametro di audit
SQL>show parameter audit_
NAME                         TYPE    VALUE                             
---------------------------- ------- ------------------------------------
audit_file_dest             string  /u01/app/oracle/admin/CDB12S06/adump
audit_trail                     string  DB


Se AUDIT_TRAIL=OS memorizza  i record di audit in file del sistema operativo indicato dal parametro audit_file_dest.
Quando è impostato invece "DB" i records vengono memorizzati sotto la tabella SYS.AUD$ del tablespace SYSTEM fatta eccezione per i record che vengono sempre scritti nell'audit trail del sistema operativo. In dettaglio le connessione di sys vengono scritte nella directory adump quindi ogni volta che ci si collega come sysdba o vengono eseguiti job che partono da sys.

Queando è impostato "NONE" (di default) vengono memorizzati nei file di audit solo le connessioni come SYSDBA.

In particolare è possibile interrogare le due viste del dizionario dati per visualizzare l'output dell'audit standard.

SQL> select * from DBA_AUDIT_TRAIL order by timestamp desc;
SQL> select * from DBA_COMMON_AUDIT_TRAIL order by extended_timestamp desc;

SQL> select username,os_username,RETURNCODE,userhost,trunc(timestamp),count(*) failed_logins 

           from dba_audit_trail 

           where returncode<>   0 

           and timestamp>trunc(sysdate) 

           group by username,os_username,RETURNCODE,userhost,trunc(timestamp) order by 5

Questo parametro non può essere modificato in memoria, ma nel file spfile e quindi diventa effettivo dopo il restart del database:
1) SQL> ALTER SYSTEM SET audit_trail=DB SCOPE=SPFILE;
2) Riavviare il database.


Disabilitare Audit standard 

occorre modificare il file spfile sotto $ORACLE_HOME/dbs ed impostare il parametro:

1) SQL> alter system set audit_trail='NONE' scope=spfile;
2) Riavviare il database.


L'audit di default controlla le attività relative ai seguenti privilegi:
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
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

Inoltre monitora di default le seguenti istruzioni
ROLE
SYSTEM AUDIT
PUBLIC SYNONYM
DATABASE LINK
PROFILE
SYSTEM GRANT

Cancellare Audit standard
Per cancellare gli audit records memorizzati dall'audit rail standard è sufficiente svuotare la tabella
SQL> delete from SYS.AUD$;

Per cancellare un sottoinsieme:
SQL> delete from SYSAUD$ where trunc(NTIMESTAMP#) <= to_date('01/05/2019','dd/mm/yyyy');

Attenzione che questa operazione può richiedere tempo e pertanto conviene farla in un momento di inattività del db. Questo perché vengono generati REDO LOGS aggiuntivi.

Ci sono 3 livelli di audit

1) A livello di Statement o di Sessione

Audit di statememt di una tabella o sessione con riferimento ad una particolare utenza.

SQL> audit table by robin;
SQL> audit session by robin;

SELECT audit_option, failure, success, user_name
FROM dba_stmt_audit_opts
ORDER BY audit_option, user_name;


AUDIT_OPTION FAILURE        SUCCESS        USER_NAME
---------------------------------------------------------------------------------------------------
CREATE SESSION BY ACCESS BY ACCESS    ROBIN
TABLE                        BY ACCESS BY ACCESS ROBIN

Se ora accediamo all'istanze con le utenze robin e cyborg sulla tabella di riferimento, la seguente query restituisce le azioni sotto audit eseguite dagli utenti.

SELECT username, extended_timestamp, action_name
FROM dba_audit_trail
WHERE username in ( 'ROBIN','CYBORG')
order by 2 asc;

USERNAME EXTENDED_TIMESTAMP                         ACTION_NAME
------------------------------------------------------------------------------------------------------------------
CYBORG 20-GEN-20 15:48:34,254830000 EUROPE/BERLIN    SESSION REC
CYBORG 20-GEN-20 15:49:42,871702000 EUROPE/BERLIN    SESSION REC
ROBIN  20-GEN-20 15:50:08,256288000 EUROPE/BERLIN      INSERT
CYBORG 20-GEN-20 15:50:17,343816000 EUROPE/BERLIN    SESSION REC


Per cancellare gli audit.
SQL> noaudit table by robin;
SQL> noaudit session by robin;


2) A livello di oggetto
Mettere sotto audit un object.
Ad esempio monitoriamo la select su una tabella.

SQL> audit select on robin.tower;

SELECT owner, object_name, object_type, ins, sel
FROM dba_obj_audit_opts
WHERE owner='ROBIN'
AND object_name='TOWER';

3) A livello di Privilegi
Tutti i system privileges che sono nella tabella SYSTEM_PRIVILEGE_MAP possono essere messi sotto audit.

SQL> audit create tablespace, alter tablespace by all;

Note: usare ALL PRIVILEGES per mettere sotto audit tutti i privilegi di sistema.

Gli audit records si trovano nella tabella DBA_PRIV_AUDIT_OPTS.

select * from DBA_PRIV_AUDIT_OPTS;

Query per controllare gli audit abilitati sul db

--Check the parameter is enabled or disable for Audit

select name || '=' || value PARAMETER 

from sys.v_$parameter 

where name like '%audit%';

--Statement Audits Enabled on this Database

select * 

from sys.dba_stmt_audit_opts;

--Privilege Audits Enabled on this Database

select * 

from dba_priv_audit_opts;

-- Object Audits Enabled on this Database

select (owner ||'.'|| object_name) object_name,

alt, aud, com, del, gra, ind, ins, loc, ren, sel, upd, ref, exe

from dba_obj_audit_opts

where alt != '-/-' or aud != '-/-'

or com != '-/-' or del != '-/-'

or gra != '-/-' or ind != '-/-'

or ins != '-/-' or loc != '-/-'

or ren != '-/-' or sel != '-/-'

or upd != '-/-' or ref != '-/-'

or exe != '-/-';

-- Default Audits Enabled on this Database

select * from all_def_audit_opts;

Fine-grained auditing (FGA) consiste nell'effettuare l'audit in base al contenuto dei dati.

Creare una policy che memorizza gli accessi alla colonna di una tabella.

begin
DBMS_FGA.ADD_POLICY(
object_schema=>'ROBIN' 
 ,object_name=>'TOWER' 
 ,policy_name=>'ACCESS_TOWER' 
 ,audit_column=>'PIANI' 
 ,enable=>FALSE 
 ,statement_types=>'SELECT'
 );
 end;
 /

Abilitare la policy.

begin
DBMS_FGA.ENABLE_POLICY(
object_schema=>'ROBIN' 
 ,object_name=>'TOWER' 
 ,policy_name=>'ACCESS_TOWER');
end;
/

Per individuare la policy interrogare la vista del dizionario dati.

SELECT policy_name ,object_schema||'.'|| object_name object_name ,policy_column ,enabled ,audit_trail
FROM dba_audit_policies;

POLICY_NAME OBJECT_NAME POLICY_COLUMN ENABLED AUDIT_TRAIL
---------------------------------------------------------------------------------------------------------------------
ACCESS_TOWER ROBIN.TOWER           PIANI YES                DB+EXTENDED

Per vedere gli accessi interrogare la seguente tabella.

SELECT db_user, timestamp, userhost
FROM dba_fga_audit_traiL
WHERE policy_name='ACCESS_TOWER'

DB_USER TIMESTAMP  USERHOST
----------------------------------------------------------
CYBORG 20-GEN-20  WX142197
BIBI         20-GEN-20  WX142197
BIBI         20-GEN-20  WX142197
ROBIN         20-GEN-20  WX142197


Per disabilitare la policy FGA.

begin
DBMS_FGA.DISABLE_POLICY
(object_schema=>'ROBIN',
object_name=>'TOWER' ,
policy_name=>'ACCESS_TOWER');
end;
/

Per cancellare una policy FGA

begin
DBMS_FGA.DROP_POLICY
(object_schema=>'ROBIN',
object_name=>'TOWER' ,
policy_name=>'ACCESS_TOWER');
end;
/