Тетрадочка DBA

Posts Tagged ‘audit’

Настраиваю аудит

Posted by shane54lv на 22 Май, 2009

Включаем все что можно для отлова всех DDL:

audit alter system, cluster, context, database link, dimension, directory, index, materialized view, procedure, profile, public database link,
      public synonym, role, rollback segment, sequence, synonym, system audit, system grant, table,  tablespace, trigger, type, user, view;

audit alter sequence, alter table, comment table, grant directory, grant procedure, grant sequence, grant table, grant type;

Смотрим, что включено:

SELECT audit_option, success, failure
 FROM dba_stmt_audit_opts;

Смотрим напрямую в AUD$:

 FROM dba_audit_trail t1;

 FROM dba_audit_trail;

И если что – чистим:

truncate table sys.aud$;

WHERE TRUNC (ntimestamp#) < TO_DATE ('01-07-2009', 'DD-MM-YYYY'); [/sourcecode] Какие бывают AUDIT ACTION’s: [sourcecode language="sql"] SELECT * FROM audit_actions; [/sourcecode] Вьюшка для FGA и комбинированная, для FGA и обычного аудита: [sourcecode language="sql"] SELECT * FROM dba_fga_audit_trail; SELECT * FROM dba_common_audit_trail; [/sourcecode] Ну и запрос для анализа аудита: [sourcecode language="sql"] SELECT TO_CHAR (t1.TIMESTAMP, 'DD Mon YYYY HH24:MI') AS "Timestamp", t1.username, t1.userhost, t1.os_username, t1.owner, t1.obj_name, t1.action_name, t1.ses_actions FROM dba_audit_trail t1 WHERE t1.obj_name NOT LIKE 'KNS_%_TMP' -- system activity AND t1.obj_name NOT LIKE 'EUL4_%' -- system activity AND t1.obj_name != 'KNS_PAP_DIR' -- system activity AND t1.obj_name != 'PAP_DB_DIRECTORY' -- system activity AND TRUNC (t1.TIMESTAMP) = TO_DATE ('06-07-2009', 'DD-MM-YYYY') ORDER BY "Timestamp" DESC; SELECT a.userid, a.STATEMENT, a.obj$name, a.userid, a.ses$actions, a."NTIMESTAMP#", b.NAME, sqltext FROM SYS.aud$ a, SYS.audit_actions b WHERE a."ACTION#" = b.action; SELECT t1.owner, t1.segment_name, ROUND (t1.BYTES / 1024 / 1024, 0) AS "Mb" FROM dba_segments t1 WHERE t1.owner = 'SYS' AND t1.segment_name = 'AUD$'; [/sourcecode]


Posted in Query'ы | Отмечено: , | Leave a Comment »