77 - [Oracle 19C] Password verification - example

 

 

• Login as sysdba - sqlplus / as sysdba

SHOW CON_NAME
/*
CON_NAME 
------------------------------
CDB$ROOT
*/


select OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, status from dba_objects
where object_name like '%VERIFY%' AND OBJECT_TYPE='FUNCTION';
/*
OWNER      OBJECT_NAME                    OBJECT_TYPE     CREATED         STATUS 
---------- ------------------------------ --------------- --------------- -------
SYS        ORA12C_VERIFY_FUNCTION         FUNCTION        17-APR-19       VALID  
SYS        VERIFY_FUNCTION_11G            FUNCTION        17-APR-19       VALID  
SYS        VERIFY_FUNCTION                FUNCTION        17-APR-19       VALID  
SYS        ORA12C_STRONG_VERIFY_FUNCTION  FUNCTION        17-APR-19       VALID  
SYS        ORA12C_STIG_VERIFY_FUNCTION    FUNCTION        17-APR-19       VALID      
*/


ALTER SESSION SET CONTAINER=ORCLPDB;
/*
Session altered.
*/


select OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, status from dba_objects
where object_name like '%VERIFY%'
AND OBJECT_TYPE='FUNCTION';
/*
OWNER      OBJECT_NAME                    OBJECT_TYPE     CREATED         STATUS 
---------- ------------------------------ --------------- --------------- -------
SYS        ORA12C_VERIFY_FUNCTION         FUNCTION        17-APR-19       VALID  
SYS        VERIFY_FUNCTION_11G            FUNCTION        17-APR-19       VALID  
SYS        VERIFY_FUNCTION                FUNCTION        17-APR-19       VALID  
SYS        ORA12C_STRONG_VERIFY_FUNCTION  FUNCTION        17-APR-19       VALID  
SYS        ORA12C_STIG_VERIFY_FUNCTION    FUNCTION        17-APR-19       VALID  

*/

 

 

 

• Create profile

CREATE PROFILE TEST_PASS
LIMIT
PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION ;
/*
Profile TEST_PASS created.
*/
col profile for a20
col limit for a25


SELECT * FROM DBA_PROFILES WHERE PROFILE='TEST_PASS';
/*
PROFILE              RESOURCE_NAME                    RESOURCE LIMIT                     COM INH IMP
-------------------- -------------------------------- -------- ------------------------- --- --- ---
TEST_PASS            COMPOSITE_LIMIT                  KERNEL   DEFAULT                   NO  NO  NO 
TEST_PASS            SESSIONS_PER_USER                KERNEL   DEFAULT                   NO  NO  NO 
TEST_PASS            CPU_PER_SESSION                  KERNEL   DEFAULT                   NO  NO  NO 
TEST_PASS            CPU_PER_CALL                     KERNEL   DEFAULT                   NO  NO  NO 
TEST_PASS            LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT                   NO  NO  NO 
TEST_PASS            LOGICAL_READS_PER_CALL           KERNEL   DEFAULT                   NO  NO  NO 
TEST_PASS            IDLE_TIME                        KERNEL   DEFAULT                   NO  NO  NO 
TEST_PASS            CONNECT_TIME                     KERNEL   DEFAULT                   NO  NO  NO 
TEST_PASS            PRIVATE_SGA                      KERNEL   DEFAULT                   NO  NO  NO 
TEST_PASS            FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT                   NO  NO  NO 
TEST_PASS            PASSWORD_LIFE_TIME               PASSWORD DEFAULT                   NO  NO  NO 
TEST_PASS            PASSWORD_REUSE_TIME              PASSWORD DEFAULT                   NO  NO  NO 
TEST_PASS            PASSWORD_REUSE_MAX               PASSWORD DEFAULT                   NO  NO  NO 
TEST_PASS            PASSWORD_VERIFY_FUNCTION         PASSWORD ORA12C_VERIFY_FUNCTION    NO  NO  NO 
TEST_PASS            PASSWORD_LOCK_TIME               PASSWORD DEFAULT                   NO  NO  NO 
TEST_PASS            PASSWORD_GRACE_TIME              PASSWORD DEFAULT                   NO  NO  NO 
TEST_PASS            INACTIVE_ACCOUNT_TIME            PASSWORD DEFAULT                   NO  NO  NO 

17 rows selected. 
*/

 

 

 

• Test

create user test100 identified by welcome  profile TEST_PASS ;
/*

Error starting at line : 92 in command -
create user test100 identified by welcome  profile TEST_PASS
Error report -
ORA-28003: password verification for the specified password failed
ORA-20000: password length less than 8 bytes
28003. 00000 -  "password verification for the specified password failed"
*Cause:    The new password did not meet the necessary complexity
           specifications and the password_verify_function failed
*Action:   Enter a different password. Contact the DBA to know the rules for
           choosing the new password

*/


create user test100 identified by welcome1234  profile TEST_PASS ;
/*

Error starting at line : 108 in command -
create user test100 identified by welcome1234  profile TEST_PASS
Error report -
ORA-28003: password verification for the specified password failed
ORA-20000: password must contain 1 or more special characters
28003. 00000 -  "password verification for the specified password failed"
*Cause:    The new password did not meet the necessary complexity
           specifications and the password_verify_function failed
*Action:   Enter a different password. Contact the DBA to know the rules for
           choosing the new password
*/


create user test100 identified by welcome1234#  profile TEST_PASS ;
/*
User TEST100 created.
*/


grant create session to test100;
/*

Grant succeeded.
*/



SQL> conn test100/welcome1234#@orclpdb
Connected.


SQL> show user
USER is "TEST100"


SQL> show con_name

CON_NAME
------------------------------
ORCLPDB



SQL> alter user test100 identified by welcome678#;

Cause : ORA-28221 error occurs when an user tries to reset his own password 
without specifying the REPLACE keyword, provided the user does not have ALTER USER privilege 
and the user is having a profile with password verify function.



SQL> alter user test100 identified by welcome678# replace welcome1234#;

User altered.