Historique des connections [Résolu]

[pdelorme]

bonjour,

j'aimerais savoir s'il existe un moyen de logguer les tentatives (réussie ou non) de connections à la base de donnée (ici Oracle 9i).
J'aimerais également déterminer pourquoi le serveur refuse certaines connections.

des idées?

merci.

P.

[scheu]

Fichier log du listener ou AUDIT ou trigger "AFTER lOGON"

[pdelorme]

le fichier
C:\oracle\oracle92\network\log\listener.log
contient toutes les tentatives de connections...

merci pour la piste.

P.

[pdelorme]

question subsidiaire :

est-il possible de récupérer l'historique des commandes effectuée au sein d'une session (même après que celle ci soit terminée) ?

merci

P.

[pdelorme]

pour ce qui est d'utiliser le trigger (bien plus pratique que le log, mais qui ne log que les connections réussi) j'ai trouvé le code suivant sur la page de
http://www.dba-oracle.com/art_builder_sec_audit.htm

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
 
connect sys/manager;
 
SELECT * FROM event_log
 
   CREATE TABLE
   stats$user_log
(
   user_id           varchar2(30),
   session_id           number(8),
   host              varchar2(30),
   last_program      varchar2(48),
   last_action       varchar2(32),
   last_module       varchar2(32),
   logon_day                 date,
   logon_time        varchar2(10),
   logoff_day                date,
   logoff_time       varchar2(10),
   elapsed_minutes       number(8)
)
;
 
   CREATE OR REPLACE TRIGGER
   logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO stats$user_log VALUES(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   NULL,
   NULL,
   NULL,
   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   NULL,
   NULL,
   NULL
);
END;
/
 
   CREATE OR REPLACE TRIGGER
   logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
UPDATE
stats$user_log
SET
last_action = (SELECT action FROM v$session WHERE     
sys_context('USERENV','SESSIONID') = audsid)
WHERE
sys_context('USERENV','SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
UPDATE
stats$user_log
SET
last_program = (SELECT program FROM v$session WHERE     
sys_context('USERENV','SESSIONID') = audsid)
WHERE
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
UPDATE
stats$user_log
SET
last_module = (SELECT module FROM v$session WHERE     
sys_context('USERENV','SESSIONID') = audsid)
WHERE
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff day
-- ***************************************************
UPDATE
   stats$user_log
SET
   logoff_day = sysdate
WHERE
   sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff time
-- ***************************************************
UPDATE
   stats$user_log
SET
   logoff_time = to_char(sysdate, 'hh24:mi:ss')
WHERE
   sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Compute the elapsed minutes
-- ***************************************************
UPDATE
stats$user_log
SET
elapsed_minutes =     
round((logoff_day - logon_day)*1440)
WHERE
sys_context('USERENV','SESSIONID') = session_id;
END;
/

et le connections sont dans :

Code :

SELECT * FROM stats$user_log

voila.

P.

[Pomalaix]

Envoyé par pdelorme

...plus pratique que le log, mais qui ne log que les connections réussi

Ce n'est pas vraiment exact.
Dans le listener.log, chaque tentative de connexion est répertoriée, avec son code d'erreur (dernier élément de la ligne) qui est relatif à la couche SQL*Net uniquement.

Je veux dire par là que le log du listener s'arrête à la porte de la base : si le mot de passe de connexion est invalide, cela ne ressort pas dans le listener.log, et la connexion apparaît comme ayant réussi.

Code :

1
2
3
4
5
 
11-SEPT.-2008 12:25:38 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORA10)(CID=(PROGRAM=C:\oracle\ora102\bin\sqlplus.exe)(HOST=XXX)(USER=yyy)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.10)(PORT=51686)) * establish * ORA10 * 12514
TNS-12514: TNS : le processus d'écoute ne connaît pas actuellement le service indiqué dans le descripteur de connexion
11-SEPT.-2008 12:26:01 * service_register * ora10 * 0
11-SEPT.-2008 12:26:56 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORA10)(CID=(PROGRAM=C:\oracle\ora102\bin\sqlplus.exe)(HOST=XXX)(USER=yyy)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.10)(PORT=51688)) * establish * ORA10 * 0

La première ligne (qui se termine par 12154) indique un échec avec l'erreur 12154.
La dernière ligne (qui se termine par 0) indique simplement qu'il n'y a pas eu d'erreur au niveau SQL*Net. On en déduit généralement que la connexion s'est établie, mais c'est une conclusion hâtive : dans le cas d'espèce, j'avais fourni un mauvais mot de passe, et la conclusion avait été refusée par la base.

[pdelorme]

Envoyé par Pomalaix

La dernière ligne (qui se termine par 0) indique simplement qu'il n'y a pas eu d'erreur au niveau SQL*Net. On en déduit généralement que la connexion s'est établie, mais c'est une conclusion hâtive : dans le cas d'espèce, j'avais fourni un mauvais mot de passe, et la conclusion avait été refusée par la base.

effectivement, merci pour la précision car j'ai cru à tord en regardant listener.log que le client avais réussi sa connexion.

du coup, je reviens à mon problème initial...
comment faire pour identifier les tentative de login infructueuse et obtenir la raison du rejet ?

merci.

[scheu]

Utilise l'audit. Regarde ce lien

[pdelorme]

Envoyé par scheu

Utilise l'audit. Regarde ce lien

merci, le lien explique comment tracer les echec de login...
ca marche nickel...

malheureusement... dans mon cas... cela prouve que la connexion était réussi... mon problème viens donc d'ailleur...
cf mon autre post : "historique des commandes"

merci...
je peux maintenant tout savoir sur les tentatives de connections ;-)...