Home >Oracle > Auditing for wrong sql



Simple and quick example auditing on insert, tested on Oracle 10.2.0.1.0.
The context: I have some batch jobs and ETL application cannot insert data on a table. Programmers get Oracle errors such ORA-01722 but unfortunally cannot print out the wrong sql statements and so they have problem debugging. Programmers asked for DBA help.
Among various Oracle tools, I pick up auditing tecnique. Auditing can be traced on files (OS) or on tables (DB, DB_EXTENDED).
In this case I choose on tables.
First, I make sure the Oracle initialisation parameters are properly setted, if not, I need to modify the parameter file and restart the db, in fact these parameter are not dynamic.

I log as dba and establish audit on any unsuccessfull insert over table TEST.t1:

I use the clause access instead of session/font>, I want intercept any single statement on the table, and whenever not successful is precisely what I am looking for.

Now that's all set, I try this basic test.
Schema TEST got this simple table t1(n number,d date);
Logged as TEST I execute the following insert statements:

insert into t1 values(90,sysdate);
insert into t1 values('aaa',sysdate);
insert into t1 values(110,sysdate);
insert into t1 values(110,to_date('2006-23-01','yyyy-mm-dd'));

Lines 2 and 4 are obiously not correct (string instead a number, incorrect month).
Logged as dba, I do aspect to find these incorrect statements on AUD$ table and in the dba_audit_trail view.
So I run a query like this:

select timestamp, userhost, terminal,owner,obj_name,action_name,returncode,sql_text from dba_audit_trail;

TIMESTAMP	USERHOST	TERMINAL	OWNER	OBJ_NAME	ACTION_NAME	RETURNCODE	SQL_TEXT
04/01/2007 23.12.08	MAGNAWORKGROUP\2M	2M	TEST	T1	INSERT	1722	insert into t1 values('aaa',sysdate)
04/01/2007 23.14.57	MAGNAWORKGROUP\2M	2M	TEST	T1	INSERT	1843	insert into t1 values(110,to_date('2006-23-01','yyyy-mm-dd'))

So it works as expected and I am ready for the serious work: incorrect insert actions have been recorded on the audit table.

When I am over with audititing, I disable the audit:

noaudit insert on test.t1;

I disable the init parameters. To delete the sys.Aud$ table records, just delete them.

---

---

Warning: include(ads/text468x15.html): failed to open stream: No such file or directory in D:\inetpub\webs\fishscriptcom\documents\view_document.php on line 131

Warning: include(): Failed opening 'ads/text468x15.html' for inclusion (include_path='.;C:\php\pear') in D:\inetpub\webs\fishscriptcom\documents\view_document.php on line 131

Tutorial
Flashback database to timestamp ripristinare il database ad una data determinata  [Oracle] 
Auditing for wrong sql How to intercept wrong dml sql statement   [Oracle] 
Using data pump in interactive mode   [Oracle] 
Alter session set current_schema How to query other user tables without specify the schema name  [Oracle] 
Procedura conteggio righe tutte le tabelle di uno schema esempio utilizzo NDS  [PLSQL] 
Inserire righe in tabelle utilizzando %rowtype scrivere codice pl/sql compatto  [PLSQL] 
Generazione di numeri casuali dbms_random.value  [PLSQL] 
Eseguire comando truncate di tutte le tabelle di uno schema atraverso dizionario dati e istruzione sql dinamica   [PLSQL] 
Dropping a database   [Oracle] 
Create new user basic sql statements  [Oracle] 
Viste note e appunti  [Oracle] 
NOT EXISTS clause basic example  [Oracle] 
Creazione di un database Oracle 10g da linea di comando esempio su sistemi Windows Server / XP: comandi e script  [Oracle] 
Clean up audit tables remove auditing logs   [Oracle] 
Nomenclatura oracle tipologie oggetti catalogo   [Oracle] 
Script
dbms_output.put_line Stampare spazi bianchi  [Oracle] 
Opzione DEFAULT per il valore di una colonna   [Oracle] 
Cercare le colonne in uno schema utilizzare le tabelle del dizionario dati di Oracle  [Oracle] 
Esempio script dos per eseguire export di schema automatizzare procedure di export e rinominare il file in base alla data corrente  [Oracle] 
Dato il nome di una vista ricava informazioni su tipo e attributi della colonna di riferimenti tabelle dizionario dati  [Oracle] 
Redo log ricollocare/trasferire i file di Redo  [Oracle] 
Scrivere su un file esempio con UTL_FILE package  [PLSQL] 
Leggere un file di testo   [PLSQL] 
Esempio funzione MOD in un loop ...  [PLSQL] 
Record sintassi di base: definizione, dichiarazione, assegnazione e stampa  [PLSQL] 
Errori intercettare codice e descrizione errori sql  [PLSQL] 
Esempio inserimento righe all'interno di un loop   [PLSQL] 
Cursore implicito SQL esempio con SQL%ROWCOUNT  [PLSQL] 
Esempio tipo VARRAY gestione array  [PLSQL] 
TYPE TABLE utilizzare le collections  [PLSQL] 
Comandi
Modificare il formato di default   [Oracle] 
systimestamp   [Oracle] 
CURRENT_TIMESTAMP   [Oracle] 
Spostare un indice in un diverso tablespace   [Oracle] 
analyze   [Oracle] 
Costruire istruzioni sql dinamiche il carattere speciale &   [Oracle] 
Exp utility esempio export di singole tabelle  [Oracle] 
Group by mese di colonna di tipo data   [Oracle] 
ALTER SEQUENCE Modificare una sequenza  [Oracle] 
Spazio libero nei tablespace utilizzo tabella DBA_FREE_SPACE  [Oracle] 
NLS_DATABASE_PARAMETERS ricavare i parametri del database  [Oracle] 
Lista control files   [Oracle] 
Spostare una tabella in un diverso tablespace   [Oracle] 
Creazione di una directory in ambiente oracle   [Oracle] 
Funzione TRUNC esempio visualizzazione primo giorno del mese corrente  [Oracle] 
Codici errore
Errore connessione client ORA-12638 Credential retreival failed  [Oracle] 
impdp / expdp importare in sql metadata del database datapump per esportare ed importare metatadati   [Oracle] 
ORA-06553: PLS-213,ORA-06553: PLS-213,ORA-06553: PLS-213 Errori in fase di creazione database, occorre eseguire gli script di catalog  [ERRORI] 
Operazioni sulle date utilizzando il nome dei mesi NLS_DATE_LANGUAGE ORA-01843: mese non valido / not a valid month  [Oracle] 

Marco Magnani marcomagnani@fishscript.com