====== Erstellen eines Protokolls zur Überwachung von fehlerhaften SQL Statements in der Datenbank - AFTER SERVERERROR Trigger verwenden======
**Erstellt November 2014**
Oracle 10g/11i/12c
\\
**Nutzen:**\\
Oft ergibt sich das Problem, das die original SQL Fehlermeldung nicht in den Applikation Layer einer Applikation wirklich sichtbar werden.
Läuft eine Anwendung als Hintergrund Job werden oft die SQL Fehler gar nicht mehr wahrgenommen oder im Log File übersehen.
Mit dem Einsatz des Error Trigger können die meisten SQL Fehler aufgezeichnet werden, in vielen Fällen auch mit den exakten SQL Statement, dass den Fehler verursacht hat.
**Ziel:**
* Erkennen von Applikationsfehlern
* Erkennen von Angriffsversuchen
Mit dem Database Event Trigger "AFTER SERVERERROR" kann fehlerhaftes SQL in der DB aufgezeichnet werden. \\
Über eine autonome Transaktion "pragma autonomous_transaction" kann diese Information in einer Error Tabelle gespeichert werden.
Da der SYS/SYSMAN/DBSMNP User sehr viele interne Fehler verursachen, werden diese User zur Zeit in aber "ausgeblendet" um nicht zu viele Log Einträge in der Datenbank zu erzeugen.
Damit die Tabelle nicht "überläuft" wird eine kleine Procedure und ein einfacher Job angelegt, der regelmäßig die alten Daten löscht.
**Risko:**
Der Trigger muss natürlich immer gültig sein! D.h. nach dem Anlegen als SYSTEM prüfen, das der Trigger auch valid ist.
Ist der Trigger invalid, kann es zu Fehlermeldungen mit Fehlern in der Applikation kommen.
Test im Training daher zuvor notwendig!
Beispiel Script um diesen Error Trigger anzulegen ( muss als SYS lokal auf der Maschine aufgerufen werden:
**Installation:**
* Script auf lokalen DB Server kopieren
* An der DB mit dem User SYS anmelden
* Script mit @create_global_errorlog.sql starten
Was wird angelegt:
* Tabelle SYSTEM.ora_error wird auf dem Tablespace SYSAUX angelegt
* Primary Key wird mit passenden Index SYSTEM.idx_ora_error_pk auf der Tabelle erzeugt
* Damit jeder die Error Tabelle auswerten kann, wird das lese Recht auf die Tabelle an Public gegranted ( kann optional bei Bedarf ausgeblendet werden)
* Eine Sequence für eine fortlaufende ID der Tabelle SYSTEM.ora_error_seq wird angelegt
* Der Logon Trigger wird erzeugt
* Der Trigger feuert zwar bei jeden Fehler, aber nur die Fehler von "normalen" Anwender werden aufgezeigt.
* Sollen auch andere User in der DB von der Aufzeichnung ausgeschlossen werden, ist die Liste "IF ora_login_user in ('SYS','DBSNMP','SYSMAN')" zu erweitern
* Für das Löschen der Einträge nach einer gewissen Zeit wird die Procedure SYSTEM.deleteOraErrorTrigTab erzeugt
* Über den Parameter p_keepdays kann beim Aufruf der Procedure eingestellt, wie viel Tage die Daten aufgehoben werden sollen
* Ein Job wird angelegt um regelmäßig die Löschroutine jeden Tag um 4:00 aufzurufen
* Über den Parameter 15 (p_keepdays => 15) wird definiert das die letzen 15 Tage in der DB verbleiben sollen
* Das letzte Statement in der Analyse zeigt ein Beispiel für eine regelmäßige Auswertung der Error Log Tabelle
**Test:**
* Anmelden mit einen normalen Standard User wie scott
* SQL Fehler erzeugen mit "select * from TABLE_NOT_EXITS"
* Auswerten des Errorlogs mit "select * from SYSTEM.ora_error "
* Fehler muss in der Tabelle auftauchen
**Tabelle:**
set serveroutput on size 1000000
prompt
DOC
-------------------------------------------------------------------------------
Creating Error Log Tab for SQL Errors over the complete DB
-------------------------------------------------------------------------------
#
prompt
DOC
-------------------------------------------------------------------------------
Errorlog Table / Sequence and Trigger will be created
-------------------------------------------------------------------------------
#
prompt
prompt '-------------------------------------------------------------------------------'
prompt
set serveroutput on size 1000000
exec DBMS_OUTPUT.put_line('start create_global_errorlog.sql');
prompt "Create Table SYSTEM.ora_error and SEQUENCE SYSTEM.ora_error_seq"
CREATE TABLE SYSTEM.ora_errors
(
id NUMBER
,log_date DATE
,log_usr VARCHAR2 (30)
,terminal VARCHAR2 (50)
,err_nr NUMBER (10)
,err_msg VARCHAR2 (4000)
,stmt CLOB
,inst_id number(2)
) tablespace sysaux
/
create unique index system.idx_ora_error_pk on system.ora_errors(id) tablespace sysaux;
alter table system.ora_errors add constraint pk_ora_errpr primary key (id) enable validate;
create index system.idx_ora_error_date on system.ora_errors(log_date) tablespace sysaux;
grant select on system.ora_error to public;
-----------
CREATE SEQUENCE SYSTEM.ora_errors_seq
/
-----------
prompt "Create the trigger log_error"
CREATE OR REPLACE TRIGGER log_error
AFTER SERVERERROR
ON DATABASE
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_id NUMBER;
v_sql_text ORA_NAME_LIST_T;
v_stmt CLOB;
v_count NUMBER;
BEGIN
v_count := ora_sql_txt (v_sql_text);
IF v_count >= 1
THEN
FOR i IN 1 .. v_count
LOOP
v_stmt := v_stmt || v_sql_text (i);
END LOOP;
END IF;
FOR n IN 1 .. ora_server_error_depth
LOOP
IF ora_login_user in ('SYS','DBSNMP','SYSMAN')
THEN
-- do nothing
NULL;
ELSE
SELECT SYSTEM.ora_errors_seq.NEXTVAL INTO v_id FROM DUAL;
INSERT INTO SYSTEM.ora_errors (id
,log_date
,log_usr
,terminal
,err_nr
,err_msg
,stmt
,inst_id)
VALUES (v_id
,SYSDATE
,ora_login_user
,ora_client_ip_address
,ora_server_error (n)
,ora_server_error_msg (n)
,v_stmt
,ora_instance_num);
END IF;
COMMIT;
END LOOP;
END log_error;
/
------ Clean procedure
CREATE or REPLACE PROCEDURE system.deleteOraErrorTrigTab (p_keepdays NUMBER)
IS
BEGIN
DELETE FROM SYSTEM.ora_errors WHERE log_date+p_keepdays < sysdate;
COMMIT;
END;
/
------ Clean procedure job
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job => X
,what => 'begin system.deleteOraErrorTrigTab (p_keepdays => 15); end;'
,next_date => sysdate
,interval => 'to_date(to_char(sysdate+1,''mm/dd/yyyy'')||'' 04:00:00'',''mm/dd/yyyy hh24:mi:ss'')'
,no_parse => FALSE
,instance => 0
,force => TRUE
);
END;
/
commit;
show errors
------ Analyse example:
column anzahl format 9999999999
column hour format A9
column LOG_USR format A10
column ERR_NR format 999999999
column mesg format A30
SELECT COUNT (*) as anzahl
,TO_CHAR (log_date, 'dd/mm hh24')||'h' as hour
,nvl(LOG_USR,'n/a') as LOG_USR
,ERR_NR
,substr(ERR_MSG,1,200) mesg
FROM SYSTEM.ora_errors
where nvl(log_usr,'n/a') not in ('SYS','SYSMAN','DBSNMP')
GROUP BY TO_CHAR (log_date, 'dd/mm hh24')||'h'
,nvl(LOG_USR,'n/a')
,ERR_NR
,substr(ERR_MSG,1,200)
order by 2,1
;
------------------------------------------------------
prompt
exec DBMS_OUTPUT.put_line('end create_global_errorlog.sql');
prompt
----
==== Create Trigger File from github ====
[gistgit project=gpipperr/OraPowerShell&file=sql/01-db-setup/create_global_errorlog.sql ]
See => https://github.com/gpipperr/OraPowerShell/blob/master/Ora_SQLPlus_SQLcL_sql_scripts/01-db-setup/create_audit_log_database.sql
----
==== Auswerten ====
Siehe auch https://github.com/gpipperr/OraPowerShell/blob/master/Ora_SQLPlus_SQLcL_sql_scripts/01-db-setup/report_global_errorlog.sql
--==============================================================================
-- GPI - Gunther Pippèrr
-- Desc: HTML Report for the entries in the audit log
-- see : https://www.pipperr.de/dokuwiki/doku.php?id=dba:oracle_sqlfehler_protokoll
-- Date: September 2019
--
--==============================================================================
col SPOOL_NAME_COL new_val SPOOL_NAME
SELECT replace(ora_database_name||'_'||SYS_CONTEXT('USERENV','HOST')||'_'||to_char(sysdate,'dd_mm_yyyy_hh24_mi')||'_sql_error_log.html','\','_')
--' resolve syntax highlight bug FROM my editer .-(
AS SPOOL_NAME_COL
FROM dual
/
set verify off
SET linesize 450 pagesize 2000
column anzahl format 999G999 heading "Count"
column first_log_entry format A18 heading "First Entry"
column last_log_entry format A18 heading "Last Entry"
column LOG_USR format A20 heading "DB Schema"
column ERR_NR format 99999 heading "Ora Err | Number"
column mesg format A100 heading "Ora Err | Message"
column HOUR format A16 heading "Hour"
column stmt format A250 heading "SQL Statemment"
spool &&SPOOL_NAME
set markup html on
ttitle left "SQL Error Total Log Summary" skip 2
SELECT COUNT (*) AS anzahl
,to_char(min(log_date),'dd.mm.yyyy hh24:mi') first_log_entry
,to_char(max(log_date),'dd.mm.yyyy hh24:mi') last_log_entry
,nvl(LOG_USR,'n/a') AS LOG_USR
,ERR_NR
,substr(ERR_MSG,1,300) mesg
FROM SYSTEM.ora_errors
WHERE nvl(log_usr,'n/a') NOT IN ('SYS','SYSMAN','DBSNMP')
GROUP BY nvl(LOG_USR,'n/a')
,ERR_NR
,substr(ERR_MSG,1,300)
ORDER BY 2,1
/
ttitle left "SQL Error Hour Report" skip 2
SELECT COUNT (*) AS anzahl
,TO_CHAR (log_date, 'dd.mm.yyyy hh24')||'h' AS HOUR
,nvl(LOG_USR,'n/a') AS LOG_USR
,ERR_NR
,substr(ERR_MSG,1,300) mesg
FROM SYSTEM.ora_errors
WHERE nvl(log_usr,'n/a') NOT IN ('SYS','SYSMAN','DBSNMP')
GROUP BY TO_CHAR (log_date, 'dd.mm.yyyy hh24')||'h'
,nvl(LOG_USR,'n/a')
,ERR_NR
,substr(ERR_MSG,1,300)
ORDER BY 2
/
ttitle left "SQL Error Log All Entries " skip 2
set long 64000
with ErrorLog as
( select stmt
, log_date
, LOG_USR
, ERR_NR
, substr(ERR_MSG,1,300) mesg
, dbms_lob.getlength(STMT) len
FROM SYSTEM.ora_errors
WHERE nvl(log_usr,'n/a') NOT IN ('SYS','SYSMAN','DBSNMP')
)
select
COUNT (*) AS anzahl
, to_char(min(log_date),'dd.mm.yyyy hh24:mi') first_log_entry
, to_char(max(log_date),'dd.mm.yyyy hh24:mi') last_log_entry
, LOG_USR
, ERR_NR
, mesg
, dbms_lob.substr(stmt,4000,1) sql_part1
, case when len > 4000 then dbms_lob.substr(stmt,4000,4001) end sql_part2
, case when len > 8000 then dbms_lob.substr(stmt,4000,8001) end sql_part3
, case when len > 12000 then dbms_lob.substr(stmt,4000,12001) end sql_part4
, case when len > 16000 then dbms_lob.substr(stmt,4000,165001) end sql_part5
FROM ErrorLog
GROUP BY LOG_USR
, ERR_NR
, mesg
, dbms_lob.substr(stmt,4000,1)
, case when len > 4000 then dbms_lob.substr(stmt,4000,4001) end
, case when len > 8000 then dbms_lob.substr(stmt,4000,8001) end
, case when len > 12000 then dbms_lob.substr(stmt,4000,12001) end
, case when len > 16000 then dbms_lob.substr(stmt,4000,165001) end
ORDER BY 1
/
set markup html off
spool off
ttitle off
-- works only in a ms windows environment
-- auto start of the result in a browser window
host &&SPOOL_NAME
===== Quellen =====
Oracle:
* http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#CHDHFBBG
Web:
* http://www.red-database-security.com/scripts/oracle_error_trigger.html
Bzgl. Oracle System Events siehe http://www.morganslibrary.org/reference/system_events.html