Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

See how long an account is locked

777 views
Skip to first unread message

Lothar Armbrüster

unread,
Aug 12, 2009, 12:20:59 PM8/12/09
to
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.ar...@t-online.de
Hauptstr. 26 |
65346 Eltville |

Michel Cadot

unread,
Aug 12, 2009, 1:19:43 PM8/12/09
to

"Lothar "Armbr�ster"" <lothar.ar...@t-online.de> a �crit dans le message de news: 87vdktj...@prometeus.nothing.none...

| 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.

dba_users.lock_date

|
| Many thanks in advance,
| Lothar
|
| --

| Lothar Armbr�ster | lothar.ar...@t-online.de
| Hauptstr. 26 |
| 65346 Eltville |


ddf

unread,
Aug 12, 2009, 1:21:47 PM8/12/09
to
On Aug 12, 11:20 am, lothar.armbrues...@t-online.de (Lothar
> Lothar Armbrüster  | lothar.armbrues...@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

Lothar Armbrüster

unread,
Aug 13, 2009, 11:44:19 AM8/13/09
to
ddf <ora...@msn.com> writes:

> On Aug 12, 11:20 am, lothar.armbrues...@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.
>>

[...]


>
> 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

Hello David,
many thanks for your hints. I was looking for LOCK_EXPIRE_TIME or something
like this. My gaze was too narrow to see the LOCK_DATE. ;-)

Regards,

0 new messages