0

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.

caa35
  • 71
  • 2
  • 10
  • You already posted a [question to find users that have not logged on in the last 90 days](http://stackoverflow.com/questions/41829385/how-to-create-an-oracle-stored-procedure-to-lock-user-accounts-not-logged-onto-d), so you already know this part. Please edit the question posting what you already have and clarifying the part you need help for. I guess you only need a way to find roles associated to a set of users. – Aleksej Jan 27 '17 at 15:43

1 Answers1

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.

  • Thank you very much. I'm teaching myself Oracle so I apologize for not connecting the dots. – caa35 Jan 27 '17 at 16:21