user log

[Roncayenne]

We have just gone to 9ias. Finance dept wants to know who was on system Saturday. Is there a user log file or a system table where this info is retained.

Thanks in advance for your help.....

Ron

 

[dbtoo2001]

Is auditing turned on?
Otherwise, look in the listener log. This may help to determine who logged in remotely.

 

[SantaMufasa]

Ron,

DBToo's suggestion is the best after-the-fact method of determining who logged in at a particular time. But if you wish to prepare for future login tracking, you can build a trigger that audits all logins to your database. If you want such a trigger to put in place for your future needs, Let us know and I'll post the code.

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:37 (13Jul04) UTC (aka "GMT" and "Zulu"), 18:37 (12Jul04) Mountain Time)

 

[jaggiebunnet]

There is no need for a trigger. Turning Auditing on will capture all information possible about who is logging in.

 

[THoey]

True, auditing will capture all information at a cost of resuources. But, if all you want to do is track log in's and out's, a simple trigger and small table will accomplish the same at much less expense...

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Roncayenne]

Thanks for the info. I will check to see if we have auditing on and see what it takes to check it. Mufasa, if you don't mine, could you post the code for the trigger? I think it would also be helpful.

Thanks for help! And for the forum!! Saves alot of searching!

Ron

 

[jaggiebunnet]

Thoey - do you have evidence to suggest that a trigger written would be quicker than Oracle's own mechanism for capturing the exact same information?

 

[THoey]

No, I was going by the fact that Oracle's minimum auditing collects more then just the login / logout information asked for. If you are wanting to track everything that gets done in the database, then yes, Oracle's auditing would probably do a better job than anything I could throw together. But for just tracking logins / logouts, I thought that I had heard that it is a slight savings over using even the minimum of Auditting.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[SantaMufasa]

Jaggie,

(Preliminary Note: Since I did not want sample output, below, from my login-audit trigger to wrap around the screen, this post is wider than normal and you will occasionally need to scroll horizontally to read the post text.)

Terry (THoey) is actually correct. Oracle's auditing functionality carries with it a certain amount of overhead and at minimum, audits more than just login and logout. As I recall, Oracle did not invision its auditing to be "On" all the time (due to the overhead). But if someone wants to have it "on" continuously, then that is certainly their prerogative.

An advantage of a login trigger is that gathering data from your user-defined login-audit table seems much simpler than manipulating the results of turning Oracle auditing on. So the key here is that Oracle does more than "...capturing the exact same information...". It would, however, be fascinating to do a true "apples-to-apples" test of the CPU cycles for each alternative. (My money would be on the trigger, however.)

Now for Ron's request: a copy of my login trigger. First, I must mention that I originally audited/recorded each distinct login in my table (i.e., each login produced an "INSERT" into the logon-audit table). For our more "active" databases, our login table soon became one of our largest tables. For our purposes, we decided that we we could live with the earliest and the most recent logins. Therefore, as an accommodation to saving space, we now just record the most recent login (i.e., "UPDATE" the row in the log in table for a user combination that has logged in before, and "INSERT" a row for brand-new users.) It ended up saving us many dozens of megabytes over time.

There are a few components that I have in my login auditing. By no means is my method the only way. I'm certain that other Tek-Tipsters can propose alternatives.

The following sections present my "login-audit" components:

Section 1 -- Create the audit table, "LOGONS" in the SYS schema:

set echo off
set feedback on
CREATE TABLE SYS.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));

Section 2 -- My user-defined function to gather session information. (There are certainly other built-in functions available; I just like to use my own in this case.):

create or replace function sys.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
		for i in (Select	osuser, machine, user into
					w_osuser, w_machine, w_user
					from v$session
					where audsid = sess_id
					order by logon_time desc) loop
			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 loop;
	end;
/*	Above function accesses session information */
/

Section 3 -- Actual Logon trigger code:
[code]
CREATE OR REPLACE TRIGGER sys.logontrigger
AFTER LOGON ON DATABASE
DECLARE
	cnt_hold	number;
BEGIN
	select count(*) into cnt_hold
		from sys.logons
		where OS_USER_NAME = get_session_info(userenv('sessionid'),'OSUSER')
		  and MACHINE_TERMINAL = get_session_info(userenv('sessionid'),'MACHINE')
		  and ORACLE_USER_NAME = get_session_info(userenv('sessionid'),'USER');
	if cnt_hold = 0 and get_session_info(userenv('sessionid'),'USER') is not null then
		INSERT INTO sys.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_session_info(userenv('sessionid'),'OSUSER')
		,get_session_info(userenv('sessionid'),'MACHINE')
		,get_session_info(userenv('sessionid'),'USER')
		,userenv('sessionid'));
	else
		update sys.logons set
			 	MAX_LOGIN_TIME = sysdate
				,login_cnt = login_cnt+1
				,LATEST_SESSION_ID = userenv('sessionid')
			where OS_USER_NAME = get_session_info(userenv('sessionid'),'OSUSER')
			  and MACHINE_TERMINAL = get_session_info(userenv('sessionid'),'MACHINE')
			  and ORACLE_USER_NAME = get_session_info(userenv('sessionid'),'USER');
	end if;
END;
/*	Above trigger audits most recent database login for an O/S-Oracle user combination */
/

Section 4 -- SQL*Plus script to display the contents of the SYS.LOGONS table:

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 sys.logons
order by max_login_time
/

Section 5 -- Sample Output from "LogonReport.sql":

                                                                    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
-------------------------- -------------------------- ---------- --------- -------------------- ------------------------- ----------------
2003-Nov-04 (Tue) 01:50:10 2003-Nov-04 (Tue) 01:59:23          4       454 Dhunt                MOBILEPC2709              SYSTEM
2003-Oct-30 (Thu) 16:04:55 2003-Nov-11 (Tue) 02:39:15         34       646 Dhunt                MOBILEPC2709              TEST2
2004-Feb-27 (Fri) 09:43:15 2004-Feb-27 (Fri) 09:43:15          1      1265 Dhunt                MOBILEPC2709              YADA3
2004-Mar-04 (Thu) 13:26:06 2004-Mar-04 (Thu) 13:26:06          1      1333 dhunt                MOBILEPC2709              DHUNT
2004-Apr-27 (Tue) 09:06:43 2004-Apr-27 (Tue) 09:06:43          1      1838 Dhunt                MOBILEPC2709              DONTDROP
2004-Jun-02 (Wed) 12:38:05 2004-Jun-02 (Wed) 12:48:11          6      2124 Dhunt                MOBILEPC2709              RAY
2003-Oct-15 (Wed) 22:58:01 2004-Jul-08 (Thu) 12:55:44        114      2429 Dhunt                MOBILEPC2709              TEST
2003-Oct-14 (Tue) 18:11:10 2004-Jul-10 (Sat) 03:39:44        854      2436 Dhunt                MOBILEPC2709              DHUNT

8 rows selected.
******************************************************************************************************************************************

Let me know, Ron, if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:39 (13Jul04) UTC (aka "GMT" and "Zulu"), 10:39 (13Jul04) Mountain Time)

 

[jaggiebunnet]

Or you could use Oracle's built in auditing for strictly only logging in and logging out.

Audit connect;

And then have a look at the following table:

DBA_AUDIT_SESSION

Much less coding than anything above. Do not believe when someone tells you that Oracle doesn't do it better until they give you facts and figures. "As I recall" and "I thought I heard" are not measurable facts.

 

[Roncayenne]

Thanks Mufasa! I appreciate you sharing your scripts. From looking at them, it takes in all the fields that I am looking for. I am going to see about modifying it so that you can enter a start and ending date and get all the detail records for that period.

This will be a BIG help. Thanks again!!
BTW, you are in GREAT powder skiing country!

Ron

 

[Roncayenne]

Jaggie,
The audit connect only writes records when a person logs in and out? Probably would have to clean up every week or so depending on the amount of traffic. Have to look into this approach also.
Thanks!
Ron

 

[carp]

jaggiebunnet -

Or you could use Oracle's built in auditing for strictly only logging in and logging out.
Audit connect;

Don't be so sure!
AUDIT CONNECT audits all actions that are inherited with the CONNECT role (and since you are a stickler for facts and figures, try this: Oracle 9i (9.0.1) SQL Reference, page 11-53).
A quick check shows the following privileges granted to this role:

select privilege from dba_sys_privs 
where grantee = 'CONNECT'
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK

So using any of these eight privileges will result in an audit record. Of course, if you modify the default configuration of the CONNECT role to only include the CREATE SESSION privilege, then this changes the default picture considerably!
But by turning auditing on, you also incur the overhead of auditing for every action performed on the database (since Oracle must check each action to see if it is auditable or not). In contrast, a login trigger fires once (upon login) and that's it.
In this case, although the code you write for your trigger may not be as fast as Oracle's internal mechanisms, the overhead differences would appear to offset any inefficiencies in your custom code.
If all that is desired is a list of who logged in on Saturday, turning Auditing on is pulling out a sledge hammer when all you need is a fly-swatter.

Roncayenne -
Whether you go with auditing or a login trigger, one of your new housekeeping tasks will probably include cleaning up the tables periodically. If there is a specific window that you need to answer for (e.g., who logged in over the past month?), then you can easily create a periodic cleanup job that deletes records that are beyond the window's threshold. But you don't want your table to get larger than you really need it to be. In a busy database, these tables can grow very large very quickly!

Elbert, CO
1536 MDT

 

[jaggiebunnet]

I stand corrected. Audit Connect should be AUDIT SESSION;

There is little point in creating and maintaining code when Oracle already does it better. The sledgehammer in this case is the code, the flyswatter is the built in functionality.

 

[carp]

Jaggiebunnet -
I guess we'll have to agree to disagree on this one. If you honestly believe that having Oracle increase the overhead for every statement's parse phase is an efficient way to keep track of who has logged into the database, then have at it.
I will continue with the belief that it is far more efficient to fire a trigger once upon login and be done with it (especially since the code is now stored as a compiled executable - the difference in efficiencies between Oracle's kernel and my custom code is now decreased substantially!).
As an aside, in the five years I worked for Oracle education and support, our general rules were always consistent -
(1) Don't turn auditing on unless you have a darned good reason for it.
(2) If your requirements are very narrowly defined and can be handled by a trigger (e.g., monitoring DML on a relatively few tables in your database), then use the trigger(s).
Both of these guidelines were dictated by the overhead that auditing imposes. Contrary to popular belief, Oracle is NOT just looking at the commands that you specify in your auditing options - it has to check ALL of them.

On the other hand, I have no argument your general thesis (which I would summarize as "Oracle's internal code will in all probability be more efficient than anything you write"). Which is why I would also recommend using built-in mechanisms such as predefined constraints instead of triggers that call procedures. But for a specific purpose (rather than a general rule), I would look at the tradeoffs involved. In this specific case ("Who logged into the database on Saturday?"), I believe the degradation to overall system performance imposed by auditing far outweighs any relative inefficiency caused by a single trigger firing upon login.

But to each his/her own.

 

[SantaMufasa]

Carp,

As usual, very eloquently stated and "Amen."

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:48 (14Jul04) UTC (aka "GMT" and "Zulu"), 08:48 (14Jul04) Mountain Time)