This is an issue that keeps tripping me up in the development lab.

I create a quick database, and then after some time I go back to try to log in, and get:

ERROR:
ORA-28002: the password will expire within 7 days

And then I spend a while trying to figure out how to disable the password expiry component of the profile.

Here then, for my benefit as much as anyone else, is my quick guide to checking the user profile, and then disabling security components that cause headaches in the development lab.

NOTE:  Exercise caution before disabling password lifespan, reuse or complexity requirements on production systems.  Hackers are increasingly targeting database systems for data theft.

First, here is a quick script show_profile.sql that works on Oracle 12c, 11g and 10g to show accounts and the profiles they use:

USERNAME   DBA STATUS  PROFILE PWD_VERIFY PLT FLA RUM PGT PWD_LOK
---------- --- ------- ------- ---------- --- --- --- --- -------
ANONYMOUS  NO  EXP&LOK DEFAULT            180  10   7   1
DBSNMP     NO  EXP&LOK DEFAULT            180  10   7   1
DIP        NO  EXP&LOK DEFAULT            180  10   7   1
OLAPSYS    NO  EXP&LOK DEFAULT            180  10   7   1
ORACLE_OCM NO  EXP&LOK DEFAULT            180  10   7   1
ORDDATA    NO  EXP&LOK DEFAULT            180  10   7   1
ORDPLUGINS NO  EXP&LOK DEFAULT            180  10   7   1
ORDSYS     NO  EXP&LOK DEFAULT            180  10   7   1
OUTLN      NO  EXP&LOK DEFAULT            180  10   7   1
SYS        YES OPEN    DEFAULT            180  10   7   1
SYSBACKUP  NO  EXP&LOK DEFAULT            180  10   7   1
SYSDG      NO  EXP&LOK DEFAULT            180  10   7   1
SYSKM      NO  EXP&LOK DEFAULT            180  10   7   1
SYSTEM     YES EXP(GR) DEFAULT            180  10   7   1
WMSYS      NO  EXP&LOK DEFAULT            180  10   7   1
XDB        NO  EXP&LOK DEFAULT            180  10   7   1
XS$NULL    NO  EXP&LOK DEFAULT            180  10   7   1

The above output shows us all users are using the DEFAULT profile, and that there is no password verify function defined.

The PLT column shows the password life time – currently 180 days.
The FLA column shows the Failed Login Attempts allowed before the account is locked.
The RUM column shows the Password Reuse Max.
The PGT column shows the Password Grace Time.
The PWD_LOK column shows the Password Lock Time.

To disable the profile entries that cause passwords to expire, use the following:

SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION NULL;
Profile altered.

SQL>alter profile default limit PASSWORD_REUSE_TIME UNLIMITED;
Profile altered.

SQL>alter profile default limit PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.

Alternatively you might create a new profile with settings just for those accounts you want to manage separately.

With a 12c multi-tenant database, the profile must use the c## naming convention or you will get an ORA-65140 error:

SQL> CREATE PROFILE c##mysecprof 
LIMIT PASSWORD_LIFE_TIME UNLIMITED 
PASSWORD_GRACE_TIME UNLIMITED 
PASSWORD_REUSE_TIME UNLIMITED 
PASSWORD_REUSE_MAX UNLIMITED 
FAILED_LOGIN_ATTEMPTS UNLIMITED 
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED;

Profile created.

SQL> alter user dbsnmp profile c##mysecprof;

User altered.