Thanks for the question, Cornellius.

Asked: January 23, 2017 - 1:41 pm UTC

Last updated: January 24, 2017 - 5:29 pm UTC

Version: Oracle 11g SQL Developer

Viewed 10K+ times! This question is

You Asked

How do I create an Oracle procedure to lock accounts that have not logged into a database for more than 90 days?

and Chris said...

In 12.2 this is easy. Create a password profile setting INACTIVE_ACCOUNT_TIME to the number of days (90) you want to automatically lock the account.

http://docs.oracle.com/database/122/DBSEG/configuring-authentication.htm#GUID-ED98E6DA-A30C-4052-A343-B516CD641737

If you're on 12.1 instead of 12.2 you can find time of the last login from dba_users:

select username, last_login from dba_users
where  nvl(last_login, sysdate - 91) < sysdate - 90;

Unfortunately you're on 11g. So you'll have to do something a bit more hand-crafted. If you have auditing enabled, you can use the audit trail to find those accounts that haven't logged in:

select username from dba_audit_trail
where  action_name = 'LOGON'
group  by username
having max(timestamp) < sysdate - 90

Make sure to outer join to dba_users to include those people who've never logged in!

Or you could write a logon trigger. This would record the login time for each user in your own table. Then use this to identify those over the threshold.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great answer

Cornellius Amey, January 23, 2017 - 6:23 pm UTC

This was helpful.

One last question

Cornellius Amey, January 23, 2017 - 6:30 pm UTC

I did not clearly understand what you meant when you stated, make sure to outer join to dba_users to include those people who've never logged in. Thanks.

January 24, 2017 - 1:56 pm UTC

I'm assuming that by "lock everyone who hasn't logged in for 90 days" you include users that never logged in. These won't appear in your audit trail. So you need to join to dba_users to see these.

Thinking about it, a not exists may be better:

select * from dba_users u
where  not exists (
  select null from dba_audit_trail t
  where  action_name = 'LOGON'
  and    u.username = t.username
  having max(timestamp) > sysdate - 90
)

Perfect.

Cornellius Amey, January 24, 2017 - 4:22 pm UTC

Thank you for your response.

Where clause

Cornellius Amey, January 24, 2017 - 4:35 pm UTC

Does Null represent username? where clause inner query has: where not exists (select null from dba_audit_trail t

January 24, 2017 - 5:29 pm UTC

Null represents "I don't know":

http://docs.oracle.com/database/122/SQLRF/Nulls.htm#SQLRF30037

With a not exists you're not returning anything from the subquery. So there's no need to select a particular column.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Great answer

One last question

Perfect.

Where clause

More to Explore

PL/SQL demos

PL/SQL docs

Oracle procedure to lock accounts not logged in for X days

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Great answer

One last question

Perfect.

Where clause

More to Explore

PL/SQL demos

PL/SQL docs