oracle password expiration help

April 26, 2011

I’m using Oracle 11g and was really confused on how the password expiration date was set (default values).

To retrieve the default password expiration values set for all new users run this query:

select LIMIT, RESOURCE_NAME
from dba_profiles
where RESOURCE_NAME in ('PASSWORD_GRACE_TIME','PASSWORD_LIFE_TIME','PASSWORD_REUSE_MAX','PASSWORD_REUSE_TIME');

Your output should look something like this:

LIMIT                                    RESOURCE_NAME
---------------------------------------- --------------------------------
180                                      PASSWORD_LIFE_TIME
UNLIMITED                                PASSWORD_REUSE_TIME
UNLIMITED                                PASSWORD_REUSE_MAX
7                                        PASSWORD_GRACE_TIME

To find all users that have a password 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 

To List all users and their expire date + current status:

select username, expiry_date, account_status
from dba_users;