Create Profile User
I profili degli utenti sono letti nel file utlpwdmg.sql sotto la direcotry $ORACLE_HOME/rdbms/admin.
SQL> select * from DBA_PROFILES where profile='DEFAULT';
RESOURCE_NAME RESOURCE LIMIT
--------------------------------------------------------------------------------
FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
PASSWORD_LIFE_TIME PASSWORD DEFAULT
PASSWORD_REUSE_TIME PASSWORD DEFAULT
PASSWORD_REUSE_MAX PASSWORD DEFAULT
PASSWORD_VERIFY_FUNCTION PASSWORD ORA12C_VERIFY_FUNCTION
PASSWORD_LOCK_TIME PASSWORD DEFAULT
PASSWORD_GRACE_TIME PASSWORD DEFAULT
INACTIVE_ACCOUNT_TIME PASSWORD DEFAULT
I valori di default sono visibili dalla seguente tabella
Accediamo ad un pluggable database e creiamo un'utenza:
SQL> alter session set container=orclpdb;
SQL> create user test01 identified by test01;
Se invece vogliamo creare un profilo con verifica della password, possiamo usare una funzione definita dal database oracle che individuamo con la seguente query:
SQL> select * from dba_objects
where object_name like '%VERIFY%'
and object_type = 'FUNCTION'
OBJECT_NAME
--------------------------------------------------------------------------------
ORA12C_VERIFY_FUNCTION
VERIFY_FUNCTION_11G
VERIFY_FUNCTION
ORA12C_STRONG_VERIFY_FUNCTION
ORA12C_STIG_VERIFY_FUNCTION
Creiamo un profilo che impone dei requisiti sulle password degli utenti, sfruttando la funzione oracle ORA12C_VERIFY_FUNCTION.
SQL> CREATE PROFILE TEST_PWD LIMIT PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION;
SQL> select * from DBA_PROFILES where profile='TEST_PWD';
SQL> select resource_name, resource_type, limit
from DBA_PROFILES
where profile='TEST_PWD'
and resource_type='PASSWORD';
--------------------------------------------------------------------------------
FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
PASSWORD_LIFE_TIME PASSWORD DEFAULT
PASSWORD_REUSE_TIME PASSWORD DEFAULT
PASSWORD_REUSE_MAX PASSWORD DEFAULT
PASSWORD_VERIFY_FUNCTION PASSWORD ORA12C_VERIFY_FUNCTION
PASSWORD_LOCK_TIME PASSWORD DEFAULT
PASSWORD_GRACE_TIME PASSWORD DEFAULT
INACTIVE_ACCOUNT_TIME PASSWORD DEFAULT
In rosso la funzione non di default definita per la verifica della pwd.
Di seguito alcune prove:
SQL> create user test02 identified by test02 profile TEST_PWD;
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20000: password length less than 8 bytes
SQL> create user test02 identified by testtesttest profile TEST_PWD;
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20000: password must contain 1 or more digits
SQL> create user test02 identified by testtesttest2 profile TEST_PWD;
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20000: password must contain 1 or more special characters
SQL> create user test02 identified by testtesttest2# profile TEST_PWD;
User created.
Il comando classico per cambiare la password dell'utente testo2 termina in errore perchè la funzione che controlla la password impone l'utilizzo dell'opzione REPLACE.
SQL> alter user test02 identified by qweerty12#;
*
ERROR at line 1:
ORA-28221: REPLACE not specified
SQL> alter user test02 identified by qweerty12# replace testtesttest2#;
User altered.