How to get a list of locked accounts / check that account is locked?

  • I can use following statement to unlock an account:

    ALTER USER username ACCOUNT UNLOCK
    

    But which statement can I use to verify that account is currently locked out?

  • Izzy

    Izzy Correct answer

    7 years ago

    As Dba's answer already shows, account status information is accessible via the dba_users view. Connected with a user having the appropriate grants, this can also be used to identify "inactive users":

    SELECT username, account_status, created, lock_date, expiry_date
      FROM dba_users
     WHERE account_status != 'OPEN';
    

    tells you for each locked/expired account when it was created, which state it is in (locked, expired, expired and locked, expired(grace)), and what time it was locked or expired. Useful for a "cleanup"; but you should consider that some accounts might just be "data holders" which are never connected to for security reasons, but required either by Oracle itself or your application(s). A good example for those is the system account OUTLN. So take care to only "clean out" accounts you know are not used/needed :)

  • Use DBA_USERS View.

    SELECT username, account_status
    FROM dba_users;
    
  • You can query this by specifying the username also:

    select username, account_status
    from dba_users
    where username = 'your_user_name';
    

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM