Oracle Profile Password verify function

User_CS62G

Hello Team,

I have to develop a customized password verify function. The function should address below requirements :

(i) Minimum password age 2 days.

(ii) Forced password reset on first logon of user.

I have got the minimum password age as below :

PROCEDURE p_CheckMinPwdAge     ( pUserName  IN VARCHAR2)

IS

 nDummy Number;

 nCount Number;  

BEGIN

   SELECT COUNT(1) INTO nCount FROM FROM SYS.USER$ WHERE NAME = pUserName;

    IF nCount > 0 THEN  

      SELECT (SYSDATE - PTIME) INTO nDummy FROM SYS.USER$ WHERE NAME = pUserName;  

    IF nDummy <= 2 THEN

     RAISE_APPLICATION_ERROR( -20007, 'Password should be used for atleast 2 days.' );

    END IF;

END IF; 

END p_CheckMinPwdAge;

I need help to implement the functionality of password reset when user logon for first time and I need to get it in password verify function.

Please help.

Solomon Yakobson

Normally you do it at user creation time: CREATE USER ... PASSWORD EXPIRE. But since you are already using SYS.USER$ you could check SPARE6 (however what is stored in SYS.USER$ isn't documented and can change between releases).

SQL>  create user test1 identified by "password08!";

User created.

SQL> grant create session to test1;

Grant succeeded.

SQL> select spare6 from sys.user$ where name = 'TEST1';

SPARE6
---------
          -- as you can see is is NULL when ID is just created.

SQL> host
Microsoft Windows [Version 10.0.17763.1637]
(c) 2018 Microsoft Corporation. All rights reserved.

I:\>sqlplus [email protected]/password08!

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 26 05:52:57 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

I:\>exit

SQL> select to_char(spare6,'mm/dd/yyyy hh24:mi:ss') from sys.user$ where name = 'TEST1';

TO_CHAR(SPARE6,'MM/
-------------------
01/26/2021 10:52:58 -- time is in UTC


SQL>

SY.

Solomon Yakobson

So if user changed password just recently and forgot it he/she will have to wait 2 days. Very productive :). Anyway, you need to associate password verification function with desired profiles. E.g.:

ALTER PROFILE some_profile LIMIT PASSWORD_VERIFY_FUNCTION p_CheckMinPwdAge;

But keep in mind, your function doesn't enforce any password complexity rules.

SY.

User_CS62G

Thanks for answering. My original question was how to implement password expire on first logon. I want to get this done through an Oracle profile.

Solomon Yakobson

Normally you do it at user creation time: CREATE USER ... PASSWORD EXPIRE. But since you are already using SYS.USER$ you could check SPARE6 (however what is stored in SYS.USER$ isn't documented and can change between releases).

SQL>  create user test1 identified by "password08!";

User created.

SQL> grant create session to test1;

Grant succeeded.

SQL> select spare6 from sys.user$ where name = 'TEST1';

SPARE6
---------
          -- as you can see is is NULL when ID is just created.

SQL> host
Microsoft Windows [Version 10.0.17763.1637]
(c) 2018 Microsoft Corporation. All rights reserved.

I:\>sqlplus [email protected]/password08!

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 26 05:52:57 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

I:\>exit

SQL> select to_char(spare6,'mm/dd/yyyy hh24:mi:ss') from sys.user$ where name = 'TEST1';

TO_CHAR(SPARE6,'MM/
-------------------
01/26/2021 10:52:58 -- time is in UTC


SQL>

SY.