Inicio > Oracle > Analysis of Oracle password expiry

Analysis of Oracle password expiry

http://blog.contractoracle.com/2009/11/analysis-of-oracle-password-expiry.html

Anuncios
  1. roberto
    17 junio, 2015 en 13:13

    Analysis of Oracle password expiry
    Oracle password expiry is managed by the profile limit PASSWORD_LIFE_TIME, but based on the user$ table attributes ASTATUS and EXPTIME.

    SQL> connect a/a
    Connected.
    SQL> select username, account_status, expiry_date from dba_users where username = ‘A’;

    USERNAME ACCOUNT_STATUS EXPIRY_DATE
    —————————— —————————– —————–
    A OPEN 08 MAY 2010 10:22:52

    SQL> select name, astatus, ptime, exptime from sys.user$ where name = ‘A’;

    NAME ASTATUS PTIME EXPTIME
    —————————— ———- ——————– ——————
    A 0 09 NOV 2009 10:22:52

    # So ASTATUS = O and EXPTIME are not set

    SQL> ALTER PROFILE “DEFAULT” LIMIT PASSWORD_LIFE_TIME 1;

    Profile altered.SQL> ALTER PROFILE “DEFAULT” LIMIT PASSWORD_GRACE_TIME 7;

    Profile altered.

    SQL> connect a/a
    ERROR:
    ORA-28002: the password will expire within 7 days

    Connected.
    SQL> select username, account_status, expiry_date from dba_users where username = ‘A’;

    USERNAME ACCOUNT_STATUS EXPIRY_DATE
    —————————— ————————— —————–
    A EXPIRED(GRACE) 27 NOV 2009 09:17:11

    SQL> select name, astatus, ptime, exptime from sys.user$ where name = ‘A’;

    NAME ASTATUS PTIME EXPTIME
    —————————— ——– ——————– ——————
    A 2 09 NOV 2009 10:22:52 27 NOV 2009 09:17:11

    # So after we login we find the ASTATUS=2 and EXPTIME is set

    SQL> ALTER PROFILE “DEFAULT” LIMIT PASSWORD_LIFE_TIME UNLIMITED;

    Profile altered.

    SQL> connect a/a
    ERROR:
    ORA-28002: the password will expire within 7 days

    Connected.

    # But we see that after we remove the password lifetime, the account is still expiredSQL> select username, account_status, expiry_date from dba_users where username = ‘A’;

    USERNAME ACCOUNT_STATUS EXPIRY_DATE
    —————————— —————————– —————–
    A EXPIRED(GRACE) 27 NOV 2009 09:17:11

    SQL> select name, astatus, ptime, exptime from sys.user$ where name = ‘A’;

    NAME ASTATUS PTIME EXPTIME
    —————————— ——– ——————– ——————
    A 2 09 NOV 2009 10:22:52 27 NOV 2009 09:17:11

    # and ASTATUS is still “2” and exptime is still set. So once expiry is set, changing the profile won’t remove expiry.

    SQL> alter user a identified by a;

    User altered.

    SQL> select username, account_status, expiry_date from dba_users where username = ‘A’;

    USERNAME ACCOUNT_STATUS EXPIRY_DATE
    —————————— ——————————– —————–
    A OPEN

    SQL> select name, astatus, ptime, exptime from sys.user$ where name = ‘A’;

    NAME ASTATUS PTIME EXPTIME
    —————————— ——– ——————– ——————
    A 0 20 NOV 2009 09:19:11 27 NOV 2009 09:17:11

    # When we change the password the PTIME is updated, and the ASTATUS is reset to O, but EXPTIME is not changed.

    # Conclusion – password expiry is controlled by the PASSWORD_LIFE_TIME profile limit, is activated at login, depends on a combination of ASTATUS and EXPTIME, and is reset by a password change. Changing the profile will not change ASTATUS. Changing the password resets ASTATUS, PTIME, but not EXPTIME.

  1. No trackbacks yet.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: