Audit Oracle : activation, consultation et purge de la piste d'audit
Activer l'audit Oracle, auditer connexions et instructions en échec, lire les vues d'audit, purger SYS.AUD$ avec DBMS_AUDIT_MGMT et localiser l'alert log.
L'audit Oracle enregistre qui se connecte à une base et quelles instructions, quels privilèges ou quels objets sont utilisés. Cet article couvre la localisation des répertoires de diagnostic et de l'alert log, l'activation de la piste d'audit standard, l'audit des connexions et des instructions en échec, la lecture des résultats et la purge de la piste d'audit.
Localiser les répertoires de diagnostic et l'alert log
À partir de la version 11g, V$DIAG_INFO donne l'emplacement des répertoires de diagnostic, dont l'alert log :
set linesize 121
col name format a30
col value format a75
SELECT * FROM gv$diag_info;
Sur les versions plus anciennes (avant 11g), consulter les paramètres d'audit et de dump :
show parameter audit;
show parameter background;
background_dump_dest contient l'alert log. Pour la trace SQL, voir SQL trace, TKPROF et trcsess (en anglais).
Activer la piste d'audit
Le paramètre d'initialisation AUDIT_TRAIL définit où les enregistrements d'audit sont stockés.
| Valeur | Comportement |
|---|---|
NONE |
L'audit est désactivé (valeur par défaut sur les anciennes versions). |
DB |
Les enregistrements sont stockés dans la table SYS.AUD$. |
DB,EXTENDED |
Comme DB, et les colonnes SQL_BIND et SQL_TEXT sont aussi renseignées. |
XML |
Les enregistrements sont stockés dans des fichiers XML. |
XML,EXTENDED |
Comme XML, avec SQL_BIND et SQL_TEXT renseignées. |
OS |
Les enregistrements sont écrits dans des fichiers texte du système d'exploitation (AUDIT_FILE_DEST donne le répertoire). |
show parameter audit_trail;
alter system set audit_trail=db,extended scope=spfile;
Autres formes :
ALTER SYSTEM SET audit_trail = NONE SCOPE=SPFILE;
-- OS: audit records are written to the operating system audit trail
ALTER SYSTEM SET audit_trail = OS SCOPE=SPFILE;
-- DB: audit records are written to the data dictionary table SYS.AUD$
ALTER SYSTEM SET audit_trail = DB SCOPE=SPFILE;
-- DB_EXTENDED (11g): as DB, with the SQL text and bind variables
ALTER SYSTEM SET audit_trail = DB_EXTENDED SCOPE=SPFILE;
Le paramètre est statique : redémarrer l'instance pour qu'il soit pris en compte.
SHUTDOWN IMMEDIATE
STARTUP
Niveaux d'audit
Il existe quatre niveaux :
- Connexion et déconnexion : surveille les connexions.
- Ordre SQL : audit par type d'ordre SQL.
- Privilège : audit d'un privilège système (
SELECT ANY,DROP ANY,CREATE ANY...). - Objet : un ordre précis sur un objet précis (par exemple
SELECTsurSCOTT.EMP).
À chaque niveau, on peut auditer les succès, les échecs ou les deux, avec un enregistrement par commande (BY ACCESS) ou un par session (BY SESSION). La commande AUDIT définit ce qui est audité : le type d'action, les tentatives réussies et/ou échouées, et la granularité.
AUDIT SESSION;
AUDIT SESSION WHENEVER SUCCESSFUL;
AUDIT SESSION WHENEVER NOT SUCCESSFUL;
AUDIT CONNECT WHENEVER NOT SUCCESSFUL;
-- one record per command
AUDIT CREATE ANY PROCEDURE
BY ACCESS
WHENEVER SUCCESSFUL;
-- one record per session, failed attempts only
AUDIT SELECT TABLE, DELETE TABLE
BY SESSION
WHENEVER NOT SUCCESSFUL;
-- object audit
AUDIT INSERT ON scott.dept;
Pour arrêter l'audit des sessions :
NOAUDIT SESSION;
Vues d'audit du dictionnaire de données
| Vue | Contenu |
|---|---|
| STMT_AUDIT_OPTION_MAP | codes des types d'option |
| AUDIT_ACTIONS | codes des actions |
| ALL_DEF_AUDIT_OPTS | options d'audit objet par défaut |
| DBA_STMT_AUDIT_OPTS | options d'audit d'ordres SQL courantes |
| DBA_PRIV_AUDIT_OPTS | options d'audit de privilèges système courantes |
| DBA_OBJ_AUDIT_OPTS, USER_OBJ_AUDIT_OPTS | options d'audit sur tous les objets / sur ceux de l'utilisateur |
| DBA_AUDIT_TRAIL, USER_AUDIT_TRAIL | toutes les entrées d'audit / celles de l'utilisateur |
| DBA_AUDIT_OBJECT, USER_AUDIT_OBJECT | entrées d'audit objet (toutes / utilisateur) |
| DBA_AUDIT_SESSION, USER_AUDIT_SESSION | entrées de connexion et déconnexion (toutes / utilisateur) |
| DBA_AUDIT_STATEMENT, USER_AUDIT_STATEMENT | entrées GRANT, REVOKE, AUDIT, NOAUDIT et ALTER SYSTEM (toutes / utilisateur) |
| DBA_AUDIT_EXISTS | entrées AUDIT EXISTS et AUDIT NOT EXISTS |
Vérifier les options d'audit en cours
Interroger les vues %AUDIT_OPTS :
SELECT * FROM sys.dba_obj_audit_opts
WHERE owner = 'SCOTT' AND object_name LIKE 'EMP%';
OWNER OBJECT_NAME OBJECT_TY ALT AUD COM DEL GRA IND INS LOC
----- ----------- --------- --- --- --- --- --- --- --- ---
SCOTT DEPT TABLE -/S -/- -/- A/- -/- S/S -/- -/- ...
SCOTT D VIEW -/- -/- -/- -/A -/- -/- -/- -/- ...
Lecture des indicateurs : - pas d'audit, S par session, A par accès. La valeur à gauche de / concerne les tentatives réussies, celle de droite les tentatives échouées. La première ligne se lit : pour la table SCOTT.DEPT, ALTER est audité en cas d'échec par session, DELETE en cas de succès par accès, et INDEX en cas de succès et d'échec par session.
Pour lister tout ce qui est actuellement activé :
-- Audit-related parameters
select name || '=' || value PARAMETER from sys.v_$parameter where name like '%audit%';
-- Statement audits
column user_name format a10
column audit_option format a40
select * from sys.dba_stmt_audit_opts;
-- Privilege audits
select * from dba_priv_audit_opts;
-- Object audits
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
select * from all_def_audit_opts;
Lire les résultats de l'audit
SELECT os_username, username, terminal, timestamp
FROM dba_audit_session;
SELECT os_username, username, terminal, timestamp, owner, obj_name
FROM dba_audit_object;
SELECT username, returncode, action_name, comment_text, sessionid, proxy_sessionid
FROM dba_audit_trail
ORDER BY sessionid;
Nombre d'enregistrements de connexion, détail et agrégation par utilisateur et par mois :
select count(*) from dba_audit_session;
COL OSUSER FOR A20
COL DBUSER FOR A20
COL TERMINAL FOR A15
SET LINESIZE 150
SET PAGESIZE 1000
SELECT os_username "OSUSER",
username "DBUSER",
terminal,
returncode,
timestamp,
TO_CHAR(timestamp, 'DD-MON-YYYY HH24:MI:SS') LOGON_TIME,
TO_CHAR(logoff_time, 'DD-MON-YYYY HH24:MI:SS') LOGOFF_TIME
FROM dba_audit_session
WHERE logoff_time IS NOT NULL
AND logoff_time > to_date('01-12-2021','DD-MM-YYYY')
AND username != 'MONITOR_USER'
ORDER BY timestamp;
COL OSUSER FOR A20
COL DBUSER FOR A20
COL TERMINAL FOR A15
SET LINESIZE 150
SET PAGESIZE 1000
SELECT os_username "OSUSER",
username "DBUSER",
terminal,
count(*),
to_char(timestamp, 'YYYY-MM') month
FROM dba_audit_session
WHERE timestamp IS NOT NULL
GROUP BY os_username, username, terminal, to_char(timestamp, 'YYYY-MM')
ORDER BY to_char(timestamp, 'YYYY-MM') DESC;
Dernier mois d'activité par utilisateur de base (utile pour repérer les comptes inutilisés) :
COL DBUSER FOR A20
SET LINESIZE 150
SET PAGESIZE 1000
SELECT
username "DBUSER",
count(*),
to_char(max(timestamp), 'YYYY-MM') month
FROM dba_audit_session
WHERE timestamp IS NOT NULL
GROUP BY username
ORDER BY to_char(max(timestamp), 'YYYY-MM') DESC;
Trouver l'origine des échecs de connexion
Activer l'audit des connexions en échec, puis lire SYS.AUD$ pour le code retour 1017 :
show parameter audit_trail
audit session whenever not successful;
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set linesize 400
set pagesize 10000
col ntimestamp# for a30 heading "Timestamp"
col userid for a20 heading "Username"
col userhost for a15 heading "Machine"
col spare1 for a15 heading "OS User"
col comment$text for a80 heading "Details" wrap
select ntimestamp#, userid, userhost, spare1, comment$text
from sys.aud$
where returncode=1017
order by 1;
La même information est disponible via DBA_AUDIT_TRAIL :
SELECT os_username, username, terminal,
to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') as timestamp,
returncode
FROM DBA_AUDIT_TRAIL
WHERE returncode <> 0
ORDER BY timestamp DESC;
RETURNCODE est le numéro de l'erreur ORA- renvoyée au client :
0: succès1017: mot de passe invalide28000: compte verrouillé
Auditer les instructions en échec et le DDL
Auditer toutes les instructions en échec sur les tables et procédures, puis désactiver :
AUDIT SELECT TABLE, INSERT TABLE, UPDATE TABLE, DELETE TABLE, EXECUTE PROCEDURE
BY ACCESS WHENEVER NOT SUCCESSFUL;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
Auditer la création et la suppression de tables :
AUDIT CREATE TABLE, SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;
AUDIT EXECUTE PROCEDURE;
AUDIT CREATE ANY TABLE;
AUDIT DROP ANY TABLE;
Consulter les enregistrements :
SELECT USERNAME, OS_USERNAME, ACTION_NAME, OBJ_NAME,
TO_CHAR(TIMESTAMP,'dd/mm/yyyy hh24:mi') Time, PRIV_USED
FROM dba_audit_trail
WHERE ACTION_NAME='CREATE TABLE';
select extended_timestamp, username, owner, obj_name, action_name, sql_text
from dba_audit_trail
where action_name not in ('LOGON','LOGOFF','LOGOFF BY CLEANUP')
order by extended_timestamp desc;
Vérifier quelles options d'audit liées aux sessions sont actives :
SELECT user_name, audit_option, success, failure
FROM DBA_STMT_AUDIT_OPTS
WHERE audit_option LIKE '%SESSION%';
Désactiver :
NOAUDIT CREATE TABLE, SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;
NOAUDIT EXECUTE PROCEDURE;
NOAUDIT CREATE ANY TABLE;
NOAUDIT DROP ANY TABLE;
Auditer toutes les actions sur un objet
Avec l'audit traditionnel :
alter system set audit_trail=db,extended scope=spfile;
audit all on scott.emp by access;
select extended_timestamp, username, owner, obj_name, action_name, sql_text
from dba_audit_trail
order by extended_timestamp desc;
Il n'existe pas d'option « auditer tous les objets ». Avec l'audit unifié, créer une politique et y ajouter les objets :
-- create the policy
create audit policy my_policy actions all on hr.regions;
alter audit policy my_policy actions all on hr.locations;
-- enable the policy
audit policy my_policy;
-- generate the statements for every table of a schema
select 'alter audit policy my_policy actions all on '||owner||'.'||table_name||';'
from dba_tables
where owner in ('HR','OE');
Inspecter les politiques et enregistrements de l'audit unifié :
SELECT policy_name, enabled_opt, user_name FROM audit_unified_enabled_policies;
SET LINESIZE 200
SET PAGESIZE 300
COLUMN audit_option FORMAT A40
COLUMN condition_eval_opt FORMAT A10
COLUMN audit_condition FORMAT A50
COLUMN policy_name FORMAT A20
SELECT policy_name, audit_option, condition_eval_opt, audit_condition
FROM audit_unified_policies
WHERE policy_name in ('ORA_SECURECONFIG','ORA_LOGON_FAILURES')
ORDER BY 1,2;
select count(*), action_name, unified_audit_policies
from cdb_unified_audit_trail
group by action_name, unified_audit_policies
order by 1 desc;
Pour lister toutes les vues liées à l'audit :
SELECT view_name
FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;
Désactiver l'audit
NOAUDIT réinitialise les options d'audit d'ordres, de privilèges et d'objets :
NOAUDIT;
NOAUDIT session;
NOAUDIT session BY scott, hr;
NOAUDIT DELETE ON emp;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
NOAUDIT ALL;
NOAUDIT ALL PRIVILEGES;
NOAUDIT ALL ON DEFAULT;
NOAUDIT session BY appowner;
NOAUDIT create session BY appowner;
NOAUDIT SELECT TABLE BY appowner;
NOAUDIT INSERT TABLE BY appowner;
NOAUDIT EXECUTE PROCEDURE BY appowner;
NOAUDIT DELETE TABLE BY appowner;
NOAUDIT DELETE ANY TABLE BY appowner;
Purger la piste d'audit
Vérifier l'espace utilisé
Lorsque la piste d'audit est stockée dans le tablespace SYSAUX, SYS.AUD$ peut devenir volumineuse :
SET LINES 400 PAGES 800
COL SCHEMA_NAME FOR A20
COL OCCUPANT_NAME FOR A25
COL OCCUPANT_DESC FOR A55
SELECT SCHEMA_NAME, OCCUPANT_NAME, OCCUPANT_DESC,
ROUND(SPACE_USAGE_KBYTES/1024) SPACE_USAGE_MB
FROM V$SYSAUX_OCCUPANTS
ORDER BY SPACE_USAGE_MB;
SELECT SEGMENT_TYPE, ROUND(SUM(BYTES)/1024/1024) MB
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'SYSAUX'
GROUP BY SEGMENT_TYPE
ORDER BY 2;
COL OWNER FOR A20
COL SEGMENT_NAME FOR A20
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME='AUD$';
Vérifier la configuration de la gestion de l'audit :
COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20
SET LINES 2000
SELECT * FROM dba_audit_mgmt_config_params;
Exemple de résultat après INIT_CLEANUP avec un intervalle de 12 heures :
DB AUDIT TABLESPACE SYSAUX STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE SYSAUX FGA AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL
AUDIT FILE MAX AGE 5 OS AUDIT TRAIL
AUDIT FILE MAX AGE 5 XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 12 STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 12 FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 12 OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 12 XML AUDIT TRAIL
Vérifier les dernières purges et, en RAC, le dernier horodatage d'archivage :
SELECT * FROM DBA_AUDIT_MGMT_CLEAN_EVENTS WHERE CLEANUP_TIME > SYSDATE - 31;
-- RAC
COLUMN AUDIT_TRAIL FORMAT A20
COLUMN LAST_ARCHIVE_TS FORMAT A40
select AUDIT_TRAIL, RAC_INSTANCE, DATABASE_ID, CONTAINER_GUID, LAST_ARCHIVE_TS
from DBA_AUDIT_MGMT_LAST_ARCH_TS;
Purger avec DBMS_AUDIT_MGMT
Initialiser le nettoyage une fois, avec un intervalle par défaut en heures :
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
DEFAULT_CLEANUP_INTERVAL => 12 );
END;
/
Déclarer que tous les enregistrements d'audit standard de type base antérieurs à une date ont été archivés :
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => to_timestamp('2013-01-01 10:00:00','YYYY-MM-DD HH24:MI:SS'),
rac_instance_number => null
);
END;
/
Purger les enregistrements plus anciens que cet horodatage :
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/
-- purge everything, ignoring the last archive timestamp
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => FALSE);
END;
/
Pour la piste d'audit OS, les mêmes procédures prennent AUDIT_TRAIL_OS :
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
last_archive_time => TO_TIMESTAMP('21-MAR-18','DD-MON-YY'),
rac_instance_number => 1);
END;
/
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
use_last_arch_timestamp => TRUE);
END;
/
Vérifier le résultat avant et après la purge, et contrôler l'existence du job de purge automatique :
select min(timestamp), max(timestamp) from dba_audit_session;
select dbid, count(*) from aud$ group by dbid;
SELECT COUNT(*) FROM aud$;
select * from DBA_AUDIT_MGMT_CLEAN_EVENTS where CLEANUP_TIME > sysdate - 31;
SELECT distinct job_name from dba_scheduler_job_run_details where job_name like '%PURGE%';
En 11g sous SQL*Plus, il est aussi possible de vider entièrement la table :
truncate table sys.aud$;
-- or
DELETE FROM SYS.AUD$;
Contournement pour les anciennes versions
Sans DBMS_AUDIT_MGMT, une procédure peut supprimer périodiquement les anciens enregistrements :
create or replace procedure purge_audit_trail (days in number) as
purge_date date;
begin
purge_date := trunc(sysdate-days);
dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
purge_date || ' started');
delete from aud$ where ntimestamp# < purge_date;
commit;
dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
purge_date || ' has completed');
end;
/
La planifier avec le scheduler (ici un job qui conserve 31 jours) :
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'AUDIT_PURGE',
job_type => 'PLSQL_BLOCK',
job_action => 'begin purge_audit_trail(31); end;',
schedule_name => 'MAINTENANCE_WINDOW_GROUP',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Audit Trail Purge',
auto_drop => FALSE,
enabled => TRUE);
END;
/
Erreur ORA-01688 et occupation des tablespaces
Lorsqu'un segment ne peut plus s'étendre (ORA-01688), vérifier l'espace libre par tablespace :
SELECT df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ((df.totalspace - tu.totalusedspace) / df.totalspace)) "Pct. Free"
FROM (SELECT tablespace_name, round(sum(bytes) / 1048576) TotalSpace FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name FROM dba_segments GROUP BY tablespace_name) tu
WHERE df.tablespace_name = tu.tablespace_name;
Dernières instructions exécutées par un utilisateur
Rechercher dans la shared pool les instructions les plus récentes d'un utilisateur de base (remplacer le nom d'utilisateur dans le filtre) :
alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS";
set linesize 400
set pagesize 0
column MODULE format a20
column SQL_PROFILE format a5
column SERVICE format a20
column LAST_LOAD_TIME format a20
SELECT
S.LAST_ACTIVE_TIME,
S.MODULE,
S.SQL_FULLTEXT,
S.SQL_PROFILE,
S.EXECUTIONS,
S.LAST_LOAD_TIME,
S.PARSING_USER_ID,
S.SERVICE
FROM
SYS.V_$SQL S,
SYS.ALL_USERS U
WHERE
S.PARSING_USER_ID=U.USER_ID
AND UPPER(U.USERNAME) IN ('ORACLE_USER_NAME_HERE')
ORDER BY TO_DATE(S.LAST_LOAD_TIME, 'YYYY-MM-DD/HH24:MI:SS') desc;
Remarques
- Les modifications de
AUDIT_TRAILnécessitent un redémarrage de l'instance (SCOPE=SPFILE). TRUNCATEetDELETEsurSYS.AUD$suppriment les preuves d'audit : archiver d'abord les enregistrements s'ils doivent être conservés.- Auditer chaque
SELECTouLOGONréussi peut faire croîtreSYS.AUD$rapidement ; prévoir un job de purge. - La procédure de purge s'appuie sur
DBMS_SYSTEM, un package non documenté. - L'audit unifié (12c et suivants) remplace la piste d'audit traditionnelle et utilise des politiques à la place des commandes
AUDITci-dessus.