Oracle 11g password expire

http://www.dba-oracle.com/t_password_security.htm

To investigate the password stuff use:

This simple script will check if a user password is expiring in the next 120 days.
set pagesize 500
set linesize 200
set trimspool on
column EXPIRE DATE format a20
select username as USER NAME, expiry_date as EXPIRE DATE, account_status
from dba_users
where expiry_date < sysdate+120
and account_status IN ( OPEN, EXPIRED(GRACE) )
order by account_status, expiry_date, username
/


Another scripts:

INVESTIGATE AND CHANGE PROFILE ENTRIES



connect sys/pasw@some_instance    as sysdba

select profile from dba_users where username='SYSTEM';

select LIMIT, RESOURCE_NAME
from dba_profiles
where RESOURCE_NAME in ('PASSWORD_GRACE_TIME','PASSWORD_LIFE_TIME','PASSWORD_REUSE_MAX','PASSWORD_REUSE_TIME')
  and PROFILE=(select profile from dba_users where username='SYSTEM');



ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

In testing this out with a new user - I am using the same password as the one I use for my own id - so the password complexity is not the issue.
If you have an 11g database - can you test it out - create a user and set their password to expire - then try to login as that user.

Please run this and see what the account status is:

set line 120
select username, account_status, expiry_date, lock_date from dba_users
                                              

1:
2:

Works fine on my 11g.
Try this also. Turn on auditing for create session (11g has it on by default I think). If not,  run: audit create session

select * from dba_audit_session where username = 'whoever';
                                              

1:

I ran the scripts that you gave me and it showed the account status as expired.

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA LOCK_DATE
------------------------------ -------------------------------- --------- ---------
PIERCEY                        EXPIRED                          25-MAR-09          
1 row selected.




I tried to sign in twice in Toad and here is my activity in a cmd window

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>C:\oracle\product\10.2.0\db_1\BIN\sqlplus piercey@test

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 26 11:23:53 2009

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

Enter password:
ERROR:
ORA-28001: the password has expired


Changing password for piercey
New password:
Retype new password:
ERROR:
ORA-01017: invalid username/password; logon denied

C:\>C:\oracle\product\10.2.0\db_1\BIN\sqlplus piercey@test

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 26 11:24:59 2009

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

Enter password:
ERROR:
ORA-28001: the password has expired


Changing password for piercey
New password:
Retype new password:
ERROR:
ORA-01017: invalid username/password; logon denied


Password unchanged
Enter user-name:

Then I tried it again from Toad

USERNAMERETURNCODE      EXTENDED_TIMESTAMP
PIERCEY      28001      2009-03-25 3:00:35.359000 PM -03:00
PIERCEY      1017      2009-03-25 3:00:46.218000 PM -03:00
PIERCEY      28001      2009-03-26 11:23:57.406000 AM -03:00
PIERCEY      28001      2009-03-26 11:25:11.531000 AM -03:00
PIERCEY      28001      2009-03-26 11:42:50.031000 AM -03:00
PIERCEY      28001      2009-03-26 11:54:59.109000 AM -03:00
PIERCEY      1017      2009-03-26 11:55:08.062000 AM -03:00

It doesn't seem to log every event.

I figured it out...it only works with version 11 client software.

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Mar 27 09:13:29 2009

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

Enter user-name: piercey
Enter password:
ERROR:
ORA-28001: the password has expired


Changing password for piercey
New password:
Retype new password:
Password changed

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Ah, thanks for the info. I will note that.

It is possible to setup the 11g database with backwards compatible security settings, possibly you remember when you installed, 11g has a new choice in DBCA.

That may be another workaround for future databases, but its just a guess, and won't help you now, though I'm sure there is a way to alter the settings on the fly, but I would have to research.