Oracle report for inactive users? 2

[cougartrace]

We have to have a report for all users who have not logged into the database in 45 days? We don't have anything like LDAP (or whatever its called nowadays) so our security is all in the database.

Anyone have any hints to help out with this?

 

[SantaMufasa]

Cougar,

One solution is to create a database-login trigger that inserts login information into a login-audit table:

create table audit_login
    (user_id           varchar2(30)
    ,session_id        number(8)
    ,host              varchar2(30)
    ,logon_date        date
    );

Table created.

create or replace trigger logon_audit_trigger
    AFTER LOGON ON DATABASE
BEGIN
    insert into audit_login values
        (user
        ,sys_context('USERENV','SESSIONID')
        ,sys_context('USERENV','HOST')
        ,sysdate
        );
END;
/

Trigger created.

conn summit/********

connected.

conn dhunt/********

connected.

select * from audit_login;

                                Host
USER_ID              SESSION_ID Name                      LOGON_DATE
-------------------- ---------- ------------------------- ----------
SUMMIT                    36018 LDS\WM-DAVEHUNT           09-FEB-12
DHUNT                     36019 LDS\WM-DAVEHUNT           09-FEB-12

2 rows selected.

To do your report, you could then access the audit_login table with code such as this:

select user_id
      ,to_char(late_dt,'dd-Mon-yyyy hh24:mi:ss') last_login
  from (select user_id,max(logon_date) late_dt
          from audit_login
         group by user_id
        -- having sysdate-max(logon_date) > 45
       ) latest
 union
(select username,'No login' from dba_users
  minus
 select user_id,'No login' from audit_login)
order by 2,1
/


USER_ID              LAST_LOGIN
-------------------- -----------------------
SUMMIT               09-Feb-2012 17:11:33
DHUNT                09-Feb-2012 17:11:42
...
SYS                  No login
SYSMAN               No login
SYSTEM               No login
TSMSYS               No login
WMSYS                No login
XDB                  No login

In the above SELECT, you can remove the "--" comments to limit the output to those who have not logged in for > 45 days and those who have not logged in.


Let us know if this is close to what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[KenCunningham]

Dave, I'm sure the above will be useful to many more than the OP (including myself), so please have a star from me!

The internet - allowing those who don't know what they're talking about to have their say.

 

[SantaMufasa]

Thank you, Ken. It's always nice to know that colleagues value contributions.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.