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
|
create or replace
TRIGGER TRG_LOG_ON
AFTER LOGON ON DATABASE
DECLARE
v_new_passwd VARCHAR2(30):='';
v_username sys.v_$session.USERNAME%TYPE;
v_date_connexion sys.v_$session.LOGON_TIME%TYPE;
Err_username EXCEPTION;
Err_date_cnx EXCEPTION;
Err_new_passwd EXCEPTION;
L_Requete varchar2(250);
job_name varchar2(64);
v_ddl_cmd varchar2(4000);
BEGIN
IF REGEXP_LIKE(USER,'^USER[0-9]{3}$') AND sys_context('USERENV', 'BG_JOB_ID') IS NULL THEN
L_Requete:='SELECT distinct USERNAME, logon_time FROM sys.v_$session
where logon_time=(
select min(logon_time)
from sys.v_$session
where REGEXP_LIKE (USERNAME, ''^USER[0-9]{3}$''))';
Execute immediate L_Requete INTO v_username ,v_date_connexion;
if v_username is null then
raise Err_username;
end if;
if v_date_connexion is null then
raise Err_date_cnx;
end if;
v_new_passwd:= PKG_OTP.CreateRandomPassword();
if v_new_passwd is null then
raise Err_new_passwd;
end if;
L_Requete:='UPDATE UTILISATEUR_ORA SET UTO_PWD_GPL=:a, UTO_DATE_STATUT=:b, UTO_STA=:c WHERE UTO_USR_GPL=:d';
Execute immediate L_Requete using v_new_passwd, v_date_connexion, 'LIBRE', v_username;
COMMIT; --Liberation d'une place dans le pool
dbms_output.put_line('TRG_LOG_ON : Liberation d une place dans le pool (COMMITED)');
v_ddl_cmd:='ALTER USER '||v_username||' IDENTIFIED BY '||v_new_passwd||'';
job_name:='trg_logon_'||to_char(current_timestamp, 'hh24missff');
/* Synchrone */
sys.DBMS_SCHEDULER.CREATE_JOB (
job_name => job_name,
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN PKG_OTP.execute_alter_user(''' || v_ddl_cmd || '''); END;',
start_date => null,
repeat_interval => null,
auto_drop => TRUE,
enabled => TRUE,
comments => 'trg_log_on job');
sys.dbms_scheduler.set_attribute(job_name,'job_priority',1); --priorité maximale = 1
sys.DBMS_SCHEDULER.RUN_JOB (
job_name,
TRUE);-- use_current_session TRUE => synchrone
END IF;
EXCEPTION
WHEN Err_username THEN PKG_COM.Ecrire_trace(PKG_COM.ERR,Substr(Sqlerrm || ': ' || L_Requete, 1, 200),'La lecture de USERNAME '||v_username||' a echoue',' ','TRG_LOG_ON');
WHEN Err_date_cnx THEN PKG_COM.Ecrire_trace(PKG_COM.ERR,Substr(Sqlerrm || ': ' || L_Requete, 1, 200),'La lecture de logon_time '||v_date_connexion ||' a echoue',' ','TRG_LOG_ON');
WHEN Err_new_passwd THEN PKG_COM.Ecrire_trace(PKG_COM.ERR,Substr(Sqlerrm || ': ' || L_Requete, 1, 200),'La regeneration de New passwd '||v_new_passwd ||' a echoue',' ','TRG_LOG_ON');
WHEN OTHERS THEN PKG_COM.Ecrire_trace(PKG_COM.ERR,Substr(' TRG_LOG_ON OTHERS :'|| Sqlerrm , 1, 200),'Erreur OTHERS '||v_username||' ??? new_passwd:*'||v_new_passwd||'*',' ','TRG_LOG_ON');
END TRG_LOG_ON; |
