Is it possible to query the history of previously locked/unlocked Oracle database user accounts? I've tried dba_user but there is no option to get history.
Add a comment
|
You can't. The database stores the actual status of an account and the time since it is expired or locked, but no history. The only history it stores is the password history, for enforcing password policies.
answered Apr 2 '16 at 9:01
-
-
@ree Sure. With some custom script, periodically collect and store the required information. – Balazs Papp Apr 2 '16 at 10:04
-
-
@ree This question was about "past history", Balazs his comment would log future changes. Logging future changes seems a new question to me. – Tom V Apr 2 '16 at 12:08
-
1A more fundamental question seems to by "why?" Of what real use could be made of this history information? What business decision is determined by it? – EdStevens Apr 2 '16 at 14:37
Going forward, you could implement auditing of alter user commands which would capture when a user ID is unlocked, but would not capture when it got locked due to number of unsuccessful login attempts. Take a look at http://docs.oracle.com/database/121/DBSEG/audit_config.htm#DBSEG1115 for details. If you really need to know when it was locked, etc. as Balazs suggests, you could invoke SQL every hour/day/week that would insert into a table a copy of the columns of interest from dba_users:
create table my_log (as_of date,
username varchar2(128),
account_status varchar2(32),
lock_date date);
Then every so often, do this:
insert into my_log (select sysdate,
a.username, a.account_status, a.lock_date from dba_users a);
answered May 25 '16 at 20:33
Once the account is open and you want to know when it was locked, you can use user$ view.
select NAME,LTIME
from user$
where NAME='abc';
