logging et triggers

Version imprimable

19/03/2007, 16h49
ufoinfo

logging et triggers

Bonjour tout le monde ,

actuellement j'ai une base de donness oracle sur laquelle je veux logger les select update et delete(DML). en utilisant la vue v$sql parfois je vois les statements(colonne sql_text) parfois non. j'ai bcp cherché (trcsess et tkprof aussi) mais je me suis la meilleur façon c'est d'ecrire des triggers qui insere le texte de l'instruction sql qui l'a ppelé ds une table. justement le probleme c'est comment je peux obtenir ce texte la à l'interieur du trigger ?? MErci de m'aider.

merci
19/03/2007, 16h54
McM

Tu ne peux pas par des triggers.
Recherche AUDIT, tu devrais trouver des sujets déjà postés à ce sujet.
19/03/2007, 17h07
ufoinfo

rebonjour

j ai essayé avec audit mais sans succes . pouvez vous me donner un lien svp .

MErci

Il est possible avec Oracle 9i ou 10g de récupérer le code SQL de l'instruction qui déclenche un trigger grâce aux attributs des événements serveur qui sont aussi utilisables par les triggers INSERT, UPDATE et DELETE sur des tables:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
dev> drop table tx;
 
Table dropped.
 
dev>
dev> create table tx (x varchar2(100));
 
Table created.
 
dev>
dev> create or replace trigger ttx
  2  after insert on tx
  3  for each row
  4  declare
  5  sql_text ora_name_list_t;
  6  v_stmt varchar2(2000);
  7  n integer;
  8  begin
  9  n := ora_sql_txt(sql_text);
 10  for i in 1..n loop
 11   v_stmt := v_stmt || sql_text(i);
 12  end loop;
 13  dbms_output.put_line('SQL: ' || v_stmt);
 14  end;
 15  /
 
Trigger created.
 
dev> show errors
No errors.
dev>
dev> set serveroutput on
dev>
dev> insert into tx values ('Ca marche');
SQL: insert into tx values ('Ca marche')
 
1 row created.
 
dev>
dev>
dev> exit

Pour utiliser l'audit, voir le Security Guide.

20/03/2007, 10h27
ufoinfo

merci

merci bcp à vous tous.j utiliserai cette solution au cas ou l'audit ne marche pas.en fait, j'avais mis le audit_trail=true et apres j'ai mis : AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE by tata. tata est l'utilisateur avec lequel je me log. le problème c'est que quand je regarde la table sys.aud$ ,colonne sql_text je ne trouve rien du tout,c null en plus quand je fé un select sur une table en utilisateur ,je reviens vers sys.aud$ je ne vois aucune ligne de plus ui a été ajouté. est ce que vous avez une explication svp.

Cordialement
20/03/2007, 11h47
pifor

Quelle est la version d'Oracle utilisée ?

Citation:

audit_trail=true

ne veut rien dire d'après Oracle® Database Reference 10g Release 2 (10.2)
Quelle est la véritable valeur de ce paramètre ?

Pour avoir le code SQL des instructions auditées, il faut avoir:

Code:

audit_trail=db, extended

encore merci

apparemment oracle n'a pa bien fiscelé tout ça :

1-pour ce qui est de l'audit le db_extended marche à moitié, quand j'entre avec sqlpluus, mon premiere update par exemple est pris je le vois ds aud$, mais le deuxieme non....le reste d'apres est ignoré.

2-pour ce qui est du trigger, voici l'erreur :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SQL> CREATE OR REPLACE TRIGGER t_lata
  2    after INSERT or delete or update ON lata
  3      FOR each row
  4      declare
  5      sql_text ora_name_list_t;
  6      v_stmt varchar2(2000);
  7      n binary_integer;
  8      begin
  9       n := ora_sql_txt(sql_text);
 10     FOR i IN 1..n loop
 11      v_stmt := v_stmt || sql_text(i);
 12     end loop;
 13     -- dbms_output.put_line('SQL: ' || v_stmt);
 14     insert into asap.logging(sysevent,table__name  ,login_user ,event_date , statements )  value
s(ora_sysevent,'lata'  ,ora_login_user ,sysdate , v_stmt )  ;
 15     end;
 16     /
 
Trigger created.
 
SQL> update lata
  2  set description= 'mmm mmm' ;
update lata
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.T_LATA", line 7
ORA-04088: error during execution of trigger 'SYS.T_LATA'

Cordialement

20/03/2007, 15h30
pifor

Vous n'avez toujours pas donné votre version d'Oracle ...: c'est pourtant une des règles d'utilisation de ce forum. Essayez aussi d'utiliser les balises pour le code.

Citation:

ORA-06512: at "SYS.T_LATA", line 7
ORA-04088: error during execution of trigger 'SYS.T_LATA'

Ce n'est peut-être pas la raison de l'erreur mais, en général, vous ne devriez pas utiliser le compte SYS pour créer des objets applicatifs (tables et trigger sur tables applicatives).

desole.

c'est vrai. ma version d'oracle est la 10g release 10.2.0.3.0 . j'ai utilisé le compte sys aprés avoir utilisé un compte normal qui donnait la meme erreur, voici l'erreur :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SQL>  CREATE OR REPLACE TRIGGER t_lata
  2    after INSERT or delete or update ON lata
  3      FOR each row
  4      declare
  5      sql_text ora_name_list_t := ora_name_list_t()  ;
  6      v_stmt varchar2(2000);
  7      n integer := 0;
  8      begin
  9       n := ora_sql_txt(sql_text) ;
 10     FOR i IN 1..n loop
 11      v_stmt := v_stmt || sql_text(i);
 12     end loop;
 13     -- dbms_output.put_line('SQL: ' || v_stmt);
 14     insert into asap.logging(sysevent,table__name  ,login_user ,event_date , statements )  value
s(ora_sysevent,'lata'  ,ora_login_user ,sysdate , v_stmt )  ;
 15     end;
 16     /
 
Trigger created.
 
SQL> update lata 
  2  set description = 'rrrrrrrrrr' ;
update lata
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "ASAP.T_LATA", line 7
ORA-04088: error during execution of trigger 'ASAP.T_LATA'

20/03/2007, 15h59
McM

T'as essayé sans ton insertion dans la table ?
Moi ça marche bien en DBMS.

Sinon : :tagcode:

tjrs meme erreur

j ai enlevé le insert et j ai mis le dbms mais la meme erreur :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SQL>  CREATE OR REPLACE TRIGGER t_lata
  2    after INSERT or delete or update ON lata
  3      FOR each row
  4      declare
  5      sql_text ora_name_list_t := ora_name_list_t()  ;
  6      v_stmt varchar2(2000) := ' ';
  7      n integer := 0;
  8      begin
  9       n := ora_sql_txt(sql_text) ;
 10     FOR i IN 1..n loop
 11      v_stmt := v_stmt || sql_text(i);
 12     end loop;
 13     dbms_output.put_line('SQL: ' || v_stmt);
 14    -- insert into asap.logging(sysevent,table__name  ,login_user ,event_date , statements )  val
ues(ora_sysevent,'lata'  ,ora_login_user ,sysdate , v_stmt )  ;
 15     end;
 16     /
 
Trigger created.
 
SQL> update lata
  2  set description = 'iiiiiiiiiii' ;
update lata
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "ASAP.T_LATA", line 7
ORA-04088: error during execution of trigger 'ASAP.T_LATA'

qu'est ce que vous avez comme conf et je n'ai pas..???
comment je pourrai utilise

Code:

et

??

et pour l'option audit , pourquoi cela ne marche pas correctement??

ça m'enerve ...

merci d'avance

20/03/2007, 16h22
McM

Faut apprendre à débugguer un peu.
Le n° de ligne que l'erreur te donne est celle à partir du DECLARE (il suffit de créer intentionellement une erreur d'affectation pour le vérifier).
Donc ton pb c'est au niveau du FOR
Donc un problème de n qui vaut NULL : ora_sql_text qui ne renvoie rien.

Citation:

Event Attribute Functions
When the database fires a trigger, you can retrieve certain attributes about the event that fired the trigger. You can retrieve each attribute with a function call. Table 9-2 describes the system-defined event attributes.

Note:
To make these attributes available, you must first run the CATPROC.SQL script.
The trigger dictionary object maintains metadata about events that will be published and their corresponding attributes.
In earlier releases, these functions were accessed through the SYS package. We recommend you use these public synonyms whose names begin with ora_.

:tagcode:
20/03/2007, 16h31
pifor

Il semble que ce soit aussi un bug de la 10.1 et la 10.2 où ora_sql_text retourne NULL d'après Metalink alors que ça marche en 9i :aie:
20/03/2007, 16h54
McM

Lol, NAB (Not a Bug) :

Citation:

BUG N° 4932810
See: . Bug 4230721 10.1.0.3.0 RDBMS 10.1.0.3.0 TRIGGERS PRODID-5 PORTID-23 4171597
Abstract: ORA_SQL_TXT FUNCTION RETURNS NULL AFTER 10G UPGRADE . . from bug 4230721
---------------- .
"This issue had surfaced in the dated labels for 9.2.0.7.0 patchset.
After discussions it was concluded that it was the expected behaviour ie.ORA_SQL_TXT should return null when dml triggers are used.
ORA_SQL_TXT is a "System defined event attribute" and is supposed to work only with "System triggers".
This is also what the Documentation says - .

Donc Désolé Pifor, mais faut plus l'utiliser pour du DML :(
20/03/2007, 17h06
ufoinfo

re

alors les gas . qu'est ce que je peux faire moi pour constituer mes logs ?? y'a t il d'autre fonctions ..??
20/03/2007, 17h55
McM

De toute manière par trigger tu ne verras jamais les SELECT.

Il te reste aussi l'outil Oracle STATSPACK.
20/03/2007, 18h03
ufoinfo

c moi

si je peu voir les insert, les update et les delete ca sera bien. les select c po grave. est ce qu'il y'a moyen ...??

L'audit fonctionne mieux avec la 10g que les attributs d'évenements des triggers pour le DML :D (y compris pour les SELECT). Pour avoir une trace de chaque requête, il faut utiliser la clause BY ACCESS de la commande AUDIT. Exemple avec la 10.2.0.1:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
 
SQL> select * from v$version;
 
BANNER                                                                          
----------------------------------------------------------------                
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod                
PL/SQL Release 10.2.0.1.0 - Production                                          
CORE    10.2.0.1.0      Production                                                      
TNS for Linux: Version 10.2.0.1.0 - Production                                  
NLSRTL Version 10.2.0.1.0 - Production                                          
 
SQL> drop user test cascade;
 
User dropped.
 
SQL> --
SQL> show parameter audit_trail;
 
NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
audit_trail                          string      DB_EXTENDED                    
SQL> create user test default tablespace users identified by test007;
 
User created.
 
SQL> alter user test quota unlimited on users;
 
User altered.
 
SQL> grant connect, resource to test;
 
Grant succeeded.
 
SQL> audit select table, update table, insert table, delete table by access;
 
Audit succeeded.
 
SQL> --
SQL> connect test/test007;
Connected.
SQL> create table t(x number, y varchar2(30));
 
Table created.
 
SQL> insert into t(x, y) values (1, 'ok');
 
1 row created.
 
SQL> host sleep 1
 
SQL> update t set y='OK' where x = 1;
 
1 row updated.
 
SQL> host sleep 2
 
SQL> delete t where x=1;
 
1 row deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> connect /
Connected.
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
 
Session altered.
 
SQL> noaudit select table, update table, insert table, delete table;
 
Noaudit succeeded.
 
SQL> select timestamp, sql_text from dba_audit_trail where username='TEST' order by timestamp;
 
TIMESTAMP                                                                       
--------------------                                                            
SQL_TEXT                                                                        
--------------------------------------------------------------------------------
20-MAR-2007 21:28:31                                                            
SELECT USER FROM DUAL                                                           
 
20-MAR-2007 21:28:31                                                            
SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P
RIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (UPPER(USER) LIKE USERID)
 
 
20-MAR-2007 21:28:31                                                            
 
TIMESTAMP                                                                       
--------------------                                                            
SQL_TEXT                                                                        
--------------------------------------------------------------------------------
SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P
RIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (UPPER(USER) LIKE USERID)
 
 
20-MAR-2007 21:28:31                                                            
SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('SQL*Plus') LIKE UPPE
R(PRODUCT)) AND   ((UPPER(USER) LIKE USERID) OR (USERID = 'PUBLIC')) AND   (UPPE
R(ATTRIBUTE) = 'ROLES')                                                         
 
 
TIMESTAMP                                                                       
--------------------                                                            
SQL_TEXT                                                                        
--------------------------------------------------------------------------------
20-MAR-2007 21:28:31                                                            
SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('SQL*Plus') LIKE UPPE
R(PRODUCT)) AND   ((UPPER(USER) LIKE USERID) OR (USERID = 'PUBLIC')) AND   (UPPE
R(ATTRIBUTE) = 'ROLES')                                                         
 
20-MAR-2007 21:28:31                                                            
insert into t(x, y) values (1, 'ok')                                            
 
20-MAR-2007 21:28:31                                                            
 
TIMESTAMP                                                                       
--------------------                                                            
SQL_TEXT                                                                        
--------------------------------------------------------------------------------
SELECT DECODE('A','A','1','2') FROM DUAL                                        
 
20-MAR-2007 21:28:32                                                            
update t set y='OK' where x = 1                                                 
 
20-MAR-2007 21:28:34                                                            
delete t where x=1                                                              
 
 
9 rows selected.