Re: See how long an account is locked
Date: Wed, 12 Aug 2009 10:21:47 -0700 (PDT)
Message-ID: <4eaf61ac-833a-45c6-bc51-abf27bfa251d_at_18g2000yqa.googlegroups.com>
On Aug 12, 11:20 am, lothar.armbrues..._at_t-online.de (Lothar Armbrüster) wrote:
> Hello out there,
>
> I'm experimenting with account locking, password expiry and the like
> on Oracle 10.2.0.4.
> I've created a profile with PASSWORD_LOCK_TIME 1/24. So when a couple of
> logins fail, the account is locked for one hour. This works as expected. In
> the Enterprise Manager I see the account as LOCKED(TIMED).
> Now the question is, is there a way to determine the time remaining that the
> account is locked. DBA_USERS does not have a column showing that. (Or at least
> I'm unable to find it ;-)
>
> Knowing the overall time the account is locked, it would suffice to know when
> the account got locked. But it did not find this information also.
>
> Many thanks in advance,
> Lothar
>
> --
> Lothar Armbrüster | lothar.armbrues..._at_t-online.de
> Hauptstr. 26 |
> 65346 Eltville |
It does, and it's named LOCK_DATE. Properly formatted you'll also see the time and can then report the length of time the lock has been in force:
select username, account_status, lock_date, round((sysdate - lock_date)
*1440, 2) lock_minutes
from dba_users
where account_status like '%TIMED%';
You can also do this:
column limit new_value locklim
select round(limit*1440, 0) limit
from dba_profiles
where profile = 'SMING'
and resource_name = 'PASSWORD_LOCK_TIME'
/
select username, account_status, lock_date, round((sysdate - lock_date) *1440, 2) lock_minutes,
&locklim - round((sysdate - lock_date)*1440, 2) time_left
from dba_users
where account_status like '%TIMED%'
/
David Fitzjarrell Received on Wed Aug 12 2009 - 12:21:47 CDT