SQL Verarbeitung in einer Stored Procedure nachvollziehen
Gerade bei komplexeren und umfangreichen Stored Proceduren ist es manchmal nicht so einfach nachzuvollziehen welche SQL Statements letztendlich ausgeführt werden. Natürlich kann man jetzt mit Debugging Techniken und Code lesen versuchen dies nachzuvollziehen, aber oft geht es nur darum festzustellen welche SQL Verarbeitungen bei einem bestimmten Aufruf ausgeführt werden.
Mit Oracle Native Auditing (aber Oracle 6) hat man hier keine Chance, da nur die Tatsache des Zugriffs auf ein Objekt auditiert wird, aber nicht irgendwelche Abhängigkeiten und Aufrufe.
Aber ab Oracle 12c sieht das anders aus - Unified Auditing mit Audit Policies können dafür genutzt werden!
Unified Auditing einschalten
Zuerst muß man sichergehen, daß Unified Auditing eingeschalten ist. Das kann man mit folgender Abfrage verifizieren:
select * from v$option where PARAMETER = 'Unified Auditing';
PARAMETER VALUE CON_ID
---------------- ----- ----------
Unified Auditing FALSE 0
In unserem Beispiel ist Unified Auditing noch nicht verfügbar - hier die Schritte, die Sie in diesem Fall machen müssen:
Jetzt einen kleinen Test bauen
Bitte beachten - der Test Benutzer darf kein DBA sein!
In unserem Beispiel nutzen wir den Benutzer Scott.
connect scott/tiger
create table DBM_dual (x number);
insert into DBM_dual values (1);
commit;
create or replace function dbm_test (zahl number) return number
is
myresult number;
begin
/* zuerst verifizieren ob DML funktionieren */
delete from DBM_dual;
insert into DBM_dual values (1);
/* und dann noch ein SELECT mit einer Bindvariable */
select zahl * 2 into myresult from DBM_dual;
return myresult;
end;
/
Als nächstes die Audit Policy anlegen - dazu braucht man entsprechende Berechtigungen.
connect system/oracle
noaudit policy DBM_AUDIT;
drop audit policy DBM_AUDIT;
create audit policy DBM_AUDIT
ACTIONS select on scott.dbm_dual,
insert on scott.DBM_dual,
update on scott.DBM_dual,
delete on scott.DBM_dual,
execute on scott.DBM_test;
audit policy DBM_AUDIT;
Jetzt können wir das ganze als Benutzer Scott verifizieren
connect scott/tiger
var myreturn number
begin
:myreturn := dbm_test(2);
end;
/
print myreturn;
MYRETURN
--------
4
Jetzt prüfen wir das Ergebnis. Um sicher zu stellen, daß die Audit Information schon auf der Disk steht, zuerst flushen.
connect system/oracle
EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
Jetzt finden wir innerhalb der gleichen SessionID alle Verarbeitungen und deren Reihenfolge, wenn man nach der ENTRY_ID sortiert.
Somit haben wir den Stored Procedure Aufruf und die darin ausgeführten Statements.
select SESSIONID, DBUSERNAME, ENTRY_ID, STATEMENT_ID, OBJECT_SCHEMA, OBJECT_NAME, UNIFIED_AUDIT_POLICIES,
SQL_TEXT, SQL_BINDS
from unified_audit_trail
where UNIFIED_AUDIT_POLICIES like '%DBM_AUDIT%' /* es koennte ja auch noch eine weitere Policy anschlagen */
order by entry_id;
SESSIONID DBUSER ENTRY STATEMENT OBJECT OBJECT UNIFIED_AUDIT
NAME ID ID SCHEMA NAME POLICIES SQL_TEXT SQL_BINDS
---------- ------ ----- --------- ------ -------- ------------- -------------------------------- ---------
854829528 SCOTT 1 11 SCOTT DBM_TEST DBM_AUDIT begin #1(0):
:myreturn := dbm_test(2);
end;
854829528 SCOTT 2 12 SCOTT DBM_DUAL DBM_AUDIT DELETE FROM DBM_DUAL
854829528 SCOTT 3 13 SCOTT DBM_DUAL DBM_AUDIT INSERT INTO DBM_DUAL VALUES (1)
854829528 SCOTT 4 14 SCOTT DBM_DUAL DBM_AUDIT SELECT :B1 * 2 FROM DBM_DUAL #1(1):2
Alle Statements auf die Tabellen, die nicht zuerst mit dem Stored Aufruf beginnen, muß man jetzt noch ignorieren.
In der Spalte SQL_BINDS findet man den Inhalt der Bind Variablen.
Hinweis
Wenn man Unified Auditing aktiviert, muß man sich auch um das cleanup des Unified Audit Trails kümmern.