Test Last Time User Logged In 1

[Michael42]

Hello,

In an Oracle9i R2 database on a Sun Solaris 8 system I have some user accounts a suspect are not beeing using. Simply disabling them and waiting for the users to yell is not an option. :-( I do not have auditing enabled for this database and cannot at this time.

How can I test the last time a user logged into the database or enable a simple mechanism that will detect\track it from this point forward (without enableing AUDITING)?

Thanks,

Michael42

 

[SantaMufasa]

Michael,

Yes, I highly recommend tracking user logins. We track logins on every Oracle instance via the following set of code:

sqlplus /nolog
connect / as sysdba
grant all on v_$session to system;
connect system/<system password>@<tns alias>
REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. The author
REM makes no warranty regarding this script's fitness for any 
REM specific industrial application nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM 
REM Please contact the author via email (dave@dasages.com) when 
REM you have comments, suggestions, and/or difficulties with this
REM script.
REM
REM [Please keep the above disclaimer and the embedded electronic 
REM  documentation with this script.]
REM **************************************************************
set echo off
set feedback on
create table logons_backup as select * from logons;
create or replace view logon_sessions as select * from v$session;
create or replace function     get_session_info
                (sess_id    in number
                , info_type in varchar2) return varchar2
        is
                w_osuser        varchar2(100);
                w_machine       varchar2(100);
                w_user          varchar2(100);
        begin
                Select  osuser, machine, user into
                                w_osuser, w_machine, w_user
                        from logon_sessions
                        where audsid = sess_id;
                if      upper(info_type) = 'OSUSER' then
                        return w_osuser;
                elsif   upper(info_type) = 'MACHINE' then
                        return w_machine;
                elsif   upper(info_type) = 'USER' then
                        return w_user;
                end if;
        end;
/*      Above function accesses session information */
/
create public synonym get_sess_info for get_session_info
/
CREATE TABLE system.LOGONS
       (MIN_LOGIN_TIME DATE,
        MAX_LOGIN_TIME DATE,
        LOGIN_CNT NUMBER,
        OS_USER_NAME VARCHAR2(100),
        MACHINE_TERMINAL VARCHAR2(100),
        ORACLE_USER_NAME VARCHAR2(30),
        LATEST_SESSION_ID NUMBER,
         CONSTRAINT LOGONS_OSUSER_MACHINE_OCUSER UNIQUE (OS_USER_NAME,
	MACHINE_TERMINAL, ORACLE_USER_NAME));
CREATE OR REPLACE TRIGGER system.logontrigger
AFTER LOGON ON DATABASE
DECLARE
	cnt_hold	number;
BEGIN
	select count(*) into cnt_hold
		from system.logons
		where OS_USER_NAME = get_sess_info(userenv('sessionid'),'OSUSER')
		  and MACHINE_TERMINAL = get_sess_info(userenv('sessionid'),'MACHINE')
		  and ORACLE_USER_NAME = get_sess_info(userenv('sessionid'),'USER');
	if cnt_hold = 0 and get_sess_info(userenv('sessionid'),'USER') is not null then
		INSERT INTO system.logons
			(MIN_LOGIN_TIME         
			,MAX_LOGIN_TIME  
			,Login_cnt       
			,OS_USER_NAME           
			,MACHINE_TERMINAL       
			,ORACLE_USER_NAME       
			,LATEST_SESSION_ID
			)
		VALUES
		(sysdate
		,sysdate
		,1
		,get_sess_info(userenv('sessionid'),'OSUSER')
		,get_sess_info(userenv('sessionid'),'MACHINE')
		,get_sess_info(userenv('sessionid'),'USER')
		,userenv('sessionid'));
	else
		update system.logons set
			 	MAX_LOGIN_TIME = sysdate
				,login_cnt = login_cnt+1
				,LATEST_SESSION_ID = userenv('sessionid')
			where OS_USER_NAME = get_sess_info(userenv('sessionid'),'OSUSER')
			  and MACHINE_TERMINAL = get_sess_info(userenv('sessionid'),'MACHINE')
			  and ORACLE_USER_NAME = get_sess_info(userenv('sessionid'),'USER');
	end if;
END;
/*	Above trigger audits all database logins except 'SYS'*/
/
col object_name format a30
select owner, object_name, object_type, status
from dba_objects
where object_name like 'LOGON%'
   or object_name like 'GET_SESS%'
/

Then, once some non-SYS user connects to the database, you can use the following couple of reports. This first report, which I call "LogonReport.sql", shows, for every non-SYS database connection:
Earliest Login Date/Time,
Latest Login Date/Time,
Number of Logins (since implementing this code),
OS User Login Name,
Login Machine/Terminal Name,
Oracle User Name:

REM ***************************************************************
REM Script produces formatted report of logins, sorted by date/time.
REM ***************************************************************
col a heading "Earliest Login Date/Time" format a26
col a2 heading "Latest Login Date/Time" format a26
col b heading "Latest|Oracle|Session|ID" format 99999999
col c heading "OS User Login Name" format a20
col d heading "Login Machine/|Terminal Name" format a25
col e heading "Oracle User Name" format a20
col f heading "Number|of|Logins" format 9,999,999
set linesize 200
select     to_char(min_login_time,'YYYY-Mon-DD (Dy) hh24:mi:ss') a
   ,to_char(max_login_time,'YYYY-Mon-DD (Dy) hh24:mi:ss') a2
   ,login_cnt f
   ,Latest_Session_ID b
   ,os_user_name c
   ,machine_terminal d
   ,Oracle_user_name e
from logons
order by max_login_time
/

                                                                    Latest
                                                          Number    Oracle
                                                              of   Session                      Login Machine/
Earliest Login Date/Time   Latest Login Date/Time         Logins        ID OS User Login Name   Terminal Name             Oracle User Name
-------------------------- -------------------------- ---------- --------- -------------------- ------------------------- ----------------
2004-Oct-28 (Thu) 01:01:00 2006-May-01 (Mon) 10:08:40         11      8317 DOMAIN-\login        DOMAIN-\computername      YADA4
2006-Apr-10 (Mon) 22:12:52 2006-Jun-24 (Sat) 09:48:58         21      8765 DOMAIN-\login        DOMAIN-\computername      GEN
2003-Oct-15 (Wed) 22:58:01 2006-Jul-19 (Wed) 14:55:06        743      9018 DOMAIN-\login        DOMAIN-\computername      TEST
2003-Oct-14 (Tue) 18:11:10 2006-Jul-21 (Fri) 13:55:33      4,097      9103 DOMAIN-\login        DOMAIN-\computername      DHUNT
2003-Nov-04 (Tue) 01:50:10 2006-Jul-21 (Fri) 13:59:46         20      9106 DOMAIN-\login        DOMAIN-\computername      SYSTEM
******************************************************************************************************************************************

Another helpful script is this one that lists all Oracle (non-DEFAULT) users for a database/instance (regardless of whether or not they have ever logged it, along with:
Disk consumption,
Latest Login Date/Time,
Number of Logins (since implementing this code),
OS User Login Name,
Login Machine/Terminal Name:

set linesize 200
col a heading "Oracle User Name" format a20
col b heading "consumption" format 999,999,999,999
col c heading "Latest Login Date/Time" format a26
col d heading "Number|of|Logins" format 9,999,999
col e heading "OS User Login Name" format a20
col f heading "Login Machine/|Terminal Name" format a25
select mx.username a
      ,mx.consumption b
      ,to_char(max_login_time,'YYYY-Mon-DD (Dy) hh24:mi:ss') c
      ,login_cnt d
      ,os_user_name e
      ,machine_terminal f
from logons d
    ,(select uc.username, consumption, user_max
        from (select u.username
                    ,nvl(c.consumption,0)consumption
                from dba_users u
                    ,(select sum(bytes) consumption, owner
                        from dba_segments
                       group by owner) c
               where u.username = c.owner(+)
             ) uc
            ,(select max(max_login_time) user_max
                    ,u.username
                from logons l,dba_users u
               where u.username = l.oracle_user_name(+)
               group by u.username
             ) ul
       where uc.username = ul.username) mx
 where mx.username = d.oracle_user_name (+)
   and mx.user_max = d.max_login_time (+)
   and mx.username not in ('SYS','SYSTEM','DONTDROP','DBSNMP','OUTLN')
 order by nvl(user_max,to_date('01-JAN-1900','dd-MON-yyyy')) desc
         ,consumption;
                                                                     Number
                                                                         of                      Login Machine/
Oracle User Name          consumption Latest Login Date/Time         Logins OS User Login Name   Terminal Name
-------------------- ---------------- -------------------------- ---------- -------------------- --------------------
DHUNT                      13,320,192 2006-Jul-21 (Fri) 14:13:35      4,099 TENFOLD\dhunt        TENFOLD\TFMOBILE2709
TEST                       26,542,080 2006-Jul-19 (Wed) 14:55:06        743 TENFOLD\dhunt        TENFOLD\TFMOBILE2709
GEN                        24,838,144 2006-Jun-24 (Sat) 09:48:58         21 TENFOLD\dhunt        TENFOLD\TFMOBILE2709
YADA4                               0 2006-May-01 (Mon) 10:08:40         11 TENFOLD\dhunt        TENFOLD\TFMOBILE2709
TEST1                               0 
FRED                                0 
DOH                                 0

Notice that the last three Oracle users have never logged into the database.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Michael42]

Santa,

Wow. That is GREAT material.

Thanks for taking the time to post.

-Michael42