FAQ OracleConsultez toutes les FAQ

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create or replace trigger interdire_toad after logon on database      
declare       
v_sid sys.v_$session.sid%TYPE; 
v_isdba sys.v_$session.username%TYPE; 
v_program sys.v_$session.program%TYPE;      
begin       
execute immediate         
'select distinct sid from sys.v_$mystat' into v_sid;       
execute immediate         
'select program from sys.v_$session where sid = :b1' into v_program using v_sid;  
execute immediate    
'select username   from  sys.v_$session where sid = :b1' into v_isdba using v_sid;       
if upper(v_program) like  'TOAD%' and v_isdba = 'USERNAME'  
then            
RAISE_APPLICATION_ERROR 
(-20001,'Connexion avec '||v_isdba||' sous TOAD interdite,contactez le DBA ',TRUE);    
end  if;      
end; 
/

1
2
3
alter trigger interdire_toad disable ; 
  
alter trigger interdire_toad enable  ;

1
2
3
INSERT INTO product_user_profile (product, userid, attribute, char_value)  
                          VALUES ('SQL*Plus', 'SCOTT', 'CREATE', 'DISABLED'); 
COMMIT ;

1
2
3
4
set linesize 250 
col CHAR_VALUE format a15 
col ATTRIBUTE format a15 
select product, userid, attribute, char_value from product_user_profile ;

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
SQL> conn system 
Entrez le mot de passe : ******** 
Connecté. 
SQL> INSERT INTO product_user_profile (product, userid, attribute, char_value) VALUES 
  2  ('SQL*Plus', 'FORMATION', 'CREATE', 'DISABLED'); 
  
1 ligne créée. 
  
SQL> commit ; 
  
Validation effectuée. 
  
SQL> conn formation/formation 
Connecté. 
SQL> create table dvp ( a varchar2(10)) ; 
SP2-0544: commande non valide : create 
SQL> conn system 
Entrez le mot de passe : ******** 
Connecté. 
SQL> delete from product_user_profile where userid='FORMATION' ;  
  
1 ligne supprimée. 
  
SQL> commit ; 
  
Validation effectuée. 
  
SQL>  conn formation/formation 
Connecté. 
  
SQL> create table dvp ( a varchar2(10)) ; 
  
Table créée.

1
2
TCP.VALIDNODE_CHECKING=yes 
TCP.INVITED_NODES=(xxx.x.x.xxx,xxx.x.x.xxx)

1
2
TCP.VALIDNODE_CHECKING=yes 
TCP.EXCLUDED_NODES=(xxx.x.x.xxx,xxx.x.x.xxx)

1
2
3
4
5
6
7
8
CREATE OR REPLACE FUNCTION restrict_pwd_change (username VARCHAR2, 
password VARCHAR2, 
old_password VARCHAR2) 
RETURN boolean IS  
BEGIN 
raise_application_error(-20009, 'ERROR: Modification du mot de passe impossible'); 
END;  
/

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION restrict_pwd_change;

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION null;

1
2
SQLPLUS /NOLOG  
CONNECT SYS/pwd@db AS SYSDBA

1
2
3
CREATE USER TEST  
  IDENTIFIED BY TEST  
  DEFAULT TABLESPACE TBSTEST;

1
2
3
4
5
SQL> CREATE PROFILE Profil_Test LIMIT PASSWORD_LIFE_TIME 60;  
Profil créé.  
  
SQL> ALTER PROFILE Profil_Test LIMIT FAILED_LOGIN_ATTEMPTS 2;  
Profil modifié.

1
2
SQL> ALTER USER TEST PROFILE Profil_Test;  
Utilisateur modifié.

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> connect test/test2@db  
ERROR:  
ORA-01017: invalid username/password; logon denied  
 
Avertissement : vous n'êtes plus connecté à ORACLE.  
 
SQL> connect test/test3@db  
ERROR:  
ORA-01017: invalid username/password; logon denied  
 
SQL> connect test/test@db  
ERROR:  
ORA-28000: the account is locked

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> SELECT profile, resource_name, limit 
  2    FROM Dba_Profiles 
  3   WHERE resource_type = 'PASSWORD' 
  4   ORDER BY profile; 
  
PROFILE         RESOURCE_NAME             LIMIT 
--------------- ------------------------- --------------- 
DEFAULT         FAILED_LOGIN_ATTEMPTS     UNLIMITED 
DEFAULT         PASSWORD_LIFE_TIME        UNLIMITED 
DEFAULT         PASSWORD_REUSE_MAX        UNLIMITED 
DEFAULT         PASSWORD_LOCK_TIME        UNLIMITED 
DEFAULT         PASSWORD_GRACE_TIME       UNLIMITED 
DEFAULT         PASSWORD_VERIFY_FUNCTION  NULL 
DEFAULT         PASSWORD_REUSE_TIME       UNLIMITED 
PROFIL_TEST     FAILED_LOGIN_ATTEMPTS     2 
PROFIL_TEST     PASSWORD_LIFE_TIME        60 
PROFIL_TEST     PASSWORD_REUSE_MAX        DEFAULT 
PROFIL_TEST     PASSWORD_LOCK_TIME        DEFAULT 
PROFIL_TEST     PASSWORD_GRACE_TIME       DEFAULT 
PROFIL_TEST     PASSWORD_VERIFY_FUNCTION  DEFAULT 
PROFIL_TEST     PASSWORD_REUSE_TIME       DEFAULT 
  
14 ligne(s) sélectionnée(s).