Home » RDBMS Server » Server Administration » sysman audit question
sysman audit question [message #229063] Thu, 05 April 2007 04:26 Go to next message
kiel44
Messages: 10
Registered: September 2006
Junior Member
hi all,
i have one question.
I used this trigger to audit users logon's.


create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);
END;

But every minute this script catch SYSMAN account logon. Is that normal or somthing was wrongly configurated?


USER_ID SESSION_ID HOST LAST_PROGRAM LAST_ACTION LAST_MODULE LOGON_DAY LOGON_TIME LOGOFF_DAY LOGOFF_TIME ELAPSED_MINUTES
SYSMAN 0 SERWER 05.04.07 11:56:30 11:56:30
SYSMAN 0 SERWER 05.04.07 11:57:35 11:57:35
SYSMAN 0 SERWER 05.04.07 11:58:40 11:58:40

Report message to a moderator

Re: sysman audit question [message #229064 is a reply to message #229063] Thu, 05 April 2007 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes it is normal.
It records session data in repository.

One question: why don't you use standard audit ("audit session" statement) instead of logon trigger?

Regards
Michel

Report message to a moderator

Re: sysman audit question [message #229071 is a reply to message #229064] Thu, 05 April 2007 04:40 Go to previous messageGo to next message
kiel44
Messages: 10
Registered: September 2006
Junior Member
Thx for info,
Razz

Because audit is not enabled now and i can't restart server. But i will do it that way with audit session.

Thx for advice.

Report message to a moderator

Re: sysman audit question [message #229079 is a reply to message #229071] Thu, 05 April 2007 05:13 Go to previous messageGo to next message
kiel44
Messages: 10
Registered: September 2006
Junior Member
I have another question.
This script doesn't record information about user sys logons.
Can anybody explain my why?

Report message to a moderator

Re: sysman audit question [message #229080 is a reply to message #229079] Thu, 05 April 2007 05:19 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You don't need to audit SYS logons. It is done by default.

Since you didn't specify your O/S, check either your audit directory $ORACLE_HOME/rdbms/audit (*NIX) or the event viewer (Windoze).

Report message to a moderator

Re: sysman audit question [message #229094 is a reply to message #229080] Thu, 05 April 2007 06:05 Go to previous messageGo to next message
kiel44
Messages: 10
Registered: September 2006
Junior Member
True i know that but easier for me is to check it in table than in file. This script doesn't record user SYS.

I have another question with select * from dba_users; i can see all user and time when they were created. But without enabled audit can i somewhere see who created them?

Report message to a moderator

Re: sysman audit question [message #229096 is a reply to message #229094] Thu, 05 April 2007 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Audit is often useless if someone can drop audit records. This is why SYS is recorded outside the DB.

2/ No, you can't

Regards
Michel

Report message to a moderator

Re: sysman audit question [message #229106 is a reply to message #229094] Thu, 05 April 2007 07:20 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
Hi,
For sys audit you have to enable ( true) below parameter
SYS ( sysdba or sysoper) Auditing.
Set AUDIT_SYS_OPERATIONS = TRUE for every successful statement from SYS ( sysdba or sysoper) audited.
All audit records for SYS are written to the operating system file that contains the audit trail.
On Windows
audit records are written as events to the Event Viewer log file

On Solaris
AUDIT_FILE_DEST parameter is not specified, the default location is $ORACLE_HOME/rdbms/audit. 
AUDIT_FILE_DEST is not supported in Windows 
audit file name (unix) is named after the "session", like ora-20043.aud



Regards
Taj

Report message to a moderator

Re: sysman audit question [message #229110 is a reply to message #229096] Thu, 05 April 2007 07:23 Go to previous messageGo to next message
kiel44
Messages: 10
Registered: September 2006
Junior Member
Thx for info.

But i have another problem.
I found this script on many oracle portals but i have problem because when i use it bdump generate me errors.
There were no info about this error.

ORA-00604: error occured at recursive SQL level 1
ORA-01427: single-row subquered returns more than one row
....

create table
stats$user_log
(
user_id varchar2(30),
session_id number(Cool,
host varchar2(30),
osuser varchar2(30),
type varchar2(10),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(32),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(Cool
)
;


create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);
END;
/

create or replace trigger
logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
update
stats$user_log
set
last_action = (select action from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
update
stats$user_log
set
last_program = (select program from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
update
stats$user_log
set
last_module = (select module from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff day
-- ***************************************************
update
stats$user_log
set
logoff_day = sysdate
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff time
-- ***************************************************
update
stats$user_log
set
logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update os user
-- ***************************************************
update
stats$user_log
set
osuser = (select osuser from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update session type
-- ***************************************************

update
stats$user_log
set
typ = (select type from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;

-- ***************************************************
-- Compute the elapsed minutes
-- ***************************************************
update
stats$user_log
set
elapsed_minutes =
round((logoff_day - logon_day)*1440)
where
sys_context('USERENV','SESSIONID') = session_id;
END;
/

Report message to a moderator

Re: sysman audit question [message #229142 is a reply to message #229110] Thu, 05 April 2007 09:18 Go to previous message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"select <something> from v$session where sys_context('USERENV','SESSIONID') = audsid" can return several rows, above all with SYS user as audsid is always 0 for SYS.

Regards
Michel

Report message to a moderator

Previous Topic: Open Cursors (merged)
Next Topic: upgrade
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 02:07:26 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.