Select User Accounts Not Logged In Database for 180 Days to Revoke Roles

Question

How do I write a SELECT statement that "show all user accounts" that have not logged into the database for more than 180 days? My goal is to revoke the users accounts roles that have not logged into the database for more the 180 days.

In other words, I need to find the roles that are currently granted to current users who have not logged in for more than 180 days.

I am using Oracle 11g.

Thank you.

Answer 1

For Oracle 12.1 and above you could use:

select role
from   dba_roles
where  role not in ( select granted_role
                     from   dba_role_privs
                     where  grantee in ( select username
                                         from   dba_users
                                         where  username not in ('SYS', 'SYSTEM')
                                           and  last_login >= trunc(sysdate)-180
                                       )
                   )
;

In Oracle 11 the innermost subquery will not work, because the column last_login did not exist in the dba_users table. Replace the innermost subquery with

select username 
from   dba_audit_trail
where  action_name = 'LOGON'
  and  username not in ('SYS', 'SYSTEM')
  and  timestamp >= trunc(sysdate) - 180

I tested the 12c version and it produces the correct output on my machine; I didn't test the 11g version, since I don't have an 11g database to test on.